真正意义上的单次扫描要求主查询仅对事实表遍历一次,须依赖窗口函数LAG/LEAD实现;MySQL 5.7及之前不支持,必须多扫;环比用LAG(sales,1) OVER(ORDER BY ym),同比需确保年月连续或补全时间序列后再LAG(sales,12)。

什么是“单次扫描”计算环比同比的核心约束
真正意义上的单次扫描,是指主查询只对原始事实表(比如 sales)做一次全表或按时间分区的遍历,不重复读取同一张表多次。很多写法看似用了一个 SELECT,但内部含多个子查询或 JOIN 同一张表的不同别名(如 t1 和 t2),实际执行计划里仍会触发多次扫描 —— 这不算单次扫描。
MySQL 8.0+、PostgreSQL、SQL Server 2012+、Oracle 等支持窗口函数的数据库,才能靠 LAG()/LEAD() 或 LAG(value, n) OVER (ORDER BY ...) 实现严格单次扫描;旧版 MySQL(5.7 及之前)无法做到,必须用变量或自连接,本质仍是多扫。
用 LAG() 计算环比(月度/日度)的正确写法
环比 = 当前周期值 / 上一周期值 − 1,关键在把“上一周期”作为同一次扫描中的前置行获取,而非另起子查询查表。
-
LAG()必须配合OVER (ORDER BY period),且period字段需能线性排序(如DATE、INT年月码) - 若按自然月环比,先确保数据已按月聚合(如用
GROUP BY YEAR(date), MONTH(date)),再套窗口函数,否则LAG()作用在明细行上无意义 - 注意
NULL处理:首行的LAG()返回NULL,直接参与除法会得NULL,建议用COALESCE(LAG(...), 0)或显式CASE WHEN
SELECT
ym,
sales_amt,
COALESCE(
ROUND((sales_amt - LAG(sales_amt) OVER (ORDER BY ym)) / NULLIF(LAG(sales_amt) OVER (ORDER BY ym), 0), 4),
0
) AS mom_ratio
FROM (
SELECT
DATE_FORMAT(order_date, '%Y%m') AS ym,
SUM(amount) AS sales_amt
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y%m')
) t;同比需要错位 12 行,但要注意年份边界
同比不是简单 LAG(sales, 12) 就完事。如果数据是按自然月聚合('202301', '202302'…),那 LAG(sales_amt, 12) OVER (ORDER BY ym) 是可行的;但如果原始数据是日粒度未聚合,或存在跨年缺失月份(比如 202212 有数据、202312 缺失),LAG(..., 12) 会取到 202211 而非 202212,结果错位。
- 安全做法:先生成完整的时间序列(用递归 CTE 或日历表补全所有年月),再
LEFT JOIN销售数据,最后对齐后用LAG(..., 12) - 若坚持纯子查询模拟单扫(如兼容 MySQL 5.7),常见陷阱是用
(SELECT ... FROM sales s2 WHERE s2.ym = CONCAT(YEAR(s1.ym)-1, LPAD(MONTH(s1.ym),2,'0')))—— 这属于相关子查询,执行时对每行都重新查一次表,I/O 放大严重,不是单次扫描 - PostgreSQL 中可用
LAG(sales_amt, 12) OVER (ORDER BY ym)直接写,前提是ym是连续整数或可排序字符串('202301''202302' ✅,但'20231''202310' ❌)
MySQL 5.7 怎么办?变量法是折中解,但不等于单扫
MySQL 5.7 不支持窗口函数,有人用用户变量(@prev_amt := @curr_amt)模拟 LAG,看起来只扫一次表,但变量赋值顺序依赖执行计划,官方已明确声明“变量在 SELECT 中的行为不保证”,尤其在加了 ORDER BY 或涉及优化器重排时极易出错。
- 强制排序 + 变量赋值必须写在同一
SELECT的字段列表中,且变量初始化要放在最外层SELECT或FROM子句(如(SELECT @prev := NULL) AS _) - 即使跑通,该方案在并发查询下可能因变量共享导致混乱,生产环境慎用
- 真正稳健的替代:升级到 MySQL 8.0,或改用物化临时表 + 自连接(虽多扫,但逻辑清晰、结果确定)
单次扫描的实质是让数据库引擎在一次迭代中完成当前行与历史行的关联。窗口函数是目前唯一被各主流引擎明确定义、稳定支持的机制。其它技巧要么牺牲可靠性,要么只是“语法上单个 SELECT”,实际执行并不省 I/O。

















