【SQL】SQL同环比计算的多种实现方式

张

张建站

2026/4/25 7:15:52

10分钟阅读

【SQL】SQL中同环比的多种计算方式一、引言二、同环比的核心概念三、实现与案例方法一LEAD / LAG 窗口函数最常用1. 环比按月2. 同比按年方法二自关联Self JOIN1. 环比自关联2. 同比自关联方法三日期偏移法DATE_SUB / DATE_ADD1. 环比DATE_SUB2. 同比DATE_SUB 减1年四、总结对比一、引言数据分析里绕不开的一个话题——同环比计算汇报的时候管理者经常会问“这个月比上个月增长了多少”、“今年相比去年同期表现如何” 这些问题背后的答案都离不开同环比。同比和去年同一时期比环比和上一个周期比。今天讲讲SQL的几种主流写法。SQL专题往期内容【SQL】基于多源SQL 去重方法对比 – 精华版【SQL】常见SQL 行列转换的方法汇总 - 精华版【SQL】MySql常见的性能优化方式【SQL】SQL同环比计算的多种实现方式【SQL】COUNT… FILTER 的适用场景【SQL】SQL的日期与时间函数【SQL】SQL-常见窗口函数有哪些-上篇二、同环比的核心概念基本概念梳理概念说明举例同比YoY和去年同一时期比今年4月 vs 去年4月环比MoM和上一个周期比今年4月 vs 今年3月时间偏移函数用 OFFSET / LAG 等取历史数据数据分析必备窗口函数在分组内做聚合与偏移复杂场景下更灵活常见踩坑避雷点月份/日期不连续时偏移后数据对不上年度同期计算要注意闰年和月份天数差异聚合粒度不同会导致结果偏差三、实现与案例下面以一张销售数据表sales为例演示几种主流写法-- 先建表CREATETABLEsales(mtDATE,-- 销售月amountDECIMAL(10,2)-- 销售额);测试数据如下mtamount2024-01-0110002024-02-0112002024-03-0111002024-04-0115002025-01-0113002025-02-0114002025-03-011350方法一LEAD / LAG 窗口函数最常用原理使用LAG(col, n)/LEAD(col,n)往前/往后取 n 行的数据直接在同一行里完成对比。这个函数也是面试中经常会考的一定要熟练应用。优点✅ 一行出结果逻辑清晰✅ 性能好窗口函数内部优化强✅ 兼容 MySQL 8.0 和 Hive缺点❌ 需要数据按时间连续否则会跳空❌ 不支持跨年/跨月批量偏移1. 环比按月-- 环比和上月比SELECTmt,amount,LAG(amount,1)OVER(ORDERBYmt)ASlast_month_amount,-- 上月销售额amount-LAG(amount,1)OVER(ORDERBYmt)ASmoom_diff,-- 环比增长额ROUND((amount-LAG(amount,1)OVER(ORDERBYmt))/LAG(amount,1)OVER(ORDERBYmt)*100,2)ASmoom_rate-- 环比增长率%FROMsalesORDERBYmt;结果mtamountlast_month_amountmoom_diffmoom_rate2024-02-011200100020020.002024-03-0111001200-100-8.33……………2. 同比按年-- 同比去年同一时期SELECTmt,amount,LAG(amount,12)OVER(ORDERBYmt)ASlast_year_amount,-- 去年同月ROUND((amount-LAG(amount,12)OVER(ORDERBYmt))/LAG(amount,12)OVER(ORDERBYmt)*100,2)ASyoy_rate-- 同比增长率%FROMsalesORDERBYmt;核心思路数据是月粒度偏移 12 行就是去年同月。方法二自关联Self JOIN原理把表和自身作JOIN按时间条件关联历史数据。优点✅ 适合复杂 JOIN 场景多表联动缺点❌ 数据量翻倍碰上大数据量长周期的效率非常低❌ 需要注意 NULL 情况首月/首年无历史数据❌ 连续月份数据不能断层否则漏关联1. 环比自关联-- 环比自关联SELECTa.mt,a.amountAScur_amount,b.amountASlast_month_amount,a.amount-b.amountASmoom_diff,ROUND((a.amount-b.amount)/b.amount*100,2)ASmoom_rateFROMsales aLEFTJOINsales bONDATE_SUB(a.mt,INTERVAL1MONTH)b.mt-- 上月本月减1个月ORDERBYa.mt;2. 同比自关联-- 同比自关联SELECTa.mt,a.amountAScur_amount,b.amountASlast_year_amount,ROUND((a.amount-b.amount)/b.amount*100,2)ASyoy_rateFROMsales aLEFTJOINsales bONDATE_SUB(a.mt,INTERVAL1YEAR)b.mt-- 去年同一月ORDERBYa.mt;方法三日期偏移法DATE_SUB / DATE_ADD原理直接构造目标日期然后去原表查对应数据。适合临时查询和调试原理其实和法2差不多优点✅ 代码最简单容易读懂✅ 适合临时查询、一次性脚本缺点❌ 大数据量下 JOIN 性能差❌ 无索引时全表扫描速度慢❌ 日期不连续时结果会丢行1. 环比DATE_SUB-- 环比查上月SELECTa.mt,a.amount,b.amountASlast_month_amount,ROUND((a.amount-b.amount)/b.amount*100,2)ASmoom_rateFROMsales aLEFTJOINsales bONb.mtDATE_SUB(a.mt,INTERVAL1MONTH)ORDERBYa.mt;2. 同比DATE_SUB 减1年-- 同比查去年同月SELECTa.mt,a.amount,b.amountASlast_year_amount,ROUND((a.amount-b.amount)/b.amount*100,2)ASyoy_rateFROMsales aLEFTJOINsales bONb.mtDATE_SUB(a.mt,INTERVAL1YEAR)ORDERBYa.mt;四、总结对比方法原理适用场景难度性能LEAD/LAG 窗口函数窗口内偏移取历史行月/周粒度数据需连续排行★★☆☆☆⭐⭐⭐⭐自关联表和自身 JOIN 按时间条件复杂多表联动/非连续时间★★★☆☆⭐⭐DATE_SUB 偏移构造历史日期再查表临时查询/数据不连续★★☆☆☆⭐⭐推荐数据整齐、连续 → 用LEAD/LAG 窗口函数性能最好复杂场景多表联动 → 用自关联临时调数据/老版本 MySQL → 用DATE_SUB划重点窗口函数是首选方案能解决大多数的同环比问题。