Построение сводных таблиц в SQL

Сводная таблица – один из самых популярных методов анализа табличных данных. Иногда мы анализируем большие таблицы — более 500 тыс. строк, но Excel обрабатывает такое количество данных достаточно долго, система постоянно зависает. Сегодня мы рассмотрим наиболее известные варианты построения сводной таблицы, доступные в SQL Server.

Предположим, у нас есть таблица с данными продаж нескольких видов продуктов (Product 1, 2, 3, 4) у разных операторов (A, B, C, D):

Из вышеуказанной таблицы мы хотим получить сводную таблицу вида:

Вариант 1: Использование оператора CASE

Для замены значения Null на обычный «0» достаточно добавить в конструкцию CASE WHEN оператор ELSE:

Не хватает итогов под таблицей. Для этого мы будем использовать оператор GROUP BY rollup:

А для того, чтобы под колонкой «product» вместо значения «NULL» вывести всем понятное «Total_sum» нам понадобится оператор coalesce.

Вариант 2: Использование оператора GROUP BY CUBE

Для быстрой группировки данных по операторам и вывода итоговых значений по каждому продукту без перечисления в коде всех операторов удобно использовать оператор GROUP BY CUBE:

Для отображения вместо NULL в колонке operator названия «Total_sum» воспользуемся оператором coalesce

Вариант 3: Использование оператора разворота таблиц PIVOT

Перед использованием этого оператора нам необходимо получить агрегированную таблицу. Для этого мы будем использовать ранее подготовленную с использованием оператора GROUP BY CUBE таблицу, используя предыдущий фрагмент кода как подзапрос (выделено цветом).

Здесь мы «поворачиваем» таблицу из прошлого запроса, используя агрегатную функцию суммы sum (Summa). При этом заголовки столбцов мы берём из поля operator, а с помощью in («A», «B», «C», «D», «total_sum») указываем какие конкретно операторы должны быть выведены (total_sum отвечает за столбец с итогами по строкам). При этом заголовки столбцов обязательно берем в двойные кавычки.

Для разворота таблицы в разрезе продуктов по каждому оператору меняем аргумент в операторе PIVOT.

Вариант 4: Динамический SQL

Запрос с PIVOT выглядит короче, чем изначальный с CASE, но названия поставщиков все ещё необходимо вносить вручную. Но что делать, если поставщиков много? Или если их список регулярно обновляется? Хотелось бы выбирать их автоматически. Здесь чистого SQL недостаточно. Он подразумевает статическую типизацию: для создания плана запроса СУБД нужно заранее указать число столбцов. Поэтому синтаксис PIVOT не позволяет использовать подзапрос. Но это ограничение легко обойти с помощью динамического SQL. Для этого названия столбцов необходимо преобразовать в строку формата «элемент_1», «элемент_2»,… , «элемент_n», и использовать их в запросе.

Для генерации строки, мы можем использовать оператор STUFF:

Полученную строку включаем в окончательный запрос:

#selectel_инструкция