SQL Interview Prep · Урок

Динамические сводные таблицы с неизвестными столбцами

Формирование столбцов сводной таблицы, когда категории заранее неизвестны

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

Сложный вопрос о сводных таблицах

Все статические сводные таблицы — будь то агрегация с помощью CASE, PIVOT в SQL Server или crosstab в PostgreSQL — имеют одно ограничение: при написании запроса необходимо перечислить выходные столбцы.

Но что если категории неизвестны — например, названия товаров меняются каждую неделю или нужен отдельный столбец для каждого активного месяца? Это динамическая сводная таблица, и это вопрос для собеседования на уровень опытного специалиста, потому что обычный SQL не может вернуть результат, список столбцов которого определяется во время выполнения.

Почему одного SQL недостаточно

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

Поэтому универсальный способ — сгенерировать текст SQL в два шага: сначала получить уникальные категории, затем собрать из них строку запроса для сводной таблицы и выполнить эту строку.

Шаг 1: сбор категорий

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

Этот результат используется на этапе построения строки. В реальной системе Вы выполняете этот запрос, сохраняете полученные строки и собираете из них следующий запрос.

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

Шаг 2: построение списка столбцов

Затем преобразуйте эти значения в разделённый запятыми список выражений CASE (или имён в квадратных скобках для PIVOT). Базы данных предоставляют функции агрегации строк, позволяющие сделать это непосредственно в SQL.

В PostgreSQL это string_agg; в MySQL — GROUP_CONCAT; в SQL Server — STRING_AGG или более старый приём с FOR XML PATH.

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

Шаг 3: сборка и выполнение

Объедините сгенерированный фрагмент в полную строку запроса, затем выполните её динамически: EXECUTE в PL/pgSQL, sp_executesql в SQL Server или PREPARE/EXECUTE в MySQL.

В этом и состоит суть динамической сводной таблицы: SQL создаёт SQL, а затем выполняет его.

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

Полный пример для PostgreSQL

В PostgreSQL оберните три шага в блок DO или функцию. Сформируйте список столбцов с помощью string_agg, вставьте его в запрос и выполните запрос с помощью EXECUTE.

Поскольку столбцы результата неизвестны до выполнения, функция, возвращающая такой результат, часто использует RETURNS SETOF record или возвращает строки в формате json, которые затем разворачивает вызывающая сторона.

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

MySQL с подготовленными инструкциями

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

У GROUP_CONCAT есть ограничение длины (group_concat_max_len), о котором могут спросить на собеседовании; увеличьте его, если категорий много.

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

Риск внедрения SQL-кода

Поскольку Вы объединяете значения данных с исполняемым SQL, динамические сводные таблицы несут риск SQL-инъекции. Если значение категории содержит кавычку или вредоносный текст, оно может нарушить работу сгенерированного запроса или перехватить управление им.

Всегда экранируйте идентификаторы и литералы с помощью безопасных средств конкретной СУБД: format('%I', ...) и %L в PostgreSQL, QUOTENAME в SQL Server. Никогда не вставляйте необработанные значения непосредственно в строку.

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

Возврат неизвестного набора столбцов

Есть и вторая сложность: вызывающая сторона не может заранее знать структуру результата. Обычно на собеседовании принимают следующие решения:

Вернуть строки как JSON, а разворачивание ключей передать уровню приложения.
Попросить процедуру вывести или сформировать запрос, а затем выполнить его вторым шагом.
Выполнить окончательное построение сводной таблицы в коде приложения (pandas, инструменте BI), когда категории уже известны.

Удобного способа вернуть произвольные столбцы одним статическим вызовом не существует.

Практический пример: сводная таблица по товарам

Предположим, товары постоянно появляются и исчезают, а в отчёте нужен отдельный столбец выручки для каждого товара, который сейчас есть в sales. Нельзя жёстко задать список, поэтому его нужно сгенерировать. PostgreSQL делает это наглядно: сформируйте фрагмент с CASE с помощью string_agg и безопасного экранирования, вставьте его в запрос, а затем выполните с помощью EXECUTE.

Объясните ход решения интервьюеру: найдите товары, оформите для каждого безопасно заключённое в кавычки имя столбца, соберите запрос и выполните его. Та же схема подходит для любой СУБД; меняются только вспомогательные средства.

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

Когда следует избегать динамических сводных таблиц

Опытные кандидаты знают, когда не стоит делать это в SQL. Динамический SQL сложнее читать, проверять, защищать и кэшировать. Часто лучше:

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

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

Быстрая проверка

Проверьте, понимаете ли Вы основную причину существования динамических сводных таблиц.

Итоги

Динамические сводные таблицы обрабатывают неизвестные наборы столбцов:

Статические сводные таблицы не подходят, поскольку столбцы результата должны быть заданы до выполнения.
Шаблон: получить уникальные категории, собрать строку SQL для сводной таблицы и динамически выполнить её.
Используйте string_agg/GROUP_CONCAT/STRING_AGG для построения списка столбцов.
Экранируйте значения (%I/%L, QUOTENAME), чтобы избежать SQL-инъекции.
Часто понятнее вернуть данные в длинном формате и построить сводную таблицу на уровне приложения.

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

Урок «Динамические сводные таблицы с неизвестными столбцами» бесплатный?

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

Чему я научусь в уроке «Динамические сводные таблицы с неизвестными столбцами»?

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

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

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

Сколько времени занимает урок «Динамические сводные таблицы с неизвестными столбцами»?

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

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

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

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

Сводные столбцы с условной агрегацией
Синтаксис PIVOT и перекрёстных таблиц в разных СУБД
Преобразование столбцов в строки
Динамические сводные таблицы с неизвестными столбцами

← Назад к SQL Interview Prep