ROLLUP,CUBE,GROUPING SETS,grouping_id()函数

1.ROLLUP

group by rollup(1,2,3), 可以理解为从右到左以一次少一列的方式依次进行group by。

例如: group by rollup(1,2,3) 则以group by(1,2,3) -> group by(1,2) -> group by(1) -> group by null(最终汇总)的顺序进行分组

相当于:

Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,null,null,sum(E) from test;

2.CUBE

group by cube(1,2,3), 需要对每一列的排列组合进行group by

例如: group by cube(1,2,3) 则以 group by(1,2,3) -> (1,2) -> (1,3) -> (2,3) -> (2) -> (3) -> group by null(最终汇总)的顺序进行分组

相当于:

Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,C,sum(E) from test group by A,C
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,B,C,sum(E) from test group by B,C
union all
Select null,B,null,sum(E) from test group by B
union all
Select null,null,C,sum(E) from test group by C
union all
Select null,null,null,sum(E) from test;

3.GROUPING SETS

自定义分组方案

group by GROUPING SETS(1,2,3)  = (1),(2),(3) 分别group by

group by grouping sets((1,2),3) = (1,2),(3) 分别group by

4.组合应用

group by A,rollup(A,B)

将对所有group by 后面的集合进行笛卡尔积

因此顺序为: (A,(A,B)),(A,A),(A,NULL) = (A,B),(A),(A)

Select A,B,sum(E) from test1 group by A, rollup(A,B);

Select A,B,sum(E) from test1 group by A,B
Union all
Select A,null,sum(E) from test1 group by A
Union all
Select A,null,sum(E) from test1 group by A;

5.GROUPING_ID()

即GROUPING函数用于区分分组后的普通行和聚合行。如果是聚合行，则返回1，反之，则是0。

GROUPING_ID是GROUPING的增强版，与GROUPING只能带一个表达式不同，它能带多个表达式。

SELECT TO_CHAR (log_date, 'YYYY') year,
         TO_CHAR (log_date, 'Q') quarter,
         TO_CHAR (log_date, 'MM') month,
         employee_id,
         MIN (old_salary),
         MIN (new_salary),
         GROUPING_ID (TO_CHAR (log_date, 'YYYY'),
                      TO_CHAR (log_date, 'Q'),
                      TO_CHAR (log_date, 'MM'))
            gid
    FROM plch_emp_log
GROUP BY ROLLUP (TO_CHAR (log_date, 'YYYY'),
                 TO_CHAR (log_date, 'Q'),
                 TO_CHAR (log_date, 'MM')),
         employee_id;

YEAR     QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY)        GID
-------- -- ---- ----------- --------------- --------------- ----------
2010     1  01           100            1000            1800          0
2010     1               100            1000            1800          1
2010     2  04           100            1800            1900          0
2010     2               100            1800            1900          1
2010     3  09           100            1900            1500          0
2010     3               100            1900            1500          1
2010                     100            1000            1500          3
2011     1  01           100            1500            2500          0
2011     1               100            1500            2500          1
2011     2  04           100            2500            2200          0
2011     2               100            2500            2200          1

YEAR     QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY)        GID
-------- -- ---- ----------- --------------- --------------- ----------
2011                     100            1500            2200          3
                         100            1000            1500          7
2010     1  01           200            1000            1600          0
2010     1  03           200            1600            2500          0
2010     1               200            1000            1600          1
2010     2  05           200            2500            2300          0
2010     2               200            2500            2300          1
2010     3  09           200            2300            3000          0
2010     3               200            2300            3000          1
2010                     200            1000            1600          3
2011     1  02           200            3000            2000          0

YEAR     QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY)        GID
-------- -- ---- ----------- --------------- --------------- ----------
2011     1               200            3000            2000          1
2011     3  07           200            2000            2800          0
2011     3               200            2000            2800          1
2011                     200            2000            2000          3
                         200            1000            1600          7
2010     2  04           300            1000            2000          0
2010     2  05           300            2000            3000          0
2010     2               300            1000            2000          1
2010     4  10           300            3000            2700          0
2010     4               300            3000            2700          1
2010                     300            1000            2000          3

YEAR     QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY)        GID
-------- -- ---- ----------- --------------- --------------- ----------
2011     1  02           300            2700            2500          0
2011     1               300            2700            2500          1
2011     3  09           300            2500            2900          0
2011     3               300            2500            2900          1
2011                     300            2500            2500          3
                         300            1000            2000          7

39 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15412087/viewspace-2150106/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15412087/viewspace-2150106/