同比环比怎么算？从日环比到跨年同比全覆盖

SQL手册第 7 题约 9 分钟读完

本文目录

总结
先统一概念
场景一：日环比（DoD）
场景二：月环比（MoM）
场景三：年同比（YoY）
场景四：周同比（WoW）
场景五：累计同比（YTD YoY）
面试综合题：一条SQL算出所有对比指标

总结

对比类型	方法	关键点
---------	------	--------
日环比 DoD	LAG(x, 1)	最简单，注意NULL
周同比 WoW	LAG(x, 7)	排除周内波动
月环比 MoM	先聚合再LAG(x, 1)	缺失月份要补0
年同比 YoY	LAG(x, 12) 或自JOIN	自JOIN不怕数据断
累计同比 YTD YoY	累计和 + 自JOIN	用DAYOFYEAR对齐

核心原则：LAG适合连续数据，自JOIN适合有缺失的数据。实际工作中推荐先用日历表/维度表补全数据，再统一用LAG，逻辑最清晰。

明天 DAY 8 聊大表JOIN优化，MapJoin、数据倾斜、Shuffle原理——这些是面试中SQL和大数据结合的重要考点。

你在工作中最常用哪种同比环比的口径？评论区聊聊～

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

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

详细讲解

DAY 5我们用LAG简单写过环比，但实际面试和工作中，同比环比的变体非常多。今天系统梳理一遍，把各种口径都讲清楚。

先统一概念

很多人分不清同比和环比，一句话讲清楚：

环比（MoM / DoD）： 和相邻上一期比。本月 vs 上月，今天 vs 昨天

同比（YoY / WoW）： 和去年同期比。今年1月 vs 去年1月，本周一 vs 上周一

环比看短期趋势，同比看长期变化（排除了季节性波动）。

场景一：日环比（DoD）

最基础的，DAY 5已经讲过，这里快速回顾：

SELECT
  dt,
  cost,
  LAG(cost, 1) OVER (ORDER BY dt) AS prev_day_cost,
  ROUND(
    (cost - LAG(cost, 1) OVER (ORDER BY dt)) * 100.0 /
    NULLIF(LAG(cost, 1) OVER (ORDER BY dt), 0),
    2
  ) AS dod_rate
FROM ad_daily_cost
WHERE channel = 'Facebook'

场景二：月环比（MoM）

这里有个关键问题：月度数据通常需要先聚合到月级别，再算环比。

WITH monthly AS (
  SELECT
    DATE_FORMAT(dt, 'yyyy-MM') AS month,
    SUM(cost) AS monthly_cost
  FROM ad_daily_cost
  WHERE channel = 'Facebook'
  GROUP BY DATE_FORMAT(dt, 'yyyy-MM')
)
SELECT
  month,
  monthly_cost,
  LAG(monthly_cost, 1) OVER (ORDER BY month) AS prev_month_cost,
  ROUND(
    (monthly_cost - LAG(monthly_cost, 1) OVER (ORDER BY month)) * 100.0 /
    NULLIF(LAG(monthly_cost, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_rate
FROM monthly

坑点：如果某个月没有数据怎么办？

比如2月份Facebook完全没投放，monthly表里就没有2月的记录。LAG取到的"上个月"其实是1月（而不是不存在的2月），导致3月的环比是和1月比的。

解法：用日历表补全所有月份。

生成连续月份

WITH all_months AS (
  -- 生成连续月份
  SELECT DATE_FORMAT(DATE_ADD('2025-01-01', idx, 'mm'), 'yyyy-MM') AS month
  FROM (SELECT EXPLODE(SEQUENCE(0, 11)) AS idx) t
),
monthly AS (
  SELECT
    DATE_FORMAT(dt, 'yyyy-MM') AS month,
    SUM(cost) AS monthly_cost
  FROM ad_daily_cost
  WHERE channel = 'Facebook'
  GROUP BY DATE_FORMAT(dt, 'yyyy-MM')
)
SELECT
  a.month,
  COALESCE(m.monthly_cost, 0) AS monthly_cost,
  LAG(COALESCE(m.monthly_cost, 0), 1) OVER (ORDER BY a.month) AS prev_month_cost
FROM all_months a
LEFT JOIN monthly m ON a.month = m.month

这个细节在实际工作中非常重要。我做广告月报的时候就遇到过，某个渠道某个月暂停投放，下个月恢复后环比显示增长了99999%，被老板质疑数据有问题。加了日历表补0之后才正常。

场景三：年同比（YoY）

方式一：LAG偏移12个月

如果数据是月度汇总表，直接LAG 12个位置：

SELECT
  month,
  monthly_cost,
  LAG(monthly_cost, 12) OVER (ORDER BY month) AS same_month_last_year,
  ROUND(
    (monthly_cost - LAG(monthly_cost, 12) OVER (ORDER BY month)) * 100.0 /
    NULLIF(LAG(monthly_cost, 12) OVER (ORDER BY month), 0),
    2
  ) AS yoy_rate
FROM monthly_stats

但这要求数据必须是连续的，中间不能断。

方式二：自JOIN（更稳健）

SELECT
  a.month,
  a.monthly_cost AS this_year,
  b.monthly_cost AS last_year,
  ROUND(
    (a.monthly_cost - b.monthly_cost) * 100.0 /
    NULLIF(b.monthly_cost, 0),
    2
  ) AS yoy_rate
FROM monthly_stats a
LEFT JOIN monthly_stats b
  ON SUBSTR(a.month, 6, 2) = SUBSTR(b.month, 6, 2)  -- 月份相同
  AND CAST(SUBSTR(a.month, 1, 4) AS INT) = CAST(SUBSTR(b.month, 1, 4) AS INT) + 1  -- 年份差1

自JOIN的好处是不怕数据中间有缺失，而且逻辑更清晰。面试的时候两种方法都说一下，展示你知道各自的优缺点。

场景四：周同比（WoW）

在广告投放中，周同比比日环比更有价值，因为广告消耗通常有明显的周内波动——工作日和周末差异很大。

SELECT
  dt,
  cost,
  LAG(cost, 7) OVER (PARTITION BY channel ORDER BY dt) AS same_day_last_week,
  ROUND(
    (cost - LAG(cost, 7) OVER (PARTITION BY channel ORDER BY dt)) * 100.0 /
    NULLIF(LAG(cost, 7) OVER (PARTITION BY channel ORDER BY dt), 0),
    2
  ) AS wow_rate
FROM ad_daily_cost

我给投放团队做的日报里，日环比和周同比是并排展示的。日环比波动大看趋势，周同比排除了"今天是周末所以低"这种噪音，更能反映真实的变化。

场景五：累计同比（YTD YoY）

这是财务和管理层最爱看的指标：今年截止当前的累计值 vs 去年同期的累计值。

WITH daily_with_year AS (
  SELECT
    dt,
    YEAR(dt)  AS yr,
    MONTH(dt) AS mn,
    DAYOFYEAR(dt) AS doy,
    cost
  FROM ad_daily_cost
  WHERE channel = 'Facebook'
),
ytd AS (
  SELECT
    yr,
    doy,
    SUM(cost) OVER (PARTITION BY yr ORDER BY doy) AS ytd_cost
  FROM daily_with_year
)
SELECT
  a.yr,
  a.doy,
  a.ytd_cost AS this_year_ytd,
  b.ytd_cost AS last_year_ytd,
  ROUND(
    (a.ytd_cost - b.ytd_cost) * 100.0 / NULLIF(b.ytd_cost, 0),
    2
  ) AS ytd_yoy_rate
FROM ytd a
LEFT JOIN ytd b
  ON a.yr = b.yr + 1
  AND a.doy = b.doy
WHERE a.yr = 2025

用DAYOFYEAR来对齐两年的数据，避免因为闰年导致日期错位的问题。

面试综合题：一条SQL算出所有对比指标

面试官可能会直接给你一张日粒度表，让你同时算出日环比、周同比、月累计、年同比。考察的是你能不能在一条SQL里组合多种窗口函数。

SELECT
  dt,
  channel,
  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,
  -- 月累计
  SUM(cost) OVER (
    PARTITION BY channel, DATE_FORMAT(dt, 'yyyy-MM')
    ORDER BY dt
  ) AS mtd_cost,
  -- 7日滑动均值
  ROUND(AVG(cost) OVER (
    PARTITION BY channel ORDER BY dt
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS avg_7d
FROM ad_daily_cost
WINDOW w AS (PARTITION BY channel ORDER BY dt)
ORDER BY channel, dt

这条SQL一次性输出了4个对比维度，面试写出来就很能说明你的SQL功底了。

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