【课堂笔记】【SQL】15.Generating Reports by Grouping Related Data

5dm

本章要点  ROLLUP   CUBE   GROUPING   GROUPING SETS

ROLLUP：在GROUP BY 子句中按组小计，通常是基于多个字段的。分门合计，最后还汇总。

示例：

SQL> select department_id,job_id,sum(salary) from employees
  2  where department_id<60
  3  group by rollup(department_id,job_id);

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           10 AD_ASST           4400
           10                         4400
           20 MK_MAN           13000
           20 MK_REP            6000
           20                       19000
           30 PU_MAN           11000
           30 PU_CLERK         13900
           30                      24900
           40 HR_REP            6500
           40                      6500
           50 ST_MAN           36400

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           50 SH_CLERK         64300
           50 ST_CLERK         55700
           50                        156400
                                      211200

15 rows selected.

 

 

分析：ROLLUP语句执行是从GROUP BY括号里面的字段中，从右到左逐渐挨个去除字段进行汇总，最后括号里面的字段都去除完就来个总的汇总。

 

CUBE:立方体的意思，用来解决交叉表的问题

示例：

SQL> select department_id,job_id,sum(salary) from employees
  2  where department_id<60
  3  group by cube(department_id,job_id);

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
                              211200
              HR_REP            6500
              MK_MAN           13000
              MK_REP            6000
              PU_MAN           11000
              ST_MAN           36400
              AD_ASST           4400
              PU_CLERK         13900
              SH_CLERK         64300
              ST_CLERK         55700
           10                   4400

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           10 AD_ASST           4400
           20                  19000
           20 MK_MAN           13000
           20 MK_REP            6000
           30                  24900
           30 PU_MAN           11000
           30 PU_CLERK         13900
           40                   6500
           40 HR_REP            6500
           50                 156400
           50 ST_MAN           36400

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           50 SH_CLERK         64300
           50 ST_CLERK         55700

24 rows selected.

 

分析：BUBE语句从右边开始把group by对象的其他字段视为空，只留一个字段依次汇总。这，让我想起了概率论与数理统计上的边际分布，道理是一样的。

 

 

 

 

5dm

简单的说，ROLLUP就是解决树状的统计，CUBE就是解决二维表（矩阵）的统计。

5dm

续：

用ROLLUP和CUBE的GROUP BY语句中会出现某些字段的值为空，那么如何来区分哪些空是该字段的值自然为空还是由于ROLLUP和CUBE产生的空呢？这就用到GROUPING

示例：

SQL> select department_id,job_id,sum(salary),
  2  grouping(department_id) as "GRP_DEPT",grouping(job_id) as "GRP_JOB"
  3  from employees
  4  where department_id<50 group by rollup(department_id,job_id);

DEPARTMENT_ID JOB_ID     SUM(SALARY)   GRP_DEPT    GRP_JOB
------------- ---------- ----------- ---------- ----------
           10 AD_ASST           4400          0          0
           10                   4400          0          1
           20 MK_MAN           13000          0          0
           20 MK_REP            6000          0          0
           20                  19000          0          1
           30 PU_MAN           11000          0          0
           30 PU_CLERK         13900          0          0
           30                  24900          0          1
           40 HR_REP            6500          0          0
           40                   6500          0          1
                               54800          1          1

11 rows selected.

 

分析：1表示因ROLLUP和CUBE产生的空，0表示自然的空。

 

GROUPING SETS:用小括号把几个子字段组合起来，视为一个字段的情况处理

示例：

SQL> select department_id,job_id,manager_id,avg(salary) from employees group by grouping sets((department_id,job_id),(job_id,manager_id));

DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
              AC_MGR            101       12008
              SH_CLERK          122        3200
              SH_CLERK          124        2825
              MK_MAN            100       13000
              ST_MAN            100        7280
              ST_CLERK          121        2675
              SA_REP            148        8650
              SH_CLERK          120        2900
              AD_ASST           101        4400
              AD_PRES                     24000
              FI_MGR            101       12008

DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
              SA_REP            146        8500
              SH_CLERK          123        3475
              IT_PROG           102        9000
              IT_PROG           103        4950
              FI_ACCOUNT        108        7920
              PU_MAN            100       11000
              AC_ACCOUNT        205        8300
              ST_CLERK          122        2700
              SA_REP            145        8500
              HR_REP            101        6500
              PR_REP            101       10000

DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
              AD_VP             100       17000
              ST_CLERK          120        2625
              ST_CLERK          124        2925
              SA_REP            147  7766.66667
              SA_REP            149  8333.33333
              ST_CLERK          123        3000
              SH_CLERK          121        3675
              MK_REP            201        6000
              PU_CLERK          114        2780
              SA_MAN            100       12200
          110 AC_ACCOUNT                   8300

DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
           90 AD_VP                       17000
           50 ST_CLERK                     2785
           80 SA_REP                 8396.55172
          110 AC_MGR                      12008
           50 ST_MAN                       7280
           80 SA_MAN                      12200
           50 SH_CLERK                     3215
           20 MK_MAN                      13000
           90 AD_PRES                     24000
           60 IT_PROG                      5760
          100 FI_MGR                      12008

DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
           30 PU_CLERK                     2780
          100 FI_ACCOUNT                   7920
           70 PR_REP                      10000
              SA_REP                       7000
           10 AD_ASST                      4400
           20 MK_REP                       6000
           40 HR_REP                       6500
           30 PU_MAN                      11000

52 rows selected.