SQL统计连续行为怎么实现_窗口函数实战案例【教学】-SQL-PHP中文网

SQL统计连续行为怎么实现_窗口函数实战案例【教学】

冰川箭仙

发布： 2025-12-23 22:31:02

原创

846人浏览过

SQL统计连续行为的核心是用窗口函数构造分组标识：通过行号差法（如per_user_seq与global_seq相减）生成group_id，再按user_id和group_id聚合获取连续段起止及长度；需区分“序列连续”与“日期连续”，并可用字符串拼接验证结果。

sql统计连续行为怎么实现_窗口函数实战案例【教学】

统计连续行为，核心是识别“连续”——即按时间或序号排列后，相邻记录满足相同条件。SQL里最常用、最可靠的方式是用窗口函数构造分组标识，再聚合。关键不在“怎么写”，而在“怎么想”：把连续段转化为可分组的逻辑块。

用行号差法识别连续登录天数

用户每天一条登录记录，要找出所有连续登录≥3天的用户及起止日期。思路是：对每个用户按日期排序，生成行号；再给所有日期整体排序（不区分用户）也生成行号；两个行号相减，同一连续段的结果恒定——这就是“连续组ID”。

先按 user_id 分组、date 排序，用 ROW_NUMBER() 得到 per_user_seq
再对全表 date 排序（忽略 user_id），得 global_seq
计算 group_id = date - INTERVAL (per_user_seq - 1) DAY，或更通用：group_id = DATE_SUB(date, INTERVAL (per_user_seq - 1) DAY)
按 user_id 和 group_id 分组，COUNT() 即连续天数，MIN/MAX 得区间

处理缺失日期的“伪连续”陷阱

如果原始数据本身有日期空缺（比如用户某天没记录，不代表没登录），直接按日期相减会出错。稳妥做法是用 序号差 而非日期差：ROW_NUMBER() over (partition by user_id order by date) 减去 ROW_NUMBER() over (order by date)，只要序号差相同，就说明中间没跳过——这是真正的“序列连续”。日期是否连续，由业务定义；SQL只保证按你给的排序逻辑识别出连续块。

连续操作类型+时长双维度统计

比如统计用户连续点击“加入购物车”且总时长≤2小时的行为段。这时不能只看序号，需结合时间戳判断间隔：
– 用 LAG() 取上一行时间，算当前与前次的时间差
– 差值 > 2小时 → 新连续段开始，用 SUM() over (rows unbounded preceding) 累计标记断点
– 最终用断点累计值作为 group_id，再聚合

新增列 is_new_seg = CASE WHEN timestamp - LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) > INTERVAL '2 HOUR' THEN 1 ELSE 0 END
用 SUM(is_new_seg) OVER (PARTITION BY user_id ORDER BY timestamp) 生成 segment_id
按 user_id + segment_id 分组，统计 COUNT、MIN、MAX、SUM 等

小技巧：用字符串拼接快速验证连续段

开发调试时，可用 GROUP_CONCAT（MySQL）或 STRING_AGG（PostgreSQL）把连续段内的日期/操作拼成字符串，一眼看出是否真连续：

SELECT user_id, segment_id, STRING_AGG(date::text, ',' ORDER BY date) AS dates_in_seg FROM grouped_data GROUP BY user_id, segment_id HAVING COUNT(*) >= 3;

看到 "2024-05-01,2024-05-02,2024-05-03" 就放心；若出现 "2024-05-01,2024-05-03"，说明漏了数据或逻辑有误。

以上就是SQL统计连续行为怎么实现_窗口函数实战案例【教学】的详细内容，更多请关注php中文网其它相关文章！