基于FIFO原则计算各RS Code入库交易余额占比列的技术问询

我来帮你搞定这个库存需求，完全贴合先进先出规则和结果要求：

需求要点回顾 #

• 需查询字段：物料ID（Stock-item）、总余额（Balance）、入库交易编码（RS Code）、对应入库数量
• 新增字段RS_Portion：基于FIFO原则计算每个RS Code对应的余额占比，同一物料的所有RS_Portion之和必须等于该物料的总余额
• 结果按交易日期排序
• 示例物料ID：2222、2262、2263

假设的数据结构 #

先假设我们有核心交易表（如果你的表结构不同，可对应调整逻辑）：
stock_transactions：存储入库/出库交易记录，字段包括：

• stock_item_id：物料ID
• rs_code：入库交易编码
• transaction_date：交易日期
• quantity：交易数量（入库为正，出库为负）

SQL实现代码 #

WITH stock_total_balance AS (
    -- 计算每个物料的当前总余额
    SELECT 
        stock_item_id,
        SUM(quantity) AS total_balance
    FROM stock_transactions
    GROUP BY stock_item_id
),
sorted_rs_records AS (
    SELECT 
        st.stock_item_id,
        st.rs_code,
        st.transaction_date,
        -- 仅取入库记录的数量（因为RS Code是入库编码）
        CASE WHEN st.quantity > 0 THEN st.quantity ELSE 0 END AS rs_quantity,
        stb.total_balance,
        -- 按FIFO顺序（日期早到晚）计算累计入库量
        SUM(CASE WHEN st.quantity > 0 THEN st.quantity ELSE 0 END) OVER (
            PARTITION BY st.stock_item_id 
            ORDER BY st.transaction_date
        ) AS cumulative_in_qty,
        -- 计算上一条记录的累计入库量，用于判断当前RS需分配的余额
        SUM(CASE WHEN st.quantity > 0 THEN st.quantity ELSE 0 END) OVER (
            PARTITION BY st.stock_item_id 
            ORDER BY st.transaction_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prev_cumulative_in_qty
    FROM stock_transactions st
    JOIN stock_total_balance stb 
        ON st.stock_item_id = stb.stock_item_id
    WHERE st.stock_item_id IN (2222, 2262, 2263) -- 过滤示例物料，可按需删除
      AND st.quantity > 0 -- 仅保留入库记录
)
SELECT 
    stock_item_id AS "Stock-item",
    total_balance AS "Balance",
    rs_code AS "RS Code",
    rs_quantity AS "RS Quantity",
    -- 核心：按FIFO逻辑计算RS_Portion
    CASE
        -- 累计入库量≤总余额：当前RS的全部数量都计入余额
        WHEN cumulative_in_qty <= total_balance THEN rs_quantity
        -- 上一次累计量<总余额：当前RS只分配剩余的余额
        WHEN COALESCE(prev_cumulative_in_qty, 0) < total_balance THEN total_balance - COALESCE(prev_cumulative_in_qty, 0)
        -- 否则：当前RS的余额占比为0（已被前面的RS分配完毕）
        ELSE 0
    END AS "RS_Portion"
FROM sorted_rs_records
ORDER BY stock_item_id, transaction_date; -- 按物料+日期排序

逻辑解释 #

1. stock_total_balance：先算出每个物料的当前总余额，作为后续分配的基准。
2. sorted_rs_records：对每个物料的入库记录按日期升序排序（FIFO的核心顺序），并计算累计入库量和上一条的累计量，这是判断每个RS该分配多少余额的关键。
3. RS_Portion计算：通过CASE语句严格实现FIFO规则：
   • 先入库的RS优先分配余额，直到总余额被全部分配完
   • 如果某个RS的入库量超过剩余未分配的余额，只分配剩余部分，后续的RS分配量为0
   • 同一物料的所有RS_Portion之和必然等于总余额

适配调整建议 #

• 如果你的交易表没有区分入库/出库的标识，可根据业务逻辑调整筛选条件。
• 如果不需要过滤特定物料，直接删除WHERE子句中的st.stock_item_id IN (2222, 2262, 2263)即可。

内容的提问来源于stack exchange，提问作者Mümin