浅析 Oracle 高级分组

SQL干货分享 2021-08-13

1510

（CSDN博主：写代码也要符合基本法）

天为罗盖地为毯，日月星辰伴我眠

什么人撒下名利网，富贵贫困不一般

也有骑马与坐轿，也有推车把担儿担

骑马坐轿修来的福，推车担担儿命该然

骏马驮着痴呆汉，美妇人常伴拙夫眠

八十老翁门前站，三岁顽童染黄泉

不是老天不睁眼，善恶到头这报应循环

各位看官老爷大家好，今天又是小刘写不出开头的一天，我们一起来看一下 Oracle 中关于 GROUP BY 的几个拓展用法

01 数据准备

今天的小刘不光写不出来开头，还造不出来数据，以下数据是我纯粹为了方便写例程而创建的一个毫无实际场景的表

02 简单分组

现在让我们来个毫无实际场景的按照 CATEGORY，CLASS 分组求 STOCK 的总和

SQL> SELECT g.category_name, g.class_name, SUM(g.stock)
  2    FROM demo_group_test g
  3   GROUP BY g.category_name, g.class_name;


CATEGORY_NAME        CLASS_NAME           SUM(G.STOCK)
-------------------- -------------------- ------------
K                    K1                             25
K                    K2                             80
K                    K3                             40
Q                    Q1                             20
Q                    Q2                             20
Q                    Q3                             50


6 rows selected

现在假设只计算 CATEGORY，CLASS 分组求和已经不能满足需要了，现在还想看个小计和总计，也就是按 CATEGORY 再加个小计，最后再加个全表的总计

03 ROLLUP

小计和总计的分组条件已经改变，如此便需要使用并集的方式来实现

SQL> SELECT *
  2    FROM (SELECT g.category_name, g.class_name, SUM(g.stock)
  3            FROM demo_group_test g
  4           GROUP BY g.category_name, g.class_name
  5          UNION ALL
  6          SELECT g.category_name, NULL, SUM(g.stock)
  7            FROM demo_group_test g
  8           GROUP BY g.category_name, NULL
  9          UNION ALL
 10          SELECT NULL, NULL, SUM(g.stock)
 11            FROM demo_group_test g
 12           GROUP BY NULL, NULL)
 13   ORDER BY category_name, class_name;


CATEGORY_NAME        CLASS_NAME           SUM(G.STOCK)
-------------------- -------------------- ------------
K                    C1                             25
K                    C2                             80
K                    C3                             40
K                                                  145
Q                    C1                             20
Q                    C2                             20
Q                    C3                             50
Q                                                   90
                                                   235


9 rows selected

三种不同的分组，带来的将是对同一张表三次的扫描读取，如果我们使用 ROLLUP 则可以避免这种重复的扫描

SQL> SELECT g.category_name, g.class_name, SUM(g.stock)
  2    FROM demo_group_test g
  3   GROUP BY ROLLUP(g.category_name, g.class_name);


CATEGORY_NAME        CLASS_NAME           SUM(G.STOCK)
-------------------- -------------------- ------------
K                    C1                             25
K                    C2                             80
K                    C3                             40
K                                                  145
Q                    C1                             20
Q                    C2                             20
Q                    C3                             50
Q                                                   90
                                                   235


9 rows selected

所以说，GROUP BY ROLLUP(COL1,COL2,COL3) 就相当于

按照 ROLLUP 列表里的顺序，从最后往前挨个置为空去分组

如果使用固定列与 ROLLUP 组合的方式，如 GROUP BY COL1, ROLLUP(COL2,COL3)，这就相当于

现在又假设了，小计和总计也满足不了我了，还要增加 CLASS 的同比，也就是说加上按 CLASS 的分组求和

04 CUBE

加一个分组，无非就是加一个 UNION ALL

SQL> SELECT *
  2    FROM (SELECT g.category_name, g.class_name, SUM(g.stock)
  3            FROM demo_group_test g
  4           GROUP BY g.category_name, g.class_name
  5          UNION ALL
  6          SELECT g.category_name, NULL, SUM(g.stock)
  7            FROM demo_group_test g
  8           GROUP BY g.category_name, NULL
  9          UNION ALL
 10          SELECT NULL, NULL, SUM(g.stock)
 11            FROM demo_group_test g
 12           GROUP BY NULL, NULL
 13          UNION ALL
 14          SELECT NULL, g.class_name, SUM(g.stock)
 15            FROM demo_group_test g
 16           GROUP BY NULL, g.class_name)
 17   ORDER BY category_name, class_name;


CATEGORY_NAME        CLASS_NAME           SUM(G.STOCK)
-------------------- -------------------- ------------
K                    C1                             25
K                    C2                             80
K                    C3                             40
K                                                  145
Q                    C1                             20
Q                    C2                             20
Q                    C3                             50
Q                                                   90
                     C1                             45
                     C2                            100
                     C3                             90
                                                   235


12 rows selected

没跑儿的咱们现在拿 CUBE 来替换一下，表扫描次数立马从 4 次降到 1 次

SQL> SELECT g.category_name, g.class_name, SUM(g.stock)
  2    FROM demo_group_test g
  3   GROUP BY CUBE(g.category_name, g.class_name);


CATEGORY_NAME        CLASS_NAME           SUM(G.STOCK)
-------------------- -------------------- ------------
                                                   235
                     C1                             45
                     C2                            100
                     C3                             90
K                                                  145
K                    C1                             25
K                    C2                             80
K                    C3                             40
Q                                                   90
Q                    C1                             20
Q                    C2                             20
Q                    C3                             50


12 rows selected

所以说，GROUP BY CUBE(COL1,COL2,COL3) 就相当于

汉语博大精深，这个情况依旧可以描述为从最后往前挨个置为空去分组，但是为了区分，这里用数学名词解释一下就是将列表中的字段自由组合，再依次分组

那么使用固定列和 CUBE 组合的方式和前例也是同理的，这里不再赘述

05 GROUPING SETS

再假如需求又变了，不看这么多了，只想分别看看 CATEGORY 和 CLASS 的求和了

SQL> SELECT *
  2    FROM (SELECT g.category_name, NULL class_name, SUM(g.stock)
  3            FROM demo_group_test g
  4           GROUP BY g.category_name, NULL
  5          UNION ALL
  6          SELECT NULL, g.class_name, SUM(g.stock)
  7            FROM demo_group_test g
  8           GROUP BY NULL, g.class_name)
  9   ORDER BY category_name, class_name;


CATEGORY_NAME        CLASS_NAME           SUM(G.STOCK)
-------------------- -------------------- ------------
K                                                  145
Q                                                   90
                     C1                             45
                     C2                            100
                     C3                             90

这三个栗子合着都是换一下关键字就解决了

SQL> SELECT g.category_name, g.class_name, SUM(g.stock)
  2    FROM demo_group_test g
  3   GROUP BY GROUPING SETS(g.category_name, g.class_name);


CATEGORY_NAME        CLASS_NAME           SUM(G.STOCK)
-------------------- -------------------- ------------
K                                                  145
Q                                                   90
                     C2                            100
                     C1                             45
                     C3                             90

所以说，GROUP BY GROUPING SETS(COL1,COL2,COL3) 就相当于

使用固定列和 GROUPING SETS 组合的方式和前例也是同理的，这里不再赘述

06 GROUPING 与 GROUPING_ID

经过前面的学习我们发现一个共同点，就是会把列表中的列置空，而且最后输出的也是空值占位

那么问题就来了，如果某列当中本来就有空值存在的话，本身就是空的和被置空的情况就难以区分

为了解决这个问题，Oracle 为我们提供了两个函数 GROUPING 和 GROUPING_ID

GROUPING 函数中的列，如果在当前行中这个列是被置空的情况，函数就返回 1，其它情况就返回 0

GROUPING_ID 函数可以认为是，基于将参数中的各列计算 GROUPING，然后按序组合成一个二进制的矢量，并将这个矢量换算成十进制数字返回

SQL> WITH rowgen AS
  2   (SELECT 0 bit_0, 1 bit_1 FROM dual),
  3  cubes AS
  4   (SELECT grouping_id(bit_0, bit_1) gid
  5          ,GROUPING(bit_0) bv_0
  6          ,GROUPING(bit_1) bv_1
  7          ,bit_0
  8          ,bit_1
  9      FROM rowgen
 10     GROUP BY CUBE(bit_0, bit_1))
 11  SELECT gid, bv_0 || bv_1 bit_vector, bit_0, bit_1 FROM cubes;


       GID BIT_VECTOR        BIT_0      BIT_1
---------- ------------ ---------- ----------
         3 11                      
         2 10                               1
         1 01                    0 
         0 00                    0          1

07 总结

分享了这么多，那么高级分组什么时候使用呢？

答：用得着的时候用

oracle

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

浅析 Oracle 高级分组

评论