SQL 多维度聚合运算|极客教程

SQL 多维度聚合运算，你想同时进行不同维度的聚合运算。例如，你希望得到一个结果集，其中包括每个员工的名字、部门、他所在部门的员工总数（包括他自己）、和他做相同工作的员工总数（该合计值中也包括他自己），以及 EMP 表中的员工总人数。

SQL 多维度聚合运算问题描述

你想同时进行不同维度的聚合运算。例如，你希望得到一个结果集，其中包括每个员工的名字、部门、他所在部门的员工总数（包括他自己）、和他做相同工作的员工总数（该合计值中也包括他自己），以及 EMP 表中的员工总人数。最终的结果集如下所示。

ENAME  DEPTNO DEPTNO_CNT        JOB JOB_CNT  TOTAL
------ ------ ---------- --------- -------- ------
MILLER     10          3 CLERK            4     14
CLARK      10          3 MANAGER          3     14
KING       10          3 PRESIDENT        1     14
SCOTT      20          5 ANALYST          2     14
FORD       20          5 ANALYST          2     14
SMITH      20          5 CLERK            4     14
JONES      20          5 MANAGER          3     14
ADAMS      20          5 CLERK            4     14
JAMES      30          6 CLERK            4     14
MARTIN     30          6 SALESMAN         4     14
TURNER     30          6 SALESMAN         4     14
WARD       30          6 SALESMAN         4     14
ALLEN      30          6 SALESMAN         4     14
BLAKE      30          6 MANAGER          3     14

SQL 多维度聚合运算解决方案

有了窗口函数，很容易解决本问题。对于不支持窗口函数的数据库，我们可以使用标量子查询。
DB2、Oracle 和 SQL Server
使用窗口函数 COUNT OVER，按照不同的分区或分组执行聚合运算。

select ename,
       deptno,
       count(*)over(partition by deptno) deptno_cnt,
       job,
       count(*)over(partition by job) job_cnt,
       count(*)over() total
  from emp

PostgreSQL 和 MySQL
在 SELECT 列表里使用标量子查询，基于不同的分组执行聚合运算。

1  select e.ename,
2         e.deptno,
3         (select count(*) from emp d
4           where d.deptno = e.deptno) as deptno_cnt,
5         job,
6         (select count(*) from emp d
7           where d.job = e.job) as job_cnt,
8         (select count(*) from emp) as total
9    from emp e

SQL 多维度聚合运算扩展知识

DB2、Oracle 和 SQL Server
这个实例充分展示了窗口函数的威力和方便性。只要简单地指定好不同的聚合运算维度，就能轻松创建出非常详尽的报表，不需要一次又一次的自连接操作，也不需要在 SELECT 列表中编写冗长笨重、性能低下的子查询。窗口函数 COUNT OVER 就这样独力完成了全部工作。为了更深入地理解输出结果，你先仔细观察每个 COUNT 操作的 OVER 子句。

count(*)over(partition by deptno)
　
count(*)over(partition by job)
　
count(*)over( )

请记住 OVER 子句的主要组成部分：首先是分区，由 PARTITION BY 指定；然后是帧（frame）或者窗口（window），由 ORDER BY 指定。我们来看第一个 COUNT，它的分区是 DEPTNO。EMP 表的数据将按照 DEPTNO 分组，并将针对每个组执行 COUNT 操作。由于没有指定帧或窗口子句（没有 ORDER BY），分组包含的全部行都会被纳入计数范围。PARTITION BY 子句会找出所有可能的 DEPTNO 值，然后调用 COUNT 函数统计每一种 DEPTNO 对应的行数。对于本例而言，COUNT(*)OVER(PARTITION BY DEPTNO) 中的 PARTITION BY 子句能够识别出的分区值会是 10、20 和 30。
第二个 COUNT 的处理过程也是如此，只不过这次要按照 JOB 分区。最后一个 COUNT 并没有指定任何分区，只放了一个空括号。空括号是指“整张表”。因此，虽然前两个 COUNT 基于指定的分组或分区进行聚合运算，最后一个 COUNT 则会计算出整个 EMP 表的行数。

　请记住 WHERE 子句执行过后窗口函数才会被评估执行。如果我们使用 WHRE 条件先行过滤掉了部分数据，例如删除了 DEPTNO 等于 10 的员工，那么 TOTAL 值将变成 11，而不再是 14。窗口函数执行过后若还想再做一些结果过滤，就必须把窗口函数查询放进内嵌视图，并从该视图中删除我们不希望看到的数据。

PostgreSQL 和 MySQL
对于主查询返回的每一行数据（EMP E 中的行），在 SELECT 列表里使用多个标量子查询基于每个 DEPTNO 和 JOB 生成不同的计数值。为得到 TOTAL 值，只要再写一个标量子查询获取 EMP 表的员工总人数即可。