SUM OVER无法实现FIFO扣减，因其仅机械累加不追踪批次消耗；需合并出入库事件、按时间排序并用窗口函数计算净库存，再结合CASE判断各批次消耗状态。
为什么直接用 SUM OVER 无法正确实现 FIFO 库存扣减
因为 SUM() OVER 是纯累加函数，它不区分“哪一批入库的货被扣掉了”，只按排序机械叠加数量。FIFO 要求按入库时间顺序逐批消耗，可能某次出库横跨多个入库批次（比如扣完第1批剩2件，再从第2批扣3件），而 SUM() OVER (ORDER BY in_time) 只能告诉你“到第n批为止共进了多少”，没法反向推每批还剩多少、哪几批被清空。
真正可行的解法：用窗口函数模拟逐行 FIFO 消耗过程
核心思路是：把每笔入库记为正数，每笔出库记为负数，按时间排序后做累计和，再用 CASE + 上下文判断当前行是否“被完全消耗”或“部分消耗”。关键不是靠 SUM() OVER 直接出结果，而是靠它辅助生成中间状态。
- 先统一时间轴：把
in_records和out_records合并成单表，用type区分'in'/'out'，in_time或out_time统一为event_time - 用
SUM(quantity) OVER (ORDER BY event_time, type ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)计算“截至当前事件的净库存余额”——注意type排序要保证同时间点先处理入库（'in''out'），否则会误判缺货 - 对每条入库记录，用
LAG()或自连接查上一行余额，再结合当前入库量倒推本批实际剩余量：GREATEST(0, LEAST(in_qty, COALESCE(prev_balance, 0) + in_qty) - COALESCE(consumed_by_later_outs, 0))—— 这步无法单靠SUM OVER完成，必须配合子查询或 CTE
PostgreSQL / SQL Server 中可落地的简化写法（避免游标）
以 PostgreSQL 为例，用递归 CTE 或窗口函数链式计算更稳妥：
WITH ranked AS (
SELECT id, qty, event_time, type,
ROW_NUMBER() OVER (ORDER BY event_time, type) AS rn
FROM events
),
running AS (
SELECT *,
SUM(CASE WHEN type = 'in' THEN qty ELSE -qty END)
OVER (ORDER BY event_time, type ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS net_stock
FROM ranked
),
fifo_calc AS (
SELECT *,
GREATEST(0, net_stock - LAG(net_stock, 1, 0) OVER (ORDER BY rn)) AS consumed_from_this
FROM running
)
SELECT id, qty, type, net_stock, consumed_from_this
FROM fifo_calc;
注意：consumed_from_this 并非真实扣减量，只是示意逻辑；实际生产中建议用 CTE 分三步：① 构建带序号的事件流；② 计算每步前余额；③ 对每个 'in' 行，用 LEAD(net_stock) 算出该批被吃掉多少——这个差值才是本批剩余量。
MySQL 8.0+ 的限制与绕过方式
MySQL 不支持在窗口函数中嵌套聚合（如 SUM(SUM() OVER ...)），且 LAG/LEAD 性能弱于 PG/SQL Server。最容易踩的坑是：用变量模拟累计时，优化器可能重排执行顺序导致结果错乱。
- 强制按序执行：在子查询里加
ORDER BY event_time, type并用LIMIT 9999999阻止优化器跳过排序 - 避免变量依赖：不要写
@stock := @stock + qty，改用SUM(qty) OVER (ORDER BY ...)作为主干，再用外部 JOIN 补充批次维度 - 如果出库量极大（如单次出库 > 总入库），
SUM() OVER算出的net_stock会变负——这说明已超卖，但你无法知道超卖发生在哪一批，必须额外标记is_shortage标志位
FIFO 的本质是状态机，SQL 擅长描述静态快照，不擅维护行间状态流转。哪怕用了 SUM OVER，也得靠多层嵌套或外部逻辑补全“哪批货动了、动了多少”这个关键信息——这点很容易被忽略。
