Как создать сводную таблицу SQL и сэкономить время и усилия
Чтобы создать сводную таблицу в SQL, вы можете использовать оператор PIVOT. Он позволяет преобразовать строки из одной таблицы в столбцы в другой таблице, основываясь на заданных агрегатных функциях. Вот пример использования оператора PIVOT:
SELECT * FROM ( SELECT category, amount FROM sales ) AS src PIVOT ( SUM(amount) FOR category IN ('Electronics', 'Clothing', 'Books') ) AS pvt;
В этом примере, мы имеем таблицу sales с колонками category и amount. Мы хотим создать сводную таблицу, где столбцами будут категории (‘Electronics’, ‘Clothing’, ‘Books’), а значениями будет сумма amount для каждой категории. На выходе получим таблицу, где каждая строка будет представлять собой сумму продаж для каждой категории:
+--------------+------+----------+------+ | 'Electronics' | 'Clothing' | 'Books' | +--------------+------+----------+------+ | 1500 | 800 | 1200 | +--------------+------+----------+------+
Таким образом, оператор PIVOT позволяет легко создавать сводные таблицы в SQL, упрощая агрегацию данных для анализа.
Детальный ответ
Как сделать сводную таблицу SQL
- order_id: уникальный идентификатор заказа
- customer_id: уникальный идентификатор клиента
- product_name: название продукта
- quantity: количество купленных продуктов
- price: цена продукта
Теперь давайте рассмотрим несколько примеров, чтобы узнать, как создавать сводную таблицу SQL.
Пример 1: Агрегирование данных
Допустим, нам нужно подсчитать общее количество проданных продуктов и сумму продаж для каждого клиента. Мы можем использовать функцию SUM для суммирования количества и цены продуктов. Используем оператор GROUP BY для группировки данных по клиенту:
SELECT customer_id, SUM(quantity) AS total_quantity, SUM(price) AS total_sales FROM orders GROUP BY customer_id;В этом примере мы выбираем столбец customer_id и суммируем значения столбцов quantity и price для каждого клиента. Мы также используем алиасы total_quantity и total_sales для отображения суммированных значений.
Пример 2: Подсчет количества
Допустим, нам нужно узнать, сколько раз каждый клиент сделал заказ. Мы можем использовать функцию COUNT для подсчета количества заказов:
SELECT customer_id, COUNT(order_id) AS order_count FROM orders GROUP BY customer_id;В этом примере мы выбираем столбец customer_id и подсчитываем количество значений столбца order_id для каждого клиента. Мы также используем алиас order_count для отображения подсчитанных значений.
Пример 3: Усреднение данных
Допустим, нам нужно найти среднюю цену продукта для каждого клиента. Мы можем использовать функцию AVG для усреднения цен:
SELECT customer_id, AVG(price) AS average_price FROM orders GROUP BY customer_id;В этом примере мы выбираем столбец customer_id и вычисляем среднее значение столбца price для каждого клиента. Мы также используем алиас average_price для отображения усредненных значений.
Пример 4: Задание условий для агрегации
Мы также можем задать условия для агрегирования данных в сводной таблице. Например, допустим, нам нужно подсчитать общее количество проданных продуктов только для заказов, у которых цена превышает 100 рублей. Мы можем использовать оператор HAVING для этого:
SELECT customer_id, SUM(quantity) AS total_quantity FROM orders GROUP BY customer_id HAVING SUM(price) > 100;В этом примере мы выбираем столбец customer_id и суммируем значения столбца quantity только для заказов, у которых общая цена превышает 100 рублей. Мы также используем условие HAVING для фильтрации результата в сводной таблице.
Надеюсь, эта статья помогла вам понять, как создать сводную таблицу SQL. Сводные таблицы — мощный инструмент для анализа данных и выполнения различных вычислений. Вы можете экспериментировать с различными функциями и условиями, чтобы получить нужный результат.
�� Как сделать сводную таблицу в SQL: простое руководство для начинающих
Как сделать сводную таблицу в SQL? В SQL можно создать сводную таблицу с использованием оператора SELECT и агрегатных функций, таких как SUM , AVG , COUNT и так далее. Ниже приведен пример запроса на создание сводной таблицы:
SELECT column1, SUM(column2) AS total FROM table GROUP BY column1;
В этом примере мы выбираем column1 и выполняем суммирование значений column2 . Затем мы группируем результаты по column1 . Результаты будут содержать уникальные значения column1 и сумму значений column2 для каждого уникального значения. Вы можете использовать различные агрегатные функции и комбинировать несколько столбцов в вашем запросе для создания сводной таблицы по вашим потребностям. Надеюсь, эта информация поможет вам создать сводную таблицу в SQL! Если у вас есть еще вопросы, не стесняйтесь задавать.
Детальный ответ
Как сделать сводную таблицу в SQL
Добро пожаловать! В этой статье мы рассмотрим, как создать сводную таблицу с использованием языка SQL. Сводные таблицы — это очень полезный инструмент, который позволяет нам агрегировать данные в более удобной форме для анализа и отчетности. Начнем! Перед тем, как мы начнем, давайте определимся с определением сводной таблицы. Сводная таблица — это таблица, которая преобразует строки в столбцы, что позволяет нам агрегировать данные по группам и выполнять вычисления.
Шаг 1: Создание таблицы
Первым шагом в создании сводной таблицы является создание исходной таблицы с данными. Давайте представим, что у нас есть таблица «sales», которая содержит данные о продажах различных продуктов. Вот пример создания и заполнения такой таблицы:
CREATE TABLE sales ( product_id INT, year INT, month INT, amount DECIMAL(10, 2) ); INSERT INTO sales (product_id, year, month, amount) VALUES (1, 2021, 1, 1000), (2, 2021, 1, 1500), (1, 2021, 2, 2000), (2, 2021, 2, 2500);
Здесь мы создаем таблицу «sales» с четырьмя столбцами: «product_id», «year», «month» и «amount». Затем мы заполняем эту таблицу некоторыми тестовыми данными.
Шаг 2: Построение сводной таблицы
Теперь, когда у нас есть исходная таблица, мы можем перейти к построению сводной таблицы. Все, что нам нужно сделать, это написать запрос SQL, который выполняет агрегацию данных и преобразует строки исходной таблицы в столбцы новой таблицы. Вот пример:
SELECT year, month, SUM(CASE WHEN product_id = 1 THEN amount ELSE 0 END) AS product_1_total, SUM(CASE WHEN product_id = 2 THEN amount ELSE 0 END) AS product_2_total FROM sales GROUP BY year, month;
В этом примере мы используем функцию CASE и операторы агрегации для построения сводной таблицы. В каждом столбце сводной таблицы мы выполняем агрегацию (суммирование) значений столбца «amount» для определенного продукта (указанного через столбец «product_id») и заданных годов и месяцев. Затем мы сгруппировываем результаты по годам и месяцам с помощью оператора GROUP BY.
Шаг 3: Результаты сводной таблицы
После выполнения запроса на построение сводной таблицы мы получим результаты, которые будут выглядеть примерно так:
| year | month | product_1_total | product_2_total | |------|-------|-----------------|-----------------| | 2021 | 1 | 1000.00 | 1500.00 | | 2021 | 2 | 2000.00 | 2500.00 |
Здесь каждая строка представляет собой комбинацию года и месяца, а каждый столбец представляет сумму продаж для определенного продукта. Таким образом, мы получаем сводную таблицу, которая удобна для анализа и отчетности.
Заключение
Поздравляю! Теперь вы знаете, как создать сводную таблицу в SQL. Мы рассмотрели основные шаги, включая создание исходной таблицы с данными, построение сводной таблицы с использованием функции CASE и операторов агрегации, а также получение результатов сводной таблицы. Использование сводных таблиц может значительно облегчить анализ и отчетность, особенно при работе с большими объемами данных. Удачи в использовании сводных таблиц в своих проектах!
Сводные таблицы в 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 = '' then t.volume end) as , sum(case when t.supplier = '' then t.volume end) as . sum(case when t.supplier = '' then t.volume end) as
Во многих СУБД доступно аналогичное решение. Тем не менее, мы уже слишком отдалились от чистого SQL. Любое использование динамического SQL подразумевает углубление в специфику конкретной СУБД (и соответствующего ей процедурного расширения SQL).
Итого: как мы выяснили, сводную таблицу можно легко создать средствами SQL. Более того, это можно множеством разных методов достаточно лишь выбрать оптимальный для вашей СУБД.
Сводные таблицы в SQL
Сводная таблица – один из самых базовых видов аналитики. Многие считают, что создать её средствами 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 A from test_supply t group by t.product order 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 A from test_supply t group 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 C from test_supply t group by t.product order 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_sum from test_supply t group 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_sum from test_supply t group 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_sum from test_supply t group 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_sum from test_supply t group by t.product union all select '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_sum from 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_sum from test_supply t group 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_sum from test_supply t group 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_sum from test_supply t group 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_sum from test_supply t group by rollup(t.product); 2. Использование PIVOT (SQL Server и Oracle)
Описанный выше подход трудно назвать красивым. Как минимум, хочется не дублировать код для каждого поставщика, а просто их перечислить. Сделать это позволяет разворот (PIVOT) таблицы, доступный в в SQL Server и Oracle. Хотя этот оператор не предусмотрен стандартом SQL, обе СУБД предлагают идентичный синтаксис.
Для начала нам необходима таблица с агрегированной статистикой, которую мы «развернём». Казалось бы, для этого достаточно взять суммы по товару и провайдеру:
select t.supplier, t.product, sum(t.volume) as agg from test_supply t group by t.product, t.supplier; 
И этого будет достаточно – если нам нужны итоги только по товарам и по провайдерам. Если же мы хотим получить все возможные итоги, необходимо выбрать все возможные сочетания товара и провайдера, в том числе такие где товар или провайдер NULL :
select t.supplier, t.product, sum(t.volume) as agg from test_supply t group by t.supplier, t.product union all select null, t.product, sum(t.volume) from test_supply t group by t.product union all select t.supplier, null, sum(t.volume) from test_supply t group by t.supplier union all select null, null, sum(t.volume) from test_supply t; 
Этот запрос можно существенно упростить, используя оператор CUBE :
select t.supplier, t.product, sum(t.volume) as agg from test_supply t group 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 agg from test_supply t group 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 agg from test_supply t group by cube(t.supplier, t.product) ) t pivot (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 cte as ( select coalesce(t.supplier, 'total_sum') as supplier, coalesce(t.product, 'total_sum') as product, sum(t.volume) as agg from test_supply t group by cube(t.supplier, t.product) ) select * from cte;
Из результатов, полученных в cte нам необходимы только уникальные значения товаров:
select distinct t.product from cte t … к которым можно поочередно присоединять объем закупок для каждого отдельно взятого поставщика:
left join cte a on t.product = a.product and a.supplier = 'A' Здесь мы используем левое соединение т.к. у поставщика может не быть поставок по некоторым продуктам.
Окончательный запрос будет выглядеть таким образом:
with cte as ( select coalesce(t.supplier, 'total_sum') as supplier, coalesce(t.product, 'total_sum') as product, sum(t.volume) as agg from test_supply t group 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_sum from cte t left join cte a on t.product = a.product and a.supplier = 'A' left join cte b on t.product = b.product and b.supplier = 'B' left join cte c on t.product = c.product and c.supplier = 'C' left join cte ts on 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 agg from test_supply t group by cube(t.supplier, t.product) order by product, supplier; В отличие от PIVOT, для «разворота» таблицы нам необходимо указывать не только названия столбцов, но и типы данных. Например, так: «product» varchar, «A» bigint, «B» bigint, «C» bigint, «total_sum» bigint .
Ещё один нюанс состоит в том, что 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" bigint, "B" bigint, "C" bigint, "total_sum" bigint); … вернёт совсем не то, что мы хотим:
Как можно заметить, там, где были NULL-овые значения, всё «съехало» влево. Например, в первой строке для Product1 итог по строке оказался в столбце для поставщика С, а поставки С — в столбце поставщика В (для которого поставок не было). Корректно проставлены данные только для Product3 т.к. для этого товара у всех поставщиков были значения. Иными словами, если бы у нас не было NULL-овых значений, запрос был бы корректным и вернул нужный результат.
Чтобы не сталкиваться с таким поведением CROSSTAB нужно использовать вариант функции с двумя параметрами. Второй параметр должен содержать запрос, выводящий список всех столбцов в результате. В нашем случае это все названия поставщиков из таблицы + «total_sum» для итогов:
(select distinct tt.supplier as supplier from test_supply tt order by supplier) union all select 'total_sum' … а полный запрос будет выглядеть так:
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 order by supplier ) union all select 'total_sum' $$ ) as cst("product" varchar, "A" bigint, "B" bigint, "C" bigint, "total_sum" bigint);
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 agg from test_supply t group by cube(t.supplier, t.product) ) as t pivot (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 ' + @colname end; 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 = '' then t.volume end) as , sum(case when t.supplier = '' then t.volume end) as . , sum(case when t.supplier = '' then t.volume end) as
Во многих СУБД доступно аналогичное решение. Тем не менее, мы уже слишком отдалились от чистого SQL. Любое использование динамического SQL подразумевает углубление в специфику конкретной СУБД (и соответствующего ей процедурного расширения SQL).
Итого: как мы выяснили, сводную таблицу можно легко создать средствами SQL. Более того, это можно множеством разных методов — достаточно лишь выбрать оптимальный для вашей СУБД.