SQL Interview Prep · 课时

处理日期和状态变化形成的孤岛

Q: 「处理日期和状态变化形成的孤岛」这节课中我会学到什么？

将连续的相同状态时段分组，这是订阅状态问题中的常见模式 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

将连续的相同状态时段分组，这是订阅状态问题中的常见模式

处理日期和状态变化形成的孤岛 是 CoddyKit 上的免费 SQL Interview Prep 课时。这是第 4 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。

변하는 값으로 정의되는 구간

비즈니스에서 가장 유용한 공백과 구간 문제는 같은 상태를 공유하는 연속된 행을 묶어 시끄러운 이벤트 기록을 깔끔한 상태 기간으로 정리하는 것입니다. 대표적인 질문은 다음과 같습니다. '구독 이벤트 기록이 주어졌을 때, 사용자가 각 상태에 머문 연속 기간마다 한 행씩 반환하십시오.'

여기서 인접함은 '값이 1만큼 다르다'는 뜻이 아닙니다. 이전 행과 상태가 변하지 않았다는 뜻입니다. 상태가 바뀌는 순간 새로운 구간이 시작됩니다. 이런 경우에는 순수한 행 번호 기법보다 LAG 기반 기법이 뛰어납니다.

구독 예시

날짜순으로 정렬된 한 사용자의 sub_events 테이블을 생각해 보십시오.

2026-01-01 활성
2026-02-01 활성
2026-03-01 일시 중지
2026-04-01 활성
2026-05-01 활성

원하는 결과는 세 개의 상태 기간입니다. 1~2월은 활성, 3월은 일시 중지, 4~5월은 활성입니다. 두 활성 기간 사이에 일시 중지 기간이 끼어 있으므로 서로 별개의 구간이라는 점에 주목하십시오. 상태가 같더라도 연속되지 않으면 서로 다른 구간입니다.

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

상태가 바뀌는 곳 표시하기

LAG를 사용하여 각 행의 상태를 이전 행의 상태와 비교하십시오. 두 상태가 다르거나 첫 번째 행처럼 이전 값이 NULL이면 새로운 구간이 시작됩니다. 상태가 바뀌면 1, 그렇지 않으면 0을 출력합니다.

사용자별로 날짜순을 엄격하게 적용하십시오. 이 데이터의 변경 표시값은 1,0,1,1,0이며, 세 기간의 경계를 나타냅니다.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

누적 합을 기간 키로 변환하기

앞에서와 같이 변경 표시값의 누적 합을 구하면 각 상태 기간에서 일정한 그룹 키를 얻을 수 있습니다. 이 행에서는 1,1,2,3,3이 됩니다. 서로 다른 각 키가 하나의 연속된 기간입니다.

상태는 1씩 증가하는 숫자가 아니므로 행 번호 차이 기법은 여기서 작동하지 않습니다. 인접함이 '변하지 않은 값'을 의미할 때는 LAG와 누적 합을 조합하는 방법이 올바른 도구입니다.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

상태 기간으로 묶기

이제 user_id, status, 누적 합 키로 GROUP BY하여 각 기간의 범위를 보고하십시오. 상태는 기간 안에서 일정하므로 GROUP BY에 포함해도 안전하며, 집계 함수 없이 상태를 선택할 수도 있습니다.

결과는 정확히 세 행입니다. 활성 01-01~02-01, 일시 중지 03-01~03-01, 활성 04-01~05-01입니다.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

从事件到半开区间

一个细微但常见的面试考点是：事件日期标记某个状态开始的时间，而该时段真正结束于下一个状态开始之时，而不是同一状态最后一条事件记录的日期。正确的时段结束点通常是下一个时段的起点，可建模为半开区间 [起点, 下一起点)。

在合并后的时段上使用 LEAD 计算下一个时段的起点，并让最后一个时段保持无结束点（NULL 或 '当前'）。

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

处理连续重复的状态

如果日志中有 active、active、active 这样的冗余记录，且它们之间没有发生变化，该如何处理？重复记录的变化标记为 0，因此累计和会自动将它们保留在同一个连续区间中。这正是我们想要的结果：连续相同的状态会合并为一个时段。

这种对重复记录的自然去重是变化标记方法的一项重要优势，值得向面试官特别说明。

时间出现间隔时应拆分时段的情况

有时仅仅状态相同还不够；即使状态相同，较大的时间间隔也应拆分时段。例如，一月份处于 active 状态，沉寂六个月后再次处于 active 状态，可能应被视为两个时段。

可以为变化标记增加第二个条件：当状态发生变化或距离上一条事件的时间超过阈值时，开始一个新的连续区间。这样就能清晰地组合两条相邻规则。

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

统计不同状态切换的次数

一个自然的追问是：“该用户切换状态多少次？”这其实就是变化标记的数量减去第一个变化标记（第一个标记表示初始状态，而不是一次切换）。

等价地说，就是时段数量减 1。累计和键已经编码了这一信息，因此可以直接利用为时段构建的同一套方法得到答案。

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

为什么这里不使用自连接

使用自连接解决状态时段问题时，需要将每条记录与相邻记录配对、检测变化，再拼接各个边界；这是一个容易出错的多步骤过程，而且在处理三个或更多时段时会变得很棘手。

LAG-变化标记-累计和-GROUP BY 流程无需任何连接，在一次遍历中即可处理任意数量的时段。清楚地阐明线性单次遍历与二次复杂度自连接之间的差异，正是高级面试官看重的推理能力。

可复用的模板

请记住这个四部分模板；只需修改 CASE 中的相邻性判断，就能解决整个状态连续区间问题系列：

标记：使用 CASE 和 LAG 检测新的连续区间。
键：对变化标记进行分区并排序后计算累计 SUM。
合并：按分区列、状态和键执行 GROUP BY。
区间（可选）：使用 LEAD 计算半开时段的结束点。

同一个骨架可以处理连续整数、日期和状态，只有 CASE 条件会改变。

快速检查

请确认您理解状态连续区间的分组规则。

回顾：状态与日期连续区间

现在您已经可以解决最完整的间断与连续区间变体：

相邻关系 = 与上一行相比状态未改变；使用 LAG 标记变化。
对变化标记计算累计和，得到每个时段的分组键。
使用 GROUP BY user_id, status, key 合并记录，得到时段范围。
使用 LEAD 计算半开区间的结束点；扩展标记逻辑，使较大的时间间隔也能触发拆分。
连续的相同记录会自动合并；切换次数也可以由同一组标记直接得到。
一个可复用的模板可以覆盖整数、日期和状态问题，只有 CASE 会改变。

至此，间断与连续区间课程就完成了。这是 SQL 面试中可靠的高级水平能力信号。

常见问题解答

「处理日期和状态变化形成的孤岛」课时是免费的吗？

是的 — 「处理日期和状态变化形成的孤岛」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。

「处理日期和状态变化形成的孤岛」这节课中我会学到什么？

将连续的相同状态时段分组，这是订阅状态问题中的常见模式你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 4 节课，共 4 节。

「处理日期和状态变化形成的孤岛」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Interview Prep 课中编写并运行代码吗？

能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Interview Prep