在SQL中如何用窗口函数实现商品库存的先进先出FIFO计算-SQL-PHP中文网

直接用 SUM() OVER (ORDER BY) 算不出真正 FIFO 库存，因其仅计算时间序累计净变动，无法模拟“先进先出”的批次匹配与状态传递过程。

在sql中如何用窗口函数实现商品库存的先进先出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 为例：

Yoroll

一款AI视频创作工具，主要用于一站式AI互动视频游戏创作平台，适合需要提升相关任务效率的用户。

下载

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 会丢失批次粒度。