您现在的位置是:主页 > 数据库技术 > 数据库技术

ROLLUP,CUBE,GROUPING SETS,grouping_id()函数有什么用

IDCBT2022-01-04服务器技术人已围观

简介小编给大家分享一下ROLLUP,CUBE,GROUPING SETS,grouping_id()函数有什么用,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们

小编给大家分享一下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'),

标签:

很赞哦! ()

本栏推荐