Построение сводных таблиц в 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», и использовать их в запросе.
PIVOT SQL Server
Оператор PIVOT SQL Server (Transact-SQL) позволяет писать кросс-табуляцию. Это означает, что вы можете агрегировать свои результаты и поворачивать строки в столбцы.
Синтаксис
Синтаксис предложения PIVOT в SQL Server (Transact-SQL):
IN ([pivot_value1], [pivot_value2], . [pivot_value_n])
) AS
Параметры или аргументы
first_column — столбец или выражение, которое будет отображаться в качестве первого столбца в сводной таблице.
first_column_alias — заголовок столбца для первого столбца в сводной таблице.
pivot_value1 , pivot_value2 , . pivot_value_n — список значений для поворота.
source_table — оператор SELECT, который предоставляет исходные данные для сводной таблицы.
source_table_alias — псевдоним для source_table .
aggregate_function — агрегирующая функция, такая как SUM, COUNT, MIN, MAX или AVG.
aggregate_column — столбец или выражение, которое будет использоваться с aggregate_function .
pivot_column — столбец, содержащий значения поворота.
pivot_table_alias — псевдоним для сводной таблицы.
Применение
PIVOT может использоваться в следующих версиях SQL Server (Transact-SQL):
Сводные таблицы в SQLite
Допустим, у нас есть таблица sales с продажами продуктов за 2020-2023 годы:
И мы хотим трансформировать ее в так называемую сводную таблицу, в которой продукты расположены по строкам, а годы — по столбцам:
Некоторые СУБД (например, SQL Server), предоставляют специальный оператор pivot для сводных таблиц. В SQLite ничего такого нет. Несмотря на это, существует несколько способов решить задачу. Давайте их разберем.
1. Фильтр по итогам
Извлечем каждый год в отдельный столбец и посчитаем фильтрованный суммарный доход для этого года по каждому продукту:
Вот наша сводная таблица:
Это универсальный метод, который работает с любой СУБД. Даже если движок БД не поддерживает filter — всегда можно переделать на case :
filter легко использовать, если столбцов немного и они известны заранее. Но что если нет?
2. Динамический SQL
Не будем «зашивать» конкретные годы в запрос. Вместо этого построим его динамически:
- Выберем все существующие годы.
- Для каждого года сгенерируем выражение . filter (where year = X) as «X» .
Этот запрос вернет такое же SQL-выражение, что мы вручную писали на предыдущем шаге (за исключением форматирования):
Осталось только выполнить его. Используем для этого функцию eval(sql) , которая входит в состав расширения define :
Примечание: расширения не работают в песочнице, так что используйте локальный SQLite, если хотите повторить этот шаг.
Здесь мы создаем представление v_sales , которое выполняет предварительно сгенерированный запрос. Теперь выберем из него данные:
3. Расширение для сводных таблиц
Если считаете, что динамический SQL это уже перебор — есть и более простое решение. Это расширение pivotvtab .
С ним достаточно перечислить три отдельных селекта:
- Для выборки строк.
- Для выборки столбцов.
- Для выборки ячеек.
Дальше расширение само все сделает:
Это даже проще, чем pivot в SQL Server!
Итого
Есть три способа построить сводную таблицу в SQLite:
- Обычный SQL с sum() и filter .
- Динамический запрос с вычислением через eval() .
- Специальное расширение pivotvtab .
Подписывайтесь на канал, чтобы не пропустить новые заметки ��
Сводные таблицы в SQL
Сводная таблица – один из самых базовых видов аналитики. Многие считают, что создать её средствами SQL невозможно. Конечно же, это не так.
Предположим, у нас есть таблица с данными закупок нескольких видов товаров (Product 1, 2, 3, 4) у разных поставщиков (A, B, C):
Типичная задача – определить размер закупок по поставщикам и товарам, т.е. построить сводную таблицу. Пользователи MS Excel привыкли получать такую аналитику буквально парой кликов:
В SQL это не так быстро, но большинство решений тривиальны.
1. Оператор CASE и аналоги
Самый простой и очевидный способ получения сводной таблицы – это хардкод с использованием оператора CASE . Например, для поставщика А можно вычислить размер поставок как sum(case when t.supplier = ‘A’ then t.volume end ). Чтобы получить объем поставок для разных товаров достаточно просто добавить группировку по полю product :
Если добавить else 0 , то для товаров, по которым не было поставок, вместо null будут выведены нули:
Если продублировать код для всех поставщиков (которых у нас три — A, B, C), мы получим необходимую нам сводную таблицу:
В неё можно добавить итог по строкам (как обычную сумму, т.е. sum(t.volume) ):
Не составит труда добавить и итог по столбцам. Для этого необходим использовать оператор ROLLUP , который позволит добавить суммирующую строку. В большинстве СУБД используется синтаксис rollup(t.product) , хотя иногда доступен и альтернативный t.product with rollup (например, SQL Server).
Результат можно сделать ещё красивее, заменив NULL на собственную подпись итога. Для этого можно использовать функцию coalesce() : coalesce(t.product, ‘total_sum’) , или же любой специфичный для конкретной СУБД аналог (например, nvl() в Oracle). Результат будет следующим:
Если ваша СУБД настолько стара, что не поддерживает rollup, – придётся использовать костыли. Например, так:
Можно (но вряд ли стоит) использовать какую-либо из вендоро-специфичных функций вместо стандартного CASE . Например, в PostgreSQL и SQLite доступен оператор FILTER :
Особенность FILTER в том, что он является частью стандарта (SQL:2003), но фактически поддерживается только в PostgreSQL и SQLite.
В других СУБД есть ряд эквивалентов CASE, не предусмотренных стандартом: IF в MySQL, DECODE в Oracle, IIF в SQL Server 2012+, и т.д. В большинстве случаев их использование не несёт никаких преимуществ, лишь усложняя поддержку кода в будущем.
2. Использование PIVOT (SQL Server и Oracle)
Описанный выше подход трудно назвать красивым. Как минимум, хочется не дублировать код для каждого поставщика, а просто их перечислить. Сделать это позволяет разворот (PIVOT) таблицы, доступный в в SQL Server и Oracle. Хотя этот оператор не предусмотрен стандартом SQL, обе СУБД предлагают идентичный синтаксис.
Для начала нам необходима таблица с агрегированной статистикой, которую мы «развернём». Казалось бы, для этого достаточно взять суммы по товару и провайдеру:
И этого будет достаточно – если нам нужны итоги только по товарам и по провайдерам. Если же мы хотим получить все возможные итоги, необходимо выбрать все возможные сочетания товара и провайдера, в том числе такие где товар или провайдер NULL :
Этот запрос можно существенно упростить, используя оператор CUBE :
Если мы хотим получить подпись итогов как ‘total_sum’ вместо NULL запрос необходимо немного откорректировать:
К такому результату уже можно применять PIVOT:
Здесь мы «поворачиваем» таблицу из прошлого запроса, используя агрегатную функцию суммы sum(agg) . При этом заголовки столбцов мы берём из поля supplier , а с помощью in («A», «B», «C», «total_sum») указываем какие конкретно поставщики должны быть выведены ( total_sum отвечает за столбец с итогами по строкам).
3. Common table expression
В принципе, для «поворота» таблицы нам не нужен оператор PIVOT как таковой. Этот запрос можно легко переписать, используя стандартный синтаксис — комбинацию CTE (common table expression) и соединений. Для этого будем использовать тот же запрос, что и для PIVOTа:
Из результатов, полученных в cte нам необходимы только уникальные значения товаров:
… к которым можно поочередно присоединять объем закупок для каждого отдельно взятого поставщика:
Здесь мы используем левое соединение т.к. у поставщика может не быть поставок по некоторым продуктам.
Окончательный запрос будет выглядеть таким образом:
Конечно, такой запрос — это proof-of-concept, поэтому выглядит он довольно экзотично.
4. Функция CROSSTAB (PostgreSQL)
В PostgreSQL доступна функция CROSSTAB , которая примерно эквивалентна PIVOT в SQL Server или Oracle. Для работы с ней необходимо расширение tablefunc :
CROSSTAB принимает в качестве основного аргумента запрос как text sql . Он будет практически тем же, что и для PIVOT , но с обязательным использованием сортировки:
В отличие от PIVOT, для «разворота» таблицы нам необходимо указывать не только названия столбцов, но и типы данных. Например, так: «product» varchar, «A» bigint, «B» bigint, «C» bigint, «total_sum» bigint .
Ещё один нюанс состоит в том, что CROSSTAB заполняет строки слева направо, игнорируя NULL-овые значения. Например, такой запрос:
… вернёт совсем не то, что мы хотим:
Как можно заметить, там, где были NULL-овые значения, всё «съехало» влево. Например, в первой строке для Product1 итог по строке оказался в столбце для поставщика С, а поставки С — в столбце поставщика В (для которого поставок не было). Корректно проставлены данные только для Product3 т.к. для этого товара у всех поставщиков были значения. Иными словами, если бы у нас не было NULL-овых значений, запрос был бы корректным и вернул нужный результат.
Чтобы не сталкиваться с таким поведением CROSSTAB нужно использовать вариант функции с двумя параметрами. Второй параметр должен содержать запрос, выводящий список всех столбцов в результате. В нашем случае это все названия поставщиков из таблицы + «total_sum» для итогов:
… а полный запрос будет выглядеть так:
5. Динамический SQL (на примере SQL Server)
Запрос с PIVOT или CROSSTAB уже функциональнее, чем изначальный с CASE (или CTE), но названия поставщиков все ещё необходимо вносить вручную. Но что делать, если поставщиков много? Или если их список регулярно обновляется? Хотелось бы выбирать их автоматически как как select distinct supplier from test_supply (или же из словаря, если он есть).
Здесь чистого SQL недостаточно. Он подразумевает статическую типизацию: для создания плана запроса СУБД нужно заранее указать число столбцов. Поэтому, например, синтаксис PIVOT не позволяет использовать подзапрос. Но это ограничение легко обойти с помощью динамического SQL! Для этого названия столбцов необходимо преобразовать в строку формата «элемент_1», «элемент_2», …, «элемент_n» , и использовать их в запросе.
Например, в SQL Server мы можем использовать STUFF для получения такой строки
… а затем включить её в окончательный запрос:
Динамический SQL вполне можно применить и к самому первому решению с CASE . Например, так:
Здесь используется цикл для итерации по доступным поставщикам в таблице test_supply (можно заменить на словарь, если он есть), после чего формируется соответствующий кусок запроса:
Во многих СУБД доступно аналогичное решение. Тем не менее, мы уже слишком отдалились от чистого SQL. Любое использование динамического SQL подразумевает углубление в специфику конкретной СУБД (и соответствующего ей процедурного расширения SQL).
Итого: как мы выяснили, сводную таблицу можно легко создать средствами SQL. Более того, это можно множеством разных методов — достаточно лишь выбрать оптимальный для вашей СУБД.