Русский
Русский
English
Статистика
Реклама

Pivoting

Сводные таблицы в SQL

28.06.2020 10:17:00 | Автор: admin
Сводная таблица один из самых базовых видов аналитики. Многие считают, что создать её средствами SQL невозможно. Конечно же, это не так.


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



Типичная задача определить размер закупок по поставщикам и товарам, т.е. построить сводную таблицу. Пользователи MS Excel привыкли получать такую аналитику буквально парой кликов:



В SQL это не так быстро, но большинство решений тривиальны.

Тестовые данные
-- таблица с полями: поставщик (supplier), товар (product), объем поставки (volume)create table test_supply (supplier varchar null, -- varchar2(10) в Oracle, и т.п.                          product varchar null,  -- varchar2(10) в Oracle, и т.п.                          volume int null                          ); -- тестовые данныеinsert into test_supply (supplier, product, volume) values ('A', 'Product 1', 928);insert into test_supply (supplier, product, volume) values ('A', 'Product 1', 422);insert into test_supply (supplier, product, volume) values ('A', 'Product 4', 164);insert into test_supply (supplier, product, volume) values ('A', 'Product 1', 403);insert into test_supply (supplier, product, volume) values ('A', 'Product 3', 26);insert into test_supply (supplier, product, volume) values ('B', 'Product 4', 594);insert into test_supply (supplier, product, volume) values ('B', 'Product 4', 989);insert into test_supply (supplier, product, volume) values ('B', 'Product 3', 844);insert into test_supply (supplier, product, volume) values ('B', 'Product 4', 870);insert into test_supply (supplier, product, volume) values ('B', 'Product 2', 644);insert into test_supply (supplier, product, volume) values ('C', 'Product 2', 733);insert into test_supply (supplier, product, volume) values ('C', 'Product 2', 502);insert into test_supply (supplier, product, volume) values ('C', 'Product 1', 97);insert into test_supply (supplier, product, volume) values ('C', 'Product 3', 620);insert into test_supply (supplier, product, volume) values ('C', 'Product 2', 776);-- проверкаselect * from test_supply;



1. Оператор CASE и аналоги



Самый простой и очевидный способ получения сводной таблицы это хардкод с использованием оператора CASE. Например, для поставщика А можно вычислить размер поставок как sum(case when t.supplier = 'A' then t.volume end). Чтобы получить объем поставок для разных товаров достаточно просто добавить группировку по полю product:

select t.product,        sum(case when t.supplier = 'A' then t.volume end) as Afrom test_supply tgroup by t.productorder by t.product;



Если добавить else 0, то для товаров, по которым не было поставок, вместо null будут выведены нули:

select coalesce(t.product, 'total_sum') as product,       sum(case when t.supplier = 'A' then t.volume end) as Afrom test_supply tgroup by t.product;



Если продублировать код для всех поставщиков (которых у нас три A, B, C), мы получим необходимую нам сводную таблицу:

select t.product,        sum(case when t.supplier = 'A' then t.volume end) as A,       sum(case when t.supplier = 'B' then t.volume end) as B,       sum(case when t.supplier = 'C' then t.volume end) as Cfrom test_supply tgroup by t.productorder by t.product;



В неё можно добавить итог по строкам (как обычную сумму, т.е. sum(t.volume)):
select t.product,       sum(case when t.supplier = 'A' then t.volume end) as A,       sum(case when t.supplier = 'B' then t.volume end) as B,       sum(case when t.supplier = 'C' then t.volume end) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by t.product;



Не составит труда добавить и итог по столбцам. Для этого необходим использовать оператор ROLLUP, который позволит добавить суммирующую строку. В большинстве СУБД используется синтаксис rollup(t.product), хотя иногда доступен и альтернативный t.product with rollup (например, SQL Server).

select t.product,       sum(case when t.supplier = 'A' then t.volume end) as A,       sum(case when t.supplier = 'B' then t.volume end) as B,       sum(case when t.supplier = 'C' then t.volume end) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);



Результат можно сделать ещё красивее, заменив NULL на собственную подпись итога. Для этого можно использовать функцию coalesce(): coalesce(t.product, 'total_sum'), или же любой специфичный для конкретной СУБД аналог (например, nvl() в Oracle). Результат будет следующим:

select coalesce(t.product, 'total_sum') as product,       sum(case when t.supplier = 'A' then t.volume end) as A,       sum(case when t.supplier = 'B' then t.volume end) as B,       sum(case when t.supplier = 'C' then t.volume end) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);



Если СУБД не поддерживает ROLLUP ...
Если ваша СУБД настолько стара, что не поддерживает rollup, придётся использовать костыли. Например, так:

select t.product,       sum(case when t.supplier = 'A' then t.volume end) as A,       sum(case when t.supplier = 'B' then t.volume end) as B,       sum(case when t.supplier = 'C' then t.volume end) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by t.productunion allselect 'total_sum',       sum(case when t.supplier = 'A' then t.volume end),       sum(case when t.supplier = 'B' then t.volume end),       sum(case when t.supplier = 'C' then t.volume end),       sum(t.volume) as total_sumfrom test_supply t;




Можно (но вряд ли стоит) использовать какую-либо из вендоро-специфичных функций вместо стандартного CASE. Например, в PostgreSQL и SQLite доступен оператор FILTER:

select coalesce(t.product, 'total_sum') as product,       sum(t.volume) filter (where t.supplier = 'A') as A,       sum(t.volume) filter (where t.supplier = 'B') as B,       sum(t.volume) filter (where t.supplier = 'C') as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);


Особенность FILTER в том, что он является частью стандарта (SQL:2003), но фактически поддерживается только в PostgreSQL и SQLite.

В других СУБД есть ряд эквивалентов CASE, не предусмотренных стандартом: IF в MySQL, DECODE в Oracle, IIF в SQL Server 2012+, и т.д. В большинстве случаев их использование не несёт никаких преимуществ, лишь усложняя поддержку кода в будущем.

MySQL: IF
select coalesce(t.product, 'total_sum') as product,        sum(IF(t.supplier = 'A', t.volume, null)) as A,       sum(IF(t.supplier = 'B', t.volume, null)) as B,       sum(IF(t.supplier = 'C', t.volume, null)) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);



Oracle: DECODE
select coalesce(t.product, 'total_sum') as product,        sum(decode(t.supplier, 'A', t.volume, null)) as A,       sum(decode(t.supplier, 'B', t.volume, null)) as B,       sum(decode(t.supplier, 'C', t.volume, null)) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);



SQL Server 2012 или выше: IIF
select coalesce(t.product, 'total_sum') as product,        sum(iif(t.supplier = 'A', t.volume, null)) as A,       sum(iif(t.supplier = 'B', t.volume, null)) as B,       sum(iif(t.supplier = 'C', t.volume, null)) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);



2. Использование PIVOT (SQL Server и Oracle)



Описанный выше подход трудно назвать красивым. Как минимум, хочется не дублировать код для каждого поставщика, а просто их перечислить. Сделать это позволяет разворот (PIVOT) таблицы, доступный в в SQL Server и Oracle. Хотя этот оператор не предусмотрен стандартом SQL, обе СУБД предлагают идентичный синтаксис.

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

select t.supplier,       t.product,       sum(t.volume) as aggfrom test_supply tgroup by t.product,         t.supplier;



И этого будет достаточно если нам нужны итоги только по товарам и по провайдерам. Если же мы хотим получить все возможные итоги, необходимо выбрать все возможные сочетания товара и провайдера, в том числе такие где товар или провайдер NULL:

select t.supplier, t.product, sum(t.volume) as aggfrom test_supply tgroup by t.supplier, t.productunion allselect null, t.product, sum(t.volume)from test_supply tgroup by t.productunion allselect t.supplier, null, sum(t.volume)from test_supply tgroup by t.supplierunion allselect null, null, sum(t.volume)from test_supply t;



Этот запрос можно существенно упростить, используя оператор CUBE:

select t.supplier,        t.product,        sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product);


Если мы хотим получить подпись итогов как 'total_sum' вместо NULL запрос необходимо немного откорректировать:

select coalesce(t.supplier, 'total_sum') as supplier,        coalesce(t.product, 'total_sum') as product,        sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product);



К такому результату уже можно применять PIVOT:

select *from ( select coalesce(t.supplier, 'total_sum') as supplier,       coalesce(t.product, 'total_sum') as product,       sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product)      ) tpivot (sum(agg)         -- NB: ниже в SQL Server - двойные кавычки, в Oracle DB - одинарные       for supplier in ("A", "B", "C", "total_sum")       ) pvt;


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

3. Common table expression



В принципе, для поворота таблицы нам не нужен оператор PIVOT как таковой. Этот запрос можно легко переписать, используя стандартный синтаксис комбинацию CTE (common table expression) и соединений. Для этого будем использовать тот же запрос, что и для PIVOTа:

with cteas(select coalesce(t.supplier, 'total_sum') as supplier,          coalesce(t.product, 'total_sum') as product,          sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product))select * from cte;



Из результатов, полученных в cte нам необходимы только уникальные значения товаров:
select distinct t.product from cte t


к которым можно поочередно присоединять объем закупок для каждого отдельно взятого поставщика:

left join cte aon t.product = a.productand a.supplier = 'A'


Здесь мы используем левое соединение т.к. у поставщика может не быть поставок по некоторым продуктам.

Окончательный запрос будет выглядеть таким образом:

with cteas(select coalesce(t.supplier, 'total_sum') as supplier,          coalesce(t.product, 'total_sum') as product,          sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product))select distinct t.product,                     a.agg as A,                    b.agg as B,                    c.agg as C,                    ts.agg as total_sumfrom cte tleft join cte aon t.product = a.product and a.supplier = 'A'left join cte bon t.product = b.product and b.supplier = 'B'left join cte con t.product = c.product and c.supplier = 'C'left join cte tson t.product = ts.product and ts.supplier = 'total_sum'order by product;



Конечно, такой запрос это proof-of-concept, поэтому выглядит он довольно экзотично.

4. Функция CROSSTAB (PostgreSQL)



В PostgreSQL доступна функция CROSSTAB, которая примерно эквивалентна PIVOT в SQL Server или Oracle. Для работы с ней необходимо расширение tablefunc:
create extension tablefunc; -- для PostgreSQL 9.1+


CROSSTAB принимает в качестве основного аргумента запрос как text sql. Он будет практически тем же, что и для PIVOT, но с обязательным использованием сортировки:
select coalesce(t.product, 'total_sum') as product,         coalesce(t.supplier, 'total_sum') as supplier,                           sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product)order by product, supplier;


В отличие от PIVOT, для разворота таблицы нам необходимо указывать не только названия столбцов, но и типы данных. Например, так: "product" varchar, "A" int8, "B" int8, "C" int8, "total_sum" int8.
Ещё один нюанс состоит в том, что CROSSTAB заполняет строки слева направо, игнорируя NULL-овые значения. Например, такой запрос:

select *from crosstab     (    $$select coalesce(t.product, 'total_sum') as product,     coalesce(t.supplier, 'total_sum') as supplier,                       sum(t.volume) as agg      from test_supply t      group by cube(t.supplier, t.product)      order by product, supplier $$     )   as cst("product" varchar, "A" int8, "B" int8, "C" int8, "total_sum" int8);


вернёт совсем не то, что мы хотим:



Как можно заметить, там, где были NULL-овые значения, всё съехало влево. Например, в первой строке для Product1 итог по строке оказался в столбце для поставщика С, а поставки С в столбце поставщика В (для которого поставок не было). Корректно проставлены данные только для Product3 т.к. для этого товара у всех поставщиков были значения. Иными словами, если бы у нас не было NULL-овых значений, запрос был бы корректным и вернул нужный результат.

Чтобы не сталкиваться с таким поведением CROSSTAB нужно использовать вариант функции с двумя параметрами. Второй параметр должен содержать запрос, выводящий список всех столбцов в результате. В нашем случае это все названия поставщиков из таблицы + total_sum для итогов:

select distinct tt.supplier as supplierfrom test_supply ttunion allselect 'total_sum'order by supplier


а полный запрос будет выглядеть так:

select * from crosstab     (    $$select coalesce(t.product, 'total_sum') as product,     coalesce(t.supplier, 'total_sum') as supplier,                       sum(t.volume) as agg      from test_supply t      group by cube(t.supplier, t.product)      order by product, supplier $$,    $$select distinct tt.supplier as supplier      from test_supply tt      union all      select 'total_sum'      order by supplier $$     )   as cst("product" varchar, "A" int8, "B" int8, "C" int8, "total_sum" int8);



5. Динамический SQL (на примере SQL Server)



Запрос с PIVOT или CROSSTAB уже функциональнее, чем изначальный с CASE (или CTE), но названия поставщиков все ещё необходимо вносить вручную. Но что делать, если поставщиков много? Или если их список регулярно обновляется? Хотелось бы выбирать их автоматически как как select distinct supplier from test_supply (или же из словаря, если он есть).

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

Например, в SQL Server мы можем использовать STUFF для получения такой строки

declare @colnames as nvarchar(max);select @colnames =            stuff((select distinct ', ' + '"' + t.supplier + '"'       from test_supply t       for xml path ('')       ), 1, 1, ''          ) + ', "total_sum"';


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

-- T-SQL (!)declare @colnames as nvarchar(max),        @query as nvarchar(max);select @colnames =            stuff((select distinct ', ' + '"' + t.supplier + '"'       from test_supply t       for xml path ('')       ), 1, 1, ''          ) + ', "total_sum"';set @query =   'select * from(select coalesce(t.supplier, ''total_sum'') as supplier, coalesce(t.product, ''total_sum'') as product, sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product)) as tpivot (sum(agg) for supplier in (' + @colnames + ')) as pvt';execute(@query);


Динамический SQL вполне можно применить и к самому первому решению с CASE. Например, так:

-- T-SQL (!)select distinct supplier into #colnames from test_supply;declare @colname as nvarchar(max),        @query as nvarchar(max);set @query = 'select coalesce(t.product, ''total_sum'') as product';while exists (select * from #colnames)begin    select top 1 @colname = supplier from #colnames;    delete from #colnames where supplier = @colname;    set @query = @query + ', sum(case when t.supplier = ''' + @colname + ''' then t.volume end) as ' + @colnameend;set @query = @query + ' , sum(t.volume) as total_sum                       from test_supply t                       group by rollup(t.product)'drop table #colnames;execute(@query);


Здесь используется цикл для итерации по доступным поставщикам в таблице test_supply (можно заменить на словарь, если он есть), после чего формируется соответствующий кусок запроса:

  sum(case when t.supplier = '<Поставщик 1>' then t.volume end) as <Поставщик 1>, sum(case when t.supplier = '<Поставщик 2>' then t.volume end) as <Поставщик 2>..., sum(case when t.supplier = '<Поставщик n>' then t.volume end) as <Поставщик n>


Во многих СУБД доступно аналогичное решение. Тем не менее, мы уже слишком отдалились от чистого SQL. Любое использование динамического SQL подразумевает углубление в специфику конкретной СУБД (и соответствующего ей процедурного расширения SQL).

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

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru