业务上可能SQL用到GROUPING SETS

grouping sets

"grouping sets"执行方式在group by后面有多列的时候,grouping sets带来的性能提升非常明显:
假设现在让你统计一下每个国家的平均工资和每个部门的平均工资，怎么办？我们可以使用 UNION ALL。此乃俩个结果集的并集而成>>

SELECT country, null department, round(avg(salary), 2) FROM employee GROUP BY country
UNION ALL
SELECT null country, department, round(avg(salary), 2) FROM employee GROUP BY department;

采用grouping sets则如下:
SELECT country, department, round(avg(salary), 2) FROM employee GROUP BY GROUPING SETS (country, department);

实际上,等价模式可以用后面几个例子来参考>>

GROUP BY GROUPING SETS (A,B,C) 等价与 GROUP BY A
UNION ALL
GROUP BY B
UNION ALL
GROUP BY C

总结:也就是多个分组并集的结果合成到一个查询结果中

我们还可以使用括号。

GROUP BY GROUPING SETS ((A,B,C)) 等价与 GROUP BY A,B,C

GROUP BY GROUPING SETS (A,(B,C)) 等价与 GROUP BY A
UNION ALL
GROUP BY B,C

我们还可以在一个GROUP BY语句中多次使用GROUPING SETS，如下：

GROUP BY GROUPING SETS (A) 等价于 GROUP BY A,B,C
,GROUPING SETS (B)
,GROUPING SETS ©

GROUP BY GROUPING SETS (A) 等价于 GROUP BY A,B,C
,GROUPING SETS ((B,C))

GROUP BY GROUPING SETS (A) 等价于 GROUP BY A,B
,GROUPING SETS (B,C) UNION ALL
GROUP BY A,C

我们还可以混合使用，如下：

GROUP BY A 等价于 GROUP BY A
,B ,B
,GROUPING SETS ((B,C)) ,C

GROUP BY A 等价于 GROUP BY A,B,C
,B UNION ALL
,GROUPING SETS (B,C) GROUP BY A,B

GROUP BY A 等价于 GROUP BY A,B,C
,B UNION ALL
,C GROUP BY A,B,C

GROUPING

即GROUPING函数用于区分分组后的普通行和聚合行。如果是聚合行，则返回1，反之，则是0;
举例说,有分组部门A/B/C,产值1/2/3 的亿元对应数据,查询grouping(部门),count(*),sum(产值)那么结果如下:

 0 	A 	1    1
 0 	B 	1    2
 0 	C 	1    3
 1 	空  3    6

GROUPING_ID

GROUPING_ID(expr1, expr2, expr3,….)的值其实是对应GROUPING(expr1),GROUPING(expr2),GROUPING(expr3)…值的串接;
实际上可用举例来参考学习一下:
GROUPING_ID实现行列转换的案例。

复制代码
with t as
( select grouping_id(deptno,job)gi_dj,deptno,job,count(*)cnt
from emp group by cube(deptno,job)),
t1 as
( select decode(gi_dj,0,deptno,1,deptno,99) deptno,decode(gi_dj,1,cnt,3,cnt)sub_total,
decode(job,‘CLERK’,cnt) c1,decode(job,‘ANALYST’,cnt)c2,decode(job,‘MANAGER’,cnt)c3,
decode(job,‘SALESMAN’,cnt)c4,decode(job,‘PRESIDENT’,cnt)c5
from t)
select deptno,max(sub_total) sub_total,max(c1)clerk,max(c2)analyst,
max(c3)manager,max(c4)salesman,max(c5)president
from t1 group by deptno order by deptno;
复制代码
最后生成的结果如下：

DEPTNO  SUB_TOTAL      CLERK     ANALYST    MANAGER     SALESMAN  PRESIDENT(第七行)

---

    10          3           1                     1                       1
    20          5           2          2          1
    30          6           1                     1            4
    99         14           4          2          3            4          1

其中，99代表合计，sub_total代表小计。这种统计类的需求在实际生产中还是应用蛮广蛮实在的毕竟有些业务需求确实如此。