如何修正SQL查询，通过Partition获取分段连续webid的首次timestamp

表结构与测试数据 #

CREATE TABLE myTable
(
    userid      text,
    webid         text,
    "timestamp" timestamp
);

INSERT INTO myTable
    ("userid", "webid", "timestamp")
VALUES ('A', '34', '2023-01-31 17:34:49.000'),
       ('A', '73', '2023-01-31 17:34:50.000'),
       ('A', '97', '2023-01-31 17:34:58.000'),
       ('A', '17', '2023-01-31 17:35:02.000'),
       ('A', '17', '2023-01-31 17:35:07.000'),
       ('A', '17', '2023-01-31 17:35:18.000'),
       ('A', '17', '2023-01-31 17:35:30.000'),
       ('A', '1', '2023-01-31 17:35:37.000'),
       ('A', '1', '2023-01-31 17:35:38.000'),
       ('A', '77', '2023-01-31 17:35:41.000'),
       ('A', '77', '2023-01-31 17:35:42.000'),
       ('A', '1', '2023-01-31 17:37:10.000'),
       ('A', '1', '2023-01-31 17:37:12.000'),
       ('A', '77', '2023-01-31 17:37:14.000'),
       ('A', '77', '2023-01-31 17:37:15.000'),
       ('B', '33', '2023-01-31 06:37:15.000'),
       ('B', '56', '2023-01-31 06:40:15.000')
;

需求说明 #

新增first_ts字段，规则如下：

• 同一userid下，连续访问相同webid时，first_ts为该连续段的首个timestamp
• 若用户跳转至其他webid后返回原webid，first_ts需刷新为新分段的首个timestamp

原查询问题 #

原查询无法正确处理非连续的相同webid分段（如第12-15行的webid='1'和webid='77'，其first_ts未刷新为新分段的首个时间）：

select userid, webid, 
lag(webid,1) over(partition by userid order by timestamp desc) as next_webid,
lag(webid,1) over(partition by userid order by timestamp asc) as previous_webid,
timestamp,
case when webid = next_webid then first_value(timestamp) over(partition by userid, webid order by timestamp) 
     when webid = next_webid and webid = previous_webid then first_value(timestamp) over(partition by userid, webid order by timestamp) 
     when webid = previous_webid then first_value(timestamp) over(partition by userid, webid order by timestamp) 
     else timestamp
end as first_ts
from myTable 
order by userid, timestamp;

修正后的查询 #

核心思路是先识别同一userid下连续相同webid的分段，再在每个分段内取首个时间戳：

WITH segmented_data AS (
    SELECT 
        userid,
        webid,
        "timestamp",
        -- 生成分段标识：当当前webid与上一条不同时，标记为新分段，累计求和得到分组ID
        SUM(CASE WHEN webid = LAG(webid) OVER (PARTITION BY userid ORDER BY "timestamp") THEN 0 ELSE 1 END) 
            OVER (PARTITION BY userid ORDER BY "timestamp") AS segment_id
    FROM myTable
)
SELECT 
    userid,
    webid,
    "timestamp",
    -- 每个分段内取首个时间戳作为first_ts
    FIRST_VALUE("timestamp") OVER (PARTITION BY userid, segment_id ORDER BY "timestamp") AS first_ts
FROM segmented_data
ORDER BY userid, "timestamp";

结果说明 #

修正后的查询会正确区分非连续的相同webid分段：

• 用户A第一次访问webid='1'的连续段（第8-9行），first_ts为2023-01-31 17:35:37.000
• 用户A跳转其他页面后再次访问webid='1'的新分段（第12-13行），first_ts刷新为2023-01-31 17:37:10.000
• 同理webid='77'的两个分段也会得到正确的first_ts值

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