SQL Academy · Урок

Генерация рядов и последовательностей

Q: Урок «Генерация рядов и последовательностей» бесплатный?

Да — полный текст урока «Генерация рядов и последовательностей» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Q: Чему я научусь в уроке «Генерация рядов и последовательностей»?

Создавайте строки с помощью рекурсии Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Q: Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Q: Сколько времени занимает урок «Генерация рядов и последовательностей»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Q: Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Создавайте строки с помощью рекурсии

«Генерация рядов и последовательностей» — бесплатный урок SQL Academy на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Что такое сгенерированная последовательность

Иногда нужен список чисел, дат или других последовательных значений, которых нет ни в одной таблице. SQL позволяет создавать такие значения на лету с помощью рекурсии или встроенных функций.

В этом уроке Вы научитесь создавать последовательности с помощью WITH RECURSIVE — мощного инструмента, позволяющего запросу обращаться к собственному результату.

Ваш первый рекурсивный CTE

Рекурсивный CTE (общее табличное выражение) состоит из двух частей, соединённых с помощью UNION ALL: базового случая, который создаёт первую строку, и рекурсивного шага, обращающегося к самому CTE для создания следующей строки.

Рекурсия останавливается, когда рекурсивный шаг не возвращает строк.

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

Как разворачивается рекурсия

Каждый шаг удобно рассматривать отдельно. Базовый случай заполняет таблицу начальными данными, затем каждый рекурсивный проход добавляет ещё одну строку, пока условие WHERE не перестанет выполняться.

Для запроса WHERE n < 5 механизм создаёт строки 1, 2, 3, 4, 5, а затем останавливается, потому что 5 < 5 ложно.

WITH RECURSIVE steps(n, note) AS (
  SELECT 1, 'base case'
  UNION ALL
  SELECT n + 1, 'recursive step'
  FROM steps
  WHERE n < 4
)
SELECT n, note FROM steps;

Генерация чётных чисел

Изменить размер шага можно просто, добавляя в рекурсивной части число больше 1. Так создаются все чётные числа от 2 до 10.

Правило всегда одно: следующее_значение = текущее_значение + шаг.

WITH RECURSIVE evens(n) AS (
  SELECT 2
  UNION ALL
  SELECT n + 2 FROM evens WHERE n < 10
)
SELECT n FROM evens;

Обратный отсчёт

Рекурсия не ограничивается увеличением чисел. Вычитайте вместо сложения — и получите убывающую последовательность. Убедитесь, что в условии остановки используется >, а не <, чтобы избежать бесконечного цикла.

WITH RECURSIVE countdown(n) AS (
  SELECT 10
  UNION ALL
  SELECT n - 1 FROM countdown WHERE n > 1
)
SELECT n FROM countdown;

Генерация диапазона дат

Одно из самых практичных применений рекурсивных CTE — создание последовательности дат. Начните с определённой даты и добавляйте по одному дню, пока не достигнете конечной даты.

Это особенно полезно для заполнения пропусков в отчётах: отображается каждая дата, даже если за этот день нет данных.

WITH RECURSIVE dates(d) AS (
  SELECT DATE '2024-01-01'
  UNION ALL
  SELECT d + INTERVAL '1 day'
  FROM dates
  WHERE d < DATE '2024-01-07'
)
SELECT d FROM dates;

Заполнение пропусков в отчёте последовательностью дат

Представьте таблицу sales, в которой есть строки только за дни, когда были продажи. Соединив сгенерированную последовательность дат с таблицей продаж с помощью LEFT JOIN, Вы получите каждый день диапазона, а для дней без продаж — NULL.

WITH RECURSIVE cal(d) AS (
  SELECT DATE '2024-03-01'
  UNION ALL
  SELECT d + INTERVAL '1 day' FROM cal WHERE d < DATE '2024-03-05'
),
sales(sale_date, amount) AS (
  VALUES
    (DATE '2024-03-01', 100),
    (DATE '2024-03-03', 250),
    (DATE '2024-03-05', 180)
)
SELECT cal.d, COALESCE(sales.amount, 0) AS amount
FROM cal
LEFT JOIN sales ON cal.d = sales.sale_date
ORDER BY cal.d;

Генерация таблицы умножения

Рекурсивные CTE могут содержать несколько столбцов, что позволяет формировать более содержательные результаты. Здесь одновременно отслеживаются индекс строки и вычисляемое значение.

WITH RECURSIVE mult(n, result) AS (
  SELECT 1, 1 * 7
  UNION ALL
  SELECT n + 1, (n + 1) * 7
  FROM mult
  WHERE n < 10
)
SELECT n, result AS seven_times_n FROM mult;

Числа Фибоначчи

Числа Фибоначчи образуют классическую последовательность, в которой каждое число равно сумме двух предыдущих: 0, 1, 1, 2, 3, 5, 8 …

Рекурсивный CTE отслеживает текущее значение a и следующее значение b, меняя их местами на каждом шаге.

WITH RECURSIVE fib(a, b) AS (
  SELECT 0, 1
  UNION ALL
  SELECT b, a + b FROM fib WHERE a < 100
)
SELECT a AS fibonacci FROM fib;

Использование generate_series в PostgreSQL

PostgreSQL предоставляет встроенную функцию generate_series(), которая создаёт последовательности без необходимости писать рекурсивный CTE. Она принимает начальное значение, конечное значение и необязательный шаг.

Это самый лаконичный способ создавать диапазоны чисел или дат в PostgreSQL.

SELECT gs AS num
FROM generate_series(1, 10, 2) AS gs;

Генерация месячных интервалов

Передайте интервал '1 month' в generate_series(), чтобы создать календарь по месяцам. Это идеально подходит для формирования заголовков ежемесячных отчётов или проверки месяцев, за которые нет данных.

SELECT gs::DATE AS month_start
FROM generate_series(
  '2024-01-01'::DATE,
  '2024-06-01'::DATE,
  INTERVAL '1 month'
) AS gs;

Проверка знаний

Проверьте, насколько хорошо Вы понимаете создание последовательностей с помощью рекурсивных CTE.

Итоги: создание последовательностей

В этом уроке Вы научились создавать последовательности чисел и дат без исходной таблицы:

WITH RECURSIVE — базовый случай и рекурсивный шаг, соединённые с помощью UNION ALL; выполнение прекращается, когда рекурсивный шаг не возвращает строк.
Гибкий размер шага — прибавляйте или вычитайте любое значение, чтобы считать вверх, считать вниз или пропускать значения.
Диапазоны дат — добавляйте интервал, чтобы создавать ежедневные, ежемесячные или другие календарные последовательности.
Многостолбцовые CTE — переносите дополнительные состояния между итерациями для формирования более сложных результатов, например чисел Фибоначчи.
generate_series() — встроенная функция PostgreSQL для наиболее лаконичного создания последовательностей чисел и дат.

Сгенерированные последовательности необходимы для заполнения пропусков в отчётах, создания тестовых данных и любых ситуаций, когда нужен полный диапазон значений независимо от содержимого данных.

Часто задаваемые вопросы

Урок «Генерация рядов и последовательностей» бесплатный?

Да — полный текст урока «Генерация рядов и последовательностей» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Генерация рядов и последовательностей»?

Создавайте строки с помощью рекурсии Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «Генерация рядов и последовательностей»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

← Назад к SQL Academy