具有多列的oracle汇总函数

Question

我有一个简单的查询：

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);

我得到的输出像：

VAL1                             VAL2                                    TOT
-------------------------------- -------------------------------- ----------
a                                a-details                                 1 
a                                                                          1 
b                                b-details                                 2 
b                                                                          2 
c                                c-details                                 3 
c                                                                          3 
Total Result                                                               6 

但我需要这样的输出：

VAL1                             VAL2                                    TOT
-------------------------------- -------------------------------- ----------
a                                a-details                                 1 
b                                b-details                                 2 
c                                c-details                                 3 
Total Result                                                               6 

提前谢谢。

Answer 1

我发现用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),());

我怀疑它更有效，因为它直接指定了要计算的级别。

http://sqlfiddle.com/#！4/8301d/3

CUBE和ROLLUP可以方便地自动生成大量聚合级别（例如，维度层次结构中的每个级别），如果您想从大型CUBE生成的集合中删除一小部分级别，则可能需要使用GROUPING ID，但GROUPING set正是为指定特定聚合级别而设计的。

Answer 2

GROUPING_ID表达式

您可以使用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);

输出：

NVL（VAL1，"OTALRESULT"）VAL2总计-----------------------------------------a详细信息1b-细节2c-细节3总结果6

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，'OTALRESULT'）VAL2总计GRP_ID-----------------------------------------a详细信息1 0a 1 1b b-详细信息2 0b 2 1c c-细节3 0c3 1总结果6 3

检查SQLFiddle

更多关于Rollup及相关主题：Tim Hall关于Rollup和Cube

（编辑）

GROUPING函数

关于评论。您可以使用GROUPING功能：

GROUPING-接受单列作为参数，如果该列包含一个null值，该值由ROLLUP或CUBE运算或"0"表示任何其他值，包括存储的空值。

返回值示例：

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);

输出：

NVL（VAL1，'OTALRESULT'）VAL2总分组（VAL1）分组（VAL2）----------------------------------------------------------------------------------a详细信息1 0 0a 1 0 1b b详细信息2 0 0b 2 0 1c c-details 3 0 0c 3 0 1总结果6 1 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
from data
group by ROLLUP(val1, val2)
HAVING GROUPING(val1) = 1
   OR (GROUPING(val1) + GROUPING(val2) = 0);

输出：

NVL（VAL1，"OTALRESULT"）VAL2总计-----------------------------------------a详细信息1b-细节2c-细节3总结果6

使用AskTom的GROUPING函数的想法，在这里。