SQL窗口函数如何计算销售额同比增长率-SQL-PHP中文网

直接用lag(sales,12)按日期排序计算同比会出错，因窗口函数取前12行而非前12个月；必须先按年月聚合、补全空月，再用统一月份标识做lag或join，才能准确对齐自然月同比。

sql窗口函数如何计算销售额同比增长率

为什么直接用 `LAG()` 算同比会出错

很多人写 LAG(sales, 12) OVER (ORDER BY date) 就以为能算年同比，结果发现数据对不上——核心问题是：窗口函数按 ORDER BY 的顺序取前12行，不是前12个月。如果某月缺数据（比如2023-02没销售记录），LAG(sales, 12) 会跳到2022-12甚至更早，导致分母错位。

真正要对比的，是“同一自然月”的去年值，不是“上第12行”的值。必须把时间对齐到年月粒度，再做关联或偏移。

先用 DATE_TRUNC('month', order_date)（PostgreSQL）或 YEAR(order_date)*100 + MONTH(order_date)（MySQL）统一月份标识
再用该标识做 LAG() 或 JOIN，而非原始日期字段
如果业务要求严格按日历月（如2024-03 vs 2023-03），必须确保聚合后每月只有一条记录，否则 LAG() 仍可能跨行错位

PostgreSQL 中用 `LAG()` 安全算同比的写法

关键在两步：先按月聚合，再用月份序号做偏移。避免直接在明细表上套 LAG()。

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', order_date)::DATE AS ym,
    SUM(amount) AS sales
  FROM orders
  GROUP BY 1
),
with_lag AS (
  SELECT
    ym,
    sales,
    LAG(sales, 12) OVER (ORDER BY ym) AS sales_ly
  FROM monthly_sales
)
SELECT
  ym,
  sales,
  ROUND((sales - sales_ly) / NULLIF(sales_ly, 0), 4) AS yoy_rate
FROM with_lag;

注意：NULLIF(sales_ly, 0) 防止除零；ORDER BY ym 保证月份严格递增，LAG(..., 12) 才真对应12个月前。

PaperFake

AI写论文

下载

MySQL 8.0+ 怎么处理没有 `DATE_TRUNC()` 的问题

MySQL 没原生 DATE_TRUNC()，得手动构造年月键，且要注意 STR_TO_DATE() 和格式匹配。

用 CONCAT(YEAR(order_date), '-', LPAD(MONTH(order_date), 2, '0')) 生成 '2023-03' 字符串，再转成日期： STR_TO_DATE(CONCAT(...), '%Y-%m')
或者更稳：用 MAKEDATE(YEAR(order_date), 1) + INTERVAL (MONTH(order_date)-1) MONTH 得到当月第一天
务必在子查询里先聚合，再用 LAG()，否则明细行数不等会导致错位

示例片段：

SELECT
  ym,
  sales,
  ROUND((sales - LAG(sales, 12) OVER (ORDER BY ym)) / NULLIF(LAG(sales, 12) OVER (ORDER BY ym), 0), 4) AS yoy_rate
FROM (
  SELECT
    STR_TO_DATE(CONCAT(YEAR(order_date), '-', LPAD(MONTH(order_date), 2, '0')), '%Y-%m') AS ym,
    SUM(amount) AS sales
  FROM orders
  GROUP BY 1
) t;