【数据分析师必备技能】：深入理解SQL窗口函数的7种经典用法

第一章：SQL窗口函数的核心概念与执行原理

SQL窗口函数是现代关系型数据库中用于执行复杂分析操作的强大工具，能够在不改变原始行数的前提下，对数据集的“窗口”范围进行聚合、排序或统计计算。与传统聚合函数不同，窗口函数不会将多行合并为单行，而是为每一行返回一个计算结果，保留了原始数据的粒度。

窗口函数的基本语法结构

窗口函数的通用语法如下：

SELECT
    column1,
    AVG(column2) OVER (
        PARTITION BY partition_expression
        ORDER BY sort_expression
        ROWS BETWEEN start AND end
    ) AS avg_value
FROM table_name;

其中：

• OVER() 定义窗口的范围和行为
• PARTITION BY 将数据分组，类似 GROUP BY，但不聚合行
• ORDER BY 指定窗口内数据的排序方式
• ROWS BETWEEN ... AND ... 明确物理行边界，如当前行前后若干行

窗口函数的执行顺序

在SQL查询流程中，窗口函数在以下阶段之后执行：

1. FROM 和 JOIN（数据源加载）
2. WHERE（行过滤）
3. GROUP BY 与聚合函数
4. SELECT 中的窗口函数计算

这使得窗口函数可以基于已分组和聚合的结果进行进一步分析。

常见窗口函数分类

类型	示例函数	用途说明
排序函数	RANK(), ROW_NUMBER(), DENSE_RANK()	对分区内的行进行排序编号
分布函数	PERCENT_RANK(), CUME_DIST()	计算行在分区中的相对位置
聚合函数	SUM(), AVG(), MAX() 等 + OVER	在窗口范围内执行聚合

graph LR A[数据输入] --> B{应用PARTITION BY} B --> C[按ORDER BY排序] C --> D[确定ROWS/RANGE窗口] D --> E[逐行计算函数值] E --> F[输出每行结果]

第二章：排序类窗口函数的经典应用

2.1 ROW_NUMBER() 实现去重与分页逻辑

在处理数据库查询时，`ROW_NUMBER()` 窗口函数常用于实现高效的数据去重与分页控制。

去重逻辑实现

通过按关键字段分组并排序，利用 `ROW_NUMBER()` 标记重复记录，仅保留序号为1的行：

SELECT *
FROM (
  SELECT id, name, ROW_NUMBER() OVER (PARTITION BY id ORDER BY update_time DESC) AS rn
  FROM user_info
) t
WHERE rn = 1;

上述语句中，`PARTITION BY id` 表示按主键分组，`ORDER BY update_time DESC` 确保保留最新记录，`rn = 1` 过滤出唯一有效行。

分页场景应用

相比 `LIMIT/OFFSET`，使用 `ROW_NUMBER()` 更适合深度分页：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY create_time) AS rn
  FROM logs
) t WHERE rn BETWEEN 101 AND 200;

此方式先全局排序生成行号，再通过范围筛选，避免偏移量过大导致性能下降。

2.2 RANK() 与 DENSE_RANK() 在排行榜中的差异解析

在处理排行榜类业务场景时，RANK() 和 DENSE_RANK() 是两个常用的窗口函数，它们的核心区别在于如何处理并列排名后的序号跳跃问题。

排名逻辑对比

• RANK()：相同值并列同一名次，但会跳过后续名次数。例如，两名第一后，下一名为第三名。
• DENSE_RANK()：相同值并列同一名次，后续名次连续递增。例如，两名第一后，下一名为第二名。

SQL 示例演示

SELECT 
  player, 
  score,
  RANK() OVER (ORDER BY score DESC) AS rank_pos,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_pos
FROM game_scores;

上述查询中，RANK() 可能产生 [1,1,3] 的结果，而 DENSE_RANK() 则生成 [1,1,2]，体现密集排名的连续性。

玩家	分数	RANK()	DENSE_RANK()
Alice	95	1	1
Bob	95	1	1
Charlie	90	3	2

2.3 NTILE() 进行数据分桶与百分位分析

分桶函数的基本原理

NTILE() 是窗口函数中用于将有序数据集划分为指定数量“桶”的重要工具。每个桶包含大致相等的记录数，适用于百分位分析、绩效分级等场景。

语法结构与参数说明

NTILE(n) OVER (ORDER BY column_name [ASC|DESC])

其中，n 表示希望划分的桶数。数据库会按排序后的结果，尽可能均匀地将数据分配至各桶。

实际应用示例

假设需将销售员按销售额分为四档（即四分位）：

SELECT 
  name, 
  sales, 
  NTILE(4) OVER (ORDER BY sales DESC) AS quartile
FROM sales_team;

该查询将所有销售人员按销售额降序排列，并均分为4组，每组编号从1到4，便于后续分析高、中、低表现区间。

• NTILE(4) 常用于四分位分析
• 当数据无法整除时，前几桶会多包含一条记录
• 结合 ORDER BY 可控制分组依据

2.4 结合PARTITION BY实现组内排序实战

在数据分析中，常需对分组内的数据进行排序。通过窗口函数结合 `PARTITION BY` 可轻松实现组内排序。

核心语法结构

使用 `ROW_NUMBER()`、`RANK()` 等窗口函数配合 `PARTITION BY` 按指定列分组后排序：

SELECT 
    department, 
    employee_name, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

该语句按部门分组，在每组内按薪资降序排列并编号。`PARTITION BY department` 表示以部门为单位划分数据窗口，`ORDER BY salary DESC` 决定组内排序方式。

应用场景对比

• ROW_NUMBER()：为每行分配唯一序号，适用于去重或取Top N；
• RANK()：相同值并列排名，后续跳过相应名次，适合竞赛类场景。

2.5 排序函数在用户行为分析中的综合案例

在用户行为分析中，排序函数常用于识别活跃用户、挖掘访问路径及计算转化漏斗。通过合理使用窗口函数与排序逻辑，可高效提取关键行为序列。

用户会话排序与行为路径分析

利用 ROW_NUMBER() 对用户操作按时间戳排序，可还原真实行为路径：

SELECT 
  user_id,
  event_time,
  event_type,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS seq
FROM user_events;

该查询为每个用户的行为事件打上时序标签，seq=1 表示首次操作，便于后续路径转化分析。

Top-N 活跃用户统计

结合 RANK() 函数可筛选出点击量最高的前 N 用户：

SELECT user_id, click_count 
FROM (
  SELECT user_id, COUNT(*) AS click_count,
         RANK() OVER (ORDER BY COUNT(*) DESC) AS rank
  FROM user_events 
  GROUP BY user_id
) t WHERE rank <= 10;

此逻辑有效识别出贡献最多交互的头部用户群体，支持精细化运营策略制定。

第三章：聚合类窗口函数的进阶用法

3.1 SUM() OVER 累计求和与滚动指标计算

在数据分析中，累计求和与滚动指标是常见的窗口函数应用场景。`SUM() OVER` 允许在不改变行粒度的前提下，对有序数据进行动态聚合。

基本语法结构

SELECT 
    date, 
    sales,
    SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sales
FROM sales_data;

该查询计算每日销售的累计总和。`ORDER BY date` 定义排序逻辑，`ROWS BETWEEN ...` 指定窗口范围：从首行到当前行。

滚动平均计算

• 使用固定行数窗口实现7天滚动平均
• 排除异常值提升指标稳定性
• 结合PARTITION BY按类别独立计算

AVG(sales) OVER (
    PARTITION BY region 
    ORDER BY date 
    ROWS 6 PRECEDING
)

此代码按区域分区，对每条记录前7天（含当天）数据求均值，适用于趋势平滑分析。

3.2 AVG() OVER 实现移动平均趋势分析

在时间序列数据分析中，移动平均是平滑波动、识别趋势的重要手段。通过窗口函数 AVG() OVER，可在不聚合原始数据的前提下计算指定范围内的均值。

基本语法结构

SELECT 
    date, 
    sales,
    AVG(sales) OVER (
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales_data;

该语句按日期排序，对当前行及其前两行的销售数据求平均，形成三日移动平均线，有效削弱短期波动影响。

窗口定义详解

• ORDER BY date：确定数据处理顺序，是时间序列分析的前提；
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：定义滑动窗口包含当前行及之前两行；
• 若需五日平均，可调整为 4 PRECEDING。

结合业务时间维度，该方法可灵活适配不同周期趋势分析需求，提升决策准确性。

3.3 COUNT() OVER 动态统计组内频次分布

在复杂的数据分析场景中，需要对分组内的数据频次进行动态统计。`COUNT() OVER()` 窗口函数能有效实现这一需求，无需聚合即可保留原始行级信息。

基本语法结构

SELECT 
    category,
    value,
    COUNT(*) OVER (PARTITION BY category) AS group_freq
FROM data_table;

该语句按 `category` 分组统计每组行数，`PARTITION BY` 定义逻辑分组范围，`COUNT(*)` 计算每组总记录数，结果附加至每一行。

应用场景示例

• 识别高频交易用户所属分组的总体活跃度
• 监控日志中某错误类型在各服务实例中的出现频次
• 辅助数据质量分析，发现异常空值集中分布的类别

结合 `ORDER BY` 与窗口帧（如 `ROWS BETWEEN`），还可实现滑动频次统计，提升分析粒度。

第四章：偏移类与分布类函数的实战技巧

4.1 LAG() 与 LEAD() 构建同比环比增长模型

在时间序列分析中，`LAG()` 和 `LEAD()` 窗口函数是构建同比增长与环比增长模型的核心工具。它们能够访问当前行前后指定偏移量的行数据，适用于对比不同时期的指标变化。

核心函数说明

• LAG(column, n)：获取当前行前第 n 行的数据，用于计算同比或环比基准值；
• LEAD(column, n)：获取当前行后第 n 行的数据，常用于预测场景。

示例：月度销售额环比增长率计算

SELECT 
  month,
  sales,
  LAG(sales, 1) OVER (ORDER BY month) AS prev_month_sales,
  ROUND((sales - LAG(sales, 1) OVER (ORDER BY month)) / LAG(sales, 1) OVER (ORDER BY month) * 100, 2) AS mom_growth_rate
FROM sales_data;

该查询通过 LAG(sales, 1) 获取上月销售额，并计算环比增长率。窗口函数按月份排序，确保时间序列逻辑正确，ROUND 控制结果精度至两位小数。

4.2 FIRST_VALUE() 与 LAST_VALUE() 提取关键时间节点

在时间序列分析中，精准提取首个和末尾事件节点对业务洞察至关重要。窗口函数 `FIRST_VALUE()` 和 `LAST_VALUE()` 能高效定位分组内有序数据的起点与终点。

基础语法结构

SELECT 
  session_id,
  FIRST_VALUE(event_time) OVER w AS first_event,
  LAST_VALUE(event_time) OVER w AS last_event
FROM events
WINDOW w AS (
  PARTITION BY session_id 
  ORDER BY event_time 
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

上述查询中，`WINDOW w` 定义了按会话分区并按时间排序的窗口框架。`ROWS BETWEEN ...` 确保 `LAST_VALUE()` 能覆盖整个分区，避免因默认范围限制导致结果异常。

典型应用场景

• 用户行为分析：识别单次会话的首次点击与最终转化时间
• 设备监控：提取传感器数据流中的最早与最晚记录点
• 日志追踪：定位异常事务的起止时刻以评估持续时长

4.3 使用NTH_VALUE() 定位特定位置的数据值

在窗口函数中，NTH_VALUE() 用于返回窗口帧内指定位置的表达式值，常用于提取第 N 条记录的数据。

语法结构

NTH_VALUE(expression, N) 
OVER ([PARTITION BY partition_expr] ORDER BY order_expr [window_frame])

其中，expression 是要取值的列，N 为正整数，表示从排序后结果中取第 N 个值。若该位置无值，则返回 NULL。

示例应用

假设查询每个部门薪资排名第二的员工：

SELECT 
  dept, name, salary,
  NTH_VALUE(name, 2) OVER (PARTITION BY dept ORDER BY salary DESC 
                           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  AS second_highest_salary_employee
FROM employee;

此查询在每个部门（PARTITION BY dept）内按薪资降序排列，并在整个结果范围内定位第二个员工姓名。注意使用 ROWS BETWEEN 确保完整帧覆盖，否则可能因默认帧导致结果为空。

4.4 偏移函数在会话划分与路径分析中的应用

在用户行为分析中，偏移函数常用于识别会话边界和重构访问路径。通过时间间隔判断用户操作的连续性，可精准划分独立会话。

会话划分逻辑

利用LAG()函数获取上一行的时间戳，计算当前操作与前一操作的时间差：

SELECT 
  user_id,
  action_time,
  LAG(action_time) OVER (PARTITION BY user_id ORDER BY action_time) AS prev_time,
  EXTRACT(EPOCH FROM (action_time - LAG(action_time) OVER (PARTITION BY user_id ORDER BY action_time))) AS time_diff
FROM user_actions;

该查询输出每个操作与其前序操作的时间差（秒），当time_diff超过设定阈值（如1800秒），则视为新会话起点。

路径序列构建

基于会话ID进行窗口聚合，可还原用户操作路径：

• 使用条件判断生成会话标识
• 按会话分组排序行为序列
• 拼接页面路径形成转化漏斗

第五章：窗口函数性能优化与最佳实践

合理使用索引提升执行效率

窗口函数在处理大规模数据时，若未配合合适的索引，可能导致全表扫描和排序操作激增。建议在 ORDER BY 和 PARTITION BY 涉及的列上创建复合索引。例如，对按用户分组并按时间排序的查询：

CREATE INDEX idx_user_time ON user_events (user_id, event_time);

该索引可显著加速如下窗口查询：

SELECT 
  user_id,
  event_time,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC)
FROM user_events;

避免不必要的窗口计算

当业务只需最新一条记录时，应优先使用 LIMIT 或半连接替代完整的窗口函数。以下为优化前后对比：

• 低效写法：计算所有行的排名
• 高效写法：利用子查询先过滤再计算

控制分区规模防止数据倾斜

若某些分组（如用户ID为0）包含海量数据，会导致单个分区处理缓慢。可通过预过滤异常值或引入二级分区缓解：

-- 引入日期作为二级分区键
ROW_NUMBER() OVER (
  PARTITION BY user_id, DATE(event_time) 
  ORDER BY event_time DESC)

监控执行计划识别性能瓶颈

使用 EXPLAIN ANALYZE 查看实际执行路径，重点关注：

• 是否触发磁盘排序（Disk-based Sort）
• 窗口节点耗时占比
• 内存使用情况

指标	建议阈值	优化手段
分区行数	< 10万	增加分区粒度
排序内存	< work_mem	调大配置或优化索引