SQL查询事件表open状态后所有下单履约事件的实现方案

你原有写法中使用max()对下单、履约事件做预聚合的逻辑，会直接把每个用户维度下符合时间条件的多条事件折叠为单条最晚记录，这是只能返回最新匹配项的核心原因。要返回所有open事件后对应的全量下单、履约匹配对，不能先做聚合，需要先给每个业务事件（下单/履约）绑定它归属的最近一次前序open事件，再做关联输出。

整体分三步处理：

• 提取所有event_type = 'open'的事件作为状态周期起点，给每个用户的open事件按发生时间打序号标记
• 筛选全量order_placed、order_fulfilled类型的业务事件，给每条事件匹配同用户下、发生时间早于它、距离它最近的open事件，标记该业务事件归属的open周期
• 基于open周期标记做关联，不做聚合直接展平同周期内的所有下单、履约事件，即可返回全量匹配对

以下写法兼容PostgreSQL、BigQuery、MySQL 8.0.2+等支持窗口函数的主流数据库：

WITH open_events AS (
    -- 提取所有open状态起点
    SELECT 
        customer_id,
        recorded_at AS open_time,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY recorded_at) AS open_rn
    FROM Table_1
    WHERE event_type = 'open'
),
business_events AS (
    -- 给下单、履约事件匹配归属的最近open周期
    SELECT
        t.customer_id,
        t.recorded_at AS event_time,
        t.event_type,
        oe.open_time,
        oe.open_rn
    FROM Table_1 t
    LEFT JOIN open_events oe
        ON t.customer_id = oe.customer_id
        AND oe.open_time <= t.recorded_at
    WHERE t.event_type IN ('order_placed', 'order_fulfilled')
    -- 仅保留距离当前事件最近的前序open事件，避免跨周期错配
    QUALIFY ROW_NUMBER() OVER (PARTITION BY t.customer_id, t.recorded_at ORDER BY oe.open_time DESC) = 1
)
-- 关联输出所有匹配结果
SELECT
    oe.customer_id,
    oe.open_time,
    be_placed.event_time AS order_placed_time,
    be_fulfilled.event_time AS order_fulfilled_time
FROM open_events oe
LEFT JOIN business_events be_placed
    ON oe.customer_id = be_placed.customer_id
    AND oe.open_rn = be_placed.open_rn
    AND be_placed.event_type = 'order_placed'
LEFT JOIN business_events be_fulfilled
    ON oe.customer_id = be_fulfilled.customer_id
    AND oe.open_rn = be_fulfilled.open_rn
    AND be_fulfilled.event_type = 'order_fulfilled'
-- 过滤无任何匹配事件的空open记录，不需要可删除该行
WHERE be_placed.event_time IS NOT NULL OR be_fulfilled.event_time IS NOT NULL
ORDER BY oe.customer_id, oe.open_time, order_placed_time, order_fulfilled_time;

如果你使用的数据库不支持QUALIFY语法（如SQL Server、低版本MySQL 8.0），可将business_events CTE替换为以下兼容写法：

business_events AS (
    SELECT * FROM (
        SELECT
            t.customer_id,
            t.recorded_at AS event_time,
            t.event_type,
            oe.open_time,
            oe.open_rn,
            ROW_NUMBER() OVER (PARTITION BY t.customer_id, t.recorded_at ORDER BY oe.open_time DESC) AS rn
        FROM Table_1 t
        LEFT JOIN open_events oe
            ON t.customer_id = oe.customer_id
            AND oe.open_time <= t.recorded_at
        WHERE t.event_type IN ('order_placed', 'order_fulfilled')
    ) tmp
    WHERE rn = 1
)

• 该写法不会对事件做聚合折叠，同一个open周期下存在多条下单、履约事件时会全部返回
• 自动隔离不同open周期的事件，不会出现前序open事件匹配到后续周期业务数据的问题
• 如果需要保留没有任何下单、履约事件匹配的open记录，删除最终查询里的WHERE条件即可

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