用 PostgreSQL 窗口函数对点击流做 30 分钟超时会话切分

问题场景

点击流表通常一行一个事件。分析会话数、会话时长和会话内点击量时，需要先按用户把事件切成会话：同一用户相邻事件间隔超过 30 分钟，就认为进入新会话。若在应用层逐行循环，代码复杂且容易受数据顺序影响。可以用 PostgreSQL 窗口函数在 SQL 内完成切分和聚合。

下面示例使用临时表和占位用户，便于直接运行。生产表去掉 TEMP，并按数据量考虑分区。

可运行的最小示例

-- 示例用临时表；生产环境去掉 TEMP 并按数据量分区
CREATE TEMP TABLE click_events (
  event_id bigserial PRIMARY KEY,
  user_id text NOT NULL,
  event_time timestamptz NOT NULL,
  event_type text NOT NULL,
  page_url text
);

CREATE INDEX idx_click_events_user_time
  ON click_events (user_id, event_time, event_id);

INSERT INTO click_events (user_id, event_time, event_type, page_url) VALUES
('u001', '2024-01-01 10:00:00+00', 'view',  '/a'),
('u001', '2024-01-01 10:10:00+00', 'click', '/a'),
('u001', '2024-01-01 10:45:00+00', 'view',  '/b'),
('u001', '2024-01-01 11:20:00+00', 'click', '/b'),
('u002', '2024-01-01 09:00:00+00', 'view',  '/x'),
('u002', '2024-01-01 09:30:00+00', 'click', '/x');

WITH ordered AS (
  SELECT
    event_id,
    user_id,
    event_time,
    event_type,
    page_url,
    lag(event_time) OVER (
      PARTITION BY user_id
      ORDER BY event_time, event_id
    ) AS prev_event_time
  FROM click_events
),
marked AS (
  SELECT
    *,
    CASE
      WHEN prev_event_time IS NULL
        OR event_time - prev_event_time > INTERVAL '30 minutes'
      THEN 1 ELSE 0
    END AS is_new_session
  FROM ordered
),
sessionized AS (
  SELECT
    *,
    sum(is_new_session) OVER (
      PARTITION BY user_id
      ORDER BY event_time, event_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_seq
  FROM marked
)
SELECT
  user_id || ':' || session_seq::text AS session_id,
  user_id,
  session_seq,
  min(event_time) AS session_start,
  max(event_time) AS session_end,
  max(event_time) - min(event_time) AS duration,
  count(*) AS event_count,
  count(*) FILTER (WHERE event_type = 'click') AS click_count
FROM sessionized
GROUP BY user_id, session_seq
ORDER BY user_id, session_seq;

核心步骤是：lag(event_time) 取同一用户上一条事件时间；用 CASE 标记新会话；再用 sum(is_new_session) 累计生成会话序号；最后按 user_id, session_seq 聚合。ORDER BY event_time, event_id 让同一时间的多个事件也有稳定顺序。

运行方法

把脚本保存为 sessionize.sql，在测试库执行：

psql -d 你的数据库名 -f sessionize.sql

也可以把 SQL 粘贴到 psql 交互窗口。示例中 u001 的事件间隔为 10 分钟、35 分钟、35 分钟，会切成 3 个会话；u002 间隔正好 30 分钟，不切分，仍为 1 个会话。

边界与限制

• 超时判定是相邻事件间隔 > 30 minutes。正好 30 分钟不切分；若业务要求大于等于 30 分钟切分，应改比较符。
• 仅按 user_id 分区。匿名访问需改用 visitor_id 或 device_id，并保证分区键非空。
• 该 SQL 是全量重算。迟到事件插入后，相关用户的会话序号可能变化，需要重跑或另行设计增量会话表。
• duration 是首末事件时间差，不是用户真实活跃时长；count(*) 统计所有事件类型，可按需过滤。
• timestamptz 比较间隔不受展示时区影响，展示时可用 AT TIME ZONE 转换。
• 索引 (user_id, event_time, event_id) 有助于按用户排序；大表还需结合数据量与执行计划调整分区或过滤条件。
• 如果存在重复上报事件，应先按业务主键去重，否则会重复计数并可能影响会话边界。