直接用 SUM() OVER (ORDER BY) 算不出真正 FIFO 库存，因其仅计算时间序累计净变动，无法模拟“先进先出”的批次匹配与状态传递过程。
为什么直接用 SUM() OVER (ORDER BY) 算不出真正的FIFO库存？
因为FIFO不是简单累加入库量、再扣减出库量就能得到剩余库存——它要求每次出库必须消耗最早入库的批次，而窗口函数本身不支持“按需穿透多行匹配”。你用 SUM(in_qty - out_qty) OVER (ORDER BY ts) 得到的是时间序累计净变动，不是真实 FIFO 剩余结构。真正要算的是：某时刻还剩哪些入库批次、各剩多少。
必须把出入库拆成「事件流」并标记方向
FIFO计算本质是模拟队列操作：入库推入，出库从队列头弹出。所以第一步要把原始数据转为带方向的事件行：
- 每条入库记录 → 一行，
event_type = 'in'，qty = in_qty - 每条出库记录 → 一行，
event_type = 'out'，qty = out_qty - 所有事件按时间戳
ts排序（同时间需约定入库优先或加细粒度排序键）
关键点：ts 必须能唯一/稳定排序，否则 FIFO 逻辑会错乱；如果业务允许，建议入库和出库用同一张表、同一时间字段，避免跨表 join 引入不确定性。
用递归 CTE 或自连接模拟 FIFO 消耗过程
窗口函数无法直接完成 FIFO 匹配，但可以用递归 CTE（PostgreSQL / SQL Server / Oracle）或变量式自连接（MySQL 8.0+）逐步模拟。以 PostgreSQL 为例：
WITH RECURSIVE fifo_events AS (
-- 初始事件流（已按 ts 排序，in 优先于 out 同时间）
SELECT id, ts, event_type, qty, ROW_NUMBER() OVER (ORDER BY ts, CASE WHEN event_type='in' THEN 0 ELSE 1 END) AS rn
FROM (
SELECT id, ts, 'in'::TEXT AS event_type, in_qty AS qty FROM inventory_in
UNION ALL
SELECT id, ts, 'out'::TEXT, out_qty FROM inventory_out
) t
),
fifo_calc AS (
-- 种子：第一个事件
SELECT rn, event_type, qty, qty AS remaining, NULL::NUMERIC AS consumed
FROM fifo_events WHERE rn = 1
UNION ALL
-- 递归：逐行处理，维护当前待消耗的入库余量
SELECT f.rn,
f.event_type,
f.qty,
CASE
WHEN f.event_type = 'in' THEN f.qty + COALESCE(prev.remaining, 0)
ELSE GREATEST(0, COALESCE(prev.remaining, 0) - f.qty)
END,
CASE
WHEN f.event_type = 'out' THEN LEAST(f.qty, COALESCE(prev.remaining, 0))
ELSE NULL
END
FROM fifo_events f
JOIN fifo_calc prev ON f.rn = prev.rn + 1
)
SELECT * FROM fifo_calc;
注意：remaining 字段表示“当前尚未被后续出库消耗掉的最近入库余量”，它不是最终库存，而是 FIFO 队列头部剩余。真实剩余库存需在最后取 MAX(remaining) 或按业务口径聚合。这个逻辑不能用纯窗口函数替代，强行用 SUM() OVER 会丢失批次粒度。
生产环境更推荐用应用层或物化视图预计算
上述递归 CTE 在百万级事件下性能急剧下降，且不可索引优化。实际项目中：
- 高频查询场景：用应用代码（Python/Java）读取有序事件流，单次遍历 O(n) 完成 FIFO 计算，结果缓存或写回临时表
- 准实时报表：用物化视图或定时任务（如每天凌晨）跑一次 FIFO 批处理，生成
batch_id → remaining_qty映射表 - 数据库能力受限时（如 MySQL 5.7）：用用户变量模拟，但必须确保
ORDER BY绝对稳定，且禁止在复杂子查询中复用变量
FIFO 的核心复杂性不在语法，而在它天然需要状态传递——而 SQL 是声明式语言，窗口函数只提供局部有序聚合，不维持跨行状态。一旦你试图用 LAG() 或 LAST_VALUE() 去“记住上一批剩余”，就会在多出库连续发生时立刻出错。
