MySQL 5.7 实现时间序列每日获取SKU最近已知价格的查询问题
MySQL 5.7 连续日期补全历史价格实现方案
原存储过程的核心问题
- 关联逻辑仅按日期匹配,未校验SKU维度,多SKU场景会出现不同商品价格的串扰
- 无调价记录的日期直接返回NULL,未做最近历史价格的回溯填充
- 临时表未做数据清理,重复调用时会残留旧日期数据,导致结果异常
- 调用时直接传数值格式的日期参数,会触发隐式类型转换,引发匹配错误
实现思路
MySQL 5.7 不支持8.0版本的窗口函数,可通过用户自定义变量遍历记录的方式实现空值填充,整体逻辑分为四步:
- 保留原逻辑生成查询时间范围内的连续日期临时表,每次执行前先清空临时表残留数据
- 将连续日期表与价格表中所有出现过的SKU做交叉匹配,生成每个SKU对应时间范围的全量日期基础数据
- 关联实际价格表时同时匹配日期、SKU两个维度,拿到每个(日期、SKU)组合的实际调价记录
- 按SKU分组、日期正序遍历记录,通过变量缓存最近一次的非空价格值;无当日调价记录的行,直接取缓存的历史价格作为当前行价格;SKU切换时自动重置价格缓存,避免跨SKU数据错乱
修正后的完整存储过程代码
delimiter // create definer = root@localhost procedure sp1(IN d1 date, IN d2 date) BEGIN -- 创建连续日期临时表 create temporary table if not exists foo (d date not null); -- 清空临时表旧数据,避免重复调用出现数据堆积 truncate table foo; -- 循环生成范围内的所有日期 set @d = d1; while @d <= d2 do insert into foo (d) values (@d); set @d = date_add(@d, interval 1 day); end while; -- 带价格填充的最终查询 select t.d as date, t.sku, -- 按逻辑填充价格:无当日调价时取最近历史值 case -- SKU切换时重置缓存的价格为当前行的实际值 when t.sku != @last_sku then @cache_price := t.price -- 当日有调价时更新缓存价格 when t.price is not null then @cache_price := t.price -- 无调价时直接返回缓存的历史价格 else @cache_price end as price, -- 缓存当前行SKU,供下一行判断是否切换分区 @last_sku := t.sku from ( -- 生成所有日期×所有SKU的基础数据集,关联实际调价记录 select foo.d, sku_set.sku, p.price from foo -- 取价格表中所有涉及的SKU cross join (select distinct sku from prices_test2) sku_set -- 双维度匹配当日调价记录 left join prices_test2 p on foo.d = p.date and sku_set.sku = p.sku order by sku_set.sku asc, foo.d asc ) t -- 初始化遍历用的自定义变量 cross join (select @last_sku := '', @cache_price := null) init_var order by t.sku asc, t.d asc; -- 清理临时表 drop temporary table foo; END//
正确的调用方式
-- 日期参数需传入合法的日期格式字符串,不能直接传数值 call sp1('2021-01-10', '2021-01-22');
关键优化说明
- 关联条件补充SKU维度校验,完全解决多SKU场景的数据串扰问题
- 变量逻辑增加SKU分区判断,保证每个SKU的价格缓存独立计算
- 临时表增加
truncate清理逻辑,避免多次调用存储过程时出现旧数据干扰 - 最终查询结果的粒度为「日期+SKU」,与Grafana时间序列图的展示格式完全匹配,可直接配置为数据源
内容的提问来源于stack exchange,提问作者merlin
相关产品推荐
相关产品推荐

