高级分组 ROOLUP、CUBE、GROUPING SETS

# ROOLUP函数

多次group by需要union时考虑使用

官方说明：

* https://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8634

* 
https://docs.oracle.com/database/121/DWHSG/preface.htm#DWHSG8052

逐一递减

ROLLUP(A,B,C)

<==>

A,B,C

A,B

A

NULL

```SQL
SELECT DEPTNO, JOB, SUM(SAL) SAL 
FROM EMP 
GROUP BY ROLLUP(DEPTNO), JOB;

SELECT DEPTNO, JOB, SUM(SAL) SAL 
FROM EMP 
GROUP BY DEPTNO, ROLLUP(JOB);

SELECT DEPTNO, JOB, SUM(SAL) SAL 
FROM EMP 
GROUP BY ROLLUP(DEPTNO, JOB);
```

# CUBE函数

交叉所有组合

CUBE(A,B,C)

<==>

A、B、C

A、B

A、C

A

B、C

B

C

NULL

```SQL
SELECT DEPTNO,job, SUM(SAL) SAL 
FROM EMP 
GROUP BY CUBE(DEPTNO,job);

SELECT DEPTNO,job, SUM(SAL) SAL 
FROM EMP 
GROUP BY CUBE(DEPTNO),job;
```

# GROUPING SETS函数

（A,B,C）

<==>

A

B

C

(A,B,ROLLUP(C,D))

<==>

A

B

ROLLUP(C,D)

```SQL
SELECT DEPTNO,job,mgr, SUM(SAL) SAL 
FROM EMP 
GROUP BY GROUPING SETS(DEPTNO,mgr,NULL),job;
```

```SQL
--GROUPING
SELECT CASE WHEN GROUPING(deptno) = '1' THEN '所有部门' ELSE to_char(DEPTNO) END AS dempno,job, SUM(SAL) SAL 
FROM EMP 
GROUP BY CUBE(DEPTNO),job

--GROUPING_ID
例如：grouping(A) = 0 ; grouping(B) = 1;

则：grouping_id(A,B) = (01) = 1;

grouping_id(B,A) = (10) = 2;

--GROUP_ID
GROUP_ID函数可用于消除GROUP BY子句返回的重复记录。
GROUP_ID()不接受任何参数。
如果某个特定的分组重复出现n次，那么GROUP_ID()返回从0到n-1之间的一个整数
```