如何基于行数据对PostgreSQL数据行做窗口划分生成时间间隔会话列

核心实现逻辑 #

核心基于窗口函数分三步计算：

• 第一步：按时间戳排序后，用LAG()函数取上一行的时间戳，计算相邻两行的时间差
• 第二步：若相邻时间差大于给定阈值T，标记为1（表示新会话开始），否则标记为0，首行无前置时间默认标记为0
• 第三步：对标记列做累加求和，最终得到的累加值就是对应的会话ID

如果需要按用户拆分独立会话，只需要在所有窗口函数的参数里加上PARTITION BY 用户ID字段即可，避免不同用户的行被错误划分到同一会话。

代码示例（以30分钟间隔为例） #

Spark SQL / Hive SQL #

SELECT
    行号,
    时间戳,
    SUM(IF(ts_diff > 30 * 60, 1, 0)) OVER (ORDER BY 时间戳) AS 所属会话
FROM (
    SELECT
        行号,
        时间戳,
        -- 计算当前行与上一行时间戳的差值，单位秒
        UNIX_TIMESTAMP(时间戳) - UNIX_TIMESTAMP(LAG(时间戳, 1) OVER (ORDER BY 时间戳)) AS ts_diff
    FROM 你的表名
) t

PostgreSQL 写法 #

SELECT
    行号,
    时间戳,
    SUM(CASE WHEN ts_diff > INTERVAL '30 minutes' THEN 1 ELSE 0 END) OVER (ORDER BY 时间戳) AS 所属会话
FROM (
    SELECT
        行号,
        时间戳,
        时间戳 - LAG(时间戳, 1) OVER (ORDER BY 时间戳) AS ts_diff
    FROM 你的表名
) t

MySQL 8.0+ 写法 #

SELECT
    行号,
    时间戳,
    SUM(CASE WHEN ts_diff > 30 * 60 THEN 1 ELSE 0 END) OVER (ORDER BY 时间戳) AS 所属会话
FROM (
    SELECT
        行号,
        时间戳,
        UNIX_TIMESTAMP(时间戳) - UNIX_TIMESTAMP(LAG(时间戳, 1) OVER (ORDER BY 时间戳)) AS ts_diff
    FROM 你的表名
) t

如果使用不支持窗口函数的MySQL 5.x版本，可以通过自定义变量赋值的方式实现相同逻辑。

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