PostgreSQL如何按相邻时间戳间隔对SQL表数据分组

核心思路 #

通过两个窗口函数的组合即可实现需求，无需时间取整，完全基于相邻记录的时间差判断会话分割点：

1. 用LAG()窗口函数获取同用户下前一条活动记录的时间戳，对比当前记录的时间差是否超过设定阈值（示例为1分钟）
2. 对超过阈值的分割点做累加计数，最终得到的计数值就是每个会话的唯一分组ID

本方案也解决了你尝试RANGE窗口函数时遇到的问题：RANGE是固定长度滑动窗口，会强制拆分超过窗口长度的连续会话，而本方案完全基于相邻记录的时间差判断，不管会话总时长多久，只要相邻间隔没超过阈值就不会被拆分。

完整实现代码 #

WITH step1 AS (
    -- 第一步：计算每条记录是否是新会话的起点
    SELECT 
        id,
        "userId",
        "creationDate",
        -- 判断：第一条记录 或者 和上一条记录的时间差超过1分钟，则标记为新会话起点
        CASE 
            WHEN LAG("creationDate") OVER (PARTITION BY "userId" ORDER BY "creationDate") IS NULL 
                THEN 1
            WHEN "creationDate" - LAG("creationDate") OVER (PARTITION BY "userId" ORDER BY "creationDate") > INTERVAL '1 minute'
                THEN 1
            ELSE 0
        END AS is_new_session
    FROM public.user_session_activity_table
),
step2 AS (
    -- 第二步：累加新会话标记，得到每个会话的唯一ID
    SELECT 
        id,
        "userId",
        "creationDate",
        SUM(is_new_session) OVER (PARTITION BY "userId" ORDER BY "creationDate") AS session_id
    FROM step1
)
-- 最终查询：你可以在此基础上自行调整sessionLength的计算逻辑
SELECT 
    id,
    "userId",
    session_id,
    -- 示例会话时长计算，单位为秒
    EXTRACT(EPOCH FROM (MAX("creationDate") OVER (PARTITION BY "userId", session_id) - MIN("creationDate") OVER (PARTITION BY "userId", session_id))) || 's' AS sessionLength
FROM step2
ORDER BY "userId", "creationDate";

效果验证 #

对应你提供的测试数据，上述SQL运行后会生成3个独立的session_id：

• session_id=1：仅包含id=1的记录
• session_id=2：包含id=2、3、4的记录
• session_id=3：包含id=5、6、7的记录
  完全符合你的分组要求。

方案优势 #

1. 完全不依赖时间取整逻辑，不会出现跨时间整点的会话被拆分的问题
2. 支持任意时长的会话，只要相邻活动的时间差不超过阈值，就会被归为同一个会话，完美适配用户长时间连续操作、中途长时间离开后再次操作的场景
3. 性能优异，仅需两次窗口函数扫描，支持大数据量下的正常运行

内容的提问来源于stack exchange，提问作者Bence Mányoki