Учебное приложение пишет: «Ты занимаешься 5 дней подряд!» Бухгалтер спрашивает: «Каких номеров счетов не хватает?» Администратор хочет знать: «В какие периоды сервер в этом месяце не работал?» Эти три вопроса — одна классическая задача SQL, пропуски и острова (gaps and islands), которую любят и на собеседованиях. Нам нужны два инструмента: ROW_NUMBER, LAG и LEAD из урока «Оконные функции» и рекурсивный CTE из урока «CTE: WITH и рекурсивные запросы».
Пропуск и островОпределение
В упорядоченной последовательности остров — максимальный непрерывный участок значений (1, 2, 3), пропуск — недостающие значения между двумя островами (4, 5). «Непрерывность» задаётся шагом: 1 для целых чисел, 1 день для дат, а для событий, например, меньше 30 минут.
Пропуски: чего не хватает
Самый простой способ — сравнить каждую строку со следующей: LEAD(id) даёт следующий номер, и если разность больше 1, между ними пропуск. Удалим отменённые заказы 4, 5 и 9 и проверим:
DELETE FROM orders WHERE id IN (4, 5, 9); -- cancelled orders
SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id FROM orders)
WHERE next_id - id > 1;Ожидаемый результат
gap_start | gap_end 4 | 5 9 | 9
Если каждый пропущенный номер должен быть отдельной строкой, рекурсивным CTE порождаем все числа от 1 до наибольшего id и убираем существующие (NOT EXISTS). «Потерю» в конце этот способ не видит: база не знает, что должно было идти после 12.
DELETE FROM orders WHERE id IN (4, 5, 9);
WITH RECURSIVE ids(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM ids WHERE n < (SELECT MAX(id) FROM orders)
)
SELECT n AS missing_id
FROM ids
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE id = n);Ожидаемый результат
missing_id 4 5 9
С датами та же идея: CTE-календарь даёт каждый день периода, LEFT JOIN присоединяет входы, а дни, оставшиеся с NULL, — пропущенные. В этом уроке используем таблицу logins (student_id, login_date): 1 — Айсель, 2 — Мурад, 3 — Лейла. Дни, которые Айсель пропустила с 1 по 10 октября:
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
(1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09'),
(2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
(2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08'),
(3, '2025-10-06'), (3, '2025-10-07'), (3, '2025-10-08'), (3, '2025-10-09');
WITH RECURSIVE calendar(day) AS (
SELECT '2025-10-01'
UNION ALL
SELECT date(day, '+1 day') FROM calendar WHERE day < '2025-10-10'
)
SELECT c.day AS missed_day
FROM calendar AS c
LEFT JOIN logins AS l ON l.login_date = c.day AND l.student_id = 1
WHERE l.student_id IS NULL;Ожидаемый результат
missed_day 2025-10-04 2025-10-07 2025-10-08 2025-10-10
ON, а не в WHERE — иначе LEFT JOIN превратился бы в обычный JOIN.Чтобы читать дальше, зарегистрируйся бесплатно
Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.
Острова: приём разности ROW_NUMBER
Пронумеруем значения по возрастанию. Внутри острова и значение, и ROW_NUMBER на каждом шаге растут на 1, поэтому их разность остаётся постоянной. После пропуска значение прыгает дальше номера, и разность растёт. Значит, разность — это «имя» острова:
Где
- value
- значение последовательности (id, номер дня)
- ROW_NUMBER()
- номер строки по порядку: 1, 2, 3, …
- grp
- ключ острова: одинаков внутри одного острова
Затем GROUP BY grp сворачивает каждый остров в строку: MIN — начало, MAX — конец, COUNT — длина.
| value | ROW_NUMBER | value − ROW_NUMBER | Остров |
|---|---|---|---|
| 1 | 1 | 0 | A |
| 2 | 2 | 0 | A |
| 3 | 3 | 0 | A |
| 6 | 4 | 2 | B |
| 7 | 5 | 2 | B |
| 9 | 6 | 3 | C |
Проверь приём вручную
Задача
Значения: 3, 4, 5, 8, 9, 12. Чему равно value − ROW_NUMBER и сколько получится островов?
Показать решениеСкрыть решение
Решение
2) Разности: 3 − 1 = 2, 4 − 2 = 2, 5 − 3 = 2, 8 − 4 = 4, 9 − 5 = 4, 12 − 6 = 6.
3) Разных значений три — 2, 4, 6, то есть 3 острова: 3–5 (3 значения), 8–9 (2), 12 (1).
С датами вычитаем номер строки в днях: date(login_date, '-' || rn || ' days'). Для дней подряд результат — одна и та же «якорная» дата. WINDOW w AS (ORDER BY login_date) один раз даёт окну имя, и несколько функций используют его как OVER w. Входы Айсель:
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
(1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09');
SELECT login_date,
ROW_NUMBER() OVER w AS rn,
date(login_date, '-' || ROW_NUMBER() OVER w || ' days') AS grp
FROM logins
WINDOW w AS (ORDER BY login_date)
ORDER BY login_date;Ожидаемый результат
login_date | rn | grp 2025-10-01 | 1 | 2025-09-30 2025-10-02 | 2 | 2025-09-30 2025-10-03 | 3 | 2025-09-30 2025-10-05 | 4 | 2025-10-01 2025-10-06 | 5 | 2025-10-01 2025-10-09 | 6 | 2025-10-03
Для всех учеников нумерация должна начинаться заново у каждого (PARTITION BY student_id), а повторные входы сначала убираются через DISTINCT. Затем GROUP BY превращает каждую серию в диапазон дат:
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
(1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09'),
(2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
(2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08'),
(3, '2025-10-06'), (3, '2025-10-07'), (3, '2025-10-08'), (3, '2025-10-09');
WITH days AS (SELECT DISTINCT student_id, login_date FROM logins),
grouped AS (
SELECT student_id, login_date,
date(login_date, '-' || ROW_NUMBER() OVER (
PARTITION BY student_id ORDER BY login_date) || ' days') AS grp
FROM days
)
SELECT student_id, MIN(login_date) AS from_day, MAX(login_date) AS to_day,
COUNT(*) AS days
FROM grouped
GROUP BY student_id, grp
ORDER BY student_id, from_day;Ожидаемый результат
student_id | from_day | to_day | days 1 | 2025-10-01 | 2025-10-03 | 3 1 | 2025-10-05 | 2025-10-06 | 2 1 | 2025-10-09 | 2025-10-09 | 1 2 | 2025-10-02 | 2025-10-05 | 4 2 | 2025-10-08 | 2025-10-08 | 1 3 | 2025-10-06 | 2025-10-09 | 4
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
(2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08');
SELECT login_date,
date(login_date, '-' || ROW_NUMBER() OVER w || ' days') AS by_row_number,
date(login_date, '-' || DENSE_RANK() OVER w || ' days') AS by_dense_rank
FROM logins
WINDOW w AS (ORDER BY login_date)
ORDER BY login_date;Ожидаемый результат
login_date | by_row_number | by_dense_rank 2025-10-02 | 2025-10-01 | 2025-10-01 2025-10-03 | 2025-10-01 | 2025-10-01 2025-10-03 | 2025-09-30 | 2025-10-01 2025-10-04 | 2025-09-30 | 2025-10-01 2025-10-05 | 2025-09-30 | 2025-10-01 2025-10-08 | 2025-10-02 | 2025-10-03
LAG и нарастающая сумма: сессии
Приём с ROW_NUMBER работает, только когда шаг ровно 1. Для просмотров сайта «подряд» измеряется иначе: если между двумя просмотрами прошло не больше 30 минут, они в одной сессии. Здесь нужен второй, более общий способ:
Перерыв
Возьми время предыдущего события через
LAGи посчитай разницу в минутах (unixepochдаёт секунды).Флаг
Если перерыв больше 30 минут или предыдущего события нет (
NULL),new_session = 1, иначе 0.Номер
Нарастающая сумма флагов (
SUM(...) OVER (ORDER BY ...)) — номер сессии.Итог
GROUP BYпо номеру сессии: начало, конец, число просмотров.
CREATE TABLE page_views (student_id INTEGER, viewed_at TEXT);
INSERT INTO page_views VALUES
(1, '2025-10-01 09:00'), (1, '2025-10-01 09:10'), (1, '2025-10-01 09:35'),
(1, '2025-10-01 11:00'), (1, '2025-10-01 11:20'),
(2, '2025-10-01 10:00'), (2, '2025-10-01 10:50');
WITH gaps AS (
SELECT student_id, viewed_at,
(unixepoch(viewed_at) - unixepoch(LAG(viewed_at) OVER w)) / 60 AS gap_min
FROM page_views
WINDOW w AS (PARTITION BY student_id ORDER BY viewed_at)
),
flagged AS (
SELECT *, CASE WHEN gap_min <= 30 THEN 0 ELSE 1 END AS new_session
FROM gaps
)
SELECT student_id, viewed_at, gap_min, new_session,
SUM(new_session) OVER (PARTITION BY student_id
ORDER BY viewed_at) AS session_no
FROM flagged
ORDER BY student_id, viewed_at;Ожидаемый результат
student_id | viewed_at | gap_min | new_session | session_no 1 | 2025-10-01 09:00 | NULL | 1 | 1 1 | 2025-10-01 09:10 | 10 | 0 | 1 1 | 2025-10-01 09:35 | 25 | 0 | 1 1 | 2025-10-01 11:00 | 85 | 1 | 2 1 | 2025-10-01 11:20 | 20 | 0 | 2 2 | 2025-10-01 10:00 | NULL | 1 | 1 2 | 2025-10-01 10:50 | 50 | 1 | 2
NULL <= 30 не истинно, поэтому первый просмотр каждого ученика автоматически открывает новую сессию.Острова одинаковых значений: периоды статуса
Иногда остров — не «подряд идущие значения», а «подряд идущие строки с одним статусом»: сервер проверяют каждый день, up или down. Здесь работает разность двух нумераций: общей ROW_NUMBER и ROW_NUMBER внутри статуса. В непрерывном периоде одного статуса обе растут одинаково, и разность постоянна:
CREATE TABLE checks (day TEXT, status TEXT);
INSERT INTO checks VALUES
('2025-10-01', 'up'), ('2025-10-02', 'up'), ('2025-10-03', 'down'),
('2025-10-04', 'down'), ('2025-10-05', 'up'), ('2025-10-06', 'down'),
('2025-10-07', 'up'), ('2025-10-08', 'up');
WITH g AS (
SELECT day, status,
ROW_NUMBER() OVER (ORDER BY day)
- ROW_NUMBER() OVER (PARTITION BY status ORDER BY day) AS grp
FROM checks
)
SELECT status, MIN(day) AS from_day, MAX(day) AS to_day, COUNT(*) AS days
FROM g
GROUP BY status, grp
ORDER BY from_day;Ожидаемый результат
status | from_day | to_day | days up | 2025-10-01 | 2025-10-02 | 2 down | 2025-10-03 | 2025-10-04 | 2 up | 2025-10-05 | 2025-10-05 | 1 down | 2025-10-06 | 2025-10-06 | 1 up | 2025-10-07 | 2025-10-08 | 2
grp уникален только вместе со статусом, поэтому GROUP BY status, grp.Мини-проект: отчёт о сериях входов
Очисти
DISTINCT— один вход в день.Острова
ROW_NUMBERпо ученику, якорная дата,GROUP BY→ конец и длина каждой серии.Показатели
Число серий, самая длинная серия и текущая серия, заканчивающаяся в день отчёта (2025-10-09); если такой нет — 0.
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
(1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09'),
(2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
(2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08'),
(3, '2025-10-06'), (3, '2025-10-07'), (3, '2025-10-08'), (3, '2025-10-09');
WITH days AS (SELECT DISTINCT student_id, login_date FROM logins),
streaks AS (
SELECT student_id, MAX(login_date) AS end_day, COUNT(*) AS len
FROM (SELECT student_id, login_date,
date(login_date, '-' || ROW_NUMBER() OVER (
PARTITION BY student_id ORDER BY login_date) || ' days') AS grp
FROM days)
GROUP BY student_id, grp
)
SELECT s.first_name,
COUNT(*) AS streaks,
MAX(k.len) AS longest,
COALESCE(MAX(CASE WHEN k.end_day = '2025-10-09'
THEN k.len END), 0) AS current_streak
FROM streaks AS k
JOIN students AS s ON s.id = k.student_id
GROUP BY k.student_id
ORDER BY longest DESC, s.first_name;Ожидаемый результат
first_name | streaks | longest | current_streak Leyla | 1 | 4 | 4 Murad | 2 | 4 | 0 Aysel | 3 | 3 | 1
Читаем отчёт: Лейла входит 4 дня без перерыва — приложение может её поздравить. Рекорд Мурада тоже 4 дня, но последний вход был 8-го, и текущая серия равна 0: пора отправить ему напоминание. У Айсель 3 серии, текущая — 1 день. Вся логика — в одном SQL-запросе, и он работает для любого числа учеников.
Задание
Заказы 3, 4 и 8 удалены. С помощью LEAD найди пропуски в id: gap_start и gap_end для каждого пропуска, по возрастанию gap_start.
DELETE FROM orders WHERE id IN (3, 4, 8);
-- find the gaps hereОжидаемый результат
gap_start | gap_end 3 | 4 8 | 8
Задание
По таблице checks из стартового кода выведи периоды, когда сервер не работал (down): from_day, to_day, days, по возрастанию from_day.
CREATE TABLE checks (day TEXT, status TEXT);
INSERT INTO checks VALUES
('2025-10-01', 'up'), ('2025-10-02', 'up'), ('2025-10-03', 'down'),
('2025-10-04', 'down'), ('2025-10-05', 'up'), ('2025-10-06', 'down'),
('2025-10-07', 'up'), ('2025-10-08', 'up');
-- periods with status = 'down'Ожидаемый результат
from_day | to_day | days 2025-10-03 | 2025-10-04 | 2 2025-10-06 | 2025-10-06 | 1
Запомни
- Остров — непрерывный участок; пропуск — недостающее между островами; «непрерывность» задаётся шагом (1, день, 30 мин).
- Пропуски:
next_id - id > 1черезLEAD(id); по строке на каждое значение — рекурсивный CTE или календарь +LEFT JOIN. - Острова:
значение − ROW_NUMBER()постоянно внутри острова; для дат —date(день, '-' || rn || ' days'). - Повторы сначала убирают через
DISTINCT(или берутDENSE_RANK), а нумерацию для каждого объекта начинают заново черезPARTITION BY. - Для любого порога:
LAG→ флаг начала → нарастающаяSUM= номер острова (сессии). - Периоды одного статуса:
ROW_NUMBER() OVER (ORDER BY t) − ROW_NUMBER() OVER (PARTITION BY status ORDER BY t).