group by(rollup ，cube ,grouping sets) and grouping

最新推荐文章于 2025-06-01 13:10:28 发布

原创最新推荐文章于 2025-06-01 13:10:28 发布 · 225 阅读

0 ·

CC 4.0 BY-SA版权

ORA-Dev 专栏收录该内容

59 篇文章

订阅专栏

本文详细介绍了SQL中的Rollup和Cube功能，通过实例演示如何使用这两种方法进行数据聚合，并对比了它们之间的区别。此外，还展示了如何利用CASE语句和GROUPING()函数来增强查询的可读性和灵活性。

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

1、创建测试数据

create table TEST
(ID INTEGER,
SORT CHAR(10),
COLOR CHAR(10),
NUM INTEGER);

insert into test (ID, SORT, COLOR, NUM)
values (1, 'book      ', 'red       ', 15);
insert into test (ID, SORT, COLOR, NUM)
values (1, 'book      ', 'blue      ', 10);
insert into test (ID, SORT, COLOR, NUM)
values (1, 'book      ', 'green     ', 10);
insert into test (ID, SORT, COLOR, NUM)
values (1, 'book      ', 'red       ', 10);
insert into test (ID, SORT, COLOR, NUM)
values (1, 'car       ', 'blue      ', 10);
insert into test (ID, SORT, COLOR, NUM)
values (1, 'car       ', 'red       ', 10);
insert into test (ID, SORT, COLOR, NUM)
values (2, 'car       ', 'red       ', 10);*/

2、关于rollup and cube概述

rollup(a,b,c) 包括N+1种组合,分别按(a,b,c )聚合，按(a,b)聚合,最后()聚合即全表聚合。

cube(a,b,c) 包括2^n种组合,分别按(a,b,c),(a,b),(a,c),(a),(b,c),(b),(c),()共8种情况聚合。

3、具体使用举例：

a、SELECT sort, color, SUM(num) FROM test GROUP BY ROLLUP(sort, color);

book      blue      10
book      green    10
book      red       25
book                  45
car       blue      10
car       red       20
car                     30
                           75

b、SELECT sort, color, SUM(num) FROM test GROUP BY CUBE(sort, color);

                             75
blue                     20
green                  10
red                      45
book                    45
book      blue      10
book      green     10
book      red       25
car                       30
car       blue      10
car       red       20

c、使用CASE转换GROUPING()的返回值--判断某列是否被聚合了grouping + rollup

     SELECT CASE
         WHEN GROUPING(sort) = 1 THEN
          'ALL_SORTS'
         ELSE
          sort
       END,
       CASE
         WHEN GROUPING(color) = 1 THEN
          'ALL_colors'
         ELSE
          color
       END, SUM(num)
FROM test
GROUP BY ROLLUP(sort, color);

book      blue             10
book      green           10
book      red                25
book      ALL_colors    45
car       blue                 10
car       red                   20
car       ALL_colors      30
ALL_SORTS ALL_colors 75

d、grouping + cube

SELECT CASE
         WHEN GROUPING(sort) = 1 THEN
          'ALL_SORTS'
         ELSE
          sort
       END,
       CASE
         WHEN GROUPING(color) = 1 THEN
          'ALL_colors'
         ELSE
          color
       END, SUM(num)
FROM test
GROUP BY CUBE(sort, color);

ALL_SORTS ALL_colors   75
ALL_SORTS blue            20
ALL_SORTS green         10
ALL_SORTS red             45
book      ALL_colors     45
book      blue           10
book      green     10
book      red         25
car       ALL_colors    30
car       blue      10
car       red       20

e、使用group by grouping set 只返回聚合的值

SELECT nvl(sort, 'all_sorts'), nvl(color, 'all_color'), SUM(num)
FROM test
GROUP BY GROUPING SETS(sort, color);

book      all_color    45
car       all_color    30
all_sorts blue      20
all_sorts green     10
all_sorts red       45