| 对齐 | 日期序列 left join 行为 |
|---|---|
| 断点 | gap 标记 |
| 段长 | 累计和分组取长度 |
连续活跃
连续活跃
WITH d AS (
SELECT user_id, dt,
date_sub(dt, row_number() OVER (PARTITION BY user_id ORDER BY dt)) AS grp
FROM dwd_active_user
)
SELECT user_id, count(1) AS continuous_days
FROM d GROUP BY user_id, grp
HAVING count(1) >= 7;连续段计算依赖「活跃日明细」的完整性，补数后要重算。