ROLLUP

最新推荐文章于 2025-05-13 23:10:57 发布

原创最新推荐文章于 2025-05-13 23:10:57 发布 · 1.1k 阅读

2 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

1 篇文章

订阅专栏

本文深入探讨了Oracle SQL中ROLLUP、CUBE和GROUPING SETS的使用方法，详细解释了这些运算符如何帮助进行复杂的数据分组和汇总。文章对比了Oracle与PostgreSQL在分组聚合语法上的差异，并提供了如何在PostgreSQL中使用UNION ALL和聚合函数来模拟ROLLUP功能的具体示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--206 & 207
在 Oracle SQL语法中， ROLLUP 用于汇总分组聚合后的数据。如果 ROLLUP 后包含多列，型如 GROUP BY ROLLUP(colA, colB) ，则会基于分组后第一个列(colA)再次进行分组汇总，最后再对所有数据进行汇总。
如果语句为 GROUP BY ROLLUP(a, b)，oracle 将会从右到左先对字段 a 和 b 先进行 GROUP BY，然后对字段 A 进行 GROUP BY，最后对全表进行 GROUP BY。如果语句为 GROUP BY ROLLUP(a, b, c), Oracle 将从右到左先会对字段 a 和 b 和 c 先进行 GROUP BY, 然后对字段 a 和 b 进行 GROUP BY，然后对字段 a 进行 GROUP BY，最后对全表进行 GROUP BY。

SELECT colA, colB, SUM(colC)
  FROM group_test
 GROUP BY ROLLUP(colA, colB);

PostgreSQL 不支持 ROLLUP 运算符。

PostgreSQL可以基于分层汇总的逻辑，使用 "UNION ALL+聚合函数" 改写 Oracle ROLLUP 运算符

SELECT colA, colB, SUM(colC)
  FROM group_test
 GROUP BY colA, colB
UNION ALL
SELECT colA, NULL, SUM(colC)
  FROM group_test
 GROUP BY colA
UNION ALL
SELECT NULL, NULL, SUM(colC)
  FROM group_test
 ORDER BY 1,2;

--注：207 原始Oracle ROLLUP的默认排序是基于 SELECT 列输出顺序，NULL默认排在最末？？？
https://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8607
http://blog.itpub.net/519536/viewspace-610995/
https://www.2cto.com/database/201501/367727.html


--208

在 Oracle SQL语法中， ROLLUP 用于汇总分组聚合后的数据。如果 ROLLUP 后包含多列，型如 GROUP BY ROLLUP(colA, colB) ，则会基于分组后第一个列(colA)再次进行分组汇总，最后再对所有数据进行汇总。
SELECT colA, colB, SUM(colC)
  FROM group_test
 GROUP BY ROLLUP(colA, colB)
HAVING SUM(colC)> 10000;

PostgreSQL不支持 ROLLUP 运算符。

PostgreSQL可以基于分层汇总的逻辑，使用 "UNION ALL+聚合函数" 改写 Oracle ROLLUP 运算符。其中HAVING子句应包含在每个 UNION ALL 分支中

SELECT colA, colB, SUM(colC)
  FROM group_test
 GROUP BY colA, colB
HAVING SUM(colC)> 10000
UNION ALL
SELECT colA, NULL, SUM(colC)
  FROM group_test
 GROUP BY colA
HAVING SUM(colC)> 10000
UNION ALL
SELECT NULL, NULL, SUM(colC)
  FROM group_test
HAVING SUM(colC)> 10000
 ORDER BY 1,2;

--210 & 211 & 212
Oracle CUBE 可以为指定的列创建各种不同组合的小计。 如果指定的列的数量为 n， GROUP BY CUBE 将创建 2 * n 个层次的小计。 CUBE 是一种比 ROLLUP 更细粒度的分组统计语句。
如果语句为 GROUP BY CUBE(a, b)， Oracle 首先对字段 a 和 b 进行 GROUP BY ，然后对字段 a 进行 GROUP by，然后对字段 b 进行 GROUP by，最后对全表进行 GROUP BY 。如果语句为 GROUP BY CUBE(a, b, c)， Oracle 进行分组的字段分别为 (a, b, c)，(a, b)，(a, c)，(b, c)，(a)，(b)，(c)，最后对全表的总计。

SELECT colA, colB, SUM(colC)
  FROM group_test
 GROUP BY ROLLUP(colA, colB);

PostgreSQL不支持 ROLLUP 运算符。

PostgreSQL可以基于分层汇总的逻辑，使用 "UNION ALL+聚合函数" 改写 Oracle ROLLUP 运算符。但需要基于colB的分组聚合。

SELECT NULL, NULL, SUM(colC)
  FROM group_test
UNION ALL
SELECT NULL, colB, SUM(colC)
  FROM group_test
 GROUP BY colB
UNION ALL
SELECT colA, colB, SUM(colC)
  FROM group_test
 GROUP BY colA, colB
UNION ALL
SELECT colA, NULL, SUM(colC)
  FROM group_test
 GROUP BY colA
 ORDER BY 1,2;

--213
https://www.cnblogs.com/simplefrog/archive/2012/07/15/2592371.html
https://blog.csdn.net/shangboerds/article/details/43988513

GROUPING SETS 内括号第一层表示 UNION ALL 并行数，以逗号区分每层UNION ALL语句块；第二层括号表示GROUP BY 依据；多个GROUPING SETS之间以笛卡尔方式构建单一GROUP BY凭据
GROUP BY GROUPING SETS (A,B,C)
等价于
GROUP BY A  
UNION ALL  
GROUP BY B  
UNION ALL  
GROUP BY C

--------------------------------
GROUP BY GROUPING SETS ((A,B,C))
等价于
GROUP BY A,B,C  
   
   
GROUP BY GROUPING SETS (A,(B,C))
等价于
GROUP BY A  
UNION ALL  
GROUP BY B,C

--------------------------------------------------------------------------------------
一个 GROUP BY 语句中多次使用 GROUPING SETS
GROUP BY GROUPING SETS (A)
       , GROUPING SETS (B)  
       , GROUPING SETS (C)

等价于

GROUP BY A,B,C  
--
GROUP BY GROUPING SETS (A)
       , GROUPING SETS ((B,C))
等价于
GROUP BY A, B, C
--
   
   
GROUP BY GROUPING SETS (A)
       , GROUPING SETS (B,C)

等价于
GROUP BY A,B 
UNION ALL
GROUP BY A,C
-------------------------------------------------------------------------------
混合使用
GROUP BY A                     
       , B
       , GROUPING SETS ((B,C))
等价于
GROUP BY A, B, C

---------
GROUP BY A
       , B
       , GROUPING SETS (B,C)
等价于
GROUP BY A,B,C
UNION ALL
GROUP BY A,B
-------------------------
GROUP BY A
       , B
       , C
       , GROUPING SETS (B,C)
等价于
GROUP BY A, B, C
UNION ALL
GROUP BY A, B, C