Участник:ArmorAdmin/PIVOT — различия между версиями

Текущая версия на 17:29, 23 августа 2016

Автор(ы): Чобиток Василий, 14-15 мая 2010

G+

В этой статье: о применении инструкций PIVOT и UNPIVOT языка T-SQL для разворота табличных данных.

Лирическое вступление

Решив возобновить свои навыки владения SQL, продолжил начатое когда-то решение упражнений на замечательном сайте sql-ex.ru. В отличие от многих других ресурсов, где проводится тестирование путем выбора варианта среди ответов на вопрос, здесь решением является собственный SQL-запрос, который должен выдать верный набор данных на тестовой базе данных.

В одной из задач возникла необходимость отобразить результирующий набор данных зеркально относительно диагонали. На практике мне не довелось сталкиваться со случаями, когда подобный разворот необходимо делать именно средствами SQL. Обычно это решается несколько проще уже на наборе данных, который был возвращён средствами SQL. Хотя, слышал, что есть любители задавать подобную задачку на собеседованиях...

На sql-ex используется СУБД MS SQL Server, работающая с расширенным вариантом SQL — Transact-SQL (T-SQL). Гугль на запрос «T-SQL повернуть таблицу» сразу подсказал, что существует инструкция pivot, а последующее изучение этой темы — unpivot.

Ужас! Таково было моё впечатление после изучения документации и примеров использования. Синтаксис специфический и интуитивно непонятный, документация и имеющиеся примеры из одной колонки делают одну строку и наоборот. А как быть с несколькими сроками и столбцами? В редких случаях имеем примеры нескольких строк и колонок в результате, но полученных агрегатными функциями. А что делать, если меня в конкретном случае агрегирование не интересует? «Ну нипанятна!»

Скажу откровенно, как работает pivot, к моменту написания этих строк я так до конца и не разобрался. Тем не менее, попробую в процессе написания понять сам и как можно проще объяснить читателям.

Формулируем задачу

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

Исходный набор                       Результирующий набор
------------------------------       ------------------------
Фамилия      Г.р.   Пол   Рост       Чел.1   Чел.2   Чел.3
------------------------------       ------------------------
Иванов       1972   м     176        Иванов  Петров  Сидорова
Петров       1981   м     181        1972    1981    1990
Сидорова     1990   ж     168        м       м       ж
                                     176     181     168

Мы видим, что есть набор данных, в котором число колонок не совпадает с числом строк (4×3), при этом колонки превращаются в строки и наоборот (3×4).

Надеюсь, что к концу статьи мы сможем проделать подобную операцию. К сожалению, одной простой командой такая транспозиция не произойдет.

Примеры будут приводится по структуре и данным из тестовой БД сайта sql-ex (другой возможности проверить запросы на СУБД MS SQL Server у меня просто нет).

Начнём с конца, с освоения unpivot, он мне показался проще.

UNPIVOT

UNPIVOT используется совместно с инструкцией SELECT и позволяет строку с данными развернуть в виде колонки.

Например, есть таблица Product, содержащая информацию о производителе, номере и типе продукции:

Product
------------------
maker  varchar(10)
model  varchar(50)
type   varchar(50)

При выполнении следующего простейшего запроса:

select maker, model, type from product where model = '1276'

получим:

maker  model  type   
A      1276   Printer

Все же для начала начнём с одной строчки. Сразу можно догадаться, что развороту данных помешает различный тип данных полей. Поэтому предыдущий запрос слегка изменяется, поле maker приводится к типу остальных полей — cast(maker as varchar(50)). Теперь ничто не мешает совершить разворот:

select aData from (
  -- Это предыдущий запрос с приведенным типом поля maker
  select cast(maker as varchar(50)) maker, model, type 
  from product where model = '1276'
) as t
unpivot (
  aData for fields in (maker, model, type)
) as unpvt

В результате выполнения этого, пока еще непонятного, запроса получим:

aData  
A
1276
Printer

Т. е. строка развернулась и стала вертикально. Имевшийся ранее селектовый запрос обёрнут новым, в котором присутствует блок unpivot. Синтаксис этого блока можно описать следующим образом:

unpivot(
  <Поле1> 
  for <Поле2> 
  in (<ПереченьПолей>)
)

где:

• <Поле1> — имя поля внешнего запроса, в которое попадут «повёрнутые» данные. В примере было задано имя поля aData и выведены его значения (select aData from ...);
• <Поле2> — имя поля, содержащего имена полей вложенного запроса. Это поле может быть выведено отдельной колонкой;
• <ПереченьПолей> — перечень имён полей вложенного запроса, выводимых в результирующую колонку. Здесь могут быть перечислены все или часть полей вложенного запроса, которые необходимо вывести в результирующую колонку.

Как можно догадаться, изменение первой строчки запроса

select fields, aData from ...

даст следующий результат:

fields  aData  
maker   A
model   1276
type    Printer

Тот же результат можно получить запросом select * from ..., только первой будет выведена колонка aData.

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

select cast(maker as varchar(50)) maker, model, type 
from product where model in ('1276', '2113')

Тестовые данные:

maker   model   type   
A       1276    Printer
E       2113    PC

В поворачивающем запросе изменился только вложенный подзапрос t:

select aData from (
  select cast(maker as varchar(50)) maker, model, type 
  from product where model in ('1276', '2113')
) as t
unpivot (
  aData for fields in (maker, model, type)
) as unpvt

В результате его выполнения получим:

aData 
A
1276
Printer
E
2113
PC

Явная бессмыслица — данные в таком виде вряд ли применимы (что получится, если добавить в конце запроса сортировку? Например: order by fields).

Чтобы данным придать осмысленность, попробуем их представить в таком виде:

model    aData 
1276     A
1276     Printer
2113     E
2113     PC

Здесь для каждой модели в отдельную колонку выведены характеристики «производитель» и «тип».

Если первую строку запроса изменить, добавив в нее поле model из вложенного запроса:

select model, aData from ...

то при выполнении запроса возникнет ошибка — поле «модель» участвует в развороте данных и не может быть выведено в отдельную колонку. Что делать? Всего лишь исключить поле model из перечня в блоке unpivot. Получим следующий рабочий запрос:

select model, aData from (
  select cast(maker as varchar(50)) maker, model, type 
  from product where model in ('1276', '2113')
) as t
unpivot (
  aData for fields in (maker, type)
) as unpvt

Если в первую строку запроса добавить еще поле fields и изменить имена полей, получим:

select model, fields, aData from (
  select cast(maker as varchar(50)) as [производитель], model, type as [тип]
  from product where model in ('1276', '2113')
) as t
unpivot (
  aData for fields in ([производитель], [тип])
) as unpvt

и результат запроса:

model  fields          aData 
1276   производитель   A
1276   тип             Printer
2113   производитель   E
2113   тип             PC

Таким образом, UNPIVOT позволяет:

• полностью или частично развернуть запись (строку) и представить её в вертикальном, колоночном виде;
• вывести в отдельной колонке имена полей или их синонимы, заданные в запросе;
• выводить поля из внутреннего запроса в виде отдельной колонки с тем ограничением, что эти поля не должны участвовать в развороте данных (отсутствовать в перечислении полей в блоке unpivot).

PIVOT

PIVOT является обратным по отношению к UNPIVOT, т. е. столбцы поворачивает в строки, но делает это несколько иначе, с использованием агрегатной функции.

Синтаксис подобен:

pivot(
  aggregation(<Поле1>)
  for <Поле2> 
  in (<ПереченьЗначений>)
)

Особенности синтаксиса:

• в отличие от unpivot, в котором <Поле1> было полем результирующего набора данных, здесь <Поле1> — имя поля, которое должно быть в исходном наборе;
• в обязательном порядке по полю <Поле1> должна выполняться агрегация (sum, count, avg и т. п.). Странная на первый взгляд особенность, её рассмотрим позднее;
• <Поле2> — имя поля исходного набора данных, значения которого будут выступать в роли колонок итогового набора данных — <ПереченьЗначений>, в эти поля будет выводится результат выполнения агрегатной функции.

Рассмотрим пример.

select * from product
pivot (
  count(model) for maker in ([A], [B], [D])
) as pvt

Результат выполнения запроса:

type     A    B    D 
Laptop   2    1    0
PC       2    1    0
Printer  3    0    2

Используемые в блоке pivot запроса поля model и maker, это поля, которые содержит исходный набор данных, таблица product. В перечислении maker in ([A], [B], [D]) значения «A», «B» и «D» — названия тех производителей из колонки maker, данные по которым необходимо вывести в виде отдельных колонок.

Здесь сразу заметно существенное ограничение в использовании pivot — с его помощью поворачивается не любой набор данных, а тот, из которого мы можем получить заранее оговорённый набор колонок (кварталы года, дни месяца, перечень конкретных компаний, сотрудников и т. п.).

При ближайшем рассмотрении тестового примера становится понятно, что в отличие от unpivot, осуществляющего «чистый» разворот, pivot в первую очередь предназначен для создания таких себе отчетов в более удобочитаемой форме, поэтому в нем и присутствует необходимость использования агрегатной функции.

В начале непонятно, откуда в примере взялась колонка type, если в запросе мы её нигде не использовали? Pivot делает отчет по тому набору данных, который ему передан. В примере он получил в качестве набора данных просто таблицу product, которая содержит три поля maker, model и type. Мы указали просчитать количество моделей по таким-то производителям, но поскольку в наборе данных есть еще поля, то pivot делает группировку и по всем остальным полям, оставляя их в итоговом наборе данных.

Т.е. с точки зрения полученных данных наш запрос полностью аналогичен такому:

select maker, type, count(model) models
from product
where maker in ('A', 'B', 'D')
group by maker, type

отличается лишь представление данных:

maker  type     models
A      Laptop   2
B      Laptop   1
A      PC       2
B      PC       1
A      Printer  3
D      Printer  2

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

select 'Число моделей:' as [Производитель:], * from (
  select maker, model from Product
  where maker in ('A', 'B', 'D')
) as pr
pivot (
  count(model) for maker in ([A], [B], [D])
) as pvt

Мы ограничили набор исходных данных, с которыми мы работаем (maker in ('A', 'B', 'D')). Сразу уточню, что если команда pivot используется без параметра xml (pivot xml), то мы явно должны указать параметры IN. При выборке в формате xml, можем просто ввести any или выбрать значения обычным select. В результате получим:

Производитель:  A  B  D
Число моделей:  7  2  2

Для придания человекочитаемости результату запроса я нарушил принципы нормализации, в колонке «Производитель:» внесено значение «Число моделей:» :-)

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

select [A] as 'Произв. 1', [B] as 'Произв. 2', [D] as 'Произв. 3' from...

Таким образом, PIVOT позволяет:

• выполнить агрегатную функцию по полю исходного набора данных, при этом считается, что по остальным полям набора производится группировка;
• результаты выполнения агрегатной функции вывести в горизонтальном виде, при этом колонками для вывода служат заранее определенные (известные) значения одного из полей исходного набора данных. Остальные поля исходного набора данных могут быть выведены отдельными колонками, результаты агрегации выводятся не только в разных колонках, но и в разных строках (получается нечто похожее на сводный отчет, «шахматка»).

Полный разворот

Работу PIVOT и UNPIVOT рассмотрели, но первоначально задекларированную задачу зеркально отобразить таблицу ещё не решили. Этим и займёмся.

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

Например, пользователь выбрал для сравнения ПК с кодами товара 1, 2, 4 и 7.

Обычный запрос

select * from pc where code in (1,2,4,7)

выдаст следующий набор данных:

code   model   speed   ram     hd      cd      price
1      1232    500     64      5.0     12x     600.0000
2      1121    750     128     14.0    40x     850.0000
4      1121    600     128     14.0    40x     850.0000
7      1232    500     32      10.0    12x     400.0000

Как можно заметить, существуют одинаковые модели, имеющие различные характеристики, поэтому сразу понятно, что для сравнения группировать характеристики следует не по модели, а по коду продукта.

Шаг 1. Вертикальное представление характеристик

На первом шаге получим характеристики в вертикальном представлении с использованием UNPIVOT.

select code, prop, val from (
    select 
        code, 
        model, 
        cast(speed as varchar(50)) speed, 
        cast(ram as varchar(50)) ram, 
        cast(hd as varchar(50)) hd, 
        cast(cd as varchar(50)) cd, 
        cast(price as varchar(50)) price
    from pc where code in (1,2,4,7)
) x
unpivot (
    val for prop in (model, speed, ram, hd, cd, price)
) unpvt

Здесь все значения, попадающие в колонку характеристик, приведены к типу наибольшего из строковых полей model — varchar(50).

Чтобы значения можно было однозначно идентифицировать, выведены колонки с кодом продукта и названием характеристики.

При выполнении запроса получим следующие данные:

code   prop    val  
1      model   1232
1      speed   500
1      ram     64
1      hd      5
1      cd      12x
1      price   600.00
2      model   1121
2      speed   750
2      ram     128
2      hd      14
2      cd      40x
2      price   850.00
4      model   1121
4      speed   600
4      ram     128
4      hd      14
4      cd      40x
4      price   850.00
7      model   1232
7      speed   500
7      ram     32
7      hd      10
7      cd      12x
7      price   400.00

Шаг 2. Сведение характеристик в строки

На предыдущем шаге характеристики ПК были представлены в вертикальном виде, но в одной колонке для всех ПК. Осталось характеристики каждого ПК вывести в отдельную колонку.

Это умеет делать PIVOT, но есть одна проблема: PIVOT — конструкция, использующая агрегацию. На sql-ex я подсмотрел достаточно простое решение: поскольку для пары code+prop в нашем наборе данных существует только одно значение val, то при группировке по этим полям выражение max(val) выдаст само это значение :-)

Запрос:

select * from (
    select code, prop, val from (
        select 
          code, 
          model, 
          cast(speed as varchar(50)) speed, 
          cast(ram as varchar(50)) ram, 
          cast(hd as varchar(50)) hd, 
          cast(cd as varchar(50)) cd, 
          cast(price as varchar(50)) price
        from pc where code in (1,2,4,7)
        ) x
    unpivot (
        val for prop in (model, speed, ram, hd, cd, price)
    ) unpvt 
) xx
pivot (
    max(val) for code in ([1],[2],[4],[7])
) pvt

В результате получим:

prop    1       2       4       7   
cd      12x     40x     40x     12x
hd      5       14      14      10
model   1232    1121    1121    1232
price   600.00  850.00  850.00  400.00
ram     64      128     128     32
speed   500     750     600     500

Шаг 3. Улучшение визуального представления

Требуемые данные в нужной структуре представления получены. Что меня не устраивает — неинформативные заголовки колонок и неудобный для восприятия порядок сортировки характеристик. Сделаем незначительные доработки запроса:

select prop as 'Хар-ка', [1] as 'Код № 1', [2] as 'Код № 2', [4] as 'Код № 4', [7] as 'Код № 7'
from (
    select code, prop, val from (
        select 
          code, 
          model [0.model], 
          cast(speed as varchar(50)) [2.speed], 
          cast(ram as varchar(50)) [3.ram], 
          cast(hd as varchar(50)) [4.hd], 
          cast(cd as varchar(50)) [5.cd], 
          cast(price as varchar(50)) [1.price]
        from pc where code in (1,2,4,7)
        ) x
    unpivot (
        val for prop in ([0.model], [2.speed], [3.ram], [4.hd], [5.cd], [1.price])
    ) unpvt 
) xx
pivot (
    max(val) for code in ([1],[2],[4],[7])
) pvt
order by prop

В результате его выполнения получим:

Хар-ка   Код № 1  Код № 2  Код № 4  Код № 7
0.model  1232     1121     1121     1232
1.price  600.00   850.00   850.00   400.00
2.speed  500      750      600      500
3.ram    64       128      128      32
4.hd     5        14       14       10
5.cd     12x      40x      40x      12x

Заключение

Инструкции PIVOT и UNPIVOT — достаточно мощный и интересный инструмент, расширяющий возможности SQL. Инструмент неидеальный, т.к. у него имеются определённые ограничения. Например, не представляется возможным повернуть набор данных, для которого значения ключевого поля и число записей (строк) заранее неизвестны. Но в случаях, подобных примеру «сравнительной характеристики», эти инструкции позволяют ещё на стороне сервера баз данных выполнить достаточно лаконичный запрос без значительных последующих преобразований данных на сервере приложений или клиенте.

Ссылки

• Операторы PIVOT и UNPIVOT на sql-tutorial.ru