Последовательности: пропуски и острова

Поиск пропущенных номеров и дат в последовательности, группировка подряд идущих строк через разность ROW_NUMBER, построение сессий через LAG и нарастающую сумму, выделение периодов с одинаковым статусом и отчёт о сериях входов.

  • Продвинутый
  • 22 мин
  • 12 вопросов
  • Урок 22 из 28

В этом уроке ты узнаешь

  • Находить пропущенные id и даты через LEAD и рекурсивный CTE
  • Группировать подряд идущие значения в острова приёмом «значение − ROW_NUMBER»
  • Делить события на сессии с помощью LAG и нарастающей SUM
  • Строить настоящий отчёт по сериям, периодам и диапазонам дат

Учебное приложение пишет: «Ты занимаешься 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 и проверим:

SQL
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.

SQL
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 октября:

SQL
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, поэтому их разность остаётся постоянной. После пропуска значение прыгает дальше номера, и разность растёт. Значит, разность — это «имя» острова:

grp = value − ROW_NUMBER() OVER (ORDER BY value)

Где

value
значение последовательности (id, номер дня)
ROW_NUMBER()
номер строки по порядку: 1, 2, 3, …
grp
ключ острова: одинаков внутри одного острова

Затем GROUP BY grp сворачивает каждый остров в строку: MIN — начало, MAX — конец, COUNT — длина.

valueROW_NUMBERvalue − ROW_NUMBERОстров
110A
220A
330A
642B
752B
963C
Острова 1–3, 6–7 и 9; пропуски 4–5 и 8.

Проверь приём вручную

Задача

Значения: 3, 4, 5, 8, 9, 12. Чему равно value − ROW_NUMBER и сколько получится островов?

Показать решениеСкрыть решение

Решение

1) Номера: 1, 2, 3, 4, 5, 6.
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. Входы Айсель:

SQL
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 превращает каждую серию в диапазон дат:

SQL
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
SQL
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 минут, они в одной сессии. Здесь нужен второй, более общий способ:

  1. Перерыв

    Возьми время предыдущего события через LAG и посчитай разницу в минутах (unixepoch даёт секунды).

  2. Флаг

    Если перерыв больше 30 минут или предыдущего события нет (NULL), new_session = 1, иначе 0.

  3. Номер

    Нарастающая сумма флагов (SUM(...) OVER (ORDER BY ...)) — номер сессии.

  4. Итог

    GROUP BY по номеру сессии: начало, конец, число просмотров.

SQL
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 внутри статуса. В непрерывном периоде одного статуса обе растут одинаково, и разность постоянна:

SQL
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.

Мини-проект: отчёт о сериях входов

  1. Очисти

    DISTINCT — один вход в день.

  2. Острова

    ROW_NUMBER по ученику, якорная дата, GROUP BY → конец и длина каждой серии.

  3. Показатели

    Число серий, самая длинная серия и текущая серия, заканчивающаяся в день отчёта (2025-10-09); если такой нет — 0.

SQL
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.

Задание · SQL
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.

Задание · SQL
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).
Заметил ошибку или неточность в уроке?

Проверь себя

Вопросов: 12. Каждый правильный ответ приносит XP.

Вопрос 1 / 12

Вопрос 1 / 12. Что такое «остров»?