在统计类业务里，最常见的需求之一就是按天输出指标。但如果某一天没有任何数据，SQL 查询的结果集中根本不会出现这一行，前端画出来的折线图自然就断了。要解决这个问题，思路其实很清晰：先生成一份连续的日期序列作为骨架，再左连接业务数据，缺失的位置用零值填充。而在分组补齐的场景下，窗口函数尤其是 lag 扮演着关键角色。本文把整套方法拆开讲透，并给出可直接运行的 SQL。
一、为什么会产生 gap：问题场景拆解
假设有一张订单表，结构很简单，只有下单时间和金额两个关键字段：
CREATE TABLE orders (
order_date DATE,
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
('2024-06-01', 100.00),
('2024-06-02', 150.00),
('2024-06-05', 200.00),
('2024-06-06', 300.00),
('2024-06-10', 500.00);如果直接执行 SELECT order_date, SUM(amount) FROM orders GROUP BY order_date，得到的结果只有 5 行，6月3日、6月4日、6月7日到6月9日都不存在。原因很朴素：SQL 的聚合只会对已有行做分组，数据库不会凭空创造出数据里没有的日期。这就是典型的 gap 问题，本质上是「业务数据的稀疏性」与「展示需求的连续性」之间的矛盾。
另一类 gap 问题更隐蔽一些：数据里存在用户分组的场景，比如某个用户连续登录了几天然后中断，隔了几天又回来。分析留存时需要判断哪些记录属于「同一段连续活跃期」，这同样需要用窗口函数来划分连续区间。两类问题看起来不同，但核心手段高度一致，都是对「与前一条记录的差值」进行分析。
二、用 lag 配合日期差值生成组标识
窗口函数 lag 可以在不改变行数的前提下，取出按指定顺序排序后的前一行数据。利用它，我们可以计算当前行与上一行之间的日期间隔，一旦间隔超过 1 天，说明这里发生了断层，应该开启一个新组。
经典的写法分两步走。第一步用 lag 拿到上一条记录的日期并计算差值，第二步根据累计求和生成分组编号。PostgreSQL 中可以直接使用：
WITH t AS (
SELECT
order_date,
order_date - LAG(order_date) OVER (ORDER BY order_date) AS diff
FROM orders
),
g AS (
SELECT
order_date,
COALESCE(diff, 0) AS diff
FROM t
)
SELECT
order_date,
SUM(CASE WHEN diff > 1 THEN 1 ELSE 0 END)
OVER (ORDER BY order_date) AS grp
FROM g;这段 SQL 的逻辑要点在于：第一条记录的 lag 结果是 NULL，用 COALESCE 处理成 0，保证它属于第一组；之后每遇到一次间隔大于 1 天的断层，分组编号就加一。最终相同 grp 值的记录必然是日期连续的。MySQL 8.0 之前没有窗口函数，只能靠用户变量模拟，写法繁琐且容易出错；MySQL 8.0 之后语法与上面基本一致，只是日期差值需要换成 DATEDIFF(order_date, LAG(order_date) OVER (ORDER BY order_date))。
拿到分组编号后，很多分析就顺理成章了。比如统计每个连续区间的起止日期和记录数，只需要在 grp 上做一次普通聚合：
SELECT
grp,
MIN(order_date) AS seg_start,
MAX(order_date) AS seg_end,
COUNT(*) AS cnt
FROM (
-- 上面的分组结果
) x
GROUP BY grp;三、补齐缺失日期：日历表与递归序列
分组只是第一步，报表展示真正需要的是「每一天都有数据」。这就得先有一份连续日期。生成方式主要有三种，各有适用场景。
第一种是日历表，建一张从业务起点到未来几年的日期维表，每天一行。它的优点是查询简单、性能稳定，还能附带节假日、周几等属性字段；缺点是需要定期维护或提前生成。有了日历表后，补齐数据的 SQL 非常直观：
SELECT
c.cal_date,
COALESCE(SUM(o.amount), 0) AS total_amount
FROM calendar c
LEFT JOIN orders o
ON o.order_date = c.cal_date
WHERE c.cal_date BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY c.cal_date
ORDER BY c.cal_date;注意这里必须用 LEFT JOIN 且日历表在左边，顺序反了就退化成内连接，缺失日期又会被过滤掉。聚合后用 COALESCE 把 NULL 转成 0，前端拿到的就是连续无断点的数据。
第二种是递归 CTE，适合不方便建维表、或者时间范围由参数动态决定的场景。PostgreSQL 和 MySQL 8.0 都支持 WITH RECURSIVE：
WITH RECURSIVE seq AS (
SELECT DATE '2024-06-01' AS d
UNION ALL
SELECT d + INTERVAL '1 day'
FROM seq
WHERE d < DATE '2024-06-30'
)
SELECT
s.d,
COALESCE(SUM(o.amount), 0) AS total_amount
FROM seq s
LEFT JOIN orders o ON o.order_date = s.d
GROUP BY s.d
ORDER BY s.d;MySQL 里写法略有不同，日期字面量用 '2024-06-01'，加一天写成 DATE_ADD(d, INTERVAL 1 DAY)，其余结构一致。第三种是 PostgreSQL 特有的 generate_series，一行就能生成日期序列，例如 SELECT generate_series('2024-06-01'::date, '2024-06-30'::date, '1 day')，配合左连接使用最为简洁。
四、分组补齐与常见陷阱
把两种技术组合起来，就能处理更复杂的场景：按用户补齐每天的活跃状态。思路是先做「用户 × 日期」的笛卡尔骨架（每个用户生成一份完整日期序列），再左连接行为数据，最后用条件表达式标记当天是否有记录。骨架的生成同样可以用递归 CTE 交叉用户表实现。
实际落地时有几个容易踩的坑值得提醒。一是索引问题，左连接条件 o.order_date = c.cal_date 上必须有索引，否则日历表行数一多，查询会退化为逐行扫描，数据量大时性能急剧下降。二是 lag 的窗口必须写 ORDER BY，漏掉排序会直接报语法错误，而排序字段选错（比如按 id 而不是按日期排序）则会得到错误的分组结果，这种错误更隐蔽。三是时间精度问题，如果字段是 datetime 类型，直接比较日期会匹配不上，需要先用 DATE(order_time) 归一化，但注意对函数包裹的列做连接会导致索引失效，更好的做法是按 order_time >= 当天零点 AND order_time < 次日零点 的范围条件来写连接。四是分组时区隔字段不要遗漏，多用户场景下 lag 的窗口要写成 PARTITION BY user_id ORDER BY order_date，否则不同用户的记录会互相干扰，把断层判断搞乱。
总结一下整套方法论：用 lag 计算相邻记录差值识别断层，用累计和生成连续区间分组，用日历表或递归序列补齐缺失日期，用 LEFT JOIN 加 COALESCE 填充零值。四个环节各司其职，掌握了之后，无论是报表补零、留存分析还是连续活跃天数统计，都可以用同一套窗口函数思路优雅地解决。