📊 SQL 入门 Day 13：窗口函数进阶

前端阿凡
21 阅读5分钟

场景引入

某电商平台运营需要分析每位用户的消费习惯：不仅要看总消费排名，还要看每位用户每次消费相比于前一次是增长还是下降、相比于全站平均线处于什么位置。上节课的 ROW_NUMBER()/RANK() 只解决了排名问题，今天我们用更强大的窗口函数搞定"同比环比"、"移动平均"、"相对位置分析"。

一、LAG 与 LEAD — 访问前后行

最常用的"同比/环比"函数，能在不改变结果行数的情况下，拿到当前行之前或之后某行的值。

SELECT
    user_id,
    order_date,
    amount,
    LAG(amount, 1) OVER (
        PARTITION BY user_id
        ORDER BY order_date
    ) AS prev_amount,
    amount - LAG(amount, 1) OVER (
        PARTITION BY user_id
        ORDER BY order_date
    ) AS growth_amount
FROM orders;

执行结果：

user_idorder_dateamountprev_amountgrowth_amount
12026-01-01100NULLNULL
12026-02-0115010050
12026-03-01120150-30
  • LAG(column, offset, default) — 向上取第 offset 行
  • LEAD(column, offset, default) — 向下取第 offset 行
  • 第一行的 prev_amount 为 NULL，可用第三个参数设默认值：LAG(amount, 1, 0)

场景：计算环比增长率

SELECT
    user_id,
    order_date,
    amount,
    ROUND(
        (amount - LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY order_date))
        / LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY order_date) * 100,
        2
    ) || '%' AS growth_rate
FROM orders;

二、FIRST_VALUE 与 LAST_VALUE — 窗口内首尾值

用于获取分区内第一个或最后一个值。注意：LAST_VALUE 默认的窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，结果通常不是你想要的，需要显式指定帧范围。

SELECT
    user_id,
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY user_id
        ORDER BY order_date
    ) AS first_amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY user_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_amount
FROM orders;

执行结果：

user_idorder_dateamountfirst_amountlast_amount
12026-01-01100100120
12026-02-01150100120
12026-03-01120100120

三、窗口帧（Window Frame）深入

窗口帧决定了窗口函数作用的行范围，语法：

{ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_end

帧边界选项：

边界含义
UNBOUNDED PRECEDING分区第一行
n PRECEDING当前行前 n 行
CURRENT ROW当前行
n FOLLOWING当前行后 n 行
UNBOUNDED FOLLOWING分区最后一行

移动平均（3 期）

SELECT
    sales_date,
    amount,
    ROUND(AVG(amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3
FROM daily_sales;

执行结果：

sales_dateamountmoving_avg_3
2026-01-01100100.00
2026-01-02200150.00
2026-01-03150150.00
2026-01-04300216.67
2026-01-05250233.33

ROWS vs RANGE vs GROUPS

  • ROWS：严格按行数计算，不理会对等值
  • RANGE：按 ORDER BY 列的值计算，相同值的行视为一组
  • GROUPS：按 ORDER BY 列的分组计算（SQL 标准较新特性）
-- RANGE: 相同日期的行会一起被包含
-- 如果 1 月 2 号有 3 条记录，RANGE 1 PRECEDING 会包含全部 3 条
SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW
    ) AS range_sum
FROM orders;

小贴士： RANGE 在 MySQL 中只支持数值和日期，不支持字符串。大多数场景用 ROWS 更直观可控。

四、NTILE — 分桶统计

将分区内的数据均匀分成 N 组，常用于"Top N% 分析"。

SELECT
    employee_id,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

执行结果：

employee_idsalaryquartile
5500001
3450001
1400002
4350002
2300003
6280003
7250004

应用： 四分位分析、A/B 测试分桶、成绩等级划分。

五、NTH_VALUE — 第 N 个值

取窗口内第 N 行的值（比 FIRST_VALUE 更通用）。

SELECT
    department_id,
    salary,
    NTH_VALUE(salary, 2) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest
FROM employees;

六、综合实战：用户 RFM 基础分析

SELECT
    user_id,
    COUNT(*) OVER w AS freq,
    MAX(order_date) OVER w AS last_order,
    SUM(amount) OVER w AS monetary,
    ROW_NUMBER() OVER (ORDER BY SUM(amount) OVER w DESC) AS rank
FROM orders
WINDOW w AS (PARTITION BY user_id)
GROUP BY user_id, order_date;

说明： MySQL 8.0+ 支持 WINDOW 子句，可将重复的窗口定义抽离出来，让 SQL 更简洁。

⚠️ 注意事项

  1. LAG/LEAD 返回 NULL：第一行/最后一行没有前驱/后继，记得用 COALESCE 或第三个参数处理
  2. LAST_VALUE 默认行为：不加帧范围时，只返回当前行之前的最后一个值，需要用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 获取分区内的真正末行
  3. 窗口函数与 GROUP BY：窗口函数在 GROUP BY 之后执行，所以 GROUP BY 后的非聚合列不能在窗口中直接引用
  4. 性能注意：窗口函数虽然方便，但会扫描全分区数据，大表上 ORDER BY 后的分区数过多可能导致性能瓶颈
  5. ORDER BY 必须带：部分窗口函数（如 RANK、LAG）必须指定 ORDER BY，否则报错

总结

  • LAG/LEAD → 环比/同比、前后行对比
  • FIRST_VALUE/LAST_VALUE/NTH_VALUE → 窗口边界分析
  • 窗口帧 (ROWS/RANGE) → 移动平均、累计统计
  • NTILE → 分桶、四分位
  • WINDOW 子句 → 复用窗口定义，SQL 更清爽

进阶窗口函数让我们能优雅地解决"行间计算"，这是传统 GROUP BY 做不到的。掌握这些技巧后，90% 的报表分析 SQL 都能信手拈来！🚀

avatar
文章
阅读
粉丝