在用户行为分析中，连续登录或连续活跃天数是常见指标。但很多业务并不要求用户每日都来，比如健身打卡、课程学习类应用，用户可能隔天或每周固定几天参与。直接用日期差判断相邻天会让这类非每日登录被误切成多段。本文介绍如何用 SQL 窗口函数识别非每日但逻辑连续的活跃周期。
一、问题本质与误区
传统连续登录统计通常假设用户每天登录，于是用 datediff(day, 上次登录, 本次登录) = 1 来判定连续。但在非每日场景中，用户 1 号、3 号、5 号登录，中间空了 2 号和 4 号，按自然日看是断开的，业务上却应算同一次连续活跃。
另一个误区是一天多次登录会被计为多行，若不先按用户和日期去重，排序后会得到错误间隔。因此第一步必须是按用户与登录日期去重，确保每天只保留一条记录，再谈连续性。
二、核心思路：日期减序号分组法
连续分组的经典技巧是：对每位用户按登录日期升序打排序号，再用登录日期减去这个序号，得到的值如果相同，就说明这些日期属于同一连续段。即便日期不相邻，只要中间没有缺失超过业务允许的空档（此处先按“不要求每日”处理，即只要用户有登录就延续），该值会保持稳定。
具体用 dense_rank() 而非 row_number()，是因为若同一天有多条日志，去重后每天序号紧挨，日期减序号的偏移量才准确。下面以 SQL Server 语法为例展示。
-- 假设有表 user_log(user_id int, login_date date)
-- 先去重，再计算连续分组键
with dedup as (
select distinct user_id, login_date
from user_log
),
ranked as (
select
user_id,
login_date,
dense_rank() over (
partition by user_id
order by login_date
) as rn
from dedup
)
select
user_id,
dateadd(day, -rn, login_date) as grp_key,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) as active_days,
datediff(day, min(login_date), max(login_date)) + 1 as span_days
from ranked
group by user_id, dateadd(day, -rn, login_date)
order by user_id, start_date;
上面的 grp_key 是日期减去序号后的基准日。同一 user_id 且 grp_key 相同的所有行，就是一次连续活跃周期。active_days 是实际登录天数，span_days 是从首次到末次登录的日历跨度，可用来衡量“坚持了几周”。
三、允许固定空档的连续定义
如果业务规定“间隔不超过 2 天也算连续”，上述简单相减会误切。此时可先按允许空档补日期，或改用偏移量比较。一种实用写法是自连接上一行，判断日期差是否小于等于阈值。
以下示例允许最多间隔 2 天（即 1 号登、4 号登视为断，1 号登、3 号登视为连）：
with dedup as (
select distinct user_id, login_date
from user_log
),
prev as (
select
a.user_id,
a.login_date,
max(b.login_date) as last_login
from dedup a
left join dedup b
on a.user_id = b.user_id
and b.login_date < a.login_date
group by a.user_id, a.login_date
),
marked as (
select
user_id,
login_date,
case
when last_login is null then 1
when datediff(day, last_login, login_date) <= 2 then 0
else 1
end as is_new_seg
from prev
),
seg as (
select
user_id,
login_date,
sum(is_new_seg) over (
partition by user_id
order by login_date
) as seg_id
from marked
)
select
user_id,
seg_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) as active_days
from seg
group by user_id, seg_id
order by user_id, seg_id;
这里 is_new_seg 标记是否开启新段，sum() over() 滚动累加得到段编号。这种方式灵活适配各种空档规则，缺点是自连接在大表上需建好 (user_id, login_date) 索引。
四、方案对比与性能建议
日期减序号法代码短、易维护，适合“有登录即连续”的宽松定义；自连接偏移法适合严格空档控制。在千万级日志上，前者只需一次窗口排序，后者涉及关联与聚合，资源消耗更高。
实际落地时，建议将去重后的日表作为中间层，每日调度产出 user_daily_active，后续连续统计直接基于该小表计算。同时针对 user_id 做分区，能显著降低排序成本。
| 方法 | 适用场景 | 复杂度 | 可扩展性 |
|---|---|---|---|
| dense_rank 减日期 | 不要求每日登录 | 低 | 高 |
| 自连接判空档 | 限定最大间隔 | 中 | 中 |
| 递归 CTE | 复杂连续规则 | 高 | 低 |
五、总结
处理非每日连续登录，关键在明确“连续”的业务语义。若只需统计有登录的日子是否成段，窗口函数减日期最简洁；若需容忍空档，则用上一条记录做阈值判断。两种写法都应先按用户与日期去重，避免脏数据干扰。
在报表开发中，将连续活跃作为留存或忠诚度维度时，务必和产品确认空档容忍度，并在代码注释中写明规则，方便后续维护人员理解这段 SQL 的分段逻辑。