SQL经典实例（附录）窗口函数

窗口函数针对指定的行集合（分组）执行聚合运算。不同之处在于，窗口函数能够为每个分组返回多个值，而聚合函数只能返回单一值。聚合运算的对象其实是一组行记录，我们称之为“窗口”（因此才有了术语“窗口函数”）。在Oracle中成为分析函数。

窗口操作

如果要计算整个公司的员工总数，传统做法是执行count()*:

select count(*) from emp;

但是有时候我们可能需要从非聚合数据行或者从不同纬度的聚合数据行里访问这一类聚合运算结果。

select ename,
       deptno,
       count(*) over() as cnt
    from emp
order by 2;

关键字 OVER 表明 COUNT 函数会作为窗口函数来调用，而不是一次普通的聚合函数调用。

执行时机

这里我们为前一节的查询语句加上一个 WHERE 子句，以过滤掉 DEPTNO 等于 20和 30 的员工。

select ename,
       deptno,
       count(*) over() as cnt
    from emp where deptno = 10
order by 2;

该示例表明 WHERE 和 GROUP BY 这一类子句执行完之后，才轮到窗口函数执行。

分区

可以使用 PARTITION BY 子句针对行数据进行分区（partition）或者分组（group），并根据其结果执行聚合运算。我们在前面的示例中看到过，如果 OVER 关键字后面跟着一个空的圆括号，那么窗口函数执行聚合运算时，会把该查询结果集整体作为一个分区来看待。因此，我们不妨把 PARTITION BY 子句理解成“动态的 GROUP BY”，它不同于传统的 GROUP BY，因为在最终的结果集中允许出现多种由 PARTITION BY 生成的分区。
考虑如下查询语句：

select ename,
       deptno,
       count(*) over(partition by deptno) as cnt
    from emp 
order by 2;

由于使用了 PARTITION BY DEPTNO 子句，现在聚合函数 COUNT 会分别计算出每一个部门的员工人数。

相较于传统的 GROUP BY，PARTITION BY 子句的另一个好处是，在同一个 SELECT 语句里我们可以按照不同的列进行分区，而且不同的窗口函数调用之间互不影响。

如下所示的查询，它会逐一列出全体员工，并返回每一个人所属的部门，所在部门的员工总数，每一个人的职位，以及公司范围内从事相同工作的员工总数。

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;

Null的影响

类似于 GROUP BY 子句，PARTITION BY 子句会把所有的 Null 归入同一个分区或者分组。
考虑如下查询：

select coalesce(comm,-1) as comm,
    count(*)over(partition by comm) as cnt
from emp 

如果不用count()而是用count(comm)*则会有如下结果:

select coalesce(comm,-1) as comm,
    count(comm)over(partition by comm) as cnt
from emp 

聚合函数会忽略掉 NULL 值。

当使用 COUNT 函数时，我们应该思考一下是否要把 Null 包括在内。使用COUNT(column) 会忽略 Null。如果希望把 NULL 值一并计入，则应该使用COUNT(*)。（此时我们要计算的不是实际的列值，而是希望知道有多少行。）

排序

当在窗口函数的 OVER 子句中使用 ORDER BY 时，我们实际上是在决定两件事：
(1) 分区内的行数据如何排序；
(2) 计算涉及哪些行数据。
我们来看一下如下所示的查询，该查询计算出了 DEPTNO 等于 10 的员工的工资累计合计值。

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;

这个查询与下列查询等价，使用range between...and显式指定了order by hiredate默认行为方式：

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;

结果均为：

上述查询中出现的 RANGE BETWEEN 子句在 ANSI 标准中被称作 Framing 子句。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
                    range 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
                    range between current row
                    and 1 following) as run_total4
    from emp
where deptno = 10;

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;

《SQL经典实例》 附录A