MySQL5.5无窗口函数 按uid分组计算同组相邻行日期差值
MySQL 5.5 实现分组相邻行日期差值的可行方案
因为MySQL 5.5完全不支持窗口函数,没法用LAG()这类语法直接取上一行的值,实际落地常用两种无依赖的实现方式:
方案1:用户变量模拟行号关联
核心逻辑是给每个分组内按日期排序的行打上连续递增的行号,再把同组内行号差为1的相邻行做关联,计算两个日期的差值。
具体实现SQL:
SELECT t1.uid, DATEDIFF(t2.data_date, t1.data_date) AS date_diff FROM ( -- 为原表每行按uid分组、data_date排序打行号 SELECT uid, data_date, @rank_curr := IF(@uid_cache = uid, @rank_curr + 1, 1) AS row_rank, @uid_cache := uid FROM your_test_table, -- 初始化用户变量 (SELECT @uid_cache := '', @rank_curr := 0) v_init ORDER BY uid, data_date ) t1 INNER JOIN ( -- 重复打一次行号,匹配相邻行 SELECT uid, data_date, @rank_next := IF(@uid_cache2 = uid, @rank_next + 1, 1) AS row_rank, @uid_cache2 := uid FROM your_test_table, (SELECT @uid_cache2 := '', @rank_next := 0) v_init2 ORDER BY uid, data_date ) t2 ON t1.uid = t2.uid AND t2.row_rank = t1.row_rank + 1 ORDER BY t1.uid, t1.data_date
注意事项:
- 必须把带变量初始化的派生表放在FROM子句最前面,否则MySQL的执行顺序可能会打乱变量赋值逻辑,导致行号计算错误
- 如果原表数据量超过10万行,可以把第一次打行号的结果存入临时表,避免两次全表扫描拖慢性能
方案2:自连接匹配相邻后序日期
如果原表已经在(uid, data_date)字段上建了联合索引,这种写法没有用户变量的坑,性能更稳定。
核心逻辑是对每一行记录,找同uid下比当前日期更大、且是最小的那个日期,两者的差值就是相邻行的差,自动过滤掉每个分组最早的那行(刚好符合预期输出不需要第一行差值的要求)。
具体实现SQL:
SELECT t_curr.uid, DATEDIFF(MIN(t_next.data_date), t_curr.data_date) AS date_diff FROM your_test_table t_curr INNER JOIN your_test_table t_next ON t_curr.uid = t_next.uid AND t_next.data_date > t_curr.data_date GROUP BY t_curr.uid, t_curr.data_date ORDER BY t_curr.uid, t_curr.data_date
注意事项:
- 没有
(uid, data_date)联合索引的话,自连接会触发全表扫描,大表场景性能极差,一定要先补索引 - 这种写法不需要处理用户变量的赋值顺序问题,结果稳定性更高
内容的提问来源于stack exchange,提问作者Jack
相关产品推荐
相关产品推荐

