怎么在SQL中通过SELF JOIN计算环比和同比增幅-SQL-PHP中文网

self join计算环比/同比需先归一化时间粒度：环比用月首/周首对齐，同比用year+month拆解；须处理null值并建索引优化性能。

怎么在sql中通过self join计算环比和同比增幅

SELF JOIN 计算环比：用日期偏移匹配上期数据

环比（月/周/日比上期）本质是把当前行和它“往前推 N 个单位时间”的那行做对比。SELF JOIN 是最直接的方式：用同一张表两次，通过日期字段做偏移关联。

关键不是硬写 DATE_SUB 或 ADD_MONTHS，而是先明确时间粒度——比如按自然月环比，就得对齐到月份首日；按周环比，得统一到周一（或周日）。否则 2024-03-15 和 2024-02-15 表面差一月，但可能跨了 29~31 天，导致分组错位。

常见错误是直接写 ON t1.date = DATE_SUB(t2.date, INTERVAL 1 MONTH)，结果因月末日期不一致（如 2024-03-31 减一个月是 2024-02-29，但那天没数据），JOIN 失败。

推荐做法：

先用 DATE_FORMAT(t.date, '%Y-%m-01') 或 TRUNC(t.date, 'MM')（Oracle）归一化到月首
再 SELF JOIN：ON DATE_FORMAT(t1.date, '%Y-%m-01') = DATE_ADD(DATE_FORMAT(t2.date, '%Y-%m-01'), INTERVAL 1 MONTH)
SELECT 时保留原始日期用于展示，用归一化字段做关联

SELF JOIN 计算同比：小心闰年和月末对齐

同比（今年某月 vs 去年同月）看似简单，但陷阱更多。直接 ON t1.date = DATE_SUB(t2.date, INTERVAL 1 YEAR) 在闰年会出问题：比如 2024-02-29 减一年是 2023-02-29，但 2023 年没有这一天，MySQL 返回 NULL，JOIN 断掉。

更稳妥的是按年月维度对齐：

用 YEAR(t.date) 和 MONTH(t.date) 拆解
JOIN 条件写成：ON YEAR(t1.date) = YEAR(t2.date) + 1 AND MONTH(t1.date) = MONTH(t2.date)
如果需排除跨年节假日扰动（如春节在 1 月或 2 月浮动），可加 WEEKOFYEAR 辅助对齐

注意：这种写法会把所有同月数据拉平（比如 2024-01-01 和 2024-01-31 都对应 2023-01 全月），若要精确到“同日同比”，必须接受 2 月 29 日无对应值，或用 LAST_DAY 做兜底。

环比/同比增幅的 NULL 安全写法

SELF JOIN 后，上期或去年同期数据缺失时，t2.value 是 NULL，直接算 (t1.value - t2.value) / t2.value 会得 NULL，而非 0 或空字符串。

必须显式处理：

用 COALESCE(t2.value, 0) 可避免除零，但 0 作为分母仍报错，所以得嵌套：NULLIF(COALESCE(t2.value, 0), 0)
更合理的是：只对有同比/环比数据的行计算，用 CASE WHEN t2.value IS NOT NULL THEN (t1.value - t2.value) / t2.value END
百分比建议乘 100 后 ROUND(..., 2)，避免浮点误差干扰显示

示例片段（MySQL）：

SELECT
  t1.date,
  t1.sales,
  t2.sales AS last_month_sales,
  ROUND(
    CASE WHEN t2.sales IS NOT NULL AND t2.sales != 0
      THEN (t1.sales - t2.sales) / t2.sales * 100
    END, 2
  ) AS mom_pct
FROM sales t1
LEFT JOIN sales t2
  ON DATE_FORMAT(t1.date, '%Y-%m-01') = DATE_ADD(DATE_FORMAT(t2.date, '%Y-%m-01'), INTERVAL 1 MONTH);

性能隐患：日期字段没索引，JOIN 直接变全表扫描

SELF JOIN 的代价是表被读两遍，如果没索引，ON 条件中的日期转换（如 DATE_FORMAT(date, '%Y-%m-01')）会让索引失效。

必须提前准备：

建生成列（MySQL 5.7+）：ALTER TABLE sales ADD COLUMN ym CHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(date, '%Y-%m')) STORED;
在该列上建索引：CREATE INDEX idx_ym ON sales(ym);
JOIN 改用 ON t1.ym = DATE_FORMAT(DATE_ADD(t2.date, INTERVAL 1 MONTH), '%Y-%m')，或更优：直接 ON t1.ym = t2.ym（需 t2 也有 ym 列）

没有索引支撑的 SELF JOIN，在百万级表上可能从秒级拖到分钟级，且无法靠加内存缓解。

真实场景中，环比同比常需聚合后计算（如按月汇总再比），这时候先 GROUP BY 再 SELF JOIN，比在明细层 JOIN 更高效——但要注意业务是否允许丢失明细维度（如用户 ID）。这个权衡点容易被忽略。