GROUPING运算符

只使用GROUP BY子句和聚合函数是无法同时得到小计和合计的。如果想要同时得到，需要使用GROUPING运算符。
理解GROUPING运算符中的CUBE的关键在于形成“积木搭建出的立方体”的印象。
虽然GROUPING运算符是标准SQL的功能，但还有些DBMS尚未支持这一功能。

同时得到合计行

想要得到上图中的结果，光靠下面的SQL是做不到的。

SELECT product_type, SUM(sale_price) 
FROM Product
GROUP BY product_type;

用下面的UNION ALL可以实现。

SELECT
    '合计' AS product_type,
    sum(sale_price)
FROM
    product
UNION ALL
    SELECT
        product_type,
        sum(sale_price)
    FROM
        product
    GROUP BY
        product_type;

但是这样的SQL看起来繁琐，而且在DBMS内部处理的成本挺高的。

ROLLUP——同时得到合计和小计

为了满足用户的需要，标准SQL引入了GROUPING运算符。使用该运算符可以很简单的得到之前那样的汇总单位不同的汇总结果。

GROUPING的运算符包含以下3种。

• ROLLUP
• CUBE
• GROUPING SETS

ROLLUP的使用方法

SELECT
    product_type,
    sum(sale_price) AS sum_price
FROM
    product
GROUP BY
    ROLLUP (product_type);

执行结果

从语法上来说，就是讲GROUP BY字句中的聚合键清单像ROLLUP(<列1>, <列2>, . . . )这样使用。该运算符的作用，一言以蔽之，就是“一次计算出把不同聚合件组合的结果”。例如，该例中就是一次计算出了如下两种组合的汇总结果。

1. GROUP BY ()
2. GROUP BY (product_type)

1中的GROUP BY没有聚合键，相当于没有GROUP BY。该合计行记录称为 (super group row)。虽然名字听上去很炫，但还是希望大家把它当作 未使用GROUP BY的合计行来理解。超级分组记录的product_type 列的键值(对 DBMS 来说)并不明确，因此会默认使用 NULL。之后会为大家讲解在此处插入恰当的字符串的方法。

将“登记日期”添加到聚合键当中

SELECT
    product_type,
    regist_date,
    sum(sale_price) AS sum_price
FROM
    product
GROUP BY
    ROLLUP (product_type, regist_date);

执行结果
该SELECT语句的结果相当于使用UNION对如下三种模式的聚合键的不同结果进行连接。

1. GROUP BY ()
2. GROUP BY (product_type)
3. GROUP BY (product_type, regist_date)

三种模式的集合
上图中，有一点蹊跷的地方。就是“衣服”分组中，有两条记录的regist_date为NULL。
其实上面5000的一个，是GROUP BY(product_type)产生的，也可以叫做超级分组记录。但两个看上去都是NULL，难以分辨。
两个NULL

为了避免混淆，SQL提供了一个用来判断超级分组记录的NULL的特定函数——GROUPING函数。该函数在其参数列的值未超级分组记录所产生的NULL时返回1，其他情况返回0。

SELECT
    grouping (product_type) AS product_type,
    grouping (regist_date) AS regist_date,
    sum(sale_price) AS sum_price
FROM
    product
GROUP BY
    ROLLUP (product_type, regist_date);

执行结果
这样就能区分超级分组记录中的NULL和原始数据本身的NULL了。
同时，我们可以使用GROUPING来添加“合计”。

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 CHAR(10))
    END AS regist_date,
    sum(sale_price) AS sum_price
FROM
    product
GROUP BY
    product_type,
    regist_date WITH ROLLUP;

上面是在MYSQL跑通的，下面是书上的，有两点不同，一个是CAST(regist_date AS CHAR(10))和CAST(regist_date AS VARCHAR(16))，另一个是ROLLUP (product_type, regist_date)和product_type, regist_date WITH ROLLUP。

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);

执行结果

另外需要注意的是，CAST的类型转换，不论是在MYSQL中CAST(.. AS CHAR(10))还是在某些里面的CAST(.. AS VARCHAR(16))，其实都是类型转换。为什么呢？为什么要换成字符串呢？这是为了满足CASE表达式所有分支的返回值必须一致的条件。如果不这样的话，那么各个分支分别返回日期类型和字符串类型的值，执行时就会发生语法错误。可以这样来考虑，在DBMS中，会为这里的执行结果定义一张Virtual Table，那么在定义虚拟表的时候，这一列到底应该用DATE类型还是VARCHAR/CHAR类型呢？所以，CASE语句一定要确保各个分支返回的结果是同一个类型。

CUBE——用数据来搭积木

CUBE是另一个常用的GROUPING运算符，CUBE是“立方体”的意思，这个名字和ROLLUP一样，都能形象地说明函数的动作。
CUBE的语法和ROLLUP一样。

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 CHAR(16))
    END AS regist_date,
    SUM(sale_price) AS sum_price
FROM
    Product
GROUP BY
    product_type,
    CUBE(regist_date);

执行结果
与 ROLLUP 的结果相比，CUBE 的结果中多出了几行记录。大家看 一下应该就明白了，多出来的记录就是只把 regist_date作为聚合键所得到的汇总结果。

1. GROUP BY()
2. GROUP BY (product_type)
3. GROUP BY(regist_date) <--添加的组合
4. GROUP BY(product_type, regist_date)

所谓CUBE，就是讲GROUP BY字句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。

CUBE的执行示意

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);

执行结果
上述结果中也没有全体的合计行(16780 日元)。与 ROLLUP 或者 CUBE 能够得到规定的结果相对，GROUPING SETS 用于从中取出个别 条件对应的不固定的结果。然而，由于期望获得不固定结果的情况少之又少，因此与ROLLUP或者CUBE比起来，使用GROUPING SETS的机
会也就很少了。