行号差值技巧
从序列中减去 ROW_NUMBER，将连续值分组成孤岛
行号差值技巧 是 CoddyKit 上的免费 SQL Interview Prep 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。
最优雅的岛屿键
行号差值技巧是面试官最希望看到的、用于连续整数或日期岛屿的技术。它只需一次减法就能生成分组键，不需要 LAG 或累计和。
整体思路是：从值本身减去一个 ROW_NUMBER。对于任何连续值段，值和行号每一步都恰好增加 1，因此它们的差值在整个连续段内保持不变。这个常量就是岛屿键。
为何差值保持不变
想象连续段中的两行相邻行。从一行到下一行，值增加 1，行号也增加 1。将两者相减，两个 +1 就会抵消，因此 value - row_number 不会改变。
但一旦出现间隙，值的增幅就会超过 1，而行号仍只增加 1。差值会变为一个新的常量。这个变化正好将一个岛屿与下一个岛屿分开。
在我们的数据中观察
回顾登录日期编号 1、2、3、7、8、10。让我们并排列出行号和差值：
- 第 1 天，rn 1，差值 0
- 第 2 天，rn 2，差值 0
- 第 3 天，rn 3，差值 0
- 第 7 天，rn 4，差值 3
- 第 8 天，rn 5，差值 3
- 第 10 天，rn 6，差值 4
差值（0、0、0、3、3、4）恰好将这些行划分为三个岛屿。差值相同就属于同一岛屿。
SELECT
day_no,
ROW_NUMBER() OVER (ORDER BY day_no) AS rn,
day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM logins
ORDER BY day_no;合并为岛屿
以差值作为分组键后，最终查询就是标准的汇总。将差值放入一个 CTE，并对其执行 GROUP BY：
这会返回与之前相同的三个岛屿，但 SQL 比 LAG 加累计和的版本更短、更清晰。对于整数或步长均匀的序列，这是应当首先采用的答案。
WITH keyed AS (
SELECT
day_no,
day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM logins
)
SELECT
MIN(day_no) AS start_day,
MAX(day_no) AS end_day,
COUNT(*) AS length
FROM keyed
GROUP BY grp
ORDER BY start_day;주의할 점: 값은 1씩 증가해야 합니다
단순 차이 기법은 수열이 각 단계마다 정확히 1씩 증가한다고 가정합니다. 이는 빈틈없는 정수와 연속된 달력 날짜에는 맞지만, 값이 다른 일정한 간격으로 증가하거나 중복 값이 있으면 성립하지 않습니다.
- 2,4,6,8과 같은 짝수 값은 값에서 행 번호를 뺀 결과만으로는 공백이 있는 것처럼 보입니다.
- 중복 값이 있으면 값은 그대로인데 행 번호만 계속 증가하므로 대응 관계가 어긋납니다.
이러한 한계와 해결 방법을 아는 것이 단순히 요령을 외운 것과 실제로 이해하는 것을 가르는 기준입니다.
고정 간격 수열 해결하기
값이 1이 아니라 알려진 상수 k만큼 증가한다면 먼저 정규화해야 합니다. 값을 k로 나누거나 정수에는 value / k를 사용하여 각 단계가 다시 1씩 증가하도록 만든 다음 행 번호를 빼십시오.
예를 들어 2씩 증가하는 짝수에는 day_no / 2 - ROW_NUMBER()를 사용합니다. 이렇게 정규화한 값은 연속된 각 항목마다 1씩 증가하므로 일정한 차이의 성질이 다시 성립합니다.
SELECT
val,
(val / 2) - ROW_NUMBER() OVER (ORDER BY val) AS grp
FROM even_series
ORDER BY val;날짜에 적용하기
날짜는 실제 상황에서 가장 흔히 등장하는 형태입니다. 달력 날짜는 행 번호에서 직접 뺄 수 없으므로 먼저 날짜를 일수로 변환하십시오. Postgres에서는 기준 날짜를 하나 정해 그 날짜를 빼서 정수 형태의 일수를 얻은 다음 같은 기법을 적용합니다.
연속된 달력 날짜의 차이는 1이므로, 일수와 행 번호의 차이는 다시 하나의 구간 안에서 일정해집니다.
WITH keyed AS (
SELECT
login_date,
(login_date - DATE '2000-01-01')
- ROW_NUMBER() OVER (ORDER BY login_date) AS grp
FROM daily_logins
)
SELECT MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS days_in_run
FROM keyed GROUP BY grp ORDER BY start_date;데이터베이스별 날짜 차이 계산
날짜를 정수로 변환하는 단계는 데이터베이스 엔진마다 다르므로, 여러 데이터베이스의 문법을 알고 있으면 면접에서 좋은 평가를 받을 수 있습니다.
- Postgres: 날짜 리터럴을 뺍니다.
login_date - DATE '2000-01-01'은 정수를 반환합니다. - MySQL:
DATEDIFF(login_date, '2000-01-01')을 사용합니다. - SQL Server:
DATEDIFF(day, '2000-01-01', login_date)을 사용합니다.
일부 엔진에서는 더 간단한 방법으로 날짜에서 ROW_NUMBER일을 직접 빼고, 간격 산술을 사용한 뒤 그 결과인 기준 날짜로 GROUP BY할 수도 있습니다.
SELECT
login_date,
login_date - (ROW_NUMBER() OVER (ORDER BY login_date)
* INTERVAL '1 day') AS grp_date
FROM daily_logins;그룹별 PARTITION 추가하기
사용자별 구간을 구하려면 그룹 열을 기준으로 행 번호를 PARTITION하십시오. 중요한 점은 그룹 키에 PARTITION 열도 포함해야 한다는 것입니다. 서로 다른 사용자가 우연히 같은 차이 값을 만들 수 있기 때문입니다.
따라서 user_id와 계산된 차이를 모두 GROUP BY하십시오. 최종 GROUP BY에서 user_id를 빠뜨리는 것은 면접관이 즐겨 찾아내는 미묘한 오류입니다.
WITH keyed AS (
SELECT user_id, day_no,
day_no - ROW_NUMBER()
OVER (PARTITION BY user_id ORDER BY day_no) AS grp
FROM logins
)
SELECT user_id, MIN(day_no) AS start_day,
MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed
GROUP BY user_id, grp
ORDER BY user_id, start_day;요령과 LAG: 무엇을 사용할까요
이제 도구 상자에 두 가지 확실한 기법이 있습니다. 상황에 맞게 선택하십시오.
- 행 번호 차이: 일정한 간격으로 증가하는 값의 연속 구간(빈틈없는 정수, 연속된 날짜)에 가장 짧고 깔끔합니다. 인접함을 '일정한 값만큼 차이 난다'는 의미로 정의할 때 첫 번째로 고려하십시오.
- LAG와 누적 합: 인접함이 고정된 수치 간격이 아닐 때 더 유연합니다. 예를 들어 '이전 행과 같은 상태'나 불규칙한 사용자 지정 규칙을 표현할 때 적합합니다.
면접에서는 무엇을 선택했는지와 그 이유를 설명하십시오. 문법보다 선택의 근거가 더 좋은 인상을 줍니다.
중복 값에 대비하기
값이 반복될 수 있는데도 연속된 각 구간을 하나씩 얻고 싶다면 먼저 DISTINCT나 그룹화 단계로 중복을 제거하여 행 번호와 값이 일대일로 대응하도록 하십시오. 또는 ROW_NUMBER 대신 DENSE_RANK를 사용하여 같은 값을 가진 항목이 같은 순위를 공유하게 할 수도 있습니다.
중복 값이 발생할 수 있는지 항상 면접관에게 확인하십시오. 중복 값이 구간을 연장해야 하는지, 아니면 구간 안에서 무시해야 하는지에 따라 적절한 대응이 달라집니다.
WITH d AS (SELECT DISTINCT day_no FROM logins)
SELECT day_no,
day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM d;빠른 확인
이 요령이 왜 작동하는지 확실히 이해했는지 확인하십시오.
정리: 차이 기법
이제 가장 깔끔한 구간 키를 사용할 수 있습니다.
- 핵심 공식:
value - ROW_NUMBER() OVER (ORDER BY value)는 연속된 각 구간에서 일정합니다. - 차이를 GROUP BY로 묶으면 시작 값, 끝 값, 길이를 얻을 수 있습니다.
- 고정 간격 수열에서는 먼저 정규화(간격으로 나누기)하십시오.
- 날짜에서는 해당 데이터베이스의 차이 함수로 정수 형태의 일수로 변환하십시오.
- 그룹별로 처리할 때는 행 번호를
PARTITION BY하고, 최종GROUP BY에 그룹 열을 포함하십시오. DISTINCT나DENSE_RANK로 중복 값에 대비하십시오.
다음으로는 구간에서 빈 공간으로 초점을 바꾸어 공백을 찾겠습니다.
常见问题解答
「行号差值技巧」课时是免费的吗？
是的 — 「行号差值技巧」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。
「行号差值技巧」这节课中我会学到什么？
从序列中减去 ROW_NUMBER，将连续值分组成孤岛 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 SQL Interview Prep 需要有经验吗？
无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。
「行号差值技巧」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 SQL Interview Prep 课中编写并运行代码吗？
能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。