写点什么

SQL 数据库:GROUPING 运算符

发布于: 2020 年 11 月 19 日
SQL数据库:GROUPING运算符

创建测试数据集

-- 创建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



ROLLUP

ROLLUP可以同时计算合计和小计,合计行记录称为超级分组记录(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 子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此,组合的个数就是 ,其中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 SETS

GROUPING 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
用户头像

正向成长 2018.08.06 加入

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

评论

发布
暂无评论
SQL数据库:GROUPING运算符