SQL 具有多列的 oracle 汇总功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20068015/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle rollup function with multiple columns
提问by ajmalmhd04
I've a simple query:
我有一个简单的查询：
WITH data(val1, val2, val3) AS
( SELECT 'a' ,'a-details' ,'1' FROM DUAL
UNION ALL
SELECT 'b' ,'b-details' ,'2' FROM DUAL
UNION ALL
SELECT 'c' ,'c-details' ,'3' FROM DUAL
)
SELECT NVL(val1,'Total Result'),
val2,
SUM(val3) tot
from data
group by rollup(val1, val2);
I get an output like:
我得到如下输出：
VAL1 VAL2 TOT
-------------------------------- -------------------------------- ----------
a a-details 1
a 1
b b-details 2
b 2
c c-details 3
c 3
Total Result 6
But I need an output like:
但我需要这样的输出：
VAL1 VAL2 TOT
-------------------------------- -------------------------------- ----------
a a-details 1
b b-details 2
c c-details 3
Total Result 6
Thanks in advance.
提前致谢。
回答by Przemyslaw Kruglej
GROUPING_ID Expression
GROUPING_ID 表达式
You can use the GROUPING_IDexpression to filter which levels of subtotals you need:
您可以使用该GROUPING_ID表达式来过滤您需要的小计级别：
WITH data AS
( SELECT 'a' AS val1 ,'a-details' AS val2 , '1' AS val3 FROM DUAL
UNION ALL
SELECT 'b' ,'b-details' ,'2' FROM DUAL
UNION ALL
SELECT 'c' ,'c-details' ,'3' FROM DUAL
)
SELECT NVL(val1,'Total Result'),
val2,
SUM(val3) tot
from data
group by ROLLUP(val1, val2)
HAVING GROUPING_ID(val1, val2) IN (0, 3);
Output:
输出：
NVL(VAL1,'TOTALRESULT') VAL2 TOT ----------------------- --------- ---------- a a-details 1 b b-details 2 c c-details 3 Total Result 6
GROUPING_IDreturns 0 for rows with no subtotals, 1 for the first level and so on, we can take a look at values returned by it:
GROUPING_ID没有小计的行返回0，第一级返回1，依此类推，我们可以看看它返回的值：
WITH data AS
( SELECT 'a' AS val1 ,'a-details' AS val2 , '1' AS val3 FROM DUAL
UNION ALL
SELECT 'b' ,'b-details' ,'2' FROM DUAL
UNION ALL
SELECT 'c' ,'c-details' ,'3' FROM DUAL
)
SELECT NVL(val1,'Total Result'),
val2,
SUM(val3) tot,
GROUPING_ID(val1, val2) AS grp_id
from data
group by ROLLUP(val1, val2);
NVL(VAL1,'TOTALRESULT') VAL2 TOT GRP_ID ----------------------- --------- ---------- ---------- a a-details 1 0 a 1 1 b b-details 2 0 b 2 1 c c-details 3 0 c 3 1 Total Result 6 3
More about Rollup and related topics: Tim Hall about Rollup and Cube
有关 Rollup 和相关主题的更多信息：Tim Hall 关于 Rollup 和 Cube
(Edit)
(编辑)
The GROUPING Function
GROUPING 函数
Regarding comment. You can use the GROUPINGfunction:
关于评论。您可以使用该GROUPING功能：
GROUPING - accepts a single column as a parameter and returns "1" if the column contains a null value generated as part of a subtotal by a
ROLLUPorCUBEoperation or "0" for any other value, including stored null values.
GROUPING - 接受单个列作为参数，如果该列包含由
ROLLUPorCUBE操作生成的作为小计的一部分的空值，则返回“1” 或任何其他值（包括存储的空值）的“0”。
Example of returned values:
返回值示例：
WITH data AS
( SELECT 'a' AS val1 ,'a-details' AS val2 , '1' AS val3 FROM DUAL
UNION ALL
SELECT 'b' ,'b-details' ,'2' FROM DUAL
UNION ALL
SELECT 'c' ,'c-details' ,'3' FROM DUAL
)
SELECT NVL(val1,'Total Result'),
val2,
SUM(val3) tot,
grouping(val1),
grouping(val2)
from data
group by ROLLUP(val1, val2);
Output:
输出：
NVL(VAL1,'TOTALRESULT') VAL2 TOT GROUPING(VAL1) GROUPING(VAL2) ----------------------- --------- ---------- -------------- -------------- a a-details 1 0 0 a 1 0 1 b b-details 2 0 0 b 2 0 1 c c-details 3 0 0 c 3 0 1 Total Result 6 1 1
So your query should look like:
所以你的查询应该是这样的：
WITH data AS
( SELECT 'a' AS val1 ,'a-details' AS val2 , '1' AS val3 FROM DUAL
UNION ALL
SELECT 'b' ,'b-details' ,'2' FROM DUAL
UNION ALL
SELECT 'c' ,'c-details' ,'3' FROM DUAL
)
SELECT NVL(val1,'Total Result'),
val2,
SUM(val3) tot
from data
group by ROLLUP(val1, val2)
HAVING GROUPING(val1) = 1
OR (GROUPING(val1) + GROUPING(val2) = 0);
Output:
输出：
NVL(VAL1,'TOTALRESULT') VAL2 TOT ----------------------- --------- ---------- a a-details 1 b b-details 2 c c-details 3 Total Result 6
Idea of using the GROUPINGfunction from AskTom, here.
使用GROUPING来自 AskTom的功能的想法，这里。
回答by David Aldridge
I find it rather easier to specify the exact sets I need with the GROUPING SET clause:
我发现使用 GROUPING SET 子句指定我需要的确切集合更容易：
WITH data(val1, val2, val3) AS
( SELECT 'a' ,'a-details' ,'1' FROM DUAL
UNION ALL
SELECT 'b' ,'b-details' ,'2' FROM DUAL
UNION ALL
SELECT 'c' ,'c-details' ,'3' FROM DUAL
)
SELECT NVL(val1,'Total Result'),
val2,
SUM(val3) tot
from data
group by grouping sets ((val1, val2),());
I suspect that it is more efficient, as it directly specifies the levels to calculate.
我怀疑它更有效，因为它直接指定要计算的级别。
http://sqlfiddle.com/#!4/8301d/3
http://sqlfiddle.com/#!4/8301d/3
CUBE and ROLLUP are handy for generating large numbers of aggregation levels automatically (eg. every level in a dimensional hierarchy), and there might be a case for using GROUPING ID if you wanted to eliminate a small subset of levels from a large CUBE-generated set, but GROUPING SET is precisely designed for specifying particular aggregation levels.
CUBE 和 ROLLUP 可方便地自动生成大量聚合级别（例如，维度层次结构中的每个级别），如果您想从大型 CUBE 生成的级别中消除一小部分级别，则可能需要使用 GROUPING ID set，但 GROUPING SET 是专门为指定特定聚合级别而设计的。
