← 返回知识库← 上一题下一题 →
SQL (ANSI)编程数据库开发员

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

用 Hintsage AI 助手通过面试

问题的答案

问题的历史

库存会计需要准确跟踪商品通过仓库流转时的成本。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 的规定要求对每个售出药丸进行准确的成本追踪，需对每个单位成本进行归属，而非简单平均成本。仓库每天处理成千上万的交易，涉及数百种商品，采购批次以不可预测的间隔和价格到达。

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

团队曾短暂考虑使用 触发器 来维护一个在每次销售时自动更新的运行余额表。然而，这在 Oracle 中引发了变异表错误，而在 PostgreSQL 中则导致复杂的延迟约束管理，给 OLTP 系统带来了级联延迟。触发器的方法也使审计跟踪变得复杂，因为它模糊了数据库元数据中的确切分配逻辑，而不是明确查询代码。

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

候选人常常忽略的内容

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

候选人常常假设 ORDER BY sold_at 已足够。然而，当时间戳冲突时，分配顺序将变得不确定，并且可能在查询执行之间有所不同。解决方案需要一个 tie-breaker 列——通常是主键或自增序列——在窗口函数的 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 而不考虑舍入上下文或剩余余数。强健的解决方案采用 银行家舍入（四舍五入到偶数）或在子查询中保留未舍入值，然后在外部查询中应用纠正算法。此调整将剩余差异加到最大的分配行上，强制和精确匹配销售总值，同时保持审计轨迹的准确性。