ROLLUP,CUBE,GROUPINGSETS,grouping_id()函数有什么用
小编给大家分享一下ROLLUP,CUBE,GROUPING SETS,grouping_id()函数有什么用，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
创新互联主要业务有网站营销策划、成都网站制作、网站设计、微信公众号开发、微信小程序定制开发、H5高端网站建设、程序开发等业务。一次合作终身朋友，是我们奉行的宗旨；我们不仅仅把客户当客户，还把客户视为我们的合作伙伴，在开展业务的过程中，公司还积累了丰富的行业经验、成都全网营销推广资源和合作伙伴关系资源，并逐渐建立起规范的客户服务和保障体系。
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.
以上是“ROLLUP,CUBE,GROUPING SETS,grouping_id()函数有什么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注创新互联行业资讯频道！
分享名称：ROLLUP,CUBE,GROUPINGSETS,grouping_id()函数有什么用
分享路径：http://ybzwz.com/article/pspsde.html
