MariaDB如何实现库存估值查询按时间戳自动迭代计算

根本不需要手动逐天修改参数，也不需要写存储过程，单条SQL就能实现全时间范围的自动计算，核心是把原来硬编码的全局查询日期，替换为每行记录自带的盘点日期字段即可。

原查询的核心问题 #

原来的写法依赖自定义变量@query_date固定计算日期，本质是把本该按行计算的库存停留天数，强行套了一个统一的全局日期，不仅要手动改参数，还存在两个明显缺陷：

• 多层嵌套的无意义GROUP BY写法，在MariaDB 10.2+默认开启严格SQL模式时会直接报错，之前能运行仅靠数据库隐式的非聚合字段抽取逻辑，稳定性很差
• 缺少聚合求和步骤，单日期计算时没有把当日所有商品的折算价值累加，仅单独取单条商品的值

可直接运行的全量计算SQL #

SELECT
    timestamp_inventory,
    SUM(
        -- 原表value是字符串类型，显式转数值避免隐式转换异常
        CAST(value AS DECIMAL(10,2)) *
        CASE
            WHEN TIMESTAMPDIFF(DAY, last_inbound_date, timestamp_inventory) < 2 THEN 0.4
            WHEN TIMESTAMPDIFF(DAY, last_inbound_date, timestamp_inventory) < 4 THEN 0.8
            ELSE 1
        END
    ) AS inventory_evaluation_value
FROM inventory
GROUP BY timestamp_inventory
ORDER BY timestamp_inventory;

计算逻辑校验 #

逐行匹配规则计算后的结果和给出的预期值完全一致：

• 停留天数计算直接用当前盘点日期减去记录自带的上次入库日期，不需要依赖外部参数
• 每个盘点日的结果为当日所有商品按规则折算后的价值总和
• 查询会自动遍历表中所有存在的盘点日期，不需要手动切换参数
  如果后续需要调整折算比例规则，仅需修改CASE判断内的条件和数值即可，整体查询结构不需要改动。

内容的提问来源于stack exchange，提问作者Michi