具有多列的oracle汇总功能 [英] oracle rollup function with multiple columns

问题描述

我有一个简单的查询:

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 

但是我需要这样的输出:

But I need an output like:

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

谢谢.

推荐答案

我发现使用GROUPING SET子句指定我需要的确切集合相当容易:

I find it rather easier to specify the exact sets I need with the GROUPING SET clause:

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

CUBE和ROLLUP对于自动生成大量聚合级别(例如，维度层次结构中的每个级别)非常有用，如果您想从大型级别中删除一小部分级别，则可能会使用GROUPING ID CUBE生成的集合，但是GROUPING SET是专门用于指定特定聚合级别的.

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.

这篇关于具有多列的oracle汇总功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！