基于30分钟规则对用户活动数据进行会话划分及首时间戳获取的PostgreSQL优化方案求助

我有一张名为myTable的用户活动表，表结构和数据如下：

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

INSERT INTO myTable
("userid", "webid", "ts")
VALUES ('A', '34', '2023-01-31 16:34:49.000'),
('A', '97', '2023-01-31 16: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', '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:52:14.000'),
('A', '77', '2023-01-31 18:12:14.000'),
('A', '77', '2023-01-31 18:45:14.000'),
('A', '77', '2023-01-31 18:55:15.000'),
('B', '33', '2023-01-31 06:37:15.000'),
('B', '56', '2023-01-31 06:40:15.000')
;

原始数据示例 #

期望输出结果 #

会话划分及first_ts规则说明 #

first_ts指的是当前会话的首个时间戳，具体规则如下：

• 同一用户连续访问同一个webid，且相邻时间间隔在30分钟以内，这些记录属于同一个会话，first_ts为该会话的第一个时间戳。比如第3、4、5行，相邻时间间隔都小于30分钟，所以它们的first_ts都是2023-01-31 17:35:02。
• 同一用户访问某webid后跳转到其他webid，之后再回到原webid，需重新开启新会话，first_ts刷新为当前时间戳。比如第6、7行访问webid=1，之后跳转到webid=77，再回到webid=1的第10、11行，这两行属于新会话，first_ts是2023-01-31 17:37:10。
• 同一用户连续访问同一个webid，但其中某两个相邻时间间隔超过30分钟，会话会被拆分，之后的记录属于新会话，first_ts刷新为当前时间戳。比如第12-16行，第14行和第15行的时间间隔为33分钟（超过30分钟），所以第15、16行属于新会话，first_ts为2023-01-31 18:45:14。

我当前的尝试脚本 #

我写了一个CTE查询，但目前还没加入session_id，因为发现生成的first_ts不正确，没办法基于它用dense_rank()生成正确的会话ID。另外说明一下，因为用的是PostgreSQL，没有datediff()函数，所以用extract(epoch())来计算时间差，效果和datediff()类似。

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ts) rn1,
ROW_NUMBER() OVER (PARTITION BY userid, webid ORDER BY ts) rn2
FROM myTable
)
SELECT userid, webid, ts,
lag(ts,1) over(partition by userid order by ts) as previous_ts,
case when extract(epoch from (ts - lag(ts,1) over(partition by userid order by ts asc))) <= 1800 then MIN(ts) OVER (PARTITION BY userid, webid, rn1 - rn2)
when extract(epoch from (ts - lag(ts,1) over(partition by userid order by ts asc))) > 1800 then ts
when lag(ts,1) over(partition by userid order by ts) is NULL then ts
end as first_ts
FROM cte
ORDER BY userid, ts;

现在想请教大家有没有更好的解决方案来实现这个需求，谢谢！

备注：内容来源于stack exchange，提问作者ccwiris