SQL流量会话划分（gaps and islands问题）：session_id错误修正方案求助

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

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

INSERT INTO myTable
    ("userid", "webid", "ts")
VALUES ('1', 'A', '2023-01-31 16:34:49.000'),
       ('2', 'A', '2023-01-31 16:34:50.000'),
       ('3', 'A', '2023-01-31 16:34:58.000'),
       ('4', 'A', '2023-01-31 17:35:02.000'),
       ('5', 'A', '2023-01-31 17:35:07.000'),
       ('6', 'A', '2023-01-31 17:35:18.000'),
       ('7', 'A', '2023-01-31 17:35:30.000'),
       ('8', 'A', '2023-01-31 17:35:37.000'),
       ('9', 'A', '2023-01-31 17:35:38.000'),
       ('10', 'A', '2023-01-31 17:35:41.000'),
       ('11', 'A', '2023-01-31 17:35:42.000'),
       ('12', 'A', '2023-01-31 17:35:42.000'),
       ('13', 'A', '2023-01-31 17:35:42.000'),
       ('14', 'A', '2023-01-31 17:35:42.000'),
       ('15', 'A', '2023-01-31 17:35:45.000'),
       ('16', 'A', '2023-01-31 17:35:45.000'),
       ('17', 'A', '2023-01-31 17:37:10.000'),
       ('18', 'A', '2023-01-31 17:37:12.000'),
       ('19', 'A', '2023-01-31 17:37:14.000'),
       ('20', 'A', '2023-01-31 17:52:14.000'),
       ('21', 'A', '2023-01-31 18:12:14.000'),
       ('22', 'A', '2023-01-31 18:45:14.000'),
       ('23', 'A', '2023-01-31 18:55:15.000'),
       ('1', 'B', '2023-01-31 06:37:15.000'),
       ('2', 'B', '2023-01-31 06:40:15.000');

注：原INSERT语句存在多余列值，已修正。

会话划分需求 #

• 同一userid连续访问同一webid，且每次访问的时间间隔在30分钟内，归为同一会话，first_ts为该会话的首个时间戳；
• 若用户访问某webid后跳转至其他webid，再返回原webid时，需生成新会话，first_ts刷新为当前时间戳；
• 同一webid的连续访问中，若某次时间间隔超过30分钟，需拆分会话，first_ts同步刷新。

现有脚本问题 #

原有脚本仅按userid分区计算会话ID，未区分同一用户下连续访问的同一个webid独立段，导致用户切换webid后返回原webid时，无法生成新的会话ID。

修正后的解决方案 #

通用SQL版 #

-- 第一步：标记用户webid切换点及上一次访问时间
CREATE TEMP TABLE lags AS
SELECT *,
       CASE WHEN webid = LAG(webid, 1, webid) OVER (PARTITION BY userid ORDER BY ts) THEN 0 ELSE 1 END AS webid_change,
       LAG(ts, 1, ts) OVER (PARTITION BY userid ORDER BY ts) AS lag_ts
FROM myTable;

-- 第二步：生成webid连续段ID，标记时间间隔超30分钟的点
CREATE TEMP TABLE session_markers AS
SELECT *,
       SUM(webid_change) OVER (PARTITION BY userid ORDER BY ts) AS webid_segment_id,
       CASE WHEN EXTRACT(EPOCH FROM (ts - lag_ts)) / 60 > 30 THEN 1 ELSE 0 END AS time_gap
FROM lags;

-- 第三步：在webid连续段内生成会话ID
CREATE TEMP TABLE sessions AS
SELECT *,
       SUM(time_gap) OVER (PARTITION BY userid, webid_segment_id ORDER BY ts) + 1 AS session_id
FROM session_markers;

-- 第四步：计算会话首次时间戳，生成最终结果
SELECT
       ROW_NUMBER() OVER (ORDER BY userid, ts) AS rowid,
       userid,
       webid,
       session_id,
       ts,
       MIN(ts) OVER (PARTITION BY userid, webid_segment_id, session_id) AS first_ts
FROM sessions
ORDER BY userid, ts;

PostgreSQL版（简化版） #

SELECT
       ROW_NUMBER() OVER (ORDER BY userid, ts) AS rowid,
       userid,
       webid,
       session_id,
       ts,
       first_ts
FROM (
    SELECT
           t.*,
           SUM(time_gap) OVER (PARTITION BY userid, webid_segment_id ORDER BY ts) + 1 AS session_id,
           MIN(ts) OVER (PARTITION BY userid, webid_segment_id, SUM(time_gap) OVER (PARTITION BY userid, webid_segment_id ORDER BY ts)) AS first_ts
    FROM (
        SELECT
               t.*,
               SUM(webid_change) OVER (PARTITION BY userid ORDER BY ts) AS webid_segment_id,
               CASE WHEN ts > lag_ts + INTERVAL '30 minutes' THEN 1 ELSE 0 END AS time_gap
        FROM (
            SELECT
                   *,
                   CASE WHEN webid = LAG(webid, 1, webid) OVER (PARTITION BY userid ORDER BY ts) THEN 0 ELSE 1 END AS webid_change,
                   LAG(ts, 1, ts) OVER (PARTITION BY userid ORDER BY ts) AS lag_ts
            FROM myTable t
        ) t
    ) t
) t
ORDER BY userid, ts;

方案说明 #

1. 通过webid_change标记用户切换webid的节点，累加生成webid_segment_id，区分用户连续访问同一个webid的独立段；
2. 在每个webid连续段内，判断相邻访问的时间间隔是否超过30分钟，标记time_gap；
3. 累加time_gap生成会话ID，确保切换webid后返回、或时间间隔超30分钟时都能生成新会话；
4. 按userid、webid_segment_id、session_id分区计算MIN(ts)，得到每个会话的首次时间戳first_ts。

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