累计求和与滑动窗口，窗口函数的高阶玩法

SQL手册第 5 题约 10 分钟读完

本文目录

总结
面试原题
数据准备
第一问：累计求和
第二问：滑动平均
第三问：环比增长率
实战场景：我在工作中怎么用的
ROWS 和 RANGE 的区别

总结

需求	窗口帧	关键函数
------	--------	----------
累计求和	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	SUM OVER
N日滑动平均	ROWS BETWEEN (N-1) PRECEDING AND CURRENT ROW	AVG OVER
环比/同比	不需要窗口帧	LAG / LEAD
窗口内最值	按需指定	MAX / MIN OVER

三个容易踩的坑：

1. 滑动窗口N天要写N-1 PRECEDING（包含当前行）

2. ROWS和RANGE有区别，显式指定ROWS更安全

3. LAG取到NULL时记得NULLIF防除零

这五天的SQL面试题系列就到这里了。从窗口函数基础到累计/滑动窗口，难度逐步递进，基本覆盖了SQL面试中80%的高频考点。

这5天的内容如果你都吃透了，SQL面试基本稳了。 我最近在复习这些的时候，又重新体会到一件事：SQL的难点从来不是语法，而是怎么把业务问题翻译成SQL逻辑。

你们在面试中遇到过什么刁钻的SQL题？评论区分享一下，我来出解析！

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长

先把这段讲顺；面试官追问时，再展开下面的原理和细节。

详细讲解

窗口函数的排名用法（ROW_NUMBER那些）你可能已经很熟了，但SUM OVER、AVG OVER配合ROWS BETWEEN的用法，才是窗口函数真正强大的地方。这道题在广告ROI分析、财务报表、运营指标看板中天天用到，面试也越来越爱考。

面试原题

给定一张每日广告消耗表，请计算：

1. 每天的累计消耗（从月初到当天）

2. 最近7天的滑动平均消耗

3. 同比前一天的消耗增长率

数据准备

-- 广告每日消耗表
ad_daily_cost(dt, channel, cost)
 
| dt         | channel  | cost  |
|------------|----------|-------|
| 2025-01-01 | Facebook | 5000  |
| 2025-01-02 | Facebook | 5500  |
| 2025-01-03 | Facebook | 4800  |
| 2025-01-04 | Facebook | 6200  |
| 2025-01-05 | Facebook | 5800  |
| 2025-01-06 | Facebook | 7100  |
| 2025-01-07 | Facebook | 6500  |
| 2025-01-08 | Facebook | 5900  |
| ...        | ...      | ...   |

第一问：累计求和

需求： 每天显示从月初到当天的累计消耗。

SELECT
  dt,
  channel,
  cost,
  SUM(cost) OVER (
    PARTITION BY channel, DATE_FORMAT(dt, 'yyyy-MM')
    ORDER BY dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS mtd_cost  -- Month To Date
FROM ad_daily_cost

关键点解析：

PARTITION BY channel, DATE_FORMAT(dt, 'yyyy-MM')：按渠道和月份分组，每个月重新开始累计

ORDER BY dt：按日期排序

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从分区第一行到当前行

其实这行 ROWS BETWEEN... 可以省略，因为当你指定了ORDER BY，默认的窗口范围就是从分区开始到当前行。但面试的时候我建议你显式写出来，表明你理解窗口帧的概念，而不是碰巧写对了。

结果：

| dt         | channel  | cost | mtd_cost |
|------------|----------|------|----------|
| 2025-01-01 | Facebook | 5000 | 5000     |
| 2025-01-02 | Facebook | 5500 | 10500    |
| 2025-01-03 | Facebook | 4800 | 15300    |
| ...        | ...      | ...  | ...      |

第二问：滑动平均

需求： 计算每天最近7天的平均消耗（含当天）。

SELECT
  dt,
  channel,
  cost,
  ROUND(
    AVG(cost) OVER (
      PARTITION BY channel
      ORDER BY dt
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ),
    2
  ) AS cost_7d_avg
FROM ad_daily_cost

为什么是6 PRECEDING不是7？

因为窗口包含当前行。6 PRECEDING + CURRENT ROW = 7行。这个是面试中非常常见的陷阱，很多人会写成 7 PRECEDING，那就变成8天的平均了。

注意前几天数据不足7天：

1月1日只有1天数据，1月2日只有2天...这些天的平均值会基于实际可用的行数计算。如果业务上要求严格7天才出数，需要加一个过滤：

SELECT * FROM (
  SELECT
    dt, channel, cost,
    AVG(cost) OVER (
      PARTITION BY channel ORDER BY dt
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS cost_7d_avg,
    COUNT(1) OVER (
      PARTITION BY channel ORDER BY dt
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS window_size
  FROM ad_daily_cost
) t
WHERE window_size = 7  -- 只保留窗口满7天的记录

第三问：环比增长率

需求： 每天和前一天比，消耗增长了多少。

SELECT
  dt,
  channel,
  cost,
  LAG(cost, 1) OVER (PARTITION BY channel ORDER BY dt) AS prev_cost,
  ROUND(
    (cost - LAG(cost, 1) OVER (PARTITION BY channel ORDER BY dt)) * 100.0 /
    NULLIF(LAG(cost, 1) OVER (PARTITION BY channel ORDER BY dt), 0),
    2
  ) AS growth_rate
FROM ad_daily_cost

LAG(cost, 1) 取前一行的值。第一天没有前一天，结果是NULL，增长率也是NULL。

面试加分写法——同时看环比和同比：

SELECT
  dt,
  channel,
  cost,
  -- 日环比（和昨天比）
  LAG(cost, 1) OVER (PARTITION BY channel ORDER BY dt) AS prev_day_cost,
  -- 周同比（和上周同一天比）
  LAG(cost, 7) OVER (PARTITION BY channel ORDER BY dt) AS prev_week_cost,
  -- 环比增长率
  ROUND(
    (cost - LAG(cost, 1) OVER w) * 100.0 / NULLIF(LAG(cost, 1) OVER w, 0), 2
  ) AS dod_rate,
  -- 周同比增长率
  ROUND(
    (cost - LAG(cost, 7) OVER w) * 100.0 / NULLIF(LAG(cost, 7) OVER w, 0), 2
  ) AS wow_rate
FROM ad_daily_cost
WINDOW w AS (PARTITION BY channel ORDER BY dt)

注意最后的 WINDOW w AS (...) ——这是命名窗口的语法，避免重复写同样的OVER子句。Hive不支持这个语法，但Spark SQL、MySQL 8.0+、PostgreSQL都支持。面试的时候用这个语法会显得你对SQL标准理解比较深。

实战场景：我在工作中怎么用的

我现在做短剧出海的广告数据分析，每天早上要给投放团队出一张消耗日报，核心指标就是这三个：

1. MTD消耗（月累计）——看这个月预算花了多少，还剩多少空间

2. 7日滑动平均——平滑掉单日波动，看真实的消耗趋势

3. 日环比——消耗突然暴涨或暴跌，需要立刻排查原因

这三个指标一条SQL全搞定：

SELECT
  dt,
  channel,
  cost,
  SUM(cost) OVER (
    PARTITION BY channel, DATE_FORMAT(dt, 'yyyy-MM')
    ORDER BY dt
  ) AS mtd_cost,
  ROUND(AVG(cost) OVER (
    PARTITION BY channel ORDER BY dt
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS cost_7d_avg,
  ROUND(
    (cost - LAG(cost, 1) OVER (PARTITION BY channel ORDER BY dt)) * 100.0 /
    NULLIF(LAG(cost, 1) OVER (PARTITION BY channel ORDER BY dt), 0),
    2
  ) AS dod_rate
FROM ad_daily_cost
WHERE dt BETWEEN '2025-01-01' AND '2025-01-31'
ORDER BY channel, dt

有一次7日均值突然从6000跳到了8000，排查发现是TikTok那边突然放量了一波新素材，CPA飙升但转化没跟上。靠这个滑动平均指标，我们比投放团队还早半天发现了问题。

ROWS 和 RANGE 的区别

这是面试官可能追问的进阶知识。

ROWS BETWEEN： 按物理行数计算窗口

RANGE BETWEEN： 按值的范围计算窗口

区别在哪？当有重复值的时候。

-- 数据：同一天有两条记录
| dt         | cost |
|------------|------|
| 2025-01-01 | 100  |
| 2025-01-01 | 200  |  -- 同一天
| 2025-01-02 | 300  |
 
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- 第1行：100
-- 第2行：100 + 200 = 300
-- 第3行：100 + 200 + 300 = 600
 
-- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- 第1行：100 + 200 = 300   ← 因为第1行和第2行dt相同，被当成同一范围
-- 第2行：100 + 200 = 300   ← 同上
-- 第3行：100 + 200 + 300 = 600

RANGE会把相同ORDER BY值的行归为同一范围，导致结果和预期不同。所以在做累计求和的时候，一般推荐显式写ROWS而不是依赖默认行为。很多引擎默认是RANGE而不是ROWS，这个隐含的差异坑过不少人。

刷题进度保存在当前浏览器