SQL查询：查找open事件后首次出现的submit/decline事件

你之前用FIRST_VALUE调试失败，基本都是两个原因：要么没排除open事件发生前的submit/decline记录，要么没过滤掉open和目标事件之间的无关事件，导致窗口函数取值范围不对。

实现逻辑 #

整个计算只需要三步：

• 先标记每个会话里open事件对应的event_id，作为判断事件先后的分界点
• 筛选所有发生在open之后、类型为submit/decline的事件，按event_id升序（即发生先后顺序）取每个会话的第一条，就是规则要求的首个目标事件
• 把计算结果关联回原events表，没有匹配到符合条件记录的会话自动返回null

通用兼容SQL写法 #

这个写法兼容MySQL 8+、PostgreSQL、Spark SQL、BigQuery、Hive 2.3+等绝大多数支持窗口函数的引擎：

WITH session_open_boundary AS (
    -- 定位每个会话open事件的位置
    SELECT
        session_id,
        event_id AS open_event_id
    FROM events
    WHERE event_name = 'open'
),
first_target_event AS (
    -- 取open之后第一个出现的submit/decline
    SELECT
        e.session_id,
        e.event_name AS first_post_open_action
    FROM events e
    INNER JOIN session_open_boundary b
        ON e.session_id = b.session_id
        AND e.event_id > b.open_event_id -- 仅保留open发生之后的事件
    WHERE e.event_name IN ('submit', 'decline')
    -- 按事件发生顺序排序，每个会话取第一条
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY e.session_id
        ORDER BY e.event_id ASC
    ) = 1
)
-- 关联回原表，生成新增列
SELECT
    e.*,
    t.first_post_open_action
FROM events e
LEFT JOIN first_target_event t
    ON e.session_id = t.session_id;

如果你的运行环境不支持QUALIFY语法（比如低版本Hive、MySQL 8.0之前的版本），把first_target_event这段替换成子查询嵌套写法即可：

first_target_event AS (
    SELECT session_id, event_name AS first_post_open_action
    FROM (
        SELECT
            e.session_id,
            e.event_name,
            ROW_NUMBER() OVER (
                PARTITION BY e.session_id
                ORDER BY e.event_id ASC
            ) AS rn
        FROM events e
        INNER JOIN session_open_boundary b
            ON e.session_id = b.session_id
            AND e.event_id > b.open_event_id
        WHERE e.event_name IN ('submit', 'decline')
    ) tmp
    WHERE rn = 1
)

规则校验 #

对应你提的三条匹配规则，返回结果完全符合要求：

• 序列为open→若干其他事件→submit→decline时，open之后排序第一的目标事件是submit，返回submit
• 序列为open→若干其他事件→decline时，open之后排序第一的目标事件是decline，返回decline
• open事件后无submit/decline事件时，左关联无匹配结果，返回null

如果你坚持要用FIRST_VALUE实现，可以用条件窗口函数的写法，不需要做表关联：

SELECT
    *,
    FIRST_VALUE(
        CASE WHEN event_name IN ('submit', 'decline') THEN event_name END
        IGNORE NULLS
    ) OVER (
        PARTITION BY session_id
        ORDER BY event_id ASC
        -- 窗口范围限定为当前行（即open事件）之后的所有记录
        ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS first_post_open_action
FROM events
-- 如果只需要给open事件打标就保留下面这行，要给全表所有行打标就删除
QUALIFY event_name = 'open'

注意这个写法要求SQL引擎支持IGNORE NULLS和窗口帧FOLLOWING边界定义，兼容性比前面的CTE写法差，优先用前面的通用方案。

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