在用户行为分析中，我们常常需要弄清楚一个用户从上次活跃到本次活跃隔了多久，进而判断他处于连续活跃、间歇活跃还是已经流失。借助SQL的窗口函数，可以直接在数据库里按用户分组并按时间排序，计算相邻记录之间的间隔天数，从而检测出用户的活跃周期。
什么是用户活跃周期
用户活跃周期通常指用户两次有效行为之间的时间跨度。如果间隔很短且稳定，说明处于活跃期；如果间隔突然变长，可能进入沉默或流失阶段。我们用一张简单的用户登录表来演示。
示例表结构
| 字段 | 说明 |
|---|---|
| user_id | 用户编号 |
| active_date | 活跃日期 |
使用LAG窗口函数计算间隔
LAG函数可以获取同一分组中前一行的值。配合PARTITION BY与ORDER BY，就能拿到每个用户上一次活跃日期，再用日期相减得到间隔。
-- 计算每位用户相邻两次活跃的天数间隔
SELECT
user_id,
active_date,
LAG(active_date) OVER (
PARTITION BY user_id
ORDER BY active_date
) AS last_active_date,
DATEDIFF(
active_date,
LAG(active_date) OVER (
PARTITION BY user_id
ORDER BY active_date
)
) AS gap_days
FROM user_activity
ORDER BY user_id, active_date;
根据间隔划分活跃周期
得到间隔后，可以设定阈值，比如间隔大于7天算一次周期中断。下面用累加方式标记周期编号。
-- 标记用户活跃周期：间隔超过7天则开启新周期
SELECT
user_id,
active_date,
gap_days,
SUM(CASE WHEN gap_days > 7 OR gap_days IS NULL THEN 1 ELSE 0 END)
OVER (PARTITION BY user_id ORDER BY active_date) AS cycle_id
FROM (
SELECT
user_id,
active_date,
DATEDIFF(
active_date,
LAG(active_date) OVER (PARTITION BY user_id ORDER BY active_date)
) AS gap_days
FROM user_activity
) t
ORDER BY user_id, active_date;
周期聚合统计
有了cycle_id，就能按用户和周期汇总次数与跨度：
- 每个周期包含几天活跃
- 周期总时长
- 周期平均间隔
SELECT
user_id,
cycle_id,
COUNT(*) AS active_cnt,
DATEDIFF(MAX(active_date), MIN(active_date)) + 1 AS cycle_span
FROM (
SELECT
user_id,
active_date,
SUM(CASE WHEN gap_days > 7 OR gap_days IS NULL THEN 1 ELSE 0 END)
OVER (PARTITION BY user_id ORDER BY active_date) AS cycle_id
FROM (
SELECT
user_id,
active_date,
DATEDIFF(
active_date,
LAG(active_date) OVER (PARTITION BY user_id ORDER BY active_date)
) AS gap_days
FROM user_activity
) a
) b
GROUP BY user_id, cycle_id
ORDER BY user_id, cycle_id;
注意事项
在写这类查询时，注意数据库对DATEDIFF参数的顺序要求不同，例如PostgreSQL常用active_date - LAG(...) 直接相减。另外，若活跃数据存在重复日期，应先按天去重再计算间隔。
窗口函数不会改变原表行数，只是在每行上附加计算结果，非常适合做行为序列分析。
小结
通过LAG等窗口函数，我们可以用纯SQL检测用户活跃周期，核心是先算相邻间隔，再按阈值切分周期。这种方法无需导出数据到脚本，在数仓或业务库里都能直接运行，方便运营与风控快速洞察用户节奏。