如何用SQL窗口函数计算同比增长率？-SQL-PHP中文网

同比增长率的sql窗口函数写法是先按年月聚合再用lag(sales,12) over (order by year_month)取去年同期值，配合nullif防除零；必须避免按年份partition by导致越界，且需统一时区与数据粒度。

如何用sql窗口函数计算同比增长率？

什么是同比增长率的SQL窗口函数写法

同比增长率本质是「当前期值减去去年同期值，再除以去年同期值」，窗口函数能帮你避免自连接或子查询。关键在于用 LAG() 拿到去年同期数据，而不是用 ROW_NUMBER() 或 RANK()——后者无法按时间偏移取值。

常见错误是直接对日期字段做 LAG(sales, 1)，结果拿的是上一条记录（比如 2023-03-02 的上一条可能是 2023-03-01），不是去年同月。必须先按年月聚合，再按年份排序。

先用 GROUP BY YEAR(order_date), MONTH(order_date) 或 DATE_FORMAT(order_date, '%Y-%m') 聚合月度数据
再用 ORDER BY year_month 排序，确保 LAG() 能跨年对齐
偏移量固定为 12（月）或 4（季度），不能写成动态值

MySQL 和 PostgreSQL 中 LAG 的参数差异

MySQL 8.0+ 和 PostgreSQL 都支持 LAG(value, offset, default)，但默认值行为不同：MySQL 若不指定第三参数，遇到越界返回 NULL；PostgreSQL 同样如此，但部分旧版本需显式写 NULL。更关键的是日期处理方式：

MySQL 常用 DATE_FORMAT(order_date, '%Y-%m') 生成字符串型年月，排序稳定
PostgreSQL 更推荐用 TO_CHAR(order_date, 'YYYY-MM')，或直接用 DATE_TRUNC('month', order_date)（返回 timestamp，排序更准）
偏移量统一用 12，别用 INTERVAL '1 year' ——LAG() 不接受时间间隔，只接受整数行数

示例（MySQL）：

切问学术

切问学术是一款AI论文写作工具，复旦大学NLP团队推出的AI学术智能体。

下载

SELECT 
  year_month,
  sales,
  LAG(sales, 12) OVER (ORDER BY year_month) AS last_year_sales,
  ROUND((sales - LAG(sales, 12) OVER (ORDER BY year_month)) / NULLIF(LAG(sales, 12) OVER (ORDER BY year_month), 0), 4) AS yoy_rate
FROM monthly_summary;

NULLIF 和除零错误怎么避坑

当去年同期销售额为 0，直接除会报错或得 Inf。不能只靠 WHERE last_year_sales != 0 过滤——这会丢掉本年有销售、去年为 0 的情况（比如新门店开业）。必须用 NULLIF() 把分母为 0 变成 NULL，再配合 ROUND(..., 4) 控制小数位。

NULLIF(denominator, 0) 是标准写法，比 CASE WHEN denominator = 0 THEN NULL ELSE ... END 简洁且可读性高
如果业务要求把「去年为 0、今年 > 0」标为 1.0（即增长无穷大），得额外加 CASE 判断，NULLIF 本身不处理这种语义
某些 BI 工具对 NULL 做图表时会跳过，需确认下游是否接受空值