0Pricing
Coding Interview Prep · Урок

Сводные столбцы с условной агрегацией

Переносимый шаблон CASE внутри SUM для преобразования строк в столбцы

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

Постановка задачи на собеседовании

Одна из самых распространённых задач на собеседованиях по составлению отчётов: преобразовать строки в столбцы. У Вас есть длинная таблица вроде sales(region, quarter, amount), а интервьюер хочет получить широкий отчёт с одним столбцом на каждый квартал.

Переносимый ответ, не зависящий от диалекта, который от Вас хотят услышать, — это условная агрегация: выражение CASE, помещённое внутрь агрегатной функции, например SUM. Освойте этот приём, и Вы сможете строить сводные отчёты в любой базе данных, даже если в ней нет ключевого слова PIVOT.

Длинная и широкая формы

Перед преобразованием в сводный отчёт назовите формы данных. Длинная форма хранит один факт в каждой строке: каждая пара регион/квартал находится в отдельной строке. Широкая форма распределяет категорию по столбцам.

  • Длинная форма: в неё легко добавлять данные, но трудно читать их рядом.
  • Широкая форма: отлично подходит для отчёта, предназначенного для человека.

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

-- Long form (the input)
region | quarter | amount
-------+---------+-------
East   | Q1      | 100
East   | Q2      | 150
West   | Q1      | 200
West   | Q2      | 250

Основной шаблон

Хитрость заключается в следующем: для каждого выходного столбца напишите CASE, который возвращает значение, если строка соответствует этому столбцу, и NULL в противном случае. Оберните его в агрегатную функцию, чтобы свернуть группу в одну строку на каждый ключ.

Читайте это так: сложить сумму, но только для строк первого квартала. Поскольку SUM игнорирует NULL, несоответствующие строки ничего не добавляют.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

Почему SUM игнорирует NULL

Этот шаблон работает благодаря факту, который интервьюеры обязательно проверят: агрегатные функции пропускают NULL. Если в CASE нет ELSE, при отсутствии подходящей ветви он возвращает NULL, поэтому SUM(CASE WHEN ... THEN amount END) складывает только выбранные строки.

Если вместо этого написать ELSE 0, это также будет работать с SUM (добавление нуля ничего не меняет), но нарушит ожидаемое поведение AVG, MIN и COUNT.

-- Both produce the same SUM result:
SUM(CASE WHEN quarter = 'Q1' THEN amount END)
SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END)

Разбор примера: квартальный отчёт

Ниже приведён полный запрос для примера данных. Каждый регион становится отдельной строкой, а каждый квартал — отдельным столбцом.

Именно GROUP BY region объединяет четыре исходные строки в две выходные. Без него Вы получили бы по одной строке на каждую исходную строку, причём большинство значений было бы равно NULL.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

-- Result:
-- region | q1  | q2
-- East   | 100 | 150
-- West   | 200 | 250

Правильный выбор агрегатной функции

Агрегатная функция, в которую Вы заключаете CASE, должна соответствовать поставленному вопросу:

  • SUM, если каждая ячейка содержит сумму значений.
  • MAX или MIN, если для каждой пары регион/квартал есть ровно одно значение и Вам нужно лишь вывести его.
  • COUNT, если каждая ячейка подсчитывает подходящие строки.

На собеседованиях часто спрашивают вариант с COUNT: сколько заказов приходится на каждый статус в каждом месяце?

SELECT
  month,
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY month;

MAX для ячеек с одним значением

Если каждая пара ключ/категория содержит одно значение (это настоящая перекрёстная таблица, а не итог), используйте MAX или MIN. Обе функции возвращают единственное значение, отличное от NULL, и игнорируют NULL из ветвей, которые не соответствуют условию.

Это безопасный выбор, когда Вы преобразуете атрибуты, а не суммируете деньги, например превращаете таблицу настроек «ключ/значение» в одну строку на сущность.

-- Turn key/value rows into one wide row per user
SELECT
  user_id,
  MAX(CASE WHEN attr = 'city'  THEN value END) AS city,
  MAX(CASE WHEN attr = 'plan'  THEN value END) AS plan
FROM user_attributes
GROUP BY user_id;

Обработка пустых ячеек вывода

Если у региона не было продаж в Q2, его ячейка q2 будет равна NULL. На собеседовании Вас могут попросить вывести вместо этого 0. Оберните весь агрегат в COALESCE.

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

SELECT
  region,
  COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN amount END), 0) AS q1,
  COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN amount END), 0) AS q2
FROM sales
GROUP BY region;

Добавление столбца общего итога

Распространённый дополнительный вопрос: добавить итог по всем сводным столбцам. Перечислять столбцы по именам не нужно. Обычный SUM(amount) в той же группировке даст итог строки, поскольку он полностью игнорирует фильтрацию через CASE.

Так Вы показываете интервьюеру, что понимаете: каждый агрегат в SELECT вычисляется независимо, но для одной и той же группы.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(amount) AS total
FROM sales
GROUP BY region;

Сокращённая запись агрегата с фильтром

PostgreSQL и стандарт SQL поддерживают конструкцию FILTER (WHERE ...) — более удобный способ записывать условную агрегацию. Такой вариант легче читать, и в нём нет шаблонного кода CASE.

Упомяните эту конструкцию на собеседовании, чтобы показать широту знаний, но помните: MySQL и SQL Server её не поддерживают, поэтому CASE остаётся переносимым решением.

-- Postgres / standard SQL
SELECT
  region,
  SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY region;

Главное ограничение

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

Эта задача называется динамической сводной таблицей и требует сгенерированного SQL. Однако для фиксированного, заранее известного набора категорий условная агрегация остаётся лучшим чистым и переносимым решением.

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

Проверьте, насколько хорошо Вы усвоили шаблон условной агрегации.

Итоги

Условная агрегация — это переносимый способ построения сводной таблицы, который принимает любой интервьюер:

  • Один CASE на каждый столбец вывода, заключённый в агрегатную функцию.
  • SUM для итогов, MAX/MIN для ячеек с одним значением, COUNT для подсчёта.
  • Всё работает потому, что агрегатные функции игнорируют NULL из ветвей, не соответствующих условию.
  • Используйте COALESCE, чтобы превращать пустые ячейки в 0.
  • Ограничение: столбцы нужно жёстко задать в запросе, поэтому далее мы рассмотрим динамические сводные таблицы.

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

Урок «Сводные столбцы с условной агрегацией» бесплатный?

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

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

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

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

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

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

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

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

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

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

  1. Сводные столбцы с условной агрегацией
  2. Синтаксис PIVOT и перекрёстных таблиц в разных СУБД
  3. Преобразование столбцов в строки
  4. Динамические сводные таблицы с неизвестными столбцами
← Назад к Coding Interview Prep