如何实现活动数据连续会话化（间隔与孤岛问题）并修正session_id错误？

数据表结构与修正后数据 #

原插入语句存在字段数量不匹配的错误（表结构仅3个字段，但插入值包含冗余字段），修正后的表结构与数据如下：

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');

会话划分规则 #

需生成包含rowid、userid、webid、session_id、ts、first_ts的结果集，规则如下：

• 同一userid连续访问同一webid，且时间间隔≤30分钟，归为同一会话，first_ts为该会话首个时间戳；
• userid跳转至其他webid后返回原webid，需新建会话，first_ts刷新为当前时间戳；
• 同一userid连续访问同一webid，若时间间隔>30分钟，会话中断，新建会话并刷新first_ts。

现有脚本问题 #

原脚本可生成正确的first_ts，但session_id存在逻辑漏洞：一是未严格按时序判断时间间隔（使用abs(datediff)可能导致反向时间误判）；二是会话划分未完全适配跨webid的场景。

修正后的PostgreSQL解决方案 #

以下脚本通过精准的时序标记与会话累计，生成符合规则的结果集：

SELECT
    row_number() OVER (ORDER BY userid, ts) AS rowid,
    userid,
    webid,
    session_id,
    ts,
    first_ts
FROM (
    SELECT
        t.*,
        MIN(ts) OVER (PARTITION BY userid, session_id) AS first_ts
    FROM (
        SELECT
            t.*,
            SUM(is_new_session) OVER (PARTITION BY userid ORDER BY ts) + 1 AS session_id
        FROM (
            SELECT
                t.*,
                CASE
                    WHEN lag(webid) OVER (PARTITION BY userid ORDER BY ts) != webid THEN 1
                    WHEN ts - lag(ts) OVER (PARTITION BY userid ORDER BY ts) > INTERVAL '30 minutes' THEN 1
                    ELSE 0
                END AS is_new_session
            FROM myTable t
        ) t
    ) t
) t
ORDER BY rowid;

修正后的通用SQL解决方案 #

适用于支持窗口函数的SQL引擎（如SQL Server）：

WITH lags AS (
    SELECT
        *,
        LAG(webid) OVER (PARTITION BY userid ORDER BY ts) AS lag_webid,
        LAG(ts) OVER (PARTITION BY userid ORDER BY ts) AS lag_ts
    FROM myTable
),
is_sessions AS (
    SELECT
        *,
        CASE
            WHEN lag_webid != webid THEN 1
            WHEN DATEDIFF(MINUTE, lag_ts, ts) > 30 THEN 1
            ELSE 0
        END AS is_new_session
    FROM lags
),
sessions AS (
    SELECT
        *,
        SUM(is_new_session) OVER (PARTITION BY userid ORDER BY ts) + 1 AS session_id
    FROM is_sessions
),
final_result AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY userid, ts) AS rowid,
        userid,
        webid,
        session_id,
        ts,
        MIN(ts) OVER (PARTITION BY userid, session_id) AS first_ts
    FROM sessions
)
SELECT * FROM final_result ORDER BY rowid;

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