如何使用SQL计算环比和同比等高级业务指标？-SQL-PHP中文网

sql中计算环比最稳妥的方法是使用lag()窗口函数，按时间排序后取前一行值，避免用date_sub导致的月末错配；需配合nullif防除零，并确保日期字段规范、无缺失。

如何使用sql计算环比和同比等高级业务指标？

SQL里怎么写环比（month-over-month）

环比本质是当前期与上一期的差值或比率，关键在于把“上期”数据和“本期”数据对齐到同一行。最稳妥的做法是用 LAG() 窗口函数，它能按时间排序后直接取前一行的值，不用关联子查询或自连接。

常见错误是用 DATE_SUB(date, INTERVAL 1 MONTH) 去匹配上月数据——这在月末（如1月31日 vs 2月28日）或跨年时容易漏行或错配；还有人用 GROUP BY 后再 JOIN 上月表，但日期不连续或有缺失时结果会出错。

实操建议：

确保时间字段是 DATE 类型，且已去重、无空值
按业务口径确定“期”：是自然月（YEAR_MONTH）、滚动30天，还是财年周期？统一用 DATE_FORMAT(order_date, '%Y-%m') 或 PERIOD_DIFF()（MySQL）生成期标识

核心写法：

SELECT  
  order_month,
  revenue,
  LAG(revenue) OVER (ORDER BY order_month) AS last_month_revenue,
  ROUND((revenue - LAG(revenue) OVER (ORDER BY order_month)) / NULLIF(LAG(revenue) OVER (ORDER BY order_month), 0), 4) AS mom_rate
FROM monthly_summary;

NULLIF(..., 0) 必须加，否则除零报错；LAG() 第一行默认返回 NULL，天然适配首期无环比

同比（year-over-year）为什么不能只改个日期减一年

同比不是简单把日期减365天，而是要对齐相同业务周期：比如2024年4月 vs 2023年4月，不是2024-04-15 vs 2023-04-14。直接用 DATE_SUB(date, INTERVAL 1 YEAR) 在闰年、月末、节假日错位时会导致数据错行。

更麻烦的是，如果原始明细表没按月聚合，而你又想算“2024年4月销售额同比”，就得先聚合再比——但若聚合逻辑（如剔除退款、含税不含税）前后不一致，同比数字就失真。

实操建议：

先用 YEAR(order_date) 和 MONTH(order_date) 构建双字段分组键，避免用字符串拼接（如 '2023-04'）导致排序错乱
用 LAG(revenue, 12)（假设按月聚合）代替日期运算，前提是数据严格按月连续、无断层；若中间缺2023年2月，则2024年2月的 LAG(..., 12) 会跳到2023年1月

更健壮的做法：用自连接 + 显式周期匹配

SELECT 
  curr.month_key,
  curr.revenue,
  prev.revenue AS last_year_revenue
FROM monthly_data curr
LEFT JOIN monthly_data prev 
  ON curr.year = prev.year + 1 
  AND curr.month = prev.month;

注意 LEFT JOIN 保证当期数据不丢，但需检查 prev.revenue 是否为 NULL（说明去年同月无数据）

多个指标一起算时，窗口函数顺序和NULL怎么处理

一个查询里同时算环比、同比、3个月移动平均，很容易写出一堆重复的 LAG() 和 AVG() OVER，不仅难读，性能也差——每个窗口函数都会单独扫描一遍数据。

Ponder AI

Ponder AI是一款AI思维导图工具，AI知识管理和思维导图工具。

下载

另一个坑是忽略 NULL 传播：比如某月收入为0，LAG() 返回 NULL，后续所有基于它的计算（如增长率）全变 NULL，而实际业务中可能希望显示“-100%”或“N/A”。

实操建议：

用 CTE 预先算好基础指标，再在主查询里复用

WITH base AS (
  SELECT 
    order_month,
    SUM(amount) AS revenue,
    LAG(SUM(amount), 1) OVER (ORDER BY order_month) AS last_month_rev,
    LAG(SUM(amount), 12) OVER (ORDER BY order_month) AS last_year_rev
  FROM orders 
  GROUP BY order_month
)
SELECT 
  order_month,
  revenue,
  CASE WHEN last_month_rev > 0 THEN (revenue - last_month_rev)/last_month_rev END AS mom_rate,
  CASE WHEN last_year_rev > 0 THEN (revenue - last_year_rev)/last_year_rev END AS yoy_rate
FROM base;

别用 COALESCE(LAG(...), 0) 填0——这会让“上月无数据”和“上月收入为0”无法区分；用 CASE WHEN last_month_rev IS NULL THEN 'N/A' ELSE ... END 更安全
移动平均慎用 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：如果某月数据缺失，窗口内只有2行，平均值就偏高；业务上更常用“过去3个完整自然月”的固定周期，得靠日期过滤+子查询

不同数据库对窗口函数的支持差异

MySQL 8.0+、PostgreSQL、SQL Server 2012+、BigQuery 都支持标准 LAG()，但旧版 MySQL（5.7）或 Hive SQL 1.x 不支持，只能用自连接或 ROW_NUMBER() 模拟，代码复杂度陡增。

Oracle 的 LAG() 默认支持 IGNORE NULLS，而 PostgreSQL 和 MySQL 不支持——如果你的“上期”数据可能为空（如某月没销售），直接 LAG() 会跳过空行取更早的值，结果错位。

实操建议：

确认目标库版本：SELECT VERSION();（MySQL）、SELECT current_setting('server_version');（PostgreSQL）

MySQL 5.7 或更老版本，用自连接模拟 LAG()：

SELECT 
  t1.order_month,
  t1.revenue,
  t2.revenue AS last_month_rev
FROM monthly_data t1
LEFT JOIN monthly_data t2 
  ON t2.order_month = DATE_SUB(t1.order_month, INTERVAL 1 MONTH);

Hive 中 LAG() 存在但性能差，建议先用 DISTRIBUTE BY + SORT BY 保证分区有序，再用 LAG()，否则结果随机
所有数据库里，OVER (ORDER BY ...) 的排序字段必须唯一，否则相同时间戳的多行会“挤”在一起，LAG() 取值不稳定；加 id 或 row_number() 作第二排序键