你是否还在用传统的 GROUP BY 进行复杂的数据分析? 是时候认识一下 SQL 窗口函数 (Window Functions) 了! 窗口函数是一种强大的 SQL 功能, 它能让你在不减少行数的情况下, 对数据集的窗口(一组相关的行)执行计算, 极大地提升数据分析的灵活性和效率.

窗口函数的基本概念

窗口函数的语法结构如下:

函数名(列名) OVER (窗口定义)

核心在于 OVER() 子句. OVER() 子句定义了函数要操作的数据窗口的范围. 你可以把它想象成一个在数据行上滑动的窗口, 函数会针对这个窗口内的行进行计算.

你可以选择各种不同的函数作为窗口函数, 主要分为两类:

• 聚合窗口函数 (Aggregate Window Functions): 例如 AVG(), SUM(), MIN(), MAX(), COUNT() 等. 这些是你已经熟悉的聚合函数, 但作为窗口函数使用时, 行为会有所不同.
• 分析窗口函数 (Analytic Window Functions): 例如 ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG() 等. 这些函数专门用于执行排序, 排名, 偏移等分析操作.

聚合窗口函数 vs. 普通聚合函数

让我们通过一个例子来理解聚合窗口函数和普通聚合函数的区别. 假设我们有一个 results 表, 记录了 10 公里赛跑的选手成绩:

如果我们想计算所有参赛者的平均完成时间, 可以使用普通的聚合函数:

SELECT AVG(time) AS average_time
FROM results;

这个查询会返回一个 单行结果, 包含所有选手平均完成时间.

现在, 我们把 AVG() 函数作为窗口函数使用, 只需加上 OVER() 子句:

SELECT *, AVG(time) OVER() AS average_time
FROM results;

运行这个查询, 你会发现结果 不再是单行, 而是返回了 results 表的所有行, 并且每行都多了一个 average_time 列, 其值都是所有参赛者的平均完成时间.

这就是聚合窗口函数和普通聚合函数的关键区别:

• 普通聚合函数: 将多行数据聚合成 少量行 (通常是一行).
• 聚合窗口函数: 为每一行数据都返回一个聚合值, 不改变结果集的行数.

OVER() 子句的强大之处: 定义数据窗口

OVER() 子句的真正威力在于, 你可以通过它灵活地定义窗口的范围, 让窗口函数在不同的数据子集上进行计算.

1. 空 OVER() 子句: 全局窗口

当我们使用空的 OVER() 子句 (例如 OVER()) 时, 窗口函数会作用于 整个结果集, 就像我们上面例子中的 AVG(time) OVER(), 它计算的是所有行的平均时间.

2. PARTITION BY 子句: 分组窗口

PARTITION BY 子句可以将数据 按照指定的列进行分组, 每个分组就是一个独立的窗口. 例如, 如果我们想计算 每个年龄组 的平均完成时间, 可以使用 PARTITION BY age_group:

SELECT *, AVG(time) OVER(PARTITION BY age_group) AS average_time_by_group
FROM results;

在这个查询中, AVG(time) OVER(PARTITION BY age_group) 会为每个年龄组 (例如 "20-29", "30-39") 创建一个窗口, 并分别计算每个窗口内 time 列的平均值. 结果集中, 属于同一个年龄组的选手, 其 average_time_by_group 值是相同的, 但不同年龄组的 average_time_by_group 值则会不同.

3. ORDER BY 子句: 排序窗口

ORDER BY 子句可以 在窗口内对数据进行排序. 这对于一些需要考虑数据顺序的窗口函数非常重要, 例如排名函数.

分析窗口函数: 更高级的数据分析

聚合窗口函数已经很强大了, 但分析窗口函数能提供更丰富的分析能力. 让我们来看看几个常用的分析窗口函数:

1. ROW_NUMBER(): 行号

ROW_NUMBER() 函数为结果集中的每一行 分配一个唯一的序号, 从 1 开始递增. 默认情况下, 行号的分配是任意的. 但我们可以结合 ORDER BY 子句, 让行号按照指定的列排序:

SELECT *, ROW_NUMBER() OVER (ORDER BY time) AS place
FROM results;

在这个例子中, ROW_NUMBER() OVER (ORDER BY time) 会按照 time 列从小到大排序, 并为每行分配一个名次 (place). 完成时间最短的选手排名第一, 依此类推.

**注意: ** 如果存在并列的情况 (例如, 多个选手完成时间相同), ROW_NUMBER() 仍然会分配不同的序号, 这可能不符合我们对排名的期望.

2. RANK(): 排名(有并列, 跳序号)

RANK() 函数也用于排名, 但它 会处理并列的情况. 当出现并列时, RANK() 会赋予相同的排名, 并 跳过后续的序号.

SELECT *,
       ROW_NUMBER() OVER (ORDER BY time) AS rn, -- 行号, 用于对比
       RANK() OVER (ORDER BY time) AS place_rank
FROM results;

假设 results 表中有两条记录的 time 都是 38, 那么这两条记录的 place_rank 值都会是 5, 而下一条记录的 place_rank 值将是 7, 序号 6 被跳过了.

3. DENSE_RANK(): 排名(有并列, 不跳序号)

DENSE_RANK() 函数也用于排名, 并且也 会处理并列的情况. 与 RANK() 不同的是, DENSE_RANK() 在出现并列时, 不会跳过后续的序号. 排名是连续的.

SELECT *,
       RANK() OVER (ORDER BY time) AS place_rank,       -- RANK 排名
       DENSE_RANK() OVER (ORDER BY time) AS place_dense_rank -- DENSE_RANK 排名
FROM results;

同样假设有两条记录的 time 都是 38, 它们 place_dense_rank 值都会是 5, 而下一条记录的 place_dense_rank 值将是 6, 序号是连续的.

Top N within Groups: 分组Top N

窗口函数的一个经典应用场景是 分组 Top N 问题, 即在每个分组内找到排名前 N 的记录. 例如, 我们想找出每个年龄组跑得最快的前三名选手.

WITH ranked_results AS (
  SELECT *,
         RANK() OVER (PARTITION BY age_group ORDER BY time) AS group_rank
  FROM results
)
SELECT *
FROM ranked_results
WHERE group_rank <= 3
ORDER BY age_group, group_rank;

这个查询的步骤是:

1. **使用窗口函数排名: ** 在 CTE ranked_results 中, 使用 RANK() OVER (PARTITION BY age_group ORDER BY time) 按照年龄组分组, 并按照完成时间排序, 计算每个选手在各自年龄组内的排名 group_rank.
2. **筛选 Top 3: ** 在主查询中, 从 ranked_results CTE 中筛选出 group_rank 小于等于 3 的记录, 即每个年龄组的前三名.

总结

SQL 窗口函数是数据分析的利器, 它提供了强大的数据窗口计算能力, 可以帮助你:

• 进行复杂的聚合分析, 例如计算移动平均值, 累计总和等.
• 实现灵活的数据排名和排序, 例如计算全局排名, 分组排名, Top N 等.
• 进行更高级的分析操作, 例如计算同比, 环比, 百分比差异等.

窗口函数代码简洁, 易读, 能有效地提升 SQL 查询的效率和可维护性. 掌握窗口函数, 将使你在数据分析领域更上一层楼!

跨数据库的窗口函数

本文示例基于 SQLite 数据库, 但窗口函数是 SQL 标准功能, 在各种主流关系型数据库 (如 MySQL 8.0+, PostgreSQL, SQL Server, Oracle) 中都得到了广泛支持. 不同数据库在窗口函数的语法和具体实现上可能略有差异, 使用时建议参考对应数据库的官方文档.

感谢阅读!