如何编写SQL获取匹配Profile_ID的最近前置start行Content_ID？

假设你的数据表名为session_activity，包含以下核心字段：

• Profile_ID：用户标识，用于匹配同一会话的用户
• Activity_Type：活动类型，取值为'start'、'end'或其他无关类型
• Content_ID：内容ID，start记录包含该值，需关联到对应end记录
• Activity_Time：活动发生时间，用于判断记录先后顺序

方案一：窗口函数实现（推荐，高效处理大数据量） #

使用LAST_VALUE窗口函数，按用户分区、时间排序，向前捕获最近的start记录的Content_ID：

WITH session_with_matched_start AS (
    SELECT
        *,
        LAST_VALUE(CASE WHEN Activity_Type = 'start' THEN Content_ID END)
            OVER (
                PARTITION BY Profile_ID
                ORDER BY Activity_Time
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS matched_start_content_id
    FROM session_activity
)
SELECT
    Profile_ID,
    Activity_Type,
    Content_ID AS end_content_id,
    matched_start_content_id
FROM session_with_matched_start
WHERE Activity_Type = 'end';

逻辑说明： #

1. 用PARTITION BY Profile_ID将数据按用户分组，确保只匹配同一用户的记录
2. ORDER BY Activity_Time保证按时间顺序遍历记录
3. LAST_VALUE(CASE...)只保留start类型记录的Content_ID，并取当前end记录之前最近的那个值
4. 最后筛选出所有end类型记录，得到目标结果

方案二：关联子查询实现（适合小数据量） #

如果数据量不大，也可以用子查询直接为每条end记录匹配最近的start：

SELECT
    e.Profile_ID,
    e.Activity_Type,
    e.Content_ID AS end_content_id,
    (
        SELECT s.Content_ID
        FROM session_activity s
        WHERE s.Profile_ID = e.Profile_ID
          AND s.Activity_Type = 'start'
          AND s.Activity_Time < e.Activity_Time
        ORDER BY s.Activity_Time DESC
        LIMIT 1
    ) AS matched_start_content_id
FROM session_activity e
WHERE e.Activity_Type = 'end';

逻辑说明： #

• 对每条end记录，子查询筛选出同一用户、时间更早的所有start记录，按时间倒序取第一条（最近的）的Content_ID

注意事项 #

• 若表中无Activity_Time，而是用自增主键（如Record_ID）判断顺序，只需将Activity_Time替换为对应字段即可
• 为Profile_ID和时间/主键字段创建索引，可大幅提升查询效率
• 若存在无对应start的end记录，matched_start_content_id会返回NULL，可通过COALESCE(matched_start_content_id, '默认值')处理空值

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