检测连续日历日
使用日期运算和行号查找不间断的日期序列
检测连续日历日 是 CoddyKit 上的免费 SQL Interview Prep 课时。 这是第 1 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。
面试题设
面试官很喜欢连续段问题，因为它们能体现您是否真正理解窗口函数和日期运算。一个典型题目是：“给定一张记录用户登录日期的表，请找出每一段连续且未中断的日历日期。”
直觉式做法是使用自连接，将每条记录与下一条记录进行比较，但在大表上这种做法会产生严重的性能开销，而且表达起来也很别扭。专业的答案会使用间断与连续区间技术。本课中，您将学习如何使用行号和日期相减，清晰地检测连续日期。
示例数据
在整个课程中，我们使用一张 logins 表，其中每行表示某位用户在某天处于活跃状态。假设重复记录已经被移除（每个日历日只有一次登录）。
user_id— 登录的用户login_date— 一个 DATE 值
用户 1 的日期为 1 月 1 日、2 日、3 日，然后出现间隔，接着是 1 月 6 日、7 日。我们预计会得到两段连续记录：一段持续 3 天，另一段持续 2 天。
SELECT * FROM logins ORDER BY user_id, login_date;
-- user_id | login_date
-- 1 | 2024-01-01
-- 1 | 2024-01-02
-- 1 | 2024-01-03
-- 1 | 2024-01-06
-- 1 | 2024-01-07核心洞察
下面这个技巧可以解决所有连续日期问题。如果按日期对记录排序，并为每条记录分配一个连续的行号，那么在任何一段连续日期中，日期与行号之间的差值都会保持不变。
为什么？因为在每个连续日期上，日期和行号都会恰好增加 1，所以它们的差值不会改变。当出现间隔时，日期会跳跃，但行号不会，因此这个常量会被打破，并开始一个新的分组。
观察差值
让我们手动处理用户 1 的数据。ROW_NUMBER 依次计数为 1、2、3、4、5。从日期中减去行号（按天数计算），观察结果。
- 1 月 1 日 − 1 = 12 月 31 日
- 1 月 2 日 − 2 = 12 月 31 日
- 1 月 3 日 − 3 = 12 月 31 日
- 1 月 6 日 − 4 = 1 月 2 日
- 1 月 7 日 − 5 = 1 月 2 日
前 3 条记录共享 12 月 31 日，后 2 条记录共享 1 月 2 日。这个共享的锚点值就是我们的分组键。
添加 ROW_NUMBER
第一个具体步骤是添加行号，并按用户进行分区，这样连续段就不会跨越用户边界；同时按日期排序。
PARTITION BY user_id 会为每位用户重新开始计数；ORDER BY login_date 保证序列遵循日历顺序。
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_date
) AS rn
FROM logins;计算分组锚点
现在从 login_date 中减去 rn 天。在 PostgreSQL 中，可以直接从日期中减去整数天数。结果就是用于标识每个连续区间的固定锚点。
请注意，不能在定义别名 rn 的同一个 SELECT 中引用它，因此必须先将前一个查询包装在 CTE 或子查询中。
WITH numbered AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) AS rn
FROM logins
)
SELECT
user_id,
login_date,
login_date - rn AS grp
FROM numbered;对连续区间分组
有了锚点后，每一段连续记录都会共享相同的 grp 值。按 user_id 和 grp 分组，然后进行聚合，得到每段记录的开始日期、结束日期和长度。
MIN(login_date)— 连续段的第一天MAX(login_date)— 连续段的最后一天COUNT(*)— 连续段中的天数
WITH numbered AS (
SELECT user_id, login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) AS rn
FROM logins
)
SELECT
user_id,
MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end,
COUNT(*) AS streak_len
FROM numbered
GROUP BY user_id, login_date - rn
ORDER BY user_id, streak_start;方言差异
日期运算的语法有所不同。在面试中提到这一点，可以展示您知识面的广度。
- PostgreSQL：
login_date - rn（日期减去整数天数） - MySQL：
DATE_SUB(login_date, INTERVAL rn DAY) - SQL Server：
DATEADD(day, -rn, login_date)
逻辑完全相同，只有函数名称发生变化。可移植的思维模型是“将每个日期按其位置向前平移，这样连续段就会归并为同一个常量”。
-- SQL Server version of the anchor
DATEADD(day, -1 * rn, login_date) AS grp为什么不使用自连接
面试官可能会问，为什么不使用类似 l1.login_date = l2.login_date + 1 的自连接。您可以说明以下原因：
- 自连接只能检测相邻性，而不是完整的连续段；要组装出完整的连续记录段，仍然需要分组。
- 如果没有合适的索引，它可能产生组合膨胀，复杂度为 O(n²)。
- 行号方法只需进行一次有序遍历，扩展性要好得多。
对于这类问题，窗口函数是现代且符合预期的答案。
防范重复记录
整个方法都假设每位用户每天只有一条记录。如果源数据中同一天有多次登录，同一日期的两条记录会获得不同的行号，从而破坏锚点。
可以先去重来避免这一问题：将时间戳转换为日期并使用 DISTINCT，或者在日期上使用 DENSE_RANK 代替 ROW_NUMBER，这样相同日期就会共享同一个编号。
WITH days AS (
SELECT DISTINCT user_id, login_ts::date AS login_date
FROM raw_logins
)
SELECT * FROM days;完整解决方案
将所有部分组合起来，就能得到一个清晰且适合面试的答案，列出每一段连续日期记录的开始日期、结束日期和长度。
这个相同的骨架——去重、编号、相减、分组——几乎可以解决您遇到的所有“连续”问题。
WITH days AS (
SELECT DISTINCT user_id, login_ts::date AS login_date
FROM raw_logins
),
numbered AS (
SELECT user_id, login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) AS rn
FROM days
)
SELECT user_id,
MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end,
COUNT(*) AS streak_len
FROM numbered
GROUP BY user_id, login_date - rn
ORDER BY user_id, streak_start;快速检查
请检验您对核心技巧的理解。
回顾
您已经学会了连续日期问题的基础模式：
- 去重，确保每位用户每天只有一条记录。
- 按日期排序、按用户分区的 ROW_NUMBER。
- 从日期中减去行号，为每段连续记录得到一个固定锚点。
- 按锚点执行 GROUP BY 并进行聚合，得到开始日期、结束日期和长度。
这个间断与连续区间骨架只需一次遍历即可扩展，并且优于自连接。接下来，您将使用它计算每位用户的最长连续记录段。
常见问题解答
「检测连续日历日」课时是免费的吗？
是的 — 「检测连续日历日」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。
「检测连续日历日」这节课中我会学到什么？
使用日期运算和行号查找不间断的日期序列 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 SQL Interview Prep 需要有经验吗？
无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 1 节课，共 4 节。
「检测连续日历日」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 SQL Interview Prep 课中编写并运行代码吗？
能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。