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_dateNULL
其实上面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的机
会也就很少了。

©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • group by 用来在原始数据上创建聚合来将数据转化为有用的信息。 基本的group by 列出个个部门的名称...
    猫猫_tomluo阅读 1,254评论 0 6
  • 8 SQL高级处理 8-1窗口函数 8-1-1什么是窗口函数 窗口函数也称为 OLAP 函数。OLAP是OnLin...
    los_pollos阅读 581评论 0 0
  • 查询基础 算术运算符 (+)(-)(*)(/) 值得注意的是:/ 在oracle中就相当于显示中的除法 5/2 =...
    Autism_37a1阅读 490评论 0 0
  • 吵架背景 邹邹有点大男子主义,认为女生应该照顾家里,代入感强的同学此时可以略过,因为你们批判的对,说的对,想的对,...
    鲍鲍大人V5阅读 130评论 0 0
  • 石家湾,望断霞纷乱,孤鹜云烟。 小艇岸横,暮色秋风同残。 柳边沙外沧浪染,卷浪花、抛向天边。 江都远、游人千里,子...
    刘小地阅读 249评论 4 43