窗口聚合与移动计算

窗口帧（Frame）

窗口帧定义了窗口函数计算时包含哪些行。

基本语法：

function() OVER (
    PARTITION BY column
    ORDER BY column
    ROWS BETWEEN start AND end
)

累计求和

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative_sum
FROM orders;

结果：

 order_date | amount  | cumulative_sum
------------+---------+----------------
 2026-01-15 | 1200.00 |        1200.00
 2026-01-16 |   25.00 |        1225.00
 2026-01-17 |   80.00 |        1305.00
 2026-01-18 |  300.00 |        1605.00
 2026-01-19 |   50.00 |        1655.00

移动平均（Moving Average）

3 行移动平均

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

结果：

 order_date | amount  | moving_avg_3
------------+---------+--------------
 2026-01-15 | 1200.00 |   1200.00000  -- 只有 1 行
 2026-01-16 |   25.00 |    612.50000  -- 2 行平均
 2026-01-17 |   80.00 |    435.00000  -- 3 行平均
 2026-01-18 |  300.00 |    135.00000  -- 最近 3 行
 2026-01-19 |   50.00 |    143.33333

窗口帧说明符

说明符	含义
CURRENT ROW	当前行
N PRECEDING	前 N 行
N FOLLOWING	后 N 行
UNBOUNDED PRECEDING	从第一行开始
UNBOUNDED FOLLOWING	到最后一行

常用组合：

-- 累计到当前行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 中心移动平均（前 1 行 + 当前 + 后 1 行）
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- 前 N 行到当前行
ROWS BETWEEN N PRECEDING AND CURRENT ROW

LAG 和 LEAD：访问相邻行

LAG：前一行的值

SELECT 
    order_date,
    amount,
    LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
    amount - LAG(amount) OVER (ORDER BY order_date) AS difference
FROM orders;

结果：

 order_date | amount  | prev_amount | difference
------------+---------+-------------+------------
 2026-01-15 | 1200.00 |             |           
 2026-01-16 |   25.00 |     1200.00 |   -1175.00
 2026-01-17 |   80.00 |       25.00 |      55.00
 2026-01-18 |  300.00 |       80.00 |     220.00
 2026-01-19 |   50.00 |      300.00 |    -250.00

LEAD：后一行的值

SELECT 
    order_date,
    amount,
    LEAD(amount) OVER (ORDER BY order_date) AS next_amount
FROM orders;

偏移 N 行

-- LAG(column, N) 前 N 行
SELECT amount, LAG(amount, 2) OVER (ORDER BY order_date) AS two_days_ago
FROM orders;

-- LEAD(column, N) 后 N 行
SELECT amount, LEAD(amount, 2) OVER (ORDER BY order_date) AS two_days_later
FROM orders;

默认值

-- LAG(column, offset, default)
SELECT 
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_or_zero
FROM orders;

FIRST_VALUE 和 LAST_VALUE

FIRST_VALUE：窗口第一行

SELECT 
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (ORDER BY order_date) AS first_order
FROM orders;

LAST_VALUE：窗口最后一行

SELECT 
    order_date,
    amount,
    LAST_VALUE(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order
FROM orders;

注意：必须指定 UNBOUNDED FOLLOWING，否则默认只到当前行。

ROWS vs RANGE

• ROWS：按物理行数计数
• RANGE：按逻辑值范围（处理并列值）

-- ROWS：严格 3 行
SUM(amount) OVER (ORDER BY score ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- RANGE：包含相同 score 的所有行
SUM(amount) OVER (ORDER BY score RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

大多数情况使用 ROWS 更直观。

实际应用

应用 1：同比增长

SELECT 
    order_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY order_date) AS prev_day,
    ROUND((amount - LAG(amount) OVER (ORDER BY order_date)) / LAG(amount) OVER (ORDER BY order_date) * 100, 2) AS growth_pct
FROM orders;

应用 2：7 天移动平均

SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7
FROM orders;

应用 3：排名变化

WITH daily_rank AS (
    SELECT 
        product,
        order_date,
        RANK() OVER (PARTITION BY order_date ORDER BY amount DESC) AS rank
    FROM orders
)
SELECT 
    product,
    order_date,
    rank,
    LAG(rank) OVER (PARTITION BY product ORDER BY order_date) AS prev_rank,
    rank - LAG(rank) OVER (PARTITION BY product ORDER BY order_date) AS rank_change
FROM daily_rank;

练习

1. 累计求和：计算每天的累计销售额。
2. 移动平均：计算 5 天移动平均销售额。
3. 环比增长：计算每天相比前一天的销售增长率。
4. LAG/LEAD：查询每个订单及其前后订单的金额。
5. FIRST_VALUE：每行显示当月第一个订单的金额。
6. 窗口帧：计算当前行及前后各 1 行的平均值（中心移动平均）。
7. 分区窗口：每个用户的订单累计金额（按日期）。
8. 实际场景：创建一个销售趋势分析，包含当日销售、7 日均值、环比增长。

完成练习后，打开本章答案页核对。

下一步

学会了窗口聚合。下一节学习 CTE（WITH 子句）：如何使用通用表表达式简化复杂查询。

回到第四章概览 · 返回SQL 学习路线