时间断点指的是时间序列数据中存在的不连续、缺失的时间片段，在用户行为分析、订单统计、设备监控等业务场景中十分常见。处理时间断点需要先定位断点位置，再结合业务需求做补全或者标记处理，SQL窗口函数可以高效完成这类操作。
时间断点的常见场景
时间断点通常出现在按固定周期统计的数据中，比如按天统计的用户活跃数，某几天没有活跃用户就会形成断点；或者设备的定时上报数据，某几个时间点没有上报记录也会产生断点。常见的断点类型有两种：
- 连续时间缺失：比如2024-01-01到2024-01-05的订单数据，缺少2024-01-03的记录，形成单日断点
- 连续区间缺失：比如2024-01-01到2024-01-10的活跃数据，缺少2024-01-04到2024-01-06三天的记录，形成连续区间断点
窗口函数处理时间断点的核心思路
处理时间断点的核心是先对时间字段排序，再通过窗口函数获取相邻时间点的差值，根据差值判断是否存在断点。常用的窗口函数包括LAG()和LEAD()，前者可以获取当前行之前指定偏移量的行数据，后者可以获取当前行之后指定偏移量的行数据。
步骤1：定位时间断点
首先使用LAG()函数获取每条记录的上一条时间记录，计算当前时间和上一条时间的差值，当差值大于固定的时间周期时，就说明存在时间断点。
假设有一张用户活跃表user_active，结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| user_id | int | 用户ID |
| active_date | date | 活跃日期 |
现在需要查询每个用户的活跃时间断点，实现代码如下：
-- 查询用户活跃时间断点
SELECT
user_id,
active_date,
prev_active_date,
-- 计算当前活跃日期和上一条活跃日期的天数差
DATEDIFF(active_date, prev_active_date) AS day_diff
FROM (
SELECT
user_id,
active_date,
-- 获取同一个用户的上一条活跃日期
LAG(active_date, 1) OVER (PARTITION BY user_id ORDER BY active_date) AS prev_active_date
FROM user_active
) t
-- 天数差大于1说明存在断点，排除第一条没有上一条记录的空值情况
WHERE day_diff > 1 AND prev_active_date IS NOT NULL
ORDER BY user_id, active_date;
步骤2：补全时间断点
定位到断点之后，如果需要补全缺失的时间区间，可以结合递归CTE生成连续的时间序列，再用左连接关联原数据，缺失的部分就对应时间断点。
以下代码实现补全2024年1月所有用户的活跃时间断点，标记出哪些日期没有活跃记录：
-- 生成2024年1月的连续日期序列
WITH recursive date_series AS (
SELECT DATE('2024-01-01') AS dt
UNION ALL
SELECT DATE_ADD(dt, INTERVAL 1 DAY)
FROM date_series
WHERE dt < '2024-01-31'
),
-- 获取所有用户的去重列表
user_list AS (
SELECT DISTINCT user_id FROM user_active
),
-- 生成所有用户和所有日期的笛卡尔积
full_user_date AS (
SELECT
u.user_id,
d.dt
FROM user_list u
CROSS JOIN date_series d
),
-- 关联原活跃数据，标记是否活跃
user_active_flag AS (
SELECT
f.user_id,
f.dt,
CASE WHEN u.active_date IS NOT NULL THEN 1 ELSE 0 END AS is_active
FROM full_user_date f
LEFT JOIN user_active u
ON f.user_id = u.user_id
AND f.dt = u.active_date
)
-- 查询所有未活跃的断点日期
SELECT
user_id,
dt AS missing_date
FROM user_active_flag
WHERE is_active = 0
ORDER BY user_id, dt;
步骤3：计算断点前后的数据差异
如果需要分析断点前后的数据变化，可以用LEAD()函数获取断点之后的第一条有效数据，对比断点前后的指标差异。
假设有一张每日订单表daily_order，包含order_date和order_cnt字段，以下代码计算订单断点前后的订单量变化：
SELECT
order_date,
order_cnt,
prev_order_cnt,
next_order_date,
next_order_cnt,
-- 计算断点前后的订单量差值
next_order_cnt - prev_order_cnt AS cnt_change
FROM (
SELECT
order_date,
order_cnt,
LAG(order_cnt, 1) OVER (ORDER BY order_date) AS prev_order_cnt,
LEAD(order_date, 1) OVER (ORDER BY order_date) AS next_order_date,
LEAD(order_cnt, 1) OVER (ORDER BY order_date) AS next_order_cnt,
-- 判断当前是否是断点后的第一条数据
DATEDIFF(LEAD(order_date, 1) OVER (ORDER BY order_date), order_date) AS next_day_diff
FROM daily_order
) t
-- 筛选断点后的第一条记录，即下一条日期和当前日期差值大于1的情况
WHERE next_day_diff > 1 AND next_order_date IS NOT NULL;
注意事项
使用窗口函数处理时间断点时需要注意以下几点：
- 时间字段需要先统一格式，确保排序和计算差值的逻辑正确，避免因为格式不一致导致结果错误
- 使用
LAG()和LEAD()函数时，PARTITION BY子句要根据业务维度设置，比如按用户、按设备分组，避免不同维度的数据互相干扰 - 补全时间断点时，递归CTE的生成范围要覆盖业务需要的全部时间区间，避免遗漏边缘时间的断点
通过窗口函数处理时间断点，不需要复杂的循环逻辑，仅用SQL语句就可以完成断点定位、补全、差异分析等操作，大幅提升时间序列数据处理的效率。开发者可以根据具体的业务需求，灵活组合不同的窗口函数实现对应的处理逻辑。