SQL无关联查询计算FactDailyUsers用户连续活跃day_in_row字段逻辑

原有代码问题 #

• 未对同用户同天的多条操作记录去重，会导致单日被重复计数，排序序号计算错误
• dateadd逻辑写反，连续区间分组的核心是「活跃日期减去该用户的活跃日期排序序号」，相同结果的日期属于同一个连续区间，加法无法得到相同分组值
• group by字段错误，不存在employee字段，需按user_id+连续区间分组值聚合

核心逻辑说明 #

1. 先对FactDailyUsers表按user_id、date分组去重，同一用户同一天不管有多少条操作记录，仅保留1条活跃记录
2. 对每个用户的活跃日期按时间正序排序，得到排序序号
3. 用活跃日期减去对应排序序号，得到连续区间的分组标识：连续的日期减去递增的序号，会得到相同的固定值
4. 按user_id+分组标识聚合，统计每组的天数即为连续活跃天数，组内最小/最大日期即为连续区间的起止时间

正确SQL实现（以MySQL为例，无关联查询，符合要求） #

SELECT 
  user_id,
  MIN(active_date) AS 连续区间开始日期,
  MAX(active_date) AS 连续区间结束日期,
  COUNT(*) AS day_in_row
FROM (
  SELECT 
    user_id,
    active_date,
    DATE_SUB(active_date, INTERVAL rn DAY) AS continuous_group
  FROM (
    -- 去重得到用户每日唯一活跃记录，同时生成排序序号
    SELECT 
      user_id,
      STR_TO_DATE(`date`, '%d/%m/%Y') AS active_date,
      ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY STR_TO_DATE(`date`, '%d/%m/%Y')) AS rn
    FROM FactDailyUsers
    GROUP BY user_id, `date`
  ) t1
) t2
GROUP BY user_id, continuous_group
ORDER BY user_id, 连续区间开始日期;

不同数据库适配说明 #

仅需调整日期处理函数即可，核心逻辑不变：

• PostgreSQL：日期转换用TO_DATE("date", 'dd/mm/yyyy')，日期减序号用active_date - rn * INTERVAL '1 day'
• SQL Server：日期转换用CONVERT(DATE, "date", 103)，日期减序号用DATEADD(DAY, -rn, active_date)

样例数据运行结果 #

该结果和样例表中预置的day_in_row取值完全匹配。

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