TSQL 分组集(Grouping Sets)

简介:

分组集(Grouping Sets)是多个分组的并集,用于在一个查询中,按照不同的分组列对集合进行聚合运算,等价于对单个分组使用“union all”,计算多个结果集的并集。使用分组集的聚合查询,返回的select 子句相同,由于select子句只能引用分组列,因此,在单个分组中缺失的分组列,TSQL返回NULL值。

TSQL使用 group by 子句分组,有4种不同的语法:

  • group by a,b
  • group by rollup(a,b)
  • group by cube(a,b)
  • group by grouping sets((),(a),(a,b),rollup(a,b),cube(a,b))

一,分组集

1,单个分组

以集合的视角来看 “group by a,b” 子句,等价于 “group by grouping sets (a,b)”,(a,b) 是单个分组,是集合相乘的结果:(a)*(b)=(a,b) ;

2,预定义的分组集(grouping sets)

  • rollup(a,b) :预定义的分组集是(),(a),(a,b);
  • cube(a,b) :预定义的的分组集是(),(a),(b),(a,b);

3,使用grouping sets 自定义分组集

单个分组的集合是分组集, 分组集 grouping sets((a),(a,b)) :表示两个分组 (a,b),(a) 的并集,查询的结果等价于:

group by (a,b)
union all
group by(a)

4,分组集运算

分组集运算法则:

  • () :表示空集,整个集合作为一个分组;任何集合和空集相乘,结果是:(a)*()=(a);
  • 分组集相乘:两两组合,例如,{(a),(b)}*{(c),(d)}={(a,c),(a,d),(b,c),(b,d)}
  • 集合相乘时,不会去重:例如,{(a),(b)}*{(),(a)}={(a),(a,a),(b),(b,a)}
  • (a,a)等价于集合(a):例如,{(a),(b)}*{(),(a)}={(a),(a),(b),(b,a)}
  • group by是分组集相乘:例如, group by grouping sets((a),(b)),c 等价于 group by grouping sets((a,c),(b,c))
  • grouping sets是分组集求并集,不会去重:例如,grouping sets((a),(a)) 等价于 grouping sets(a) union all grouping sets(a)

4.1,解析:grouping sets(rollup(a,b),b) 等价于 group by cube(a,b)

解析过程:rollup(a,b)定义的分组集是(),(a),(a,b),并上分组(b),就是:((),(a),(a,b),(b)),等价于cube(a,b)。

4.2,解析 group by grouping sets((a),(b)), rollup(a)

解析过程:grouping sets((a),(b)),定义两个分组集合((a),(b)),rollup(a)定义两个分组集合:((),(a)),

两个分组集进行相乘:{(a),(b)}*{(),(a)}={(a),(a),(b),(b,a)},集合相乘时,不会去重;

二,示例

1,创建示例数据

复制代码
create table dbo.Inventory
(
Item int not null,
Color varchar(10) not null,
Quantity int not null,
Store int not null
)
复制代码

2,将整个集合作为一个分组,grouping sets 是()

复制代码
select null as Item, null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory

--等价于
select  null as Item, null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory
group by grouping sets(())
复制代码

3,grouping sets是(a)

复制代码
select Item,null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Item 
order by Item

--等价于
select Item,null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets( (Item))
order by Item
复制代码

4,grouping sets 是(a,b)

复制代码
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from Inventory 
group by  Item,Color 
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from Inventory 
group by grouping sets( (Item,Color)) 
order by Item,Color
复制代码

5,分组集是:rollup(a,b),或 grouping sets是((),(a),(a,b))

复制代码
--rollup(a,b)的grouping sets是(),(a),(a,b)
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by ROLLUP(Item,Color) 
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets((),(Item),(Item,Color))
order by Item,Color
复制代码

6,分组集是:cube(a,b),或grouping sets是(),(a),(b),(a,b)

复制代码
--cube(a,b)的grouping sets是(),(a),(b),(a,b)
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by ROLLUP(Item,Color) 
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets((),(Item),(Color),(Item,Color))
order by Item,Color
复制代码

7,对rollup(a,b),使用单个分组和union来实现

复制代码
--rollup(a,b)的grouping sets是(),(a),(a,b)
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by ROLLUP(Item,Color) 
order by Item,Color

--等价于
select null as Item, null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory

union ALL
select Item,null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by Item 

union ALL
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Item,Color 
复制代码

8,对cube(a,b),使用单个分组和union来实现

复制代码
--cube(a,b)的grouping sets是(),(a),(b),(a,b)
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by cube(Item,Color) 
order by Item,Color

--等价于
select null as Item, null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory

union ALL
select Item,null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Item 

union ALL
select null as Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Color 
union all 
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by Item,Color 
复制代码

9, cube(a,b)的等价分组集是:grouping sets(rollup(a,b),b),或grouping sets((),(a),(a,b),(b))

复制代码
--cube(a,b)的组合是(),(a),(b),(a,b)
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from Inventory 
group by CUBE( Item,Color) 
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets((),(Item),(Color),(Item,Color))
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets(rollup(Item,Color),(Color))
order by Item,Color
复制代码

10,分组集相乘,结果集不去重

解析 grouping sets((a),(b)), rollup(a)等价于 grouping((a),(a),(b),(b,a))

解析过程是:grouping sets((a),(b)), 定义两个分组集是(a),(b),rollup(a)定义两个分组集是:(),(a)

对这个分组集进行集合乘法运算:{(a),(b)}*{(),(a)}={(a),(a),(b),(b,a)}

复制代码
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets((Item),(Color)),rollup(Item)
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets((Item),(Color),(Item,Item),(Color,Item))
order by Item,Color
复制代码

解析: grouping sets((a,b)),rollup(a)

解析过程:grouping sets((a,b)),定义分组集(a,b),rollup(a)定义分组集:{(),(a)},对这两个分组集合进行集合乘法运算:(a,b)*{(),(a)}={(a,b),(a,b)},实际上是两个相同的group by grouping sets((a,b)) 进行 union all 运算求并集。

复制代码
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets((Item,Color)),rollup(Item)
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets((Item,Color),(Color,Item))
order by Item,Color
复制代码

三,分组集用法总结

1, cube和rollup 预定义grouping sets,

  • rollup(a,b):预定义的grouping sets是(),(a),(a,b);
  • cube(a,b):预定义的grouping sets是(),(a),(b),(a,b);

2,集合的乘法

group by a,b 表示的是分组集(a),(b)的乘法:(a)*(b)=(a,b)

group by grouping sets((a),(b)),c 表示的是分组集((a),(b)),(c)的乘法:((a),(b))*(c)=((a,c),(b,c))

3,集合的并集

grouping sets((a),(b)),表示的是分组集的并集,等价于:

grouping sets(a)
union all 
grouping sets(b)

参考文档:

Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

GROUP BY (Transact-SQL)

GROUPING SETS Equivalents

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
分类: SQL Server





本文转自悦光阴博客园博客,原文链接:http://www.cnblogs.com/ljhdo/p/5056757.html,如需转载请自行联系原作者
目录
相关文章
|
6月前
|
数据库
解决which is not functionally dependent on columns in GROUP BY clause;...sql_mode=only_full_group_by
解决which is not functionally dependent on columns in GROUP BY clause;...sql_mode=only_full_group_by
47 0
|
8月前
|
存储 SQL 关系型数据库
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column的解决办法
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column的解决办法
88 0
|
10月前
|
SQL 关系型数据库 BI
PG:什么是grouping sets
PG:什么是grouping sets
101 0
|
SQL 分布式计算 Spark
SPARK Expand问题的解决(由count distinct、group sets、cube、rollup引起的)
SPARK Expand问题的解决(由count distinct、group sets、cube、rollup引起的)
571 0
SPARK Expand问题的解决(由count distinct、group sets、cube、rollup引起的)
1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause
1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause
174 0
1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause
|
SQL 关系型数据库 MySQL
【已解决】SELECT list is not in GROUP BY clause and contains nonaggregated column
MySQL5.7.5后only_full_group_by成为sql_mode的默认选项之一,这可能导致一些sql语句失效。
188 0
|
SQL 关系型数据库 MySQL
报错:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregat
报错:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregat
281 0
报错:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregat
|
SQL 关系型数据库 MySQL
MySQL - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
MySQL - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
162 0
|
SQL 关系型数据库 MySQL
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
346 0