如何在SQL中生成每日各用户对应事件的最近发生日期表？

我有一张事件表，结构如下：

希望生成如下结构的结果表：

其中：

• asOfDate 是连续的日历日期
• mostRecentEventDate 是对应 userId 和 eventType 在 asOfDate 之前（含当天）的最近事件发生日期

举个例子，执行查询：

SELECT mostRecentEventDate 
FROM new_table 
WHERE userId = 'alice'
AND eventType = 'login' 
AND asOfDate = '2022-02-04'

期望得到alice在2022-02-04之前的最近登录日期。

目前有一段代码可以实现需求，但性能很差：

WITH all_combinations AS (
    SELECT * 
    FROM date_range -- 包含所有日历日期的表，日期字段为asOfDate
    CROSS JOIN user_events
    WHERE date <= asOfDate
)

SELECT 
    *, 
    ROW_NUMBER() OVER(
        PARTITION BY userId, eventType ORDER BY date DESC
    ) AS recency_index
FROM all_combinations 
WHERE recency_index = 1

原方案的核心问题是CROSS JOIN会生成海量中间数据，当日期范围大、用户和事件类型数量多的时候，性能会急剧下降。以下是两种更高效的实现方式：

方式一：基于事件区间匹配 #

WITH user_event_pairs AS (
    -- 提取所有唯一的用户-事件类型组合，避免重复处理
    SELECT DISTINCT userId, eventType
    FROM user_events
),
date_user_event AS (
    -- 生成日期范围与用户-事件组合的笛卡尔积，数据量远小于原方案
    SELECT 
        dr.asOfDate,
        uep.userId,
        uep.eventType
    FROM date_range dr
    CROSS JOIN user_event_pairs uep
),
event_with_next_date AS (
    -- 给每个事件标记下一次同类型事件的日期，用于划分区间
    SELECT 
        ue.userId,
        ue.eventType,
        ue.date,
        LEAD(ue.date) OVER(PARTITION BY ue.userId, ue.eventType ORDER BY ue.date) AS next_event_date
    FROM user_events ue
)
-- 匹配每个日期对应的事件区间，取最近事件日期
SELECT 
    due.asOfDate,
    due.userId,
    due.eventType,
    MAX(e.date) AS mostRecentEventDate
FROM date_user_event due
LEFT JOIN event_with_next_date e
    ON due.userId = e.userId
    AND due.eventType = e.eventType
    AND due.asOfDate >= e.date
    AND (due.asOfDate < e.next_event_date OR e.next_event_date IS NULL)
GROUP BY due.asOfDate, due.userId, due.eventType
ORDER BY due.asOfDate, due.userId, due.eventType;

方式二：利用LATERAL JOIN（适用于PostgreSQL、SQL Server等） #

如果你的数据库支持LATERAL JOIN，可以用更简洁的写法，直接为每个用户-日期组合查询最近事件：

SELECT 
    dr.asOfDate,
    uep.userId,
    uep.eventType,
    recent_event.mostRecentEventDate
FROM date_range dr
CROSS JOIN (SELECT DISTINCT userId, eventType FROM user_events) uep
LEFT JOIN LATERAL (
    SELECT MAX(date) AS mostRecentEventDate
    FROM user_events ue
    WHERE ue.userId = uep.userId
      AND ue.eventType = uep.eventType
      AND ue.date <= dr.asOfDate
) recent_event ON true
ORDER BY dr.asOfDate, uep.userId, uep.eventType;

性能优化建议 #

在user_events表上建立复合索引(userId, eventType, date)，可以大幅提升上述查询的执行效率。

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