воскресенье, 16 октября 2011 г.

Кросс таблица, PIVOT

Динамическое распределение данных по столбцам.

Пример исходной таблицы:
CountryGoodsRevenue
Ukrainemeat15000
Ukrainebeer8700
Russiamilk8000
Russiabread7300
Polandcheese11000
...


Вид, к которому ее необходимо преобразовать. Т.е. кросс таблица в которой значения goods вынесены в названия столбцов:
GoodsUkraineRussiaPoland
bread3500730015200
beer17700900018700
milk12000163006000
meat45000047500
cheese110002350011000


Кросс таблица не является реляционной т.к. заголовки столбцов формируются из данных хранящихся в самой таблице, реляционной таблице. И хотя такое преобразование было востребовано, из-за этой «нереляционности» разработчики не спешили реализовывать поддержку такой возможности в своих СУБД. Оператор PIVOT появился, начиная только с MS SQL Server 2005 и Oracle 11g.

Без использования PIVOT
Преобразовать такую таблицу можно было преобразовать используя условие CASE (или DECODE)
select 
s.goods,
sum(case when s.country = 'ukraine' then s.revenue else 0 end) as ukraine,
sum(case when s.country = 'russia' then s.revenue else 0 end) as russia,
sum(case when s.country = 'poland' then s.revenue else 0 end) as poland
from sales s
group by s.goods
GoodsUkraineRussiaPoland
bread3500730015200
beer17700900018700
milk12000163006000
meat45000047500
cheese110002350011000


Использование выражения PIVOT в запросе позволяет транспонировать строки в столбцы (PIVOT) или столбцы в строки (UNPIVOT, соответственно).

Синтаксис запроса:
SELECT …
FROM …
PIVOT
(
pivot
pivot for
pivot in
)
WHERE …
Где:
pivot – столбцы по которым будет производится агрегация
pivot for – столбцы по которым будет производится группировка
pivot in – определяет названия столбцов в таблице в которые будут подставляться значения агрегатов.

Пример использования PIVOT
select * from sales
PIVOT (
sum(REVENUE) 
for COUNTRY 
in ('ukraine' Ukraine, 'russia' Russia, 'poland' Poland))
GoodsUkraineRussiaPoland
bread3500730015200
beer17700900018700
milk12000163006000
meat4500047500
cheese110002350011000


Дополнительные преобразования запроса
Если необходимы дополнительные преобразования данных до кросс запроса можно использовать вложенный запрос, либо выражение WITH.
В примерах ниже не используются дополнительные преобразования, а лишь указано где в общей конструкции запроса они могут быть реализованы.
Вложенный запрос
select * from
(select * from sales -- преобразование данных, запрос)
PIVOT (sum(REVENUE) for COUNTRY in ('ukraine' Ukraine, 'russia' Russia, 'poland' Poland ))
Выражение WITH
with sl as (select * from sales -- преобразование данных, запрос)
select * from sl
PIVOT (sum(REVENUE) for COUNTRY in ('ukraine' Ukraine, 'russia' Russia, 'poland' Poland ))


Использование алиасов
Алиасы могут применятся к значением указанным в pivot in и к значениям агрегатов. Если задан алиас для агрегата, то он будет выведен в название столбца с использованием в качестве разделителя символа «_».
select * from sales
PIVOT (sum(REVENUE) as revenue 
for COUNTRY 
in ('ukraine' Ukraine, 'russia' Russia, 'poland' Poland ))
GoodsUkraine_revenueRussia_revenuePoland_revenue
bread3500730015200
beer17700900018700
milk12000163006000
meat4500047500
cheese110002350011000


Использование нескольких агрегатов
Если используется несколько агрегатов, например, кроме дохода еще нужно посчитать кол-во магазинов в каждой из стран то они разделяются запятыми:
select * from sales
PIVOT (sum(REVENUE) as revenue, count(SHOP_NAME) as shop_cnt 
for COUNTRY 
in ('ukraine' Ukraine, 'russia' Russia, 'poland' Poland ))
В таком случае в результате будет по два столбца для каждой страны, с доходом и количеством магазинов. Отображаться они будут в том порядке в котором указаны в условии.

Группировка по нескольким столбцам
Если используется группировка не только по странам, а например и по городам. Можно использовать сравнение нескольких значений
select * from sales_points
PIVOT (sum(REVENUE) as revenue 
for (COUNTRY, CITY)
in (('ukraine', 'kiev') as Ukraine_Kiev, 
('russia', 'moskow') as Russia_Moskow, 
('poland', 'warsaw') as Poland_Warsaw))


XML
К сожалению, во всех рассмотренных вариантах названия для pivot столбцов задаются явно. Нельзя использовать ни вложенный запрос, ни динамически менять значения.
По отношению к рассматриваемой таблице это значит, что если будет не 3 страны, а 30 все их нужно перечислять в pivot in. А если у нас появятся продажи в других странах, их так же придется добавлять «вручную».
Выходом из такой ситуации может быть использование XML. Тогда запрос примет следующий вид:
select * from sales
PIVOT XML (sum(REVENUE)
for COUNTRY 
in (ANY))


UNPIVOT
Совершает обратное преобразование по отношению к PIVOT. Т.е. столбцы преобразовывает в строки.
Синтаксис аналогичен PIVOT
SELECT …
FROM …
UNPIVOT [INCLUDE|EXCLUDE NULLS]
(
pivot
pivot for
pivot in
)
WHERE …