SQL窗口函数

窗口函数

窗口函数的基本概念

1. 窗口函数的作用

窗口函数本质是在线分析处理（OLAP）函数，相比常用的SQL语句可以实现更加复杂的业务功能，例如产品的累计销售额统计、分类排名、同比/环比分析等，这些功能通常很难通过聚合函数和分组操作来实现，但是应用窗口函数却可以轻松解决。窗口函数的最大特点在于不会改变原始查询结果的行数，这使得窗口函数不会像聚合函数那样使得一部分数据“不可见”，这个特性也使得窗口函数可以配合SQL的嵌套查询实现更加灵活的功能。

2. 窗口函数的语法

window_function(agrs) over([partition by expr][order by expr [ASC|DESC]][frame])

• window_function()为可实现窗口的应用函数，根据效用可以分为以下三类。
  • 排序窗口函数：包括ROW_NUMBER()、RANK()、DENSE_RANK()、PERCENT_RANK()、CUME_DIST()、NTILE()。
  • 聚合窗口函数：包括AVG()、SUM()、COUNT()、MAX()、MIN()。
  • 取值窗口函数：包括LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()。
• over()表示前方的函数为一个窗口函数，若括号内为空则表示将所有数据作为一个分组进行汇总。
• partition by为分组关键词，会根据后方的参数指定本次窗口函数执行依据的分组字段。
• order by为排序关键词，会根据后方的参数指定本次窗口函数执行结果呈现的排序方式，默认ASC升序。
• frame用于指定一个移动的分析窗口，窗口总是位于分区的范围之内，是分区的一个子集。在指定了分析窗口之后，窗口函数不再基于分区进行分析，而是基于窗口内的数据进行分析。

3. 移动窗口

移动窗口需要在窗口函数语法的末尾指定，共分为两种模式，其中ROWS表示以数据行为单位计算窗口的偏移量，RANGE表示以数值（例如10天、5km等）为单位计算窗口的偏移量。

-- 移动窗口形式一（移动窗口从指定行开始到当前行结束）
{ROWS|RANGE} frame_start

-- 移动窗口形式二（移动窗口从指定行开始到指定行结束）
{ROWS|RANGE} between frame_start and frame_end end

• frame_start代表移动窗口的起始位置，可选值为：
  • UNBOUNDED PRECEDING表示窗口从分区的第一行开始。
  • N PRECEDING表示窗口从当前行之前的第N行开始。
  • CURRENT ROW表示窗口从当前行开始。
• frame_end代表移动窗口的结束位置，可选值为：
  • CURRENT ROW表示窗口到当前行结束。
  • M FOLLOWING表示窗口到当前行之后的第M行结束。
  • UNBOUNDED FOLLOWING表示窗口到分区的最后一行结束。
• 若不指定则默认between UNBOUNDED PRECEDING and CURRENT ROW end

4. 应用窗口函数的思维范式

• 根据应用场景判断使用何种窗口函数。
• 根据应用场景判断是否需要指定移动窗口。
• 确定分组字段，若无则跳过partition by。
• 确定排序字段及排序方式。

5. 窗口函数与聚合函数的区别

• 语法不同
  聚合函数通常需要搭配关键词group by和having使用；而窗口函数的关键词为over、partition by等。

• 作用范围不同
  聚合函数的作用范围是整个整个数据表或者数据集；而窗口函数的作用范围是一组记录（即窗口）。

• 返回结果不同
  聚合函数的计算结果只有一个，代表着原始数据集应用聚合函数计算的下钻结果；而窗口函数会保留原始数据集的行数。

• 性能消耗不同
  窗口函数通常比聚合函数更消耗资源，因为它需要在每个行上进行计算，而聚合函数只需要计算一次。

排序窗口函数的应用

1. 排序窗口函数详解

• ROW_NUMBER()可以为分区中的每行数据分配一个序列号，序列号从1开始，且不会重复。形如1,2,3,4,…。
• RANK()返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。形如1,2,2,4,…。
• DENSE_RANK()返回当前行在分区中的名次。即使存在名次相同的数据，后续的排名也是连续值。形如1,2,2,3,…。
• PERCENT_RANK()以百分比的形式返回当前行在分区中的名次，计算方式为：（分组内当前行RANK值-1）/（分组内总行数-1），值域为[0,1]。如果存在名次相同的数据，后续的排名将会产生跳跃。
• CUME_DIST()计算并返回当前行在分区内的累积分布，计算方式为：分组内小于等于当前值的行数/分组内总行数，值域为[0,1]。
• NTILE(N)调用时需要传入参数N，将分区内的数据分为N等份，并返回当前行所在的分片位置，当切片不均时，默认增加第一个切片的分布。

2. TopN问题

eg：

原题链接：184. 部门工资最高的员工 - 力扣（LeetCode）

查找出每个部门（departmentId）中薪资（salary）最高的员工部门（department）、员工姓名（name）及薪资。

SELECT S.NAME, S.EMPLOYEE, S.SALARY
FROM (
    SELECT D.NAME,
           T.NAME EMPLOYEE,
           T.SALARY,
           ROW_NUMBER() OVER(PARTITION BY T.DEPARTMENTID ORDER BY T.SALARY DESC) RN
    FROM EMPLOYEE T
    JOIN DEPARTMENT D
    ON T.DEPARTMENTID = D.ID) S
WHERE S.RN = 1;

3. TopN%问题

eg：

销量表

PRODUCT_ID	SALES_VOLUME
1	200
2	300
3	221
4	337
5	104
6	206
7	303
8	780
9	693
10	892

查找销量（SALES_VOLUME）占比前10%的商品信息。

-- 方法一：
SELECT *
FROM (
    SELECT PRODUCT_ID,
	   	   PERCENT_RANK() OVER(ORDER BY SALES_VOLUME DESC) AS RANK_PERCENT
	FROM SALES
) TMP
WHERE RANK_PERCENT <= 0.1;

-- 方法二：
WITH TMP AS (
    SELECT PRODUCT_ID,
    	   ROW_NUMBER() OVER(ORDER BY SALES_VOLUME DESC) AS RANK
    FROM SALES
)
SELECT * FROM TMP AS A,
LEFT JOIN (
    -- 求排名阈值
    SELECT CEILING(COUNT(1) * 0.1) AS RANK_BAR
    FROM SALES
) AS B
ON 1=1
WHERE A.RANK <= B.RANK_BAR;

-- 方法三：
SELECT * 
FROM (
    SELECT PRODUCT_ID,
	       NTILE(10) OVER(ORDER BY SALES_VOLUME DESC) AS NTILE_RANK
	FROM SALES
) TMP
WHERE NTILE_RANK <= 1;

聚合窗口函数的应用

1. 移动平均值问题

AVG(expr)函数在作为窗口函数使用时，可以用于计算随着当前行移动的窗口内数据行的平均值。

eg：

产品表

PRODUCT	LOG_MONTH	DEPARTMENT	GMV
bags	2021-10	B	45
bags	2021-11	B	51
bags	2021-12	B	62
bags	2022-01	B	56
bags	2022-02	B	58
bags	2022-03	B	59
makeup	2021-10	A	69
makeup	2021-11	A	180
makeup	2021-12	A	230
makeup	2022-01	A	245
makeup	2022-02	A	120
makeup	2022-03	A	300
toys	2021-10	B	24
toys	2021-11	B	23
toys	2021-12	B	25
toys	2022-01	B	20
toys	2022-02	B	10
toys	2022-03	B	54
…	…	…	…

滚动求近三个月每一产品的平均GMV、每一产品从上架开始到上月的平均GMV以及每一产品从上架开始到本月的平均GMV。

SELECT *,
	   AVG(GMV) OVER(PARTITION BY PRODUCT, DEPARTMENT ORDER BY LOG_MONTH ROWS 2 PRECEDING) AS ROLLIING_3_MONTH_AVG_GMV,
	   AVG(GMV) OVER(PARTITION BY PRODUCT, DEPARTMENT ORDER BY LOG_MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING END) AS AVG_GMV_BEFORE_CURRENT,
	   AVG(GMV) OVER(PARTITION BY PRODUCT, DEPARTMENT ORDER BY LOG_MONTH) AS AVG_GMV
FROM PRODUCTS
ORDER BY PRODUCT, LOG_MONTH;

2. 累计求和问题

SUM(expr)函数在作为窗口函数使用时，可以用于统计指定窗口内的累计值。

eg：

产品表如上例，求每一产品截至当前月份的累计GMV。

SELECT *,
	   SUM(GMV) OVER(PARTITION BY PRODUCT, DEPARTMENT ORDER BY LOG_MONTH) AS ROLLING_SUM_GMV
FROM PRODUCTS
ORDER BY PRODUCT, LOG_MONTH;

3. 历史最高/最低问题

MAX(expr)/MIN(expr)函数在作为窗口函数使用时，可以统计某一指标的历史最高/最低取值。

eg：

产品表如上例，求每一产品历史最高/最低GMV。

SELECT *,
	   MAX(GMV) OVER(PARTITION BY PRODUCT, DEPARTMENT ORDER BY LOG_MONTH ROWS BETWEEN UNBOUNDED PRECENDING AND CURRENT ROW END) AS HIST_HIGH_GMV,
	   MIN(GMV) OVER(PARTITION BY PRODUCT, DEPARTMENT ORDER BY LOG_MONTH ROWS BETWEEN UNBOUNDED PRECENDING AND CURRENT ROW END) AS HIST_LOW_GMV
FROM PRODUCTS
ORDER BY PRODUCT, LOG_MONTH;

取值窗口函数的应用

1. 取值窗口函数详解

• LAG(expr, N)返回窗口内当前行之前第N行的指定列数据。
• LEAD(expr, N)返回窗口内当前行之后第N行的指定列数据。
• FIRST_VALUE(expr)返回窗口内第一行的指定列数据。
• LAST_VALUE(expr)返回窗口内最后一行的指定列数据。
• NTH_VALUE(expr, N)返回窗口内第N行的指定列数据。

注意：LAG()与LEAD()不支持动态窗口大小，以整个分区作为分析窗口。

2. 环比问题

环比增长率计算公式：（本期-上一期）/上一期*100%。

eg：

产品表如上例，求每一产品GMV的环比增长率。

SELECT *,
	   LAG(GMV, 1) OVER(PARTITION BY PRODUCT, DEPARTMENT ORDER BY LOG_MONTH) AS LAG_GMV
	   CAST(GMV AS DOUBLE) / LAG(GMV, 1) OVER(PARTITION BY PRODUCT, DEPARTMENT ORDER BY LOG_MONTH) - 1 AS GROWTH_RATE
FROM PRODUCTS
ORDER BY PRODUCT, LOG_MONTH;

3. 同比问题

同比增长率计算公式：（本年同期-去年同期）/去年同期*100%

eg：

产品表如上例，求每一产品GMV的同比增长率。

SELECT *,
	   LAG(GMV, 12) OVER(PARTITION BY PRODUCT, DEPARTMENT ORDER BY LOG_MONTH) AS LAST_YEAR_GMV
	   CAST(GMV AS DOUBLE) / LAG(GMV, 1) OVER(PARTITION BY PRODUCT, DEPARTMENT ORDER BY LOG_MONTH) - 1 AS GROWTH_RATE
FROM PRODUCTS
ORDER BY PRODUCT, LOG_MONTH;