如何为每个生产单元获取各产品类型的最后一条Consume记录？

核心思路 #

通过窗口函数LAST_VALUE追踪每个产品类型的最新Consume记录，利用IGNORE NULLS实现向前填充，让后续的Build操作自动继承当前时间点之前的最新物料消耗信息，同时保留原始操作的顺序和结构。

完整SQL代码 #

WITH trace_data AS (
    SELECT
        Action,
        Product_Name,
        Product_Serial,
        Time_Stamp,
        -- Oracle中用||简化字符串拼接
        Product_Name || ' - ' || Product_Serial AS Barcode
    FROM TraceDB
    WHERE Action IN ('Build', 'Consume')
      AND Time_Stamp > SYSDATE - 90
),
consume_tracking AS (
    SELECT
        *,
        -- 追踪Product 1的最新Consume记录
        LAST_VALUE(CASE WHEN Action = 'Consume' AND Product_Name = 'Product 1' THEN Barcode END IGNORE NULLS)
            OVER (ORDER BY Time_Stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS latest_product1,
        -- 追踪Product 2的最新Consume记录
        LAST_VALUE(CASE WHEN Action = 'Consume' AND Product_Name = 'Product 2' THEN Barcode END IGNORE NULLS)
            OVER (ORDER BY Time_Stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS latest_product2,
        -- 追踪Product 3的最新Consume记录
        LAST_VALUE(CASE WHEN Action = 'Consume' AND Product_Name = 'Product 3' THEN Barcode END IGNORE NULLS)
            OVER (ORDER BY Time_Stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS latest_product3
    FROM trace_data
)
SELECT
    Action,
    Product_Name,
    Product_Serial,
    -- 仅Build操作展示最新消耗记录，Consume操作留空
    CASE WHEN Action = 'Build' THEN latest_product1 END AS Product1,
    CASE WHEN Action = 'Build' THEN latest_product2 END AS Product2,
    CASE WHEN Action = 'Build' THEN latest_product3 END AS Product3
FROM consume_tracking
-- 按时间倒序排列，与示例输出顺序匹配
ORDER BY Time_Stamp DESC;

代码说明 #

1. trace_data CTE：预处理原始数据，生成物料条码Barcode，过滤近90天的Build和Consume操作。
2. consume_tracking CTE：
   • 使用LAST_VALUE窗口函数，针对每个产品类型，从时间线起始到当前行，仅取Consume操作的Barcode，并通过IGNORE NULLS忽略非目标类型的记录，确保始终获取最新的消耗记录。
   • 窗口范围设置为UNBOUNDED PRECEDING AND CURRENT ROW，保证当前行能看到之前所有历史数据。
3. 主查询：对Build操作展示对应的最新消耗记录，Consume操作对应的列留空，最后按时间倒序输出，与示例结果的顺序一致。

内容的提问来源于stack exchange，提问作者Rob MacNeill