认识窗口函数

窗口函数也称为OLAP（Online Anallytical Processing）函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

flowchart LR
    a(窗口函数) -->  b[窗口函数名] --> b1>静态窗口函数]
    b -->b2>滑动窗口函数]
    a --> c[窗口函数数据集]--- d[over]
    
    b1>静态窗口函数]--- sort[排名函数] --- row_number
    sort--- dense_rank
    sort--- rank
    sort --- ntile
    
    d---part[partitiono by]
    d --- order[order by]
    d---rows[rows/range]
    d --- preceding[preceding]---b2
    d --- following[following]---b2

flowchart LR
    b2>滑动窗口函数]
    b2-->cal["计算函数：
    MIN()、MAX()、SUM()、COUNT()、AVG()、
    STDDEV_POP()、STDDEV_SAMP()、MEDIAN()"]
    b2-->get["取值函数：
    LISTAGG()、FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()"]
    
    

什么叫窗口？

窗口可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。

• 静态窗口：不同的记录对应相同大小的窗口，这种属于静态窗口。
• 滑动窗口：不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

窗口函数的简单语法

窗口函数可以在保留原表中的全部数据的情况下，对某些字段做分组排序或者计算，而GROUP BY只能保留与分组字段聚合的结果。

<窗口函数名> OVER (PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名>)

窗口函数的执行顺序

graph LR
    A[FROM]-->B[WHERE]-->C[GROUP BY]-->D[HAVING]-->E[SELECT]-->F[WINDOW]-->G[ORDER BY]-->H[LIMIT]

静态窗口函数（排名函数）

ROW_NUMBER()

当前分组内<font color="red">连续不重复</font>排序

SELECT 
    GRID_ID,
    STAFF_AGE,
    ROW_NUMBER() OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) ROW_NUM
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

RANK()

当前分组内<font color="red">不连续重复</font>排序

SELECT 
    GRID_ID,
    STAFF_AGE,
    RANK() OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) RANK_NUM
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

DENSE_RANK()

当前分组内<font color="red">连续重复</font>排序

SELECT 
    GRID_ID,
    STAFF_AGE,
    DENSE_RANK() OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) DENSE_RANK_NUM
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

NTILE()

• 函数名： NTILE()
• 语法：NTILE(n)
• 作用：用于<font color="red">将分组数据按照顺序切分成n片，返回当前切片值。</font>将一个有序的数据集划分为多个桶(bucket)，并为每行分配一个适当的桶数（切片值，第几个切片，第几个分区等概念）。它可用于将数据划分为相等的小切片，为每一行分配该小切片的数字序号。

SELECT 
    PROV_ID,
    GRID_ID,
    STAFF_AGE,
    NTILE(4) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) NTILE_NUM
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

从上图可以看出，

滑动窗口函数

计算函数

统计

SELECT 
    GRID_ID,
    STAFF_AGE,
    COUNT(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) AVG_AGE
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

累加

SELECT 
    GRID_ID,
    STAFF_AGE,
    SUM(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) AVG_AGE
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

求平均

SELECT 
    GRID_ID,
    STAFF_AGE,
    AVG(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) AVG_AGE
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

取值函数

分组第一个值

• 函数名： FIRST_VALUE()

SELECT 
    GRID_ID,
    STAFF_AGE,
    FIRST_VALUE(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) FIRST_VAL
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

分组最后一个值

• 函数名 ： LAST_VALUE()

SELECT 
    GRID_ID,
    STAFF_AGE,
    LAST_VALUE(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) LAST_VAL
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

分组第N个值

• 函数名： NTH_VALUE()

SELECT 
    GRID_ID,
    STAFF_AGE,
    NTH_VALUE(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) NTH_VAL
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

合并多行数据

• 函数名： LISTAGG()
• 语法： LISTAGG(measure_expr[, 'delimiter']) WITHIN GROUP (order_by_clause) OVER query_partition_clause
• 返回类型： VARCHAR

SELECT 
    GRID_ID,
    STAFF_AGE,
    LISTAGG(STAFF_NAME,',') WITHIN GROUP(ORDER BY STAFF_CODE) OVER(PARTITION BY GRID_ID ) LIST_AGG_NAME
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

从上图可以看出，在每一行都会<font color="red">合并当前分组内的所有数据</font>。

窗口数据集

OVER 子句 用于为行为定义一个窗口（WINDOW) ，以便于进行特定的运算，可以<font color="red">把行的窗口简单认为是运算要操作的数据集。</font>

写窗口函数时，ORDER BY 后面可以有参数，ROWS/RANGE 和 PRECEDING/FOLLOWING，在组合使用这些参数后，窗口就会变成滑动窗口，因为涉及到动态窗口，所以在理解上比较抽象。

PRECEDING

PRECEDING 的意思是之前 N 行，理解为不含当前行的情况下，截至到之前 N行 ，这种方法叫做 <font color="red">移动平均</font> 。

SELECT 
    GRID_ID,
    STAFF_AGE,
    AVG(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE ROWS 2 PRECEDING) ROW_NUM
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

从上图可以看出，在每一行都会求出 （当前行+前两行）数据的平均值。

FOLLOWING

SELECT 
    GRID_ID,
    STAFF_AGE,
    AVG(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE 
                        ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING ) PRE_FOLLOW_AVG
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

从上图可以看出，在每一行都会求出 （当前行+当前行后两行） 数据的平均值。

PRECEDING 和 FOLLOWING 连用

SELECT 
    GRID_ID,
    STAFF_AGE,
    AVG(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE 
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) PRE_FOLLOW_AVG
FROM 
    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO 
WHERE MONTH_ID='202111' AND PROV_ID='076';

从上图可以看出，在每一行都会求出 （当前行前一行+当前行+当前行后一行） 数据的平均值。

注意事项

1. 原则上窗口函数只能写在 SELECT 子句中
2. 窗口函数名的括号不能漏掉
3. PARTITION BY 可以省略，但是结果就不再分组。
4. NTILE 不支持 ROWS BETWEEN。

应用真题解析

题目1：找出每个部门工资第二高的员工

现有一张公司员工信息表 <u>EMPLOYEE</u> ，表中包含如下 4 个字段。

EMPLOYEE表的<u>数据如下表</u>所示：

还有一张部门信息表department，表中包含如下两个字段。

department_id（部门ID）：VARCHAR。

department_name（部门名称）：VARCHAR。

department表的数据如下表所示。

数据导入格式

DROP TABLE IF EXISTS employee;
CREATE TABLE employee(
    mployee_id VARCHAR(8),
    employee_name VARCHAR(8),
employee_salary INT(8),
department VARCHAR(8)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
employee (employee_id,employee_name,employee_salary,department) 
VALUE ('a001','Bob',7000,'b1')
     ,('a002','Jack',9000,'b1')
     ,('a003','Alice',8000,'b2')
     ,('a004','Ben',5000,'b2')
     ,('a005','Candy',4000,'b2')
     ,('a006','Allen',5000,'b2')
     ,('a007','Linda',10000,'b3');
     
DROP TABLE IF EXISTS department;
CREATE TABLE department(
department_id VARCHAR(8),
department_name VARCHAR(8)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
department (department_id,department_name) 
VALUE ('b1','Sales')
     ,('b2','IT')
     ,('b3','Product');