【SQL】SQL窗口函数OVER()详解

该文章内容来源于https://zhuanlan.zhihu.com/p/80100130
作者：Zain Mei
仅用于个人学习，不对外公布。

写在最前：如果GROUP BY和窗口函数连用，那么窗口函数执行在GROUP BY之后，在ORDER BY之前。

本文分四个部分：
SUM(), MIN(), MAX(), AVG()等聚合函数的使用；
NTILE(), ROW_NUMBER(), RANK(), DENSE_RANK()的使用；
LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()的使用；
GROUPING SET(), WITH CUBE, WITH ROLLUP的使用。

表名：loan
字段名：name（贷款人的唯一标识），orderdate（贷款日期），amount（贷款金额）。

name    orderdate    amount
jack    2019/1/2    8000
tony    2019/8/8    6000
mart    2017/1/1    8000
neil    2018/4/11    12000
...    ...    ...

一、SUM(), MIN(), MAX(), AVG()等聚合函数，可以直接使用OVER()进行分区计算

SELECT *,
    /* 求前三次贷款的金额之和 */
    SUM(amount) OVER(PARTITION BY name 
                     ORDER BY orderdate
                     ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv1,
    /* 求历史所有贷款 累加到下一次贷款 的金额之和 */
    SUM(amount) OVER(PARTITION BY name
                     ORDER BY orderdate
                     ROWS BETWEEN UNBOUNED PRECEDING AND 1 FOLLWING) AS pv2
FROM loan;

1. 限定窗口

使用窗口函数OVER()可以让聚合函数在限定的窗口中进行聚合。
窗口的限定语法（称为WINDOW子句）为：

ROWS BETWEEN '时间点' AND '时间点';

时间点的使用方法是：

n PRECEDING ; /* 前n行 */
m PRECEDING ; /* 后m行 */
CURRENT ROW ; /* 当前行 */
UNBOUNDED PRECEDING ; /* 不限制具体行数，起始行 */

2. OVER()与GROUP BY的区别

根据某一字段GROUP BY之后，其余字段都必须按照此分区进行计算；
而OVER()使得单个字段可以进行分区。

二、NTILE(), ROW_NUMBER(), RANK(), DENSE_RANK()，可以为数据集新增加序列号

SELECT *,
    /* 按name将数据切分成10个区域，并返回属于第几个区 */
    NTILE(10) OVER(PARTITION BY name
                   ORDER BY orderdate) AS f1,
    /* 按照name分区，并按照orderdate进行排序，并返回排序编号 */
    ROW_NUMBER() OVER(PARTITION BY name
                      ORDER BY orderdate) AS f2,  
    RANK() OVER(PARTITION BY name
                ORDER BY orderdate) AS f3, 
    DENSE_RANK() OVER(PARTITION BY name
                      ORDER BY orderdate) AS f4
FROM loan;

1. NTILE()函数
指定“桶”的个数，对数据进行分桶，并且返回“桶”的序号。
给记录进行编号遵守两个原则：
第一，序号小的记录数不能小于序号大的记录数；
第二，所有桶的记录数要么相同，要么从某一个桶开始后面所有桶的记录数都与该桶相同。

2. 三种排序函数的区别
ROW_NUMBER()返回的是一组连续的序号；
RANK()对数值相同的记录标记相同的序号（并列），并且会跳过下一个序号；
DENSE_RANK()对数值相同的记录标记相同的序号（并列），但不会跳过下一个序号。

三、LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()返回一系列指定的点

SELECT *,
    /* 取上一笔贷款的日期，缺失默认值为NULL */
    LAG(orderdate, 1) OVER(PARTITION BY name
                           ORDER BY orderdate) AS last_dt,
    /* 取下一笔贷款的日期，缺失指定值为'1970-1-1' */
    LEAD(orderdate, 1, '1970-1-1') over(PARTITION BY name
                                        ORDER BY orderdate) AS next_dt,
    /* 取最早一笔贷款的日期 */
    FIRST_VALUE(orderdate) over(PARTITION BY name
                                ORDER BY orderdate) AS first_dt,
    /* 取最新一笔贷款的日期 */
    LAST_VALUE(orderdate) over(PARTITION BY name
                               ORDER BY orderdate) AS last_dt
FROM loan;

LAG(字段名, n, [默认值])将数据向前错位n行；
LEAD(字段名, n, [默认值])将数据向后错位n行；
FIRST_VALUE(字段名)取当前分区的第一个值；
LAST_VALUE(字段名)取当前分区的最后一个值。

四、使用GROUPING SET(), WITH CUBE, WITH ROLLUP对GROUP BY进行限制

1. GROUPING SET操作

SELECT A, B, C
FROM loan
/* 分别按照月份和日进行分区 */
GROUP BY SUBSTRING(orderdate, 1, 7), orderdate
GROUPING SETS(SUBTRING(orderdate, 1, 7), orderdate)
ORDER BY GROUPING_ID;

GROUPING_ID是GROUPING SET操作后自动生成的。
它的作用是为了区分每条输出结果是属于哪个GROUP BY的数据，它是根据GROUP BY后面声明的顺序字段 是否存在于当前GROUP BY中 的一个二进制位组合数据。
GROUPING SETS()必须先做GROUP BY操作。

例：
(A, C)的GROUPING_ID为：
GROUPING_ID(A, C) = GROUPING(A)+GROUPING(B)+GROUPING(C) = 101(二进制，即5)

如果解释器发现GROUP BY A, C，但是SELECT A, B, C，那么运行时会将所有FROM表取出的结果复制一份，B都置为NULL，也就是在结果中，B都为NULL。

2. WITH CUBE操作

SELECT A, B, C
FROM loan
/* 分别按照月份和日进行分区 */
GROUP BY SUBSTRING(orderdate, 1, 7), orderdate
WITH CUBE
ORDER BY GROUPING_ID;

WITH CUBE和GROUP SET()的区别就是前者返回的是GROUP BY字段的笛卡尔积。

3. WITH ROLLUP操作

SELECT A, B, C
FROM loan
/* 分别按照月份和日进行分区 */
GROUP BY SUBSTRING(orderdate, 1, 7), orderdate
WITH ROLLUP
ORDER BY GROUPING_ID;

WITH ROLLUP则不会产生第二列为键的聚合结果。

4 字符串截取函数
SUBSTRING(string A, int start, int len)
注意SQL的索引从1开始。