同环比、月同比/年同比写出来总差一天——时间类指标SQL怎么写才不出错？

-- 基础写法：窗口函数做同环比（适合连续无缺口的日期序列）
SELECT stat_date, pay_amount,
  LAG(pay_amount, 1) OVER (ORDER BY stat_date)   AS prev_day,   -- 昨日
  LAG(pay_amount, 7) OVER (ORDER BY stat_date)   AS prev_week,  -- 上周同期
  ROUND(pay_amount / NULLIF(LAG(pay_amount, 1)
        OVER (ORDER BY stat_date), 0) - 1, 4)    AS dod_ratio
FROM ads_trade_daily
WHERE stat_date >= '2025-01-01';
 
-- 注意两点：
-- 1. NULLIF防除零：基期为0时环比应显示为空，不是报错也不是无穷大
-- 2. LAG(n)依赖“每天都有一行”——中间缺一天，上周同期就错位成6天前

-- 日期维表：所有时间口径预计算，一次建对，全公司复用
CREATE TABLE dim_date (
  date_key        STRING COMMENT '20250310',
  prev_day_key    STRING COMMENT '昨日',
  prev_week_key   STRING COMMENT '7天前（上周同期）',
  prev_month_key  STRING COMMENT '上月同期日，2/28处理见下',
  prev_year_key   STRING COMMENT '去年同期日，闰年处理见下',
  month_begin_key STRING COMMENT '本月1日（月同比起点）',
  year_begin_key  STRING COMMENT '本年1月1日（年同比起点）',
  week_of_year    STRING COMMENT 'ISO周号',
  is_month_end    TINYINT COMMENT '是否月末'
);
 
-- 同环比一律走JOIN，维度缺行也不会错位
SELECT cur.stat_date, cur.country, cur.pay_amount,
       pw.pay_amount AS prev_week_amount
FROM ads_trade_daily cur
JOIN dim_date d  ON cur.stat_date = d.date_key
LEFT JOIN ads_trade_daily pw
  ON pw.stat_date = d.prev_week_key
 AND pw.country   = cur.country;   -- 基期缺数=NULL，不会错位

日期维表把所有恶心的边界处理集中到一个地方：3月31日的“上月同期”是2月28日还是2月30日（不存在）？2月29日的“去年同期”是哪天？——这些规则在建维表时和业务一次性定死（我们的约定：对齐不到就取当月最后一天），之后所有报表自动继承同一套规则。没有日期维表的团队，这些边界每个开发各写一套CASE WHEN，口径想一致都难。

月同比/年同比的两个经典坑

坑一：月同比对比要“同进度”。3月10日的月同比和2月整月比是没有意义的，要和2月1-10日（上月同进度月同比）比。很多报表直接拿月同比除以上月总额，月初环比总是暴跌、月末总是回正，业务方看得一头雾水。

坑二：年同比在年初的除零和跳变。1月1日的年同比环比、以及去年同期年同比为0的分支，都要显式处理成NULL展示为“-”。

-- 月同比同进度对比的正确写法
SELECT
  SUM(CASE WHEN t.stat_date BETWEEN d.month_begin_key AND d.date_key
           THEN pay_amount END)  AS 月同比_amount,      -- 本月1日~今天
  SUM(CASE WHEN t.stat_date BETWEEN d.prev_month_begin_key
                                AND d.prev_month_same_day_key
           THEN pay_amount END)  AS last_月同比_amount  -- 上月1日~上月同日
FROM ads_trade_daily t
CROSS JOIN (SELECT * FROM dim_date WHERE date_key = '${bizdate}') d
WHERE t.stat_date >= d.prev_month_begin_key;
-- “同进度”三个字是月同比对比的灵魂，维表里预置好同日键最稳

时间口径陷阱清单

陷阱	现象	处理
周的定义	ISO周（周一起）vs 周日起，跨年周归属	全司统一用ISO周，写进字典
月末对齐	3/31的上月同期不存在	约定取上月最后一天，维表固化
闰年2/29	去年同期不存在	约定对齐2/28，维表固化
基期缺数	新上线业务没有去年数据	同比显示“-”，不显示0或暴涨
节假日错位	春节在1月or2月，同比失真	补充农历对齐口径供参考
时区	跨国业务“同一天”不同区间	下一篇专门讲

节假日错位对电商、内容行业特别致命：春节漂移会让1、2月的同比完全失真。我们的做法是核心指标在春节窗口额外提供一条“农历对齐同比”曲线，两条线一起看，业务方自己就能分辨哪部分是节日效应。

面试高频追问

追问一：环比暴跌，业务方问是不是数据错了，怎么快速判断？

时间类指标异常的排查有个固定顺序：先查基期再查本期。第一步看基期是不是异常高（昨天有大促、有补数、有重复计算），环比暴跌经常是“昨天错了”而不是“今天错了”；第二步看本期数据是否完整（今天的分区跑完了没有，是不是拿半天数据在和全天比——这是最高频的假暴跌）；第三步才是业务真实波动。把“数据完整性检查”做进报表：产出时间没到、分区行数异常时，当天的环比先不展示，能消灭一大半这类问询。

追问二：为什么不直接用BI工具自带的同环比功能？

BI的快速计算功能做探索分析很方便，但正式报表我更倾向在数据层算好。三个原因：一是口径可控——BI的“上月同期”怎么处理月末对齐，行为因工具而异，黑盒；二是跨报表一致——数据层算一次，所有报表、取数、API拿到同一个数，BI层各图表各算容易发散；三是性能——数据层算好是取数，BI现算是多倍查询。原则还是那条：核心指标的计算逻辑收口在数据层，BI只做展示。

追问三：实时报表里的“今日环比昨日”怎么做才公平？

实时场景比的应该是“同进度”：今天截至14:00的值，对比昨天截至14:00的值，而不是昨天全天。实现上给实时聚合表按小时（或分钟桶）存累计值，对比时取昨天同一时间桶。不做同进度处理的实时环比整天都是“暴跌”，到23:59才追平，纯粹制造焦虑。这个和月同比同进度是同一个思想：对比的分母必须是可比的。

实战踩坑：跨年那周的周报翻车

在某短剧出海公司踩过ISO周的坑。周报用了某个SQL函数取周号，跨年那周（12月29日到1月4日）出了事：那个函数按自然年算周，把这一周劈成了两半，12月的三天算作第52周、1月的四天算作第1周，两个“残周”的数据都异常低，周报曲线在年末年初连续两个深坑。投放同学以为业务暴跌，紧急拉会。查出来后我把周口径统一成ISO周（跨年周完整归属到ISO规则定义的那一年），日期维表里预置week_of_year，所有报表禁止自己调周函数。第二年跨年，曲线平滑如常。

-- 坑的复现：不同函数对跨年周的处理完全不同
SELECT WEEKOFYEAR('2025-01-01');  -- ISO规则：属于上一年第52/53周或第1周
SELECT WEEK('2025-01-01');        -- 某些实现：直接算第0或第1周
-- 同一天，不同函数给出不同周号，两张报表就此分叉
 
-- 解法：周号只从日期维表取，禁止业务SQL直接调周函数
SELECT d.week_of_year, SUM(t.pay_amount)
FROM ads_trade_daily t JOIN dim_date d ON t.stat_date = d.date_key
GROUP BY d.week_of_year;

教训：日期函数的行为在不同引擎、不同函数之间差异极大。时间口径这种全局性的东西，必须收敛到日期维表这一个出口。

总结

时间类指标不出错靠两件事：口径钉死（基期写进指标名、周和月末闰年规则写进字典）、对齐交给日期维表（按日期JOIN，不用LAG按行偏移，不让业务SQL裸写日期函数）。月同比/年同比记住“同进度”三个字，实时环比也一样。面试时讲出“LAG错位”“跨年周”这两个坑的细节，面试官立刻知道你是真的在生产环境写过周报月报的人。

下一篇把时间的坑挖到最深处：海外业务的日报，“今天”到底是哪天——跨时区报表怎么处理？

你踩过最隐蔽的日期坑是什么？评论区聊聊～

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