SQL 数据库:GROUPING 运算符

关注

发布于: 2020 年 11 月 19 日

创建测试数据集
-- 创建Product表	
CREATE TABLE Product(
	product_id		CHAR(4)			NOT NULL,
	product_name 	VARCHAR(100)	NOT NULL,
	product_type	VARCHAR(32)		NOT NULL,
	sale_price		INTEGER			DEFAULT 0,
	purchase_price	INTEGER,
	regist_date		DATE,
	PRIMARY KEY(product_id)
);
-- 插入数据
BEGIN TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
COMMIT;
假设我们想要实现对不同种类的销售价格进行小计和合计，需要进行UNION，实现如下：
-- 计算小计和合计
SELECT '合计' AS product_type, SUM(sale_price) FROM Product/*合计*/
	UNION ALL
	SELECT product_type, SUM(sale_price) FROM Product	/*小计*/
		GROUP BY product_type;
ROLLUP可以同时得出合计和小计。
GROUPING运算符GROUPING 运算符包含以下 3 种 ：
ROLLUP
CUBE
GROUPING SETS
﻿
ROLLUPROLLUP可以同时计算合计和小计，合计行记录称为超级分组记录（super group row），默认使用NULL作为聚合键。SQL 提供了一个用来判断超级分组记录的NULL的特定函数——GROUPING 函数。该函数在其参数列的值为超级分组记录所产生的NULL时返回 1，其他情况返回 0，在实际的使用中，GROUPING函数能够简单地分辨出原始数据中的NULL和超级分组记录中的NULL。
﻿
假设根据商品类型计算销售价格的合计和小计
-- 超级分组采用默认NULL
SELECT product_type, SUM(sale_price) AS sum_price
 	FROM Product
 	GROUP BY ROLLUP(product_type);
-- 超级分组修改为'合计'
SELECT CASE WHEN GROUPING(product_type) = 1
		THEN '合计'
		ELSE product_type
		END AS product_type,
		SUM(sale_price) AS sum_price
	FROM Product
	GROUP BY ROLLUP(product_type);
﻿
再假设根据商品类型和登记日期计算销售价格的合计和小计
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
 	FROM Product
 	GROUP BY ROLLUP(product_type, regist_date);
该SELECT 语句的结果相当于使用 UNION 对如下 3 种模式的聚合级的不同结果进行连接：
GROUP BY ()
GROUP BY (product_type)
GROUP BY (producttype, registdate)
﻿
-- 对小计的超级分组重命名 
SELECT CASE WHEN GROUPING(product_type) = 1 
			THEN '商品种类 合计' 
			ELSE product_type
			END AS product_type,
		CASE WHEN GROUPING(regist_date) = 1 
			THEN '登记日期 合计' 
			ELSE CAST(regist_date AS VARCHAR(16))/*保持返回值类型一致*/
			END AS regist_date,
	SUM(sale_price) AS sum_price
	FROM Product
	GROUP BY ROLLUP(product_type, regist_date);
﻿
CUBE所谓 CUBE，就是将 GROUP BY 子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此，组合的个数就是 2n，其中n 是聚合键的个数。
SELECT CASE WHEN GROUPING(product_type) = 1 
			THEN '商品种类 合计' 
			ELSE product_type
			END AS product_type,
		CASE WHEN GROUPING(regist_date) = 1 
			THEN '登记日期 合计' 
			ELSE CAST(regist_date AS VARCHAR(16))/*保持返回值类型一致*/
			END AS regist_date,
	SUM(sale_price) AS sum_price
	FROM Product
	GROUP BY CUBE(product_type, regist_date);
相较于ROLLUP会多出来regist_date为聚合键得到的小计结果。
﻿
该SELECT 语句的结果相当于使用 UNION 对如下 3 种模式的聚合级的不同结果进行连接：
GROUP BY ()
GROUP BY (product_type)
 GROUP BY (registdate)  
GROUP BY (product_type, registdate)
﻿
GROUPING SETSGROUPING SETS以用于从 ROLLUP 或者 CUBE 的结果中取出部分记录。
﻿
假设取出将商品类型和登记日期各自作为聚合键计算汇总结果，忽略合计结果
SELECT CASE WHEN GROUPING(product_type) = 1 
			THEN '商品种类 合计' 
			ELSE product_type
			END AS product_type,
		CASE WHEN GROUPING(regist_date) = 1 
			THEN '登记日期 合计' 
			ELSE CAST(regist_date AS VARCHAR(16))/*保持返回值类型一致*/
			END AS regist_date,
	SUM(sale_price) AS sum_price
	FROM Product
	GROUP BY GROUPING SETS(product_type, regist_date);
﻿
参考资料书籍《SQL基础教程(第2版)》
﻿

发布于: 2020 年 11 月 19 日阅读数: 22

原文链接:【http://xie.infoq.cn/article/b33480bc922cd9acb12d1232d】。

大规模数据处理学习者

关注

正向成长 2018.08.06 加入

想要坚定地做大规模数据处理(流数据方向)，希望结合结合批处理的传统处理方式，以及之后流批混合处理方向进行学习和记录。

发布

暂无评论

创作场景

SQL 数据库:GROUPING 运算符

GROUPING运算符

ROLLUP

CUBE

GROUPING SETS

参考资料

大规模数据处理学习者

评论