韭菜生活
窗口函数
编辑
2024-12-16
SQL编程思想：基于5种主流数据库代码实现
00

目录

定义
聚合窗口函数 Aggregate Window Function
移动平均值
累计求和
排名窗口函数 Ranking Window Function
排名函数比较
累计分布
取值窗口函数 Value Window Function
环比
同比
复合增长率

窗口函数和聚合函数的区别在于，窗口函数为每一行记录都返回一个结果，而聚合函数为每个分组返回一个结果。

图10.1 聚合函数与窗口函数的区别

定义

在某些数据库中，窗口函数也被称为在线分析处理（OLAP）函数，或者分析函数（Analytic Function）​。

sql
win_func_name ([expression]) OVER (
	PARTITION BY ... -- 类似 GROUP BY
	ORDER BY ...
	frame_clause -- 窗口大小
)

frame_clause:
{ ROWS | RANGE } frame_start
{ ROWS | RANGE } BETWEEN frame_start AND frame_end

窗口函数 OVER 子句中的 frame_clause 选项用于指定一个移动的分析窗口，窗口总是位于分区的范围之内，是分区的一个子集。

  • ROWS 表示以数据行为单位计算窗口的偏移量。
  • RANGE 表示以数值（例如10天、5km等）为单位计算窗口的偏移量。
  • frame_start 选项用于定义窗口的起始位置
    • UNBOUNDED PRECEDING 表示窗口从分区的第一行开始。
    • N PRECEDING 表示窗口从当前行之前的第 N 行开始。
    • CURRENT ROW 表示窗口从当前行开始。
  • frame_end 选项用于定义窗口的结束位置
    • CURRENT ROW 表示窗口到当前行结束。
    • M FOLLOWING 表示窗口到当前行之后的第 M 行结束。
    • UNBOUNDED FOLLOWING 表示窗口到分区的最后一行结束。

理解“当前行”吗？在文章开头说过，窗口函数为每一行记录生成一个结果，“当前行”就表示窗口函数在为哪一行生成结果。

窗口大小选项举例

sql
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

上面的语句用下图表示

图10.2 窗口大小选项

简单使用例子

sql
-- 不同部门分别统计员工的月薪合计
SELECT emp_name "员工姓名", salary "月薪", dept_id "部门编号",
	SUM(salary) OVER (
		PARTITION BY dept_id
	) AS "部门合计"
FROM employee;

-- 员工在部门内的月薪排名
SELECT emp_name "姓名", salary "月薪", dept_id "部门编号",
	RANK() OVER ( -- 窗口内的数据排序后，RANK 返回从1-n的整数 
		PARTITION BY dept_id
		ORDER BY salary DESC
	) AS "部门排名"
FROM employee;
  • 对于 oracle、pg 以及 sqlite，窗口函数中的 ORDER BY 也可以使用 NULLS FIRST 或者 NULLS LAST 选项指定空值的排序位置。

聚合窗口函数 Aggregate Window Function

包括 AVG()、SUM()、COUNT()、MAX() 以及 MIN() 等函数。

移动平均值

sql
-- 获取不同产品每个月销售额、最近3个月平均销售额
SELECT product AS "产品", ym "年月", amount "销售额",
	AVG(amount) OVER (
		PARTITION BY product
		ORDER BY ym
		ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 从当前行的前2行开始，到当前行结束
	) AS "最近平均销售额"
FROM sales_monthly
ORDER BY product, ym;

累计求和

sql
-- 不同产品截至当前月份的累计销售额
SELECT product AS "产品", ym "年月", amount "销售额",
	SUM(amount) OVER (
		PARTITION BY product
		ORDER BY ym
		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 从当前分区第一行开始，到当前行结束
	) AS "累计销售额"
FROM sales_monthly
ORDER BY product, ym;

  • 对于聚合窗口函数，如果没有指定 ORDER BY 选项，默认的窗口大小就是整个分区。如果指定了 ORDER BY 选项，默认的窗口大小就是分区的第一行到当前行。
sql
-- 查找短期之内（5天）累计转账超过100万元的账户
-- oracle mysql pg
SELECT log_ts, from_user, total_amount
FROM (
	SELECT log_ts, from_user,
		SUM(amount) OVER (
			PARTITION BY from_user
			ORDER BY log_ts
			RANGE INTERVAL '5' DAY PRECEDING -- 根据排序的 log_ts，从前5天开始，到当前结束
		) AS total_amount
	FROM transfer_log
	WHERE TYPE = '转账'
) t
WHERE total_amount >= 1000000;

-- sqlite 不支持 INTERVAL，但是可以将时间转为整数
WITH t1(log_ts, unix, from_user, amount) AS (
	SELECT log_ts, CAST(STRFTIME('%s', log_ts) AS INT), from_user, amount
	FROM transfer_log
	WHERE type = '转账'
)
SELECT log_ts, from_user, total_amount
FROM (
	SELECT log_ts, from_user,
		SUM(amount) OVER (
			PARTITION BY from_user
			ORDER BY unix
			RANGE 5 * 86400 PRECEDING
		) AS total_amount
	FROM t1
) t
WHERE total_amount >= 1000000;
  • mss 中的 RANGE 窗口大小选项只能指定 UNBOUNDED PRECEDING、UNBOUNDED FOLLOWING 或者 CURRENT ROW。

排名窗口函数 Ranking Window Function

包括 ROW_NUMBER()、RANK()、DENSE_RANK()、PERCENT_RANK()、CUME_DIST() 以及 NTILE() 等函数。

  • ROW_NUMBER 函数可以为分区中的每行数据分配一个序列号，序列号从1开始。
  • RANK 函数返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
  • DENSE_RANK 函数返回当前行在分区中的名次。即使存在名次相同的数据，后续的排名也是连续值。
  • PERCENT_RANK 函数以百分比的形式返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
  • CUME_DIST 函数计算当前行在分区内的累积分布。
  • NTILE 函数将分区内的数据分为N等份，并返回当前行所在的分片位置。
  • 排名窗口函数不支持动态的窗口大小选项，而是以整个分区作为分析的窗口。

排名函数比较

sql
SELECT d.dept_name AS "部门名称", e.emp_name AS "姓名", e.salary AS "月薪",
	ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "row_number",
	RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "rank",
	DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "dense_rank",
	PERCENT_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "percent_rank"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id);

  • 可以使用 COUNT() 窗口函数产生和ROW_NUMBER函数相同的结果

更简单的写法

  • oracle mss 不支持简写
sql
-- mysql pg sqlite
SELECT d.dept_name AS "部门名称", e.emp_name AS "姓名", e.salary AS "月薪",
	ROW_NUMBER() OVER w AS "row_number",
	RANK() OVER w AS "rank",
	DENSE_RANK() OVER w AS "dense_rank",
	PERCENT_RANK() OVER w AS "percent_rank"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
WINDOW w AS (PARTITION BY e.dept_id ORDER BY e.salary DESC);

sql
-- 查找每个部门中最早入职的2名员工
WITH ranked_emp AS (
	SELECT d.dept_name, e.emp_name, e.hire_date,
		ROW_NUMBER() OVER (PATRITION BY e.dept_id ORDER BY e.hire_date) AS rn
	FROM employee e
	JOIN department d ON (e.dept_id = d.dept_id)
)
SELECT dept_name "部门名称", emp_name "姓名", hire_date "入职日期", rn "入职顺序"
FROM ranked_emp
WHERE rn <= 2;

累计分布

CUME_DIST 返回排名在当前行之前（包含当前行）所有数据所占的比率。

sql
SELECT emp_name AS "姓名", salary AS "月薪",
	CUME_DIST() OVER (ORDER BY salary) AS "累计占比"
FROM employee;

  • 结果显示8%的员工月薪小于或等于4000元
sql
-- 将员工按照入职先后顺序分为5组，并计算每个员工所在的分组
SELECT emp_name AS "姓名", hire_date AS "入职日期",
	NTILE(5) OVER (ORDER BY hire_date) AS "分组位置"
FROM employee;

取值窗口函数 Value Window Function

用于返回指定位置上的数据行，包括 FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()、NTH_VALUE() 等函数。

  • LAG 函数可以返回窗口内当前行之前的第 N 行数据。
  • LEAD 函数可以返回窗口内当前行之后的第 N 行数据。
  • FIRST_VALUE 函数可以返回窗口内第一行数据。
  • LAST_VALUE 函数可以返回窗口内最后一行数据。
  • NTH_VALUE 函数可以返回窗口内第 N 行数据。
  • 其中，LAG 函数和 LEAD 函数不支持动态的窗口大小，它们以整个分区作为分析的窗口。

环比

指的是本期数据与上期数据相比的增长，(后一期数据 - 前一期数据) / 前一个数据 * 100%。

sql
-- 各种产品每个月的环比增长率
SELECT product AS "产品", ym "年月", amount "销售额",
	(
		(amount - LAG(amount, 1) /*上一期销售额*/ OVER (PARTITION BY product ORDER BY ym)) / LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym)
	) * 100 AS "环比增长率（%）"
FROM sales_monthly
ORDER BY product, ym;

同比

指的是本期数据与上一年度或历史同期相比的增长。

sql
-- 各种产品每个月的同比增长率
SELECT product AS "产品", ym "年月", amount "销售额",
	(
		(amount - LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym)) / LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym)
	) * 100 AS "同比增长率（%）"
FROM sales_monthly
ORDER BY product, ym;

复合增长率

直接上公式

复合增长率=((第n期数据第1期数据)1n−1−1)×100复合增长率 = \left( (\frac{第n期数据}{第1期数据})^{\frac{1}{n-1}} - 1 \right) \times 100
sql
WITH s (product, ym, amount, first_amount, num) AS (
	SELECT product, ym, amount,
		FIRST_VALUE(amount) OVER (PARTITION BY product ORDER BY ym),
		ROW_NUMBER() OVER (PATRITION BY product ORDER BY ym)
	FROM sales_monthly
)
SELECT product AS "产品", ym "年月", amount "销售额",
	(
		POWER(1.0*amount/first_amount, 1.0/NULLIF(num-1,0)) - 1
	) * 100 AS "月均复合增长率（%）"
FROM s
ORDER BY product, ym;


sql
-- 不同产品最低销售额、最高销售额以及第三高销售额所在的月份
SELECT product AS "产品", ym "年月", amount "销售额",
	FIRST_VALUE(ym) OVER (
		PARTITION BY product ORDER BY amount DESC
		ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS "最高销售额月份",
	LAST_VALUE(ym) OVER (
		PARTITION BY product ORDER BY amount DESC
		ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS "最低销售额月份",
	-- mss 不支持 NTH_VALUE
	NTH_VALUE(ym, 3) OVER (
		PARTITION BY product ORDER BY amount DESC
		ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS "第三高月份"
FROM sales_monthly
ORDER BY product, ym;

  • FIRST_VALUE、LAST_VALUE、NTH_VALUE 的默认窗口都是从分区的第一行到当前行。

本文作者:jdxj

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！