SQL分析函数教程 - rollup - cube

SQL 分析函数教程 - rollup - cube

oracle 有一些令人兴奋的ANSI SQL扩展功能，可以快速实现计算聚合和上卷功能，主要语句包括：rollup、cube、group。这些简单SQL可以很容易实现聚合功能，无需我们通过存储过程实现。下面先从rollup语法开始。

使用Rollup实现分组聚合

ROLLUP语句在跨指定的维度组上计算多个级别的小计。它还计算了一个总数。ROLLUP是GROUP BY子句的一个简单扩展，因此它的语法非常容易使用。

SELECT
   deptno,
   job,
   count(*),
   sum(sal)
FROM
   emp
GROUP BY
   ROLLUP(deptno,job);

运行结果如下：

 DEPTNO JOB         COUNT(*)   SUM(SAL)
--------- --------- ---------   ---------
       10 CLERK              1       1300
       10 MANAGER            1       2450
       10 PRESIDENT          1       5000
       10                    3       8750
       20 ANALYST            2       6000
       20 CLERK              2       1900
       20 MANAGER            1       2975
       20                    5      10875

使用cube实现交叉聚合

在多维术语中，“cube”是详细行的交叉表汇总。cube语句计算一组维度所有可能组合的小计，同时还计算总计。这是所有交叉报表通常需要的一组信息，因此CUBE可以使用一条select语句计算交叉报表。

下面示例计算每一部门的每个岗位薪资汇总情况。

SELECT
   deptno,
   job,
   count(*),
   sum(sal)
FROM
   emp
GROUP BY
   CUBE(deptno,job);

结果如下：

DEPTNO JOB         COUNT(*)   SUM(SAL)
--------- --------- ---------   ---------
       10 CLERK              1       1300
       10 MANAGER            1       2450
       10 PRESIDENT          1       5000
       10                    3       8750
       20 ANALYST            2       6000
       20 CLERK              2       1900
       20 MANAGER            1       2975
       20                    5      10875
       30 CLERK              1        950
       30 MANAGER            1       2850
       30 SALESMAN           4       5600
       30                    6       9400
          ANALYST            2       6000
          CLERK              4       4150
          MANAGER            3       8275
          PRESIDENT          1       5000
          SALESMAN           4       5600
                            14      29025

总结

rollup生成的结果集比cube少，cube生产所有维度组合情况的分组聚合。一般rollup的维度有层次性，可以实现上卷和下钻功能，如年、月、日报表。

其实postgreSql、mysql、sqlserver等主流数据库都有对应分析函数（窗口函数）实现，且语法几乎一致，读者可以详细进行对比分析。