如何合并间隔1天的用户活动分组以计算连续活跃天数

你原来用「日期 - 行号」生成grp的方法，只能处理无间隔的连续日期；但需求是间隔1天也算连续，所以需要调整分组逻辑，把间隔≤1天的相邻分组合并。

正确实现步骤 #

假设临时表tmp包含UserID、activity_date（已去重，每个用户每天一行），以及你之前生成的grp（DATE_SUB(activity_date, INTERVAL row_number() over(partition by UserID order by activity_date) DAY)），可以按以下步骤调整：

1. 计算相邻分组的日期间隔：对每个用户按grp排序，算出当前grp和前一个grp的天数差
2. 标记新分组起始行：当天数差>1时，标记为新组的开始
3. 生成合并后的分组ID：对标记值做累积求和，得到每个用户的大分组ID
4. 统计连续天数：按用户和大分组ID取min/max日期，计算连续天数

完整SQL示例 #

WITH grouped_data AS (
  SELECT
    UserID,
    activity_date,
    grp,
    -- 标记新组：第一行 或 当前grp与上一个grp间隔>1天
    CASE
      WHEN LAG(grp) OVER(PARTITION BY UserID ORDER BY grp) IS NULL THEN 1
      WHEN DATE_DIFF(grp, LAG(grp) OVER(PARTITION BY UserID ORDER BY grp), DAY) > 1 THEN 1
      ELSE 0
    END AS is_new_group
  FROM tmp
),
final_groups AS (
  SELECT
    UserID,
    activity_date,
    -- 累积求和生成合并后的分组ID
    SUM(is_new_group) OVER(PARTITION BY UserID ORDER BY grp) AS merged_group_id
  FROM grouped_data
)
SELECT
  UserID,
  MIN(activity_date) AS start_date,
  MAX(activity_date) AS end_date,
  DATE_DIFF(MAX(activity_date), MIN(activity_date), DAY) + 1 AS consecutive_days
FROM final_groups
GROUP BY UserID, merged_group_id
ORDER BY UserID, start_date;

原SQL的问题 #

你原来的AggregateGroup只是标记了部分行（最后一行或间隔1天的行），没有生成一个能把所有连续行归为同一组的ID。后续用min/max时，无法把间隔1天的多个grp下的行聚合到一起，自然得不到正确结果。

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