在库存估值和成本分层的背景下，如何仅使用 ANSI SQL 实现严格的 FIFO（先进先出）分配算法，以将每笔出货销售交易与特定的入库采购批次匹配，计算每个销售单位的确切成本基础？

问题的回答

问题的历史

库存会计要求在货物通过仓库流动时精确跟踪成本。FIFO（先进先出）假定最早购买的商品先被出售，这对于易腐物品或通货膨胀环境至关重要。与平均成本法相比，FIFO要求将每笔销售与特定的历史采购批次匹配，这创造了一个许多对许多的关系挑战，早于现代SQL标准。

问题

给定两个表——purchases（lot_id、quantity、unit_cost、received_at）和sales（sale_id、quantity、sold_at），我们必须将每笔销售数量分配给最老的未售出库存。这产生了三种复杂性：单笔销售可能消耗多个部分批次，单一批次可能涉及多个销售，且分配必须遵循时间顺序，而不使用过程循环。传统的JOIN方法失败，因为它们无法跟踪跨行个别批次的耗尽状态。

解决方案

使用窗口函数计算累积和，将离散数量转换为连续区间。将采购转化为累积范围 [prior_cumulative+1, current_cumulative]，销售转化为类似范围。对重叠区间进行JOIN，识别哪个批次支持哪个销售。交集长度乘以批次的unit_cost即得成本基础。该集合理论方法避免了递归，并完全适用于ANSI SQL。

WITH purchase_cumulative AS (
    SELECT 
        lot_id,
        unit_cost,
        received_at,
        SUM(quantity) OVER (ORDER BY received_at, lot_id ROWS UNBOUNDED PRECEDING) - quantity AS cum_start,
        SUM(quantity) OVER (ORDER BY received_at, lot_id ROWS UNBOUNDED PRECEDING) AS cum_end
    FROM purchases
),
sales_cumulative AS (
    SELECT 
        sale_id,
        sold_at,
        SUM(quantity) OVER (ORDER BY sold_at, sale_id ROWS UNBOUNDED PRECEDING) - quantity AS cum_start,
        SUM(quantity) OVER (ORDER BY sold_at, sale_id ROWS UNBOUNDED PRECEDING) AS cum_end
    FROM sales
)
SELECT 
    s.sale_id,
    p.lot_id,
    p.unit_cost,
    LEAST(s.cum_end, p.cum_end) - GREATEST(s.cum_start, p.cum_start) AS allocated_quantity,
    (LEAST(s.cum_end, p.cum_end) - GREATEST(s.cum_start, p.cum_start)) * p.unit_cost AS allocated_cost
FROM sales_cumulative s
JOIN purchase_cumulative p 
    ON s.cum_start < p.cum_end AND s.cum_end > p.cum_start
ORDER BY s.sale_id, p.received_at;

生活中的情况

一家制药分销商跟踪由于供应商波动而价格不同的药品批次。FDA法规要求对每个出售的药丸进行确切的成本追踪，必须进行逐单位成本归属，而非平均成本。仓库处理数千笔每日交易，涉及数百个 SKU，采购批次的到达时间和价格不可预测。

最初的方法使用了存储过程中的CURSOR，顺序迭代销售，并逐行减少批次余额。尽管功能上正确，但该方法在高峰时段造成严重锁定竞争，因为它在库存表上保持锁定较长时间。此外，该过程逻辑在并发INSERT操作下未能通过ACID合规性测试，导致幻读和批次的重复消费。

团队短暂考虑了使用触发器维护一个运行余额表，该表在每次销售时自动更新。然而，这在Oracle中引入了变更表错误，在PostgreSQL中则引入了复杂的延迟约束管理，给OLTP系统造成了级联延迟。触发器方法还通过将确切的分配逻辑隐藏在数据库元数据中，而非显式查询代码，复杂化了审计记录。

所选解决方案实施了使用窗口函数的区间重叠方法，以预先计算累积边界。这使数据库优化器能够利用排序合并连接，而不是嵌套循环连接，将10,000单位销售报告的成本计算时间从45秒减少到200毫秒。结果使得在月末财务结算期间实现了实时的销售成本报告，而没有阻碍库存交易，实现了完整的SERIALIZABLE隔离合规性。

候选人常常忽略的内容

你如何处理采购和销售事件共享相同时间戳的边缘情况，以确保确定的 FIFO 排序？

候选人常常假设ORDER BY sold_at已足够。然而，当时间戳碰撞时，分配顺序变得不确定，并且可能在查询执行之间变化。解决方案需要一个平局突破者列——通常是主键或自增顺序——放在窗口函数的ORDER BY子句中。如果没有这种严格的排序，同时发生的两笔销售可能由于查询优化器的执行计划中的竞争条件错误地重复消费同一批次的数量，侵犯库存完整性。

为什么使用 FLOAT 或 DOUBLE PRECISION 的数量列会破坏 FIFO 分配结果？

许多候选人对货币或数量计算使用浮点类型，却不知道IEEE 754浮点无法准确表示像0.1这样的十进制分数。这种不精确导致的累积和错误在数千行中不断加剧，导致预计包含正好100个单位的批次记录为99.999999或100.000001。因此，区间重叠数学要么错过有效重叠，要么创建幻影负分配。解决方案要求对所有数量和成本列使用DECIMAL或NUMERIC类型，具有明确精度，以确保精确的整数算术，防止财务差异。

当一笔销售跨多个批次且不同单位成本时，如何更正累计的舍入误差？

当一笔销售分裂为价格为 $0.33、$0.33 和 $0.34 的三个批次时，直接舍入每个单项可能导致分配成本的总和与销售预期总值偏差一分钱。候选人常常直接计算 allocated_quantity * unit_cost，而没有考虑舍入上下文或剩余。如果没有妥善解决，上述方法会使得总销售值在保持审计轨迹准确性的同时，加倍带来输出错误。更健壮的解决方案应用了银行家舍入（四舍五入到偶数）或在子查询中保留未舍入值，然后在外部查询中应用修正算法。此调整将剩余差异添加到最大的分配项上，强迫总和恰好匹配总销售值，同时保持审计轨迹准确性。