【Oracle】增强group by子句相关函数
1：GROUPING()函数
该函数只接受一个参数，参数为数据表的一列。如果该列为空返回1，否则返回0。它仅能与 GROUP BY,ROLLUP,CUBE,GROUPING SETS 一起使用。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
## 与rollup配合应用 SCOTT@ORA11GR2> SELECT deptno, job, GROUPING(deptno), GROUPING(job), SUM(sal) FROM emp GROUP BY ROLLUP(deptno, job); DEPTNO JOB GROUPING(DEPTNO) GROUPING(JOB) SUM(SAL) ------- --------- ---------------- ------------- ---------- 10 CLERK 0 0 1300 10 MANAGER 0 0 2450 10 PRESIDENT 0 0 5000 10 0 1 8750 20 CLERK 0 0 1900 20 ANALYST 0 0 6000 20 MANAGER 0 0 2975 20 0 1 10875 30 CLERK 0 0 950 30 MANAGER 0 0 2850 30 SALESMAN 0 0 5600 30 0 1 9400 1 1 29025 13 rows selected. SCOTT@ORA11GR2> |
2：GROUPING SETS，按照分组字段分别分组
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SCOTT@ORA11GR2> SELECT deptno, job, SUM(sal) FROM emp GROUP BY GROUPING SETS(deptno, job); DEPTNO JOB SUM(SAL) ------- --------- ---------- 30 9400 20 10875 10 8750 CLERK 4150 SALESMAN 5600 PRESIDENT 5000 MANAGER 8275 ANALYST 6000 8 rows selected. SCOTT@ORA11GR2> ## 以上SQL等价于 SELECT deptno, NULL AS job, SUM(sal) AS sal FROM emp GROUP BY deptno UNION ALL SELECT NULL, job, SUM(sal) FROM emp GROUP BY job ORDER BY 1 DESC; |
3：GROUPING_ID()函数
如果把列看作向量，每列返回的值为非空，则返回0，为空则返回1，从左至右组成一个二进制的位序列。
这个函数返回的结果不太好描述，作者就不罗嗦了，真是没什么用。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
## 与rollup配合使用 SCOTT@ORA11GR2> SELECT deptno, job, GROUPING(deptno) AS g_deptno, GROUPING(job) AS g_job, grouping_id(deptno, job) AS g_dj, grouping_id(job, deptno) AS g_jd, SUM(sal) AS sal FROM emp GROUP BY ROLLUP(deptno, job); DEPTNO JOB G_DEPTNO G_JOB G_DJ G_JD SAL ------- --------- ---------- ------- ------- ------- ------- 10 CLERK 0 0 0 0 1300 10 MANAGER 0 0 0 0 2450 10 PRESIDENT 0 0 0 0 5000 10 0 1 1 2 8750 20 CLERK 0 0 0 0 1900 20 ANALYST 0 0 0 0 6000 20 MANAGER 0 0 0 0 2975 20 0 1 1 2 10875 30 CLERK 0 0 0 0 950 30 MANAGER 0 0 0 0 2850 30 SALESMAN 0 0 0 0 5600 30 0 1 1 2 9400 1 1 3 3 29025 13 rows selected. SCOTT@ORA11GR2> ## 与cube配合使用 SCOTT@ORA11GR2> SELECT deptno, job, GROUPING(deptno) AS g_deptno, GROUPING(job) AS g_job, grouping_id(deptno, job) AS g_dj, grouping_id(job, deptno) AS g_jd, SUM(sal) AS sal FROM emp GROUP BY CUBE(deptno, job); DEPTNO JOB G_DEPTNO G_JOB G_DJ G_JD SAL ------- --------- ---------- ------- ------- ------- ------- 1 1 3 3 29025 CLERK 1 0 2 1 4150 ANALYST 1 0 2 1 6000 MANAGER 1 0 2 1 8275 SALESMAN 1 0 2 1 5600 PRESIDENT 1 0 2 1 5000 10 0 1 1 2 8750 10 CLERK 0 0 0 0 1300 10 MANAGER 0 0 0 0 2450 10 PRESIDENT 0 0 0 0 5000 20 0 1 1 2 10875 20 CLERK 0 0 0 0 1900 20 ANALYST 0 0 0 0 6000 20 MANAGER 0 0 0 0 2975 30 0 1 1 2 9400 30 CLERK 0 0 0 0 950 30 MANAGER 0 0 0 0 2850 30 SALESMAN 0 0 0 0 5600 18 rows selected. SCOTT@ORA11GR2> |
4：GROUP_ID()函数
此函数没有参数，它返回 GROUP BY,ROLLUP,CUBE,GROUPING SETS 统计的重复记录。
若某个特定的分组重复出现n次，那么该函数返回从0到n-1的n个整数，这些整数可以看作是对重复记录进行连续编号。
比如，统计后相同的记录有4行，当出现第一行时，记录不重复，group_id()值为0， 当第一次出现与第一行重复的记录时，group_id()值为1，当第二次重复出现，值为2，第三次重复出现，值为3。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SCOTT@ORA11GR2> SELECT deptno, job, SUM(sal), group_id() FROM emp GROUP BY deptno, ROLLUP(deptno, job); DEPTNO JOB SUM(SAL) GROUP_ID() ------- --------- ---------- ---------- 10 CLERK 1300 0 10 MANAGER 2450 0 10 PRESIDENT 5000 0 20 CLERK 1900 0 20 ANALYST 6000 0 20 MANAGER 2975 0 30 CLERK 950 0 30 MANAGER 2850 0 30 SALESMAN 5600 0 10 8750 0 20 10875 0 30 9400 0 10 8750 1 20 10875 1 30 9400 1 15 rows selected. SCOTT@ORA11GR2> |
5：小结
是不是没啥用？