BigQuery事件序列识别逻辑求助：筛选符合特定顺序的用户

数据集 #

ID | POST10 | EVENTS_TIMESTAMP |
1  | picked | 2022.11.06 1:00pm|
1  | profile| 2022.11.06 1:30pm| 
1  | front  | 2022.11.06 1:35pm|

2  | profile| 2022.11.06 1:00pm|
2  | profile| 2022.11.06 1:30pm| 
2  | front  | 2022.11.06 1:35pm|
2  | front  | 2022.11.06 1:36pm|

3  | picked | 2022.11.06 1:00pm|
3  | front  | 2022.11.06 1:30pm| 
3  | profile| 2022.11.06 1:35pm|
3  | front  | 2022.11.06 1:36pm|

逻辑要求 #

• 每个用户的第一个事件必须是picked
• 第一个picked之后必须出现profile事件
• 第一个picked和第一个profile的时间区间内，没有front事件（front可出现在这两个事件之前或之后）

预期结果 #

ID | ANSWER      |
1  | SELECTED    | 
2  | NOT SELECTED| 
3  | NOT SELECTED| 

原SQL问题 #

我编写的SQL无法正确检查picked和profile之间的时间窗口是否存在front，逻辑存在错误：

(case when  
(min(case when (post10) like '%picked%' then EVENTS_TIMESTAMP else null end) over (partition by (ID)) 
>= 
 min(case when (post10) like '%profile%' then EVENTS_TIMESTAMP else null end) over (partition by (ID)))
 AND
 (min(case when (post10) like '%profile%' then EVENTS_TIMESTAMP else null end) over (partition by (ID))
 >= 
 min(case when (post10) like '%front%' then EVENTS_TIMESTAMP else null end) over (partition by (ID)))
then 'SELECTED'
else 'NOT SELECTED' end) as ANSWER

核心思路 #

要满足需求，需分三步验证核心条件：

1. 确认用户的第一个事件是picked
2. 确认用户存在profile事件，且第一个profile的时间晚于第一个picked
3. 确认第一个picked和第一个profile的时间区间内，没有任何front事件

修正代码 #

WITH user_event_metrics AS (
    SELECT 
        ID,
        POST10,
        EVENTS_TIMESTAMP,
        -- 每个用户的最早事件时间
        MIN(EVENTS_TIMESTAMP) OVER (PARTITION BY ID) AS first_event_time,
        -- 每个用户第一个picked的时间
        MIN(CASE WHEN POST10 = 'picked' THEN EVENTS_TIMESTAMP END) OVER (PARTITION BY ID) AS first_picked_time,
        -- 每个用户第一个profile的时间
        MIN(CASE WHEN POST10 = 'profile' THEN EVENTS_TIMESTAMP END) OVER (PARTITION BY ID) AS first_profile_time,
        -- 检查picked和profile之间是否存在front：存在则返回对应时间，否则为null
        MIN(CASE 
            WHEN POST10 = 'front' 
            AND EVENTS_TIMESTAMP > MIN(CASE WHEN POST10 = 'picked' THEN EVENTS_TIMESTAMP END) OVER (PARTITION BY ID)
            AND EVENTS_TIMESTAMP < MIN(CASE WHEN POST10 = 'profile' THEN EVENTS_TIMESTAMP END) OVER (PARTITION BY ID)
            THEN EVENTS_TIMESTAMP 
        END) OVER (PARTITION BY ID) AS front_in_interval
    FROM your_table_name
)
SELECT DISTINCT
    ID,
    CASE 
        WHEN first_event_time = first_picked_time 
             AND first_profile_time IS NOT NULL 
             AND first_profile_time > first_picked_time
             AND front_in_interval IS NULL
        THEN 'SELECTED'
        ELSE 'NOT SELECTED'
    END AS ANSWER
FROM user_event_metrics;

结果验证 #

• ID=1：第一个事件是picked，profile在picked之后，且两者之间无front，返回SELECTED
• ID=2：第一个事件不是picked，返回NOT SELECTED
• ID=3：picked和profile之间存在front，返回NOT SELECTED

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