Oracle分组函数之ROLLUP

r囧r小猫 2022-03-15 06:20 607阅读 0赞

# 初始化数据 #

创建测试表group\_test

create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
    
    insert into group_test values (10,'Coding',    'Bruce',1000);
    insert into group_test values (10,'Programmer','Clair',1000);
    insert into group_test values (10,'Architect', 'Gideon',1000);
    insert into group_test values (10,'Director',  'Hill',1000);
    
    insert into group_test values (20,'Coding',    'Jason',2000);
    insert into group_test values (20,'Programmer','Joey',2000);
    insert into group_test values (20,'Architect', 'Martin',2000);
    insert into group_test values (20,'Director',  'Michael',2000);
    
    insert into group_test values (30,'Coding',    'Rebecca',3000);
    insert into group_test values (30,'Programmer','Rex',3000);
    insert into group_test values (30,'Architect', 'Richard',3000);
    insert into group_test values (30,'Director',  'Sabrina',3000);
    
    insert into group_test values (40,'Coding',    'Samuel',4000);
    insert into group_test values (40,'Programmer','Susy',4000);
    insert into group_test values (40,'Architect', 'Tina',4000);
    insert into group_test values (40,'Director',  'Wendy',4000);
    
    commit;

#### 1、初始数据 ####

GROUP_ID 		JOB        NAME           SALARY
            10 		Coding     Bruce            1000
            10 		Programmer Clair            1000
            10 		Architect  Gideon           1000
            10 		Director   Hill             1000
            20 		Coding     Jason            2000
            20 		Programmer Joey             2000
            20 		Architect  Martin           2000
            20 		Director   Michael          2000
            30 		Coding     Rebecca          3000
            30 		Programmer Rex              3000
            30 		Architect  Richard          3000
            30 		Director   Sabrina          3000
            40 		Coding     Samuel           4000
            40 		Programmer Susy             4000
            40 		Architect  Tina             4000
            40 		Director   Wendy            4000

#### 2、先看一下普通分组的效果：对group\_id进行普通的group by操作—按照小组进行分组 ####

> select group_id,sum(salary) from group_test group by group_id;
    
      GROUP_ID SUM(SALARY)
    ---------- -----------
            30       12000
            20        8000
            40       16000
            10        4000

#### 3、对group\_id进行普通的rollup操作—按照小组进行分组，同时求总计 ####

> select group_id,sum(salary) from group_test group by rollup(group_id);
    
      GROUP_ID SUM(SALARY)
    ---------- -----------
            10        4000
            20        8000
            30       12000
            40       16000
                     40000

使用Group By语句翻译一下上面的SQL语句如下:

> select group_id,sum(salary) from group_test group by group_id
    > union all
    > select null, sum(salary) from group_test
    > order by 1;
    
      GROUP_ID SUM(SALARY)
    ---------- -----------
            10        4000
            20        8000
            30       12000
            40       16000
                     40000

#### 4、再看一个rollup两列的情况 ####

> select group_id,job,sum(salary) from group_test group by rollup(group_id, job);
    
      GROUP_ID JOB        SUM(SALARY)
    ---------- ---------- -----------
            10 Coding            1000
            10 Director          1000
            10 Architect         1000
            10 Programmer        1000
            10                   4000
            20 Coding            2000
            20 Director          2000
            20 Architect         2000
            20 Programmer        2000
            20                   8000
            30 Coding            3000
            30 Director          3000
            30 Architect         3000
            30 Programmer        3000
            30                  12000
            40 Coding            4000
            40 Director          4000
            40 Architect         4000
            40 Programmer        4000
            40                  16000
                                40000
    
    21 rows selected.

使用Group By语句翻译一下上面的SQL语句如下:

> select group_id,job,sum(salary) from group_test group by group_id, job
    > union all
    > select group_id,null,sum(salary) from group_test group by group_id
    > union all
    > select null,null,sum(salary) from group_test
    > order by 1,2;
    
      GROUP_ID JOB        SUM(SALARY)
    ---------- ---------- -----------
            10 Architect         1000
            10 Coding            1000
            10 Director          1000
            10 Programmer        1000
            10                   4000
            20 Architect         2000
            20 Coding            2000
            20 Director          2000
            20 Programmer        2000
            20                   8000
            30 Architect         3000
            30 Coding            3000
            30 Director          3000
            30 Programmer        3000
            30                  12000
            40 Architect         4000
            40 Coding            4000
            40 Director          4000
            40 Programmer        4000
            40                  16000
                                40000
    
    21 rows selected.

#### 5、补充一步，体验一下GROUPING函数的效果 ####

> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id, job);
    
      GROUP_ID JOB        GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
    ---------- ---------- ------------------ ------------- -----------
            10 Coding                      0             0        1000
            10 Director                    0             0        1000
            10 Architect                   0             0        1000
            10 Programmer                  0             0        1000
            10                             0             1        4000
            20 Coding                      0             0        2000
            20 Director                    0             0        2000
            20 Architect                   0             0        2000
            20 Programmer                  0             0        2000
            20                             0             1        8000
            30 Coding                      0             0        3000
            30 Director                    0             0        3000
            30 Architect                   0             0        3000
            30 Programmer                  0             0        3000
            30                             0             1       12000
            40 Coding                      0             0        4000
            40 Director                    0             0        4000
            40 Architect                   0             0        4000
            40 Programmer                  0             0        4000
            40                             0             1       16000
                                           1             1       40000
    
    21 rows selected.

GROUPING函数对应的列（例如JOB字段）是由ROLLUP函数所产生的空值则显示“1”，即对此列进行汇总计算后的结果。  
如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。  
结果其实是对初始数据集进行group\_id和job分组，然后根据group\_id进行汇总，最后在之前（根据group\_id进行汇总后）的基础上根据job汇总。

如果将rollup中的两个列交换会有什么情况呢？请看：

> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(job,group_id);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpdWxpbnNhbWE_size_16_color_FFFFFF_t_70]  
结果其实是对初始数据集进行job和group\_id（注意顺序）分组，然后根据job进行汇总，最后在之前（根据job进行汇总后）的基础上根据group\_id汇总。

如果将rollup中job移到外面呢？

select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id),job;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpdWxpbnNhbWE_size_16_color_FFFFFF_t_70 1]  
结果其实是对初始数据集进行job和group\_id（注意顺序，可以看到其实rollup的执行顺序靠后，将rollup和job换个位置结果一样）分组，然后根据job进行汇总，这里就没有在之前（根据job进行汇总后）的基础上根据group\_id汇总了。

部分转载自：来自 “ ITPUB博客 ” ，链接：[http://blog.itpub.net/519536/viewspace-610995/][http_blog.itpub.net_519536_viewspace-610995]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpdWxpbnNhbWE_size_16_color_FFFFFF_t_70]: /images/20220315/404f27d07cb54af0b5f4b58052ca1d58.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpdWxpbnNhbWE_size_16_color_FFFFFF_t_70 1]: /images/20220315/ecbd9acf43b24453b1f67ef3f2f57763.png
[http_blog.itpub.net_519536_viewspace-610995]: http://blog.itpub.net/519536/viewspace-610995/