SQL窗口函数如何拆分用户连续活跃区间-SQL-PHP中文网

“连续活跃区间”指用户在日历上不间断登录的日期段，需通过lag()识别断点、sum()累计生成session_id来标记每一段，如2024-01-01~01-03为第1段、01-05为第2段；核心是分两步：先用lag判断与前一日是否间隔大于1天并标记断点，再用sum over累计断点得到唯一组号，最后按user_id和session_id聚合起止日期与天数。

sql窗口函数如何拆分用户连续活跃区间

什么是“连续活跃区间”？先看数据长什么样

用户每天登录会产生一条记录，比如 user_id + login_date。所谓“连续活跃”，是指某用户在日历上连续多天都有记录（中间不能断）。拆分的目标，就是把同一用户的多段连续登录，各自打上独立的区间编号，例如：

2024-01-01 ~ 2024-01-03 → 第1段
2024-01-05 → 第2段（隔了一天）
2024-01-06 ~ 2024-01-08 → 第3段

关键不是“求最长连续天数”，而是“识别并标记每一段连续区间”。

用 `LAG()` + `ROW_NUMBER()` 构造分组标识

核心思路是：对每个用户按日期排序，判断当前行和上一行是否“只差1天”。如果断了，就开启新组。

SELECT
  user_id,
  login_date,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) 
    - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date - INTERVAL '1 day' * (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) - 1)) AS grp_id
FROM user_login;

这写法太绕——实际更稳的方式是两步走：

Pencil.dev

一款AI图像与设计工具，主要用于基于MCP的画布式AI辅助设计工具，可以集成在IDE中，设计与代码一体化，适合需要提升相关任务效率的用户。

下载

先用 LAG(login_date) 算出前一天日期，再用 CASE WHEN login_date - LAG(login_date) OVER (...) > 1 THEN 1 ELSE 0 END 标记“断点”
再用 SUM(...) 累计断点，生成递增的组号

推荐写法：

WITH flagged AS (
  SELECT
    user_id,
    login_date,
    CASE WHEN login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) > 1 
         THEN 1 ELSE 0 END AS is_break
  FROM user_login
),
grouped AS (
  SELECT
    user_id,
    login_date,
    SUM(is_break) OVER (PARTITION BY user_id ORDER BY login_date) AS session_id
  FROM flagged
)
SELECT
  user_id,
  MIN(login_date) AS start_date,
  MAX(login_date) AS end_date,
  COUNT(*) AS days
FROM grouped
GROUP BY user_id, session_id;