如何在PostgreSQL中使用窗口函数快速补齐断茬时间轴-SQL-PHP中文网

LAG()仅用于检测时间断层，补齐时间轴需先用GENERATE_SERIES生成完整时间点再LEFT JOIN；GENERATE_SERIES参数须显式指定类型和单位，JOIN前需统一截断时间精度；缺口识别后应分段补全而非全量扫描，聚合时用COALESCE确保空值为0。

如何在postgresql中使用窗口函数快速补齐断茬时间轴

LAG() 只能检测断层，不能补齐时间轴——窗口函数本身不生成新行，必须配合 GENERATE_SERIES 或递归 CTE 先造出完整时间点，再 LEFT JOIN。

`GENERATE_SERIES` 是补齐时间轴的起点，不是终点

GENERATE_SERIES 必须传入明确类型：起止参数得是 DATE 或 TIMESTAMP，写成 '2024-01-01' 字符串会报错，要写成 '2024-01-01'::DATE
步长必须显式带单位：'1 day'、'5 minute'、'1 month'，漏掉 INTERVAL 或单位（比如只写 '1'）会失败
如果原始数据含时间戳但你要按天对齐，JOIN 前必须统一截断：date_trunc('day', event_time) 或 event_time::DATE，否则无法匹配 GENERATE_SERIES 产出的日期值

示例（补 2024-01-01 到 2024-01-31 每天）：

SELECT gs.dt
FROM generate_series('2024-01-01'::DATE, '2024-01-31'::DATE, '1 day'::INTERVAL) AS gs(dt)

`LAG()` 的唯一正经用途是定位缺口区间

直接用 LAG(event_time) OVER (ORDER BY event_time) 算出上一行时间，再做差值判断是否跳天/跳小时，比全量生成再对比快得多

缺口识别语句模板：

SELECT event_time,
     LAG(event_time) OVER (ORDER BY event_time) AS prev_time,
     event_time - LAG(event_time) OVER (ORDER BY event_time) AS gap
FROM events
WHERE event_time >= '2024-01-01';

注意首行 prev_time 是 NULL，这是正常行为；别在 WHERE 里过滤它，否则漏掉第一条记录
如果发现 gap > INTERVAL '1 day'，说明中间缺了至少一天——这时才该用 GENERATE_SERIES(prev_time + INTERVAL '1 day', event_time - INTERVAL '1 day', '1 day') 针对该段补，而不是硬扫全范围

补齐后聚合时，`COALESCE(SUM(), 0)` 不可省

LEFT JOIN 后，缺失日期对应业务字段全是 NULL，SUM(amount) 自动忽略 NULL，结果为 NULL，不是 0
COUNT(*) 会把补出来的空行也计进去，而 COUNT(amount) 才只统计非空值；若要“每日订单数”，得用 COALESCE(COUNT(amount), 0)
错误写法：SUM(t.amount) → 图表里某天直接消失
正确写法：COALESCE(SUM(t.amount), 0) AS daily_sales
GROUP BY 必须用生成序列的字段（如 gs.dt），不能用原始表的 t.event_time（它可能是 NULL）