附录A 窗口函数

A.5 窗口操作

就像聚合函数一样，窗口函数针对指定的行集合(分组)执行聚合运算。不同之处在于，窗口函数能够为每个分组返回多个值，而聚合函数只能返回单一值。聚合运算的对象其实是一组行记录，我们称之为窗口。
计算整个公司的员工人数，传统做法是针对EMP调用COUNT(*).

SELECT COUNT(*) AS cnt FROM emp;

有时候我们需要从非聚合数据行或者不同维度的聚合数据行里访问这一类聚合运算结果。窗口函数能帮助我们轻松完成这一类操作。例如，下面的查询语句展示了如何使用窗口函数同时检索出明细行(每个员工一行)和聚合运算结果(员工总人数)

mysql> SELECT ename,deptno,count(*) over () AS cnt FROM emp ORDER BY 2;
+--------+--------+-----+
| ename  | deptno | cnt |
+--------+--------+-----+
| KING   |     10 |  14 |
| MILLER |     10 |  14 |
| CLARK  |     10 |  14 |
| SCOTT  |     20 |  14 |
| FORD   |     20 |  14 |
| JONES  |     20 |  14 |
| SMITH  |     20 |  14 |
| ADAMS  |     20 |  14 |
| MARTIN |     30 |  14 |
| TURNER |     30 |  14 |
| BLAKE  |     30 |  14 |
| ALLEN  |     30 |  14 |
| JAMES  |     30 |  14 |
| WARD   |     30 |  14 |
+--------+--------+-----+
14 rows in set (0.04 sec)

上述示例调用了窗口函数COUNT(*) OVER() 。关键字OVER 表明COUNT函数会作为窗口来调用，而不是一次作为普通的聚合函数调用。基本上，SQL标准中列出的全部聚合函数都能用作窗口函数，关键字OVER的作用是帮助语法解析器区分不同的使用场景。
COUNT(*) OVER 到底做了什么操作？ 它为上述查询语句返回的每一行数据提供了额外的一列。该列表示EMP表一共有多少行记录。这里关键字OVER后面的圆括号是空的，其实我们也可以在里面放上一个额外的子句，以指明窗口函数操作的行记录范围。保持括号为空，这里明确告诉窗口函数把全体行记录Wie操作对象。因此上述每一行输出的结构都是14。

A.6 执行时机

窗口函数的执行会被安排在整个SQL处理的最后一步，但会先于ORDER BY 子句执行。下面举例说明窗口函数的执行时机。

mysql> SELECT ename,deptno,count(*) over () AS cnt FROM emp WHERE deptno=10 ORDER BY 2;
+--------+--------+-----+
| ename  | deptno | cnt |
+--------+--------+-----+
| CLARK  |     10 |   3 |
| KING   |     10 |   3 |
| MILLER |     10 |   3 |
+--------+--------+-----+
3 rows in set (0.04 sec)

每一行的CNT值不再是14，而是变成了3。在上述示例中，正是因为WHERE子句先将结果集限制为3，才导致了窗口函数的返回值为3(当SQL处理到了SELECT 子句的时候，就只剩3行数据留给窗口行数了)。该示例表明WHERE和GROUP BY这一类子句执行完之后，才轮到窗口函数执行。

A.7 分区

可以使用PARTITION BY 子句针对行数据进行分区(partition) 或者分组(group) ，并根据其结果执行聚合运算。正如之前琐事，如果OVER关键字后面跟着一个空的圆括号，那么窗口函数执行聚合运算时，会把该查询结果集单做整体作为一个分区看待。因此，可以把PARTITION BY子句理解成“动态的 group by”。它不同于传统的GROUP BY，因为在最终的结果集中允许出现多种由PATTITION BY生成的分区。借助PARTITION BY针对指定的行数据进行分区和聚合运算(新的分区出现时，聚合运算结果会被重新计算)，则所有值(每一个分区的每一个值)都会被返回，而不会只返回一组具有代表性的行记录。

mysql> SELECT ename,deptno,count(*) over (PARTITION BY deptno) AS cnt FROM emp ORDER BY 2;
+--------+--------+-----+
| ename  | deptno | cnt |
+--------+--------+-----+
| KING   |     10 |   3 |
| MILLER |     10 |   3 |
| CLARK  |     10 |   3 |
| SCOTT  |     20 |   5 |
| FORD   |     20 |   5 |
| JONES  |     20 |   5 |
| SMITH  |     20 |   5 |
| ADAMS  |     20 |   5 |
| MARTIN |     30 |   6 |
| TURNER |     30 |   6 |
| BLAKE  |     30 |   6 |
| ALLEN  |     30 |   6 |
| JAMES  |     30 |   6 |
| WARD   |     30 |   6 |
+--------+--------+-----+
14 rows in set (0.05 sec)

上述查询仍然会返回EMP表的全部14行记录，但是由于使用了PARTITION BY DEPTNO子句，现在聚合函数COUNT会分别计算每一个部门的员工人数，只有当部门发生变化时，聚合运算的额结果才会被重新计算，因此同一个部门(同一个分区)的员工会得到相同的CNT值。同时我们也要注意到，每一个分区的信息都会被返回，每一个分区的所有成员也都会被返回，和下面的查询语句相比，上市使用窗口函数查询更高效。

select e.ename,
       e.deptno,
       (select count(*) from emp d where e.deptno = d.deptno) as cnt
  from emp e
 order by 2

相较于传统的GROUP BY，PARTITION BY子句的另一个好处是，在同一个SELECT语句里我们可以按照不同的列进行分区，而且不同的窗口函数调用之间互不影响。如下查询，它会逐一列出全体员工，并返回每一个人所属的部门，所在部门的员工总数，每一个人的职位，以及公司范围内从事相同工作的员工总数。

mysql> select ename,
       deptno,
       count(*) over(partition by deptno) as dept_cnt,
       job,
       count(*) over(partition by job) as job_cnt
  from emp
 order by 2
;
+--------+--------+----------+-----------+---------+
| ename  | deptno | dept_cnt | job       | job_cnt |
+--------+--------+----------+-----------+---------+
| KING   |     10 |        3 | PRESIDENT |       1 |
| MILLER |     10 |        3 | CLERK     |       4 |
| CLARK  |     10 |        3 | MANAGER   |       3 |
| SCOTT  |     20 |        5 | ANALYST   |       2 |
| FORD   |     20 |        5 | ANALYST   |       2 |
| SMITH  |     20 |        5 | CLERK     |       4 |
| ADAMS  |     20 |        5 | CLERK     |       4 |
| JONES  |     20 |        5 | MANAGER   |       3 |
| JAMES  |     30 |        6 | CLERK     |       4 |
| MARTIN |     30 |        6 | SALESMAN  |       4 |
| TURNER |     30 |        6 | SALESMAN  |       4 |
| WARD   |     30 |        6 | SALESMAN  |       4 |
| BLAKE  |     30 |        6 | MANAGER   |       3 |
| ALLEN  |     30 |        6 | SALESMAN  |       4 |
+--------+--------+----------+-----------+---------+
14 rows in set (0.05 sec)

A.8 Null的影响

类似于GROUP BY ，PARTITION BY子句会把所有的Null归入同一个分区或者分组。因此，PARTITION BY对Null值的影响也类似于GROUP BY。如下：计算每一种业务提成对应的员工人数(为增强可读性，当业务提成为Null时，返回-1)。

mysql> select coalesce(comm,-1) as comm,
count(*)over(partition by comm) as cnt
from emp;
+------+-----+
| comm | cnt |
+------+-----+
|   -1 |  10 |
|   -1 |  10 |
|   -1 |  10 |
|   -1 |  10 |
|   -1 |  10 |
|   -1 |  10 |
|   -1 |  10 |
|   -1 |  10 |
|   -1 |  10 |
|   -1 |  10 |
|    0 |   1 |
|  300 |   1 |
|  500 |   1 |
| 1400 |   1 |
+------+-----+
14 rows in set (0.04 sec)

上述查询使用了COUNT(*)。因而返回是相应的记录行数。如果使用COMM列的话，返回结果大相径庭

mysql>  select coalesce(comm,-1) as comm,
count(comm)over(partition by comm) as cnt
from emp;
+------+-----+
| comm | cnt |
+------+-----+
|   -1 |   0 |
|   -1 |   0 |
|   -1 |   0 |
|   -1 |   0 |
|   -1 |   0 |
|   -1 |   0 |
|   -1 |   0 |
|   -1 |   0 |
|   -1 |   0 |
|   -1 |   0 |
|    0 |   1 |
|  300 |   1 |
|  500 |   1 |
| 1400 |   1 |
+------+-----+
14 rows in set (0.05 sec)

使用COUNT(COMM)，它只会计数不为Null的COMM值。

聚合函数仅仅计数非NUll值。

A.9 排序

在使用窗口函数的时候，数据的排序方式可能会对最终的查询结果产生实质性的影响。因此，窗口函数的OVER子句也支持ORDER BY语法。我们可以使用ORDER BY子句指定分区内的行数据如何排序(如果 OVER关键字没有出现PARTITION BY子句，此处的 分区 指的是整查询个结果集)。
当在窗口函数的OVER子句中使用ORDER BY 时，实际上是在决定两件事。

1. 分区内的行数据如何排序
2. 计算涉及哪些行的数据。

计算出了 DEPTNO 等于 10 的员工的工资累计合计值。

-- 以下查询是使用mariadb查询的，在oracle中，是严格按照时间排序的。
mysql> select deptno,
       ename,
       hiredate,
       sal,
       sum(sal) over(partition by deptno) as total1,
       sum(sal) over() as total2,
       sum(sal) over(order by hiredate) as running_total
  from emp
 where deptno = 10
;
+--------+--------+------------+------+--------+--------+---------------+
| deptno | ename  | hiredate   | sal  | total1 | total2 | running_total |
+--------+--------+------------+------+--------+--------+---------------+
|     10 | MILLER | 1982-01-23 | 1300 | 8750   | 8750   | 1300          |
|     10 | CLARK  | 1981-06-09 | 2450 | 8750   | 8750   | 3750          |
|     10 | KING   | 1981-11-17 | 5000 | 8750   | 8750   | 8750          |
+--------+--------+------------+------+--------+--------+---------------+
3 rows in set (0.03 sec)

上述，其中一个SUM查询后面的圆括号是空的。为什么TOTAL1和TOTAL2查询结果相同？窗口函数的执行时机决定了上述结果，经过WHERE子句的过滤，合计的对象就只剩下DEPTNO等于10的工资了。在上述中只存在一个分区，即整个结果集只包括DEPTNO等于10的记录，因此TOTAL1和TOTAL2具有相等的值。

上述RUNNING_TOTAL 是累加值。OVER子句里加上ORDER BY之后，实际上确实在分区内部指定了一个默认的“滑动窗口”。正是由于ORDER BY HIREDATE子句的存在使得合计运算能够自动终止于当前行。

-- mysql支持 mariadb不支持
select deptno,
       ename,
       hiredate,
       sal,
       sum(sal) over(partition by deptno) as total1,
       sum(sal) over() as total2,
       sum(sal) over(order by hiredate range between unbounded preceding and current row) as running_total
  from emp
 where deptno = 10

mariadb不支持上述，它的写法是这样的

CREATE TABLE `student_test` (
  name char(10),
  test char(10),
  score tinyint(4)
);

INSERT INTO student_test VALUES 
    ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
    ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
    ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
    ('Tatiana', 'SQL', 87);

SELECT name, test, score, SUM(score) 
  OVER (ORDER BY score RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_score 
  FROM student_test ORDER BY score;

mariadb大概是 ORDER BY 之后的 那个是 聚合函数的那一部分。

上述查询出现的 RANGE BETWEEN子句在ANSI标准中被称为Framing子句。

A.10 Framing子句

以之前章节的结果集，解释Framing子句的工作原理如下，先从最早入职的员工CLARK开始。

1. 首先CLARK的工作是2450，我们应该把入职时间早于CLARK的所有员工的工资一并找出来相加求和。但是CLARK是DEPTNO等于10的部门最早入职的员工。因此这里只要针对CLARK的工资2450进行求和即可。这是上述查询第一行gRUNNING_TOTAL值的由来。
2. 按照HIREDATE排序的话。下一个员工是KING，我们看一下Framing子句针对他做了哪些操作。合计运算从当前行的工资5000(KING的工资)开始，在此之前的行数据(入职早于KING的员工的工资)也要包括在内。因而合计是5000+245-=7450。这就是上述查询结果第二行RUNNING_TITAL值的由来。
3. 按照HIDERDATE排序的话，分区中最后一个员工是MILLER，我们再看一下Framing 子句针对他做了哪些操作。 SAL 列的合计运算从当前行的工资 1300（MILLER 的工资）开始，在此之前的行数据（入职时间早于MILLER的员工的工资）也要包括在内。CLARK和KING都比MILLER入职时间早，因此MILLER对应的RUNNING_TOTAL要把他们两个人的工资也计入在内：2450+5000+1300，计算结果是 8750，这就是上述查询结果中 MILLER 这一行对应的 RUNNING_TOTAL 值的由来。

正是Framing子句帮助我们生成了累积合计值。除了规定计算的顺序，ORDER BY同时也指定了一个默认的滑动窗口。

通常而言，Framing子句能定义动态变化的“数据子窗口”，并将其融入聚合运算。我们可以使用多种形式的语法指定数据子窗口。

select deptno,
       ename,
       hiredate,
       sal,
       sum(sal) over(order by hiredate range between unbounded preceding and current row) as run_total1,
       sum(sal) over(order by hiredate rows between 1 preceding and current row) as run_total2,
       sum(sal) over(order by hiredate range between current row and unbounded following) as run_total3,
       sum(sal) over(order by hiredate rows between current row and 1 following) as run_total4
  from emp
 where deptno = 10 ;
 ---------------------

    DEPTNO ENAME      HIREDATE           SAL RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3 RUN_TOTAL4
---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
        10 CLARK      1981-06-09        2450       2450       2450       8750       7450
        10 KING       1981-11-17        5000       7450       7450       6300       6300
        10 MILLER     1982-01-23        1300       8750       6300       1300       1300

select ename,
       sal,
       min(sal) over(order by sal) min1,
       max(sal) over(order by sal) max1,
       min(sal) over(order by sal range between unbounded preceding and unbounded following) min2,
       max(sal) over(order by sal range between unbounded preceding and unbounded following) max2,
       min(sal) over(order by sal range between current row and current row) min3,
       max(sal) over(order by sal range between current row and current row) max3,
       max(sal) over(order by sal rows between 3 preceding and 3 following) max4
  from emp;

A.12 代码可读性+性能=威力

窗口函数非常强大，使用它写出的查询语句能够把明细数据和聚合运算结果融为一体。

-- 每个部门有多少名员工，每个部门有多少不同的职位

select deptno,
       job,
       count(*) over(partition by deptno) as emp_cnt,
       count(job) over(partition by deptno, job) as job_cnt,
       count(*) over() as total
  from emp;