MySQL：grouping函数实现合计和小计

幸因余荫

一、背景
有时会遇到这样的需求，计算出每个品类的销售额和总销售额，即要计算出小计和合计。
对于mysql而言，通过group by和聚合函数是不能直接计算出小计和合计的；一般都要通过union all语句进行连接在一起。
INSERT INTO product(`product_id`, `product_type`, `product_name`, `sale_price`) VALUES
('0001', '衣服', 'T恤', 1000.00),('0002', '厨房用品', '打孔器', 500.00),
('0003', '衣服', '运动T恤', 4000.00),('0004', '厨房用品', '菜刀', 3000.00),
('0005', '厨房用品', '高压锅', 6800.00),('0006', '', '叉子', 500.00),
('0007', '厨房用品', '擦彩板', 880.00),('0008', '学习用品', '圆珠笔', 100.00),('0009', '学习用品', '橡皮檫', 88.00);

SELECT "合计" as product_type,sum(sale_price) as price
FROM product
union all
(
SELECT product_type,sum(sale_price) as price
FROM product
GROUP BY product_type
)

二、grouping函数及运算符
而mysql要同时得到小计和合计，就要使用grouping运算符。
grouping运算符包括rollup、cube、grouping sets三种，语法形式为：
GROUP BY 列名1,列名2… WITH ROLLUP，
GROUP BY 列名1,列名2… WITH CUBE，
GROUP BY GROUPING SETS((a),(b))。
（1）rollup
①rollup可同时计算出合计和小计；即一次计算出不同聚合键组合的结果。
SELECT product_type,sum(sale_price) as price
FROM product
GROUP BY product_type with rollup;

②rollup会默认把NULL当做聚合键；而grouping函数用来返回每个分组是否rollup结果，是为1否为0，就能区分哪些NULL是正常记录，哪些是ROLLUP结果。
SELECT product_type,GROUPING(product_type) as product_type_tag,sum(sale_price) as price
FROM product
GROUP BY product_type WITH ROLLUP;

SELECT product_type,
       CASE WHEN GROUPING(product_type)=1 THEN "总计" ELSE "小计" END as product_type_tag,
       sum(sale_price) as price
FROM product
GROUP BY product_type WITH ROLLUP;



③grouping还可以针对多个字段来统计汇总值，grouping(a,b)，这时grouping的值就包括0,1,2；因为grouping(a,b)=grouping(a)+grouping(b)
④grouping还可用于having子句，如用来过滤掉非ROLLUP的结果。
（2）cube
对于cube的每一个参数，可理解为取值为参与分组与不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合，对n个参数的cube，有2^n分组。
对于GROUP BY a,b,c CUBE，首先对(a,b,c)进行group by，然后依次是(a,b),(a,c),(a),(b,c),(b),(c)进行group by，最后对全表进行group by操作，一共8次分组。
#我的mysql版本不支持cube，没有验证
SELECT product_type,sum(sale_price) as price
from product
GROUP BY product_type with CUBE;grouping与cube的联合使用类似与rollup的联合使用。
（3）GROUPING SETS
cube的结果是根据聚合键的所有可能的组合计算而来，而GROUPING SETS可以用于从cube的组合结果中取出指定的组合对应的结果，可以包括重复的分组。
#我的mysql暂不支持
SELECTYEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
SUM(SubTotal) AS Incomes
FROM Sales.SalesOrderHeader
GROUP BYGROUPING SETS
(
YEAR(OrderDate),(YEAR(OrderDate),MONTH(OrderDate)
);

九人

站位支持