如何用带条件的min(lag) over分区计算A类事件与同用户B类事件最小时间差

核心思路 #

要求的当前A类事件和同用户下所有B类事件的最小时间差，本质只需要找到和该A事件时间距离最近的B类事件即可：按用户分区、时间排序后，仅需取当前A事件向前最近的1条B事件、向后最近的1条B事件，二者和当前A事件的时间差取最小值就是最终结果，不需要和同用户下所有B事件做匹配，计算量极低。
PostgreSQL 9.6 已经支持窗口函数的IGNORE NULLS特性，可以直接跳过非B类事件，不需要写复杂的子查询或者自关联。

实现代码 #

SELECT
    event_id,
    user_id,
    date,
    LEAST(
        -- 计算与前序最近B事件的间隔，无匹配时返回空
        CASE WHEN prev_b_date IS NOT NULL THEN date - prev_b_date END,
        -- 计算与后序最近B事件的间隔，无匹配时返回空
        CASE WHEN next_b_date IS NOT NULL THEN next_b_date - date END
    ) AS min_delta
FROM (
    SELECT
        event_id,
        user_id,
        date,
        event_type_A,
        -- 取当前行之前最近的B类事件时间，自动跳过非B类记录
        LAG(CASE WHEN event_type_B THEN date END) IGNORE NULLS OVER (
            PARTITION BY user_id 
            ORDER BY date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS prev_b_date,
        -- 取当前行之后最近的B类事件时间，自动跳过非B类记录
        LEAD(CASE WHEN event_type_B THEN date END) IGNORE NULLS OVER (
            PARTITION BY user_id 
            ORDER BY date
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_b_date
    FROM events
) t
WHERE event_type_A = true;

性能优化说明 #

• 该方案仅需对事件表做1次全表扫描加窗口排序，时间复杂度为O(n log n)，远优于自关联、子查询全量匹配的O(n²)复杂度，适配大规模数据集场景。
• 给事件表创建(user_id, date)的联合B树索引，可以直接跳过窗口排序步骤，查询性能会再提升一个量级。
• 代码没有单独做事件类型字段合并，直接使用原表的两个布尔字段做判断，减少一层CTE开销。

补充说明 #

如果date字段为时间戳类型，需要返回数值型的间隔（比如秒数、天数），可以把时间差计算部分替换为：

• 秒级间隔：EXTRACT(EPOCH FROM date - prev_b_date)
• 天级间隔：(date - prev_b_date)::int
  如果某条A类事件所属用户没有任何B类事件，对应的min_delta会返回NULL，符合无有效间隔的业务逻辑。

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