如何用SQL追踪用户会话内的站点访问序列（含重访场景）

核心思路是通过窗口函数追踪站点切换：只要当前站点与上一次访问的站点不同（包括首次访问），就递增site_num；连续访问同一站点则保持同一编号，返回已访问过的站点时生成新编号。

解决方案SQL #

假设数据表名为user_site_visits，执行以下查询：

SELECT
  visitor,
  session_num,
  site,
  page_view_num,
  timestamp,
  SUM(CASE 
        WHEN site != prev_site OR prev_site IS NULL THEN 1 
        ELSE 0 
      END) OVER (
        PARTITION BY visitor, session_num 
        ORDER BY timestamp
      ) AS site_num
FROM (
  SELECT
    visitor,
    session_num,
    site,
    page_view_num,
    timestamp,
    -- 获取同用户同会话的上一个访问站点
    LAG(site) OVER (
      PARTITION BY visitor, session_num 
      ORDER BY timestamp
    ) AS prev_site
  FROM user_site_visits
) AS sub
ORDER BY visitor, session_num, timestamp;

逻辑解释 #

1. 子查询部分：用LAG(site)窗口函数，按visitor和session_num分区、timestamp排序，获取当前访问的前一个站点。会话中首次访问的记录prev_site为NULL。
2. 外层聚合：通过SUM() OVER()累加站点切换的标记值：
   • 当prev_site为NULL（首次访问），或当前站点与prev_site不同时，标记为1
   • 连续访问同一站点时标记为0
   • 累加结果就是符合要求的site_num，比如User A的序列A→B→C→A会生成site_num 1→2→3→4，完全匹配需求。

如果page_view_num是会话内的递增访问顺序编号（无重复），可以替换ORDER BY timestamp为ORDER BY page_view_num，结果一致。

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