Oralce高级sql之rollup与cube - 数据库编程

SEARCH CLERK 1980 800 RESEARCH CLERK 1987 1100 RESEARCH MANAGER 1981 2975 SALES CLERK 1981 950 SALES MANAGER 1981 2850 SALES SALESMAN 1981 5600 11 rows selected. SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal) 2 from emp a, dept b 3 where a.deptno = b.deptno 4 group by rollup(b.dname, a.job, to_char(hiredate, 'yyyy')); DNAME JOB TO_C SUM(SAL) -------------- --------- ---- ---------- SALES CLERK 1981 950 SALES CLERK 950 SALES MANAGER 1981 2850 SALES MANAGER 2850 SALES SALESMAN 1981 5600 SALES SALESMAN 5600 SALES 9400 RESEARCH CLERK 1980 800 RESEARCH CLERK 1987 1100 RESEARCH CLERK 1900 RESEARCH ANALYST 1981 3000 RESEARCH ANALYST 1987 3000 RESEARCH ANALYST 6000 RESEARCH MANAGER 1981 2975 RESEARCH MANAGER 2975 RESEARCH 10875 ACCOUNTING CLERK 1982 1300 ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 1981 2450 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 1981 5000 ACCOUNTING PRESIDENT 5000 ACCOUNTING 8750 29025 24 rows selected.

1.3 部分 rollup(Partial rollup)

当你只想统计部分字段时, 可以使用部分 rollup. 例如, group by a, rollup(b, c), 这条语句将创建三个(2 + 1)级别的小计. 分别为级别 (a, b, c), 级别 (a, b) 以及级别 (a).

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)
  2  from emp a, dept b
  3  where a.deptno = b.deptno
  4  group by b.dname, rollup(a.job, to_char(hiredate, 'yyyy'));

DNAME          JOB       TO_C   SUM(SAL)
-------------- --------- ---- ----------
SALES          CLERK     1981        950
SALES          CLERK                 950
SALES          MANAGER   1981       2850
SALES          MANAGER              2850
SALES          SALESMAN  1981       5600
SALES          SALESMAN             5600
SALES                               9400
RESEARCH       CLERK     1980        800
RESEARCH       CLERK     1987       1100
RESEARCH       CLERK                1900
RESEARCH       ANALYST   1981       3000
RESEARCH       ANALYST   1987       3000
RESEARCH       ANALYST              6000
RESEARCH       MANAGER   1981       2975
RESEARCH       MANAGER              2975
RESEARCH                           10875
ACCOUNTING     CLERK     1982       1300
ACCOUNTING     CLERK                1300
ACCOUNTING     MANAGER   1981       2450
ACCOUNTING     MANAGER              2450
ACCOUNTING     PRESIDENT 1981       5000
ACCOUNTING     PRESIDENT            5000
ACCOUNTING                          8750

23 rows selected.

从上面的结果中可以看出，部分 rollup 产生的结果:

普通的汇总行是由 group by 产生而不是 rollup不会产生总计

2. cube

cube 可以为指定的列创建各种不同组合的小计. 如果指定的列的数量为 n, group by cube 将创建 2 * n 个层次的小计. cube 是一种比 rollup 更细粒度的分组统计语句。先看看 cube 语句的结果：

2.1 什么时候使用 cube

当需求中有类似 cross-tabular report (交叉报表)时对于数据仓库中的统计汇总表, rollup 能够简化统计汇总表并且提高查询统计汇总表的速度

2.2 cube 例子

SQL> select b.dname, a.job, sum(a.sal)
  2  from emp a, dept b
  3  where a.deptno = b.deptno
  4  group by cube(b.dname, a.job);

DNAME          JOB       SUM(A.SAL)
-------------- --------- ----------
                              29025
               CLERK           4150
               ANALYST         6000
               MANAGER         8275
               SALESMAN        5600
               PRESIDENT       5000
SALES                          9400
SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
RESEARCH                      10875
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
ACCOUNTING                     8750
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000

18 rows selected.

从上面的结果可以看出，group by cube(b.dname, a.job) 语句首先统计所有部门的总薪水，然后统计每个职位(a.job)的总薪水，然后统计每个部门(b.dname)的总薪水,最后统计每个部门每个职位(b.dname, a.job)的总薪水。

如果语句为 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)，最后对全表的总计
下面演示 cube 三个字段的例子：

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)
  2  from emp a, dept b
  3  where a.deptno = b.deptno
  4  group by cube(b.dname, a.job, to_char(hiredate, 'yyyy'));

DNA

Oralce高级sql之rollup与cube(二)