You need to enable JavaScript to run this app. MySQL 5.7 实现时间序列每日获取SKU最近已知价格的查询问题
优惠活动
大模型
产品
解决方案
定价
更多

MySQL 5.7 实现时间序列每日获取SKU最近已知价格的查询问题

MySQL 5.7 连续日期补全历史价格实现方案

原存储过程的核心问题

  • 关联逻辑仅按日期匹配,未校验SKU维度,多SKU场景会出现不同商品价格的串扰
  • 无调价记录的日期直接返回NULL,未做最近历史价格的回溯填充
  • 临时表未做数据清理,重复调用时会残留旧日期数据,导致结果异常
  • 调用时直接传数值格式的日期参数,会触发隐式类型转换,引发匹配错误

实现思路

MySQL 5.7 不支持8.0版本的窗口函数,可通过用户自定义变量遍历记录的方式实现空值填充,整体逻辑分为四步:

  1. 保留原逻辑生成查询时间范围内的连续日期临时表,每次执行前先清空临时表残留数据
  2. 将连续日期表与价格表中所有出现过的SKU做交叉匹配,生成每个SKU对应时间范围的全量日期基础数据
  3. 关联实际价格表时同时匹配日期、SKU两个维度,拿到每个(日期、SKU)组合的实际调价记录
  4. 按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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.07 08:54:09