如何在SQL中按列条件匹配RECEIVE与后续PUTAWAY时间戳并过滤无效数据？

核心需求 #

从包含event_d、event_lcl_ts、location_id、event_code、container_id的容器事件表中，生成结果表：

• 输出字段：container_id、receive_timestamp、putaway_timestamp、location_id
• 每个RECEIVE事件关联其首个后续的PUTAWAY事件时间戳
• 过滤无前置RECEIVE的PUTAWAY事件
• 保留未发生PUTAWAY的RECEIVE事件（对应putaway_timestamp为NULL）

方案1：使用LATERAL JOIN（简洁高效，适用于PostgreSQL/BigQuery等） #

直接为每个RECEIVE事件查询最早的后续PUTAWAY，逻辑清晰：

SELECT
    r.container_id,
    r.event_lcl_ts AS receive_timestamp,
    p.event_lcl_ts AS putaway_timestamp,
    r.location_id
FROM container_events r
LEFT JOIN LATERAL (
    -- 匹配当前RECEIVE事件的首个后续PUTAWAY
    SELECT event_lcl_ts
    FROM container_events
    WHERE container_id = r.container_id
      AND event_code = 'PUTAWAY'
      AND event_lcl_ts > r.event_lcl_ts
    ORDER BY event_lcl_ts ASC
    LIMIT 1
) p ON true
WHERE r.event_code = 'RECEIVE'
ORDER BY r.container_id, r.receive_timestamp;

方案2：使用CTE+窗口函数（兼容性更广） #

若数据库不支持LATERAL JOIN，可通过分层处理事件序列实现：

WITH event_sequence AS (
    -- 仅保留目标事件并按时间排序
    SELECT
        container_id,
        event_lcl_ts,
        event_code,
        location_id,
        ROW_NUMBER() OVER (PARTITION BY container_id ORDER BY event_lcl_ts) AS event_seq
    FROM container_events
    WHERE event_code IN ('RECEIVE', 'PUTAWAY')
),
receive_events AS (
    -- 提取所有RECEIVE事件
    SELECT
        container_id,
        event_lcl_ts AS receive_timestamp,
        location_id,
        event_seq AS receive_seq
    FROM event_sequence
    WHERE event_code = 'RECEIVE'
),
putaway_events AS (
    -- 为每个PUTAWAY关联最近的前置RECEIVE
    SELECT
        container_id,
        event_lcl_ts AS putaway_timestamp,
        MAX(CASE WHEN event_code = 'RECEIVE' THEN event_seq END) OVER (
            PARTITION BY container_id ORDER BY event_seq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS linked_receive_seq
    FROM event_sequence
    WHERE event_code = 'PUTAWAY'
)
-- 关联RECEIVE与对应的首个PUTAWAY
SELECT
    r.container_id,
    r.receive_timestamp,
    p.putaway_timestamp,
    r.location_id
FROM receive_events r
LEFT JOIN putaway_events p
    ON r.container_id = p.container_id
    AND r.receive_seq = p.linked_receive_seq
QUALIFY ROW_NUMBER() OVER (PARTITION BY r.container_id, r.receive_timestamp ORDER BY p.putaway_timestamp) = 1
ORDER BY r.container_id, r.receive_timestamp;

关键说明 #

• 无PUTAWAY的RECEIVE处理：通过LEFT JOIN保留这类记录，putaway_timestamp自动填充为NULL
• 过滤无前置RECEIVE的PUTAWAY：由于以RECEIVE事件作为主查询来源，无对应RECEIVE的PUTAWAY不会被关联到结果中，自然被过滤
• 首个后续PUTAWAY：通过ORDER BY event_lcl_ts ASC LIMIT 1（方案1）或QUALIFY ROW_NUMBER()（方案2）确保只取最早的PUTAWAY事件

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