Sql pivot: Использование операторов PIVOT и UNPIVOT — SQL Server
Содержание
Как с помощью Pivot агрегировать информацию из множества столбцов? — Разработка на vc.ru
Иногда в практике можно столкнуться с задачей, когда необходимо получить выгрузку, которая содержит в себе агрегирующую (сводную) информацию по какому-то объекту (сотруднику, клиенту, счету, карте). Для реализации такого подхода можно использовать оператор pivot в T-SQL, который разворачивает столбец в строку, преобразуя уникальные значения этого столбца в несколько выходных столбцов.
1609
просмотров
Применение данного оператора описывается в документации на примере одного столбца, но что делать, если количество столбцов для разворота больше одного. В данной статье хотелось бы рассмотреть, как раз такой случай.
Создадим демонстрационную таблицу со списком клиентов и его предложений:
Необходимо получить сводную таблицу по клиенту, для этого дополнительно создадим точки поворота (столбцы number_product_id, number_product_name, number_sales).
WITH CTE_Rank AS
(
SELECT Client,product_id,product_name, sales
,number_product_id=’i_product_id’ + CAST( DENSE_RANK() OVER
(PARTITIONBY Client ORDERBY product_id) ASVARCHAR(10) )
,number_product_name=’i_product_name’ + CAST( DENSE_RANK() OVER
(PARTITIONBY Client ORDERBYproduct_id) ASVARCHAR(10) )
,number_sales=’i_sales’ + CAST( DENSE_RANK() OVER
(PARTITIONBY Client ORDERBYproduct_id)ASVARCHAR(10))
FROM #T
)
select*from CTE_Rank
Добавим в запрос оператор разворота Pivot по каждому из столбцов number_product_id, number_product_name, number_sales.
Рассмотрим синтаксис Pivot на примере одного из столбцов:
- Client— столбец, по которому мы будем осуществлять группировку
- MAX(product_id)— агрегатная функция по столбцу product_id, в нашем случае не играет какой-либо роли, но необходимо ее указание согласно синтаксису оператора;
- FORnumber_product_idin([i_product_id1], [i_product_id2, [i_product_id3])— указание колонки со значениями, которые будут выступать в качестве названия результирующих столбцов
- AS pivot_id— обязательный псевдоним.
SELECT Client, [i_product_id1], [i_product_id2], [i_product_id3] ,
[i_product_name1], [i_product_name2], [i_product_name3] ,
[i_sales1], [i_sales2], [i_sales3]
FROM CTE_Rank AS R
PIVOT(MAX(product_id) FOR number_product_id IN ([i_product_id1], [i_product_id2], [i_product_id3])) AS pivot_id
PIVOT(MAX(product_name) FOR number_product_name IN ([i_product_name1], [i_product_name2], [i_product_name3])) AS pivot_name
PIVOT(MAX(sales) FOR number_sales IN ([i_sales1], [i_sales2],[i_sales3])) AS pivot_sales
Результат немного отличается от того, который мы ожидали получить — результаты агрегации выводятся не только в разных колонках, но и в разных строках.
Чтобы «схлопнуть» данную выборку, необходимо произвести группировку по столбцам, которые не были результирующими оператора Pivot, в нашем случае это столбец Client.
SELECT Client, i_product_id1 =MAX(i_product_id1), i_product_id2 =MAX(i_product_id2), i_product_id3 =MAX(i_product_id3)
, i_product_name1 =MAX([i_product_name1]), i_product_name2 =MAX([i_product_name2]), i_product_name3 =MAX([i_product_name3])
, i_sales1 =MAX([i_sales1]), i_sales2 =MAX([i_sales2]), i_sales3 =MAX([i_sales3])
FROM CTE_Rank AS R
PIVOT(MAX(product_id) FOR number_product_id IN ([i_product_id1], [i_product_id2],[i_product_id3])) AS pivot_id
PIVOT(MAX(product_name) FOR number_product_name IN ([i_product_name1], [i_product_name2],[i_product_name3])) AS pivot_name
PIVOT(MAX(sales) FOR number_sales IN ([i_sales1], [i_sales2],[i_sales3])) AS pivot_sales
GROUPBY Client
Теперь результат соответствует поставленной задаче.
Также можно динамически формировать строку запроса, чтоб не перечислять результирующие столбцы Pivot, и выполнять эту сроку через команду Execute.
Использование оператора Pivot существенно облегчает жизнь, но не стоит забывать, что существуют и другие способы, с помощью которых можно получить аналогичный результат, и выбор должен происходить в зависимости от характера поставленной задачи и влияние выбранного способа на общую производительность.
В качестве основы для написания статьи использовалась публикация PIVOT on two or more fields in SQL Server, альтернативный способ использования Pivot для множества столбцов, описан здесь.
Используем Pivot для множества столбцов
Время прочтения: 3 мин.
Применение
данного оператора описывается в документации на примере одного столбца, но что делать,
если количество столбцов для разворота больше одного. В данной статье хотелось
бы рассмотреть, как раз такой случай.
Создадим демонстрационную таблицу со списком клиентов и его предложений:
Необходимо получить сводную таблицу по клиенту, для этого дополнительно создадим точки поворота (столбцы number_product_id, number_product_name, number_sales).
WITH CTE_Rank AS ( SELECT Client,product_id,product_name, sales ,number_product_id='i_product_id' + CAST( DENSE_RANK() OVER (PARTITIONBY Client ORDERBY product_id) ASVARCHAR(10) ) ,number_product_name='i_product_name' + CAST( DENSE_RANK() OVER (PARTITIONBY Client ORDERBYproduct_id) ASVARCHAR(10) ) ,number_sales='i_sales' + CAST( DENSE_RANK() OVER (PARTITIONBY Client ORDERBYproduct_id)ASVARCHAR(10)) FROM #T ) select*from CTE_Rank
Добавим в запрос оператор разворота Pivot по каждому из
столбцов number_product_id, number_product_name, number_sales.
Рассмотрим синтаксис Pivot на примере одного из столбцов:
- Client — столбец, по которому
мы будем осуществлять группировку - MAX(product_id) — агрегатная функция по столбцу
product_id, в нашем случае не играет какой-либо роли, но необходимо ее указание
согласно синтаксису оператора; - FOR number_product_idin([i_product_id1], [i_product_id2],[i_product_id3]) — указание колонки со значениями, которые будут
выступать в качестве названия результирующих столбцов - AS pivot_id— обязательный псевдоним.
SELECT Client, [i_product_id1], [i_product_id2], [i_product_id3] , [i_product_name1], [i_product_name2], [i_product_name3] , [i_sales1], [i_sales2], [i_sales3] FROM CTE_Rank AS R PIVOT(MAX(product_id) FOR number_product_id IN ([i_product_id1], [i_product_id2], [i_product_id3])) AS pivot_id PIVOT(MAX(product_name) FOR number_product_name IN ([i_product_name1], [i_product_name2], [i_product_name3])) AS pivot_name PIVOT(MAX(sales) FOR number_sales IN ([i_sales1], [i_sales2],[i_sales3])) AS pivot_sales
Результат немного отличается от того, который мы ожидали получить — результаты агрегации выводятся не только в разных колонках, но и в разных строках. Чтобы “схлопнуть” данную выборку, необходимо произвести группировку по столбцам, которые не были результирующими оператора Pivot, в нашем случае это столбец Client.
SELECT Client, i_product_id1 =MAX(i_product_id1), i_product_id2 =MAX(i_product_id2), i_product_id3 =MAX(i_product_id3) , i_product_name1 =MAX([i_product_name1]), i_product_name2 =MAX([i_product_name2]), i_product_name3 =MAX([i_product_name3]) , i_sales1 =MAX([i_sales1]), i_sales2 =MAX([i_sales2]), i_sales3 =MAX([i_sales3]) FROM CTE_Rank AS R PIVOT(MAX(product_id) FOR number_product_id IN ([i_product_id1], [i_product_id2],[i_product_id3])) AS pivot_id PIVOT(MAX(product_name) FOR number_product_name IN ([i_product_name1], [i_product_name2],[i_product_name3])) AS pivot_name PIVOT(MAX(sales) FOR number_sales IN ([i_sales1], [i_sales2],[i_sales3])) AS pivot_sales GROUPBY Client
Теперь результат соответствует поставленной задаче.
Также можно динамически формировать строку запроса, чтоб не перечислять результирующие столбцы Pivot, и выполнять эту сроку через команду Execute.
Использование оператора Pivot существенно облегчает жизнь, но не стоит
забывать, что существуют и другие способы, с помощью которых можно получить
аналогичный результат, и выбор должен происходить в зависимости от характера
поставленной задачи и влияние выбранного способа на общую производительность.
В качестве основы для написания статьи использовалась публикация PIVOT
on two or more fields in SQL Server,
альтернативный способ использования Pivot для множества
столбцов, описан здесь.
SQL Pivot: преобразование строк в столбцы
Попробуйте эту записную книжку в Databricks
Ознакомьтесь с электронной книгой «Почему Data Lakehouse — ваше следующее хранилище данных», чтобы узнать о внутренней работе платформы Databricks Lakehouse.
ОБНОВЛЕНО 10.11.2018
Pivot впервые был представлен в Apache Spark 1.
6 как новая функция DataFrame, которая позволяет пользователям чередовать табличное выражение, превращая уникальные значения из одного столбца в отдельные столбцы.
Версия Apache Spark 2.4 расширяет эту мощную функциональность поворота данных и для наших пользователей SQL. В этом блоге, используя записи температуры в Сиэтле, мы покажем, как мы можем использовать эту общую функцию SQL Pivot для достижения сложных преобразований данных.
Изучение летних температур с помощью Pivot
Этим летом в Сиэтле температура поднялась до некомфортного уровня, достигнув 80 и 90 градусов в течение девяти дней в июле.
| Дата | Температура (°F) |
|---|---|
| 22.07.2018 | 86 |
| 23.07.2018 | 90 |
| 24.07.2018 | 91 |
| 07- 25-2018 | 92 |
| 26-07-2018 | 92 |
| 27-07-2018 | 8 8 |
28. 07.2018 | 85 |
| 29.07. 2018 | 94 |
| 30.07.2018 | 89 |
Предположим, мы хотим изучить или изучить, существовала ли историческая тенденция повышения уровня содержания ртути. Один из интуитивно понятных способов изучить и представить эти числа — представить месяцы в качестве столбцов, а затем среднемесячные максимумы каждого года в одной строке. Таким образом будет легко сравнивать температуры как по горизонтали, между соседними месяцами, так и по вертикали, между разными годами.
Теперь, когда у нас есть поддержка синтаксиса PIVOT в Spark SQL, мы можем добиться этого с помощью следующего SQL-запроса.
ВЫБЕРИТЕ ИЗ (
ВЫБЕРИТЕ год (дата) год, месяц (дата) месяц, темп.
ОТ high_temps
ГДЕ дата МЕЖДУ ДАТА '2015-01-01' И ДАТА '2018-08-31'
)
ВРАЩАТЬСЯ (
CAST(avg(temp) AS DECIMAL(4, 1))
ЗА месяц в (
1 ЯНВАРЯ, 2 ФЕВРАЛЯ, 3 МАРТА, 4 АПРЕЛЯ, 5 МАЯ, 6 ИЮНЯ,
7 ИЮЛЯ, 8 АВГУСТА, 9 СЕН, 10 ОКТЯБРЯ, 11 НОЯБРЯ, 12 ДЕКАБРЯ
)
)
ЗАКАЗАТЬ ПО ГОДУ DESC
Приведенный выше запрос выдаст следующий результат:
| ГОД | ЯНВАРЬ | ФЕВ | МАРТ | 9 АПРЕЛЬ0022 | МАЙ | ИЮНЬ | ИЮЛЬ | АВГ | СЕНТЯБРЬ | ОКТЯБРЬ | НОЯБРЬ | ДЕК |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2018 | 49,7 | 45,8 | 54,0 | 58,6 | 70,8 9003 0 | 71,9 | 82. 8 | 79.1 | NULL | NULL | NULL | NULL |
| 2017 | 46,6 | 51,6 | 57,3 | 67,0 | 72,1 | 78,3 | 81,5 | 73,8 | 61,1 | 51,3 900 30 | 45,6 | |
| 2016 | 49,1 | 53,6 | 56,4 | 65,9 | 68,8 | 73,1 | 76,0 | 79,5 | 69,6 | 60,6 | 56,0 | 41,9 |
| 2015 | 54,5 | 57,9 | 59,9 | 68,0 | 78,9 | 82,6 | 79,0 | 68,5 | 63,6 | 49,4 |
Что ж, похоже, бывают хорошие и плохие годы. 2016 год кажется довольно энергосберегающим.
Сведение в SQL
Давайте подробнее рассмотрим этот запрос, чтобы понять, как он работает. Во-первых, нам нужно указать предложение FROM , которое является входом сводки, другими словами, таблицы или подзапроса, на основе которого будет выполняться сводка.
В нашем случае нас интересуют годы, месяцы и высокие температуры, поэтому именно эти поля появляются в подзапросе.
Во-вторых, давайте рассмотрим еще одну важную часть запроса — предложение PIVOT . Первый аргумент предложения PIVOT — это агрегатная функция и столбец для агрегирования. Затем мы указываем столбец сводной таблицы в подпункте FOR в качестве второго аргумента, за которым следует оператор IN , содержащий значения столбца сводной таблицы в качестве последнего аргумента.
Сводной столбец — это точка, вокруг которой будет вращаться таблица, а значения сводного столбца будут транспонированы в столбцы выходной таблицы. 9Предложение 0087 IN также позволяет указать псевдоним для каждого сводного значения, что упрощает создание более понятных имен столбцов.
Важная идея сводной таблицы заключается в том, что она выполняет групповое агрегирование на основе списка неявных столбцов, сгруппированных по , вместе со сводной колонкой.
Столбцы неявной группировки по — это столбцы из предложения FROM , которые не появляются ни в какой агрегатной функции или в качестве сводного столбца.
В приведенном выше запросе с опорным столбцом, являющимся месяцем столбца, и неявным группа по столбцу , являющемуся годом столбца, выражение avg(temp) будет агрегировано для каждой отдельной пары значений (год, месяц) , где месяц равен одному из указанных значений сводного столбца. В результате каждое из этих агрегированных значений будет отображено в соответствующей ячейке строки года и столбца месяца.
Стоит отметить, что из-за этой неявной группы -по нам нужно убедиться, что любой столбец, который мы не хотим включать в сводной вывод, должен быть исключен из FROM , иначе запрос приведет к нежелательным результатам.
Указание нескольких агрегатных выражений
В приведенном выше примере показано, что в предложении PIVOT используется только одно агрегатное выражение, хотя на самом деле при необходимости пользователи могут указать несколько агрегатных выражений.
Опять же, с приведенными выше данными о погоде мы можем перечислить максимальные высокие температуры вместе со средними высокими температурами в период с июня по сентябрь.
ВЫБЕРИТЕ ИЗ ( ВЫБЕРИТЕ год (дата) год, месяц (дата) месяц, темп. ОТ high_temps ГДЕ дата МЕЖДУ ДАТА '2015-01-01' И ДАТА '2018-08-31' ) ВРАЩАТЬСЯ ( CAST(avg(temp) AS DECIMAL(4, 1)) avg, max(temp) max ЗА месяц в (6 ИЮНЯ, 7 ИЮЛЯ, 8 АВГУСТА, 9Сентябрь) ) ЗАКАЗАТЬ ПО ГОДУ DESC
В случае нескольких агрегатных выражений столбцы будут представлять собой декартово произведение значений сводного столбца и агрегатных выражений с именами _ .
| год | JUN_avg | JUN_max | JUL_avg | JUL_max | AUG_avg | AUG_max | SEP_avg | SEP_max |
|---|---|---|---|---|---|---|---|---|
| 2018 | 71,9 | 88 | 82,8 | 94 | 79,1 | 94 | ПУСТО | ПУСТО |
| 20 17 | 72,1 | 96 | 78,3 | 87 | 81,5 | 94 | 73,8 | |
| 2016 | 73,1 | 93 | 76,0 | 89 | 79,5 | 95 | 78 | |
| 2015 | 78,9 | 92 | 82,6 | 95 | 79,0 | 92 | 68,5 | 81 |
Группирование столбцов по сравнению со сводными столбцами
Теперь предположим, что мы хотим включить низкие температуры в наше исследование трендов температуры из этой таблицы.
суточных низких температур:
| Дата | Температура (°F) |
|---|---|
| … | … |
| 01.08.2018 | 59 |
| 02.08.2018 | 58 |
| 03.08.2018 | 59 |
| 58 | |
| 05.08.2018 | 59 |
| 06-08-2018 | 59 |
| … | … |
эти две таблицы на «Дата » столбец. Однако, поскольку мы собираемся использовать сводную таблицу, которая выполняет группировку по датам, мы можем просто объединить две таблицы, используя СОЕДИНЕНИЕ ВСЕ . Позже вы увидите, что этот подход также дает нам большую гибкость:
. ВЫБЕРИТЕ дату, температуру, 'H' в качестве флага ОТ high_temps СОЮЗ ВСЕХ ВЫБЕРИТЕ дату, температуру, 'L' в качестве флага ОТ low_temps
Теперь давайте попробуем наш сводной запрос с новой комбинированной таблицей:
ВЫБЕРИТЕ ИЗ (
ВЫБЕРИТЕ год(дата) год, месяц(дата) месяц, темп, флаг `H/L`
ОТ (
ВЫБЕРИТЕ дату, температуру, 'H' в качестве флага
ОТ high_temps
СОЮЗ ВСЕХ
ВЫБЕРИТЕ дату, температуру, 'L' в качестве флага
ОТ low_temps
)
ГДЕ дата МЕЖДУ ДАТА '2015-01-01' И ДАТА '2018-08-31'
)
ВРАЩАТЬСЯ (
CAST(avg(temp) AS DECIMAL(4, 1))
ЗА месяц в (6 ИЮНЯ, 7 ИЮЛЯ, 8 АВГУСТА, 9Сентябрь)
)
ЗАКАЗАТЬ ПО ГОДУ DESC, `H/L` ASC
В итоге получаем средний максимум и средний минимум за каждый месяц за последние 4 года в одной таблице.
Обратите внимание, что нам необходимо включить флаг столбца в сводной запрос, иначе выражение avg(temp) будет основано на сочетании высоких и низких температур.
| год | H/L | ИЮНЬ | ИЮЛ | АВГ | СЕНТЯБРЬ |
|---|---|---|---|---|---|
| 2018 | H | 71,9 | 82,8 | 79,1 | NULL |
| 2018 | L 90 030 | 53,4 | 58,5 | 58,5 | NULL |
| 2017 | H | 72,1 9 0030 | 78,3 | 81,5 | 73,8 |
| 2017 | Л | 53,7 | 56,3 | 59,0 | 55,6 |
| 2016 | Н | 73,1 | 76,0 | 79,5 | 69,9 |
| 2016 | L | 53,9 | 57,6 | 59,9 | 52,9 |
| 2015 | H | 78,9 | 82,6 90 030 | 79,0 | 68,5 |
| 2015 | L | 56,4 | 59,9 | 58,5 | 52,5 |
Вы могли заметить, что теперь у нас есть две строки для каждого года, одна для высоких температур, а другая для низких температур.
. Это потому, что мы включили еще один столбец, флаг в сводном вводе, который, в свою очередь, становится еще одним столбцом неявной группировки в дополнение к исходному столбцу год .
Альтернативно, флаг вместо группирующего столбца может также служить сводным столбцом. Итак, теперь у нас есть два сводных столбца: месяц и флаг :
.
ВЫБЕРИТЕ ИЗ (
ВЫБЕРИТЕ год (дата) год, месяц (дата) месяц, темп, флаг
ОТ (
ВЫБЕРИТЕ дату, температуру, 'H' в качестве флага
ОТ high_temps
СОЮЗ ВСЕХ
ВЫБЕРИТЕ дату, температуру, 'L' в качестве флага
ОТ low_temps
)
ГДЕ дата МЕЖДУ ДАТА '2015-01-01' И ДАТА '2018-08-31'
)
ВРАЩАТЬСЯ (
CAST(avg(temp) AS DECIMAL(4, 1))
ЗА (месяц, флаг) в (
(6, 'H') JUN_hi, (6, 'L') JUN_lo,
(7, 'H') JUL_hi, (7, 'L') JUL_lo,
(8, 'H') AUG_hi, (8, 'L') AUG_lo,
(9, 'H') SEP_hi, (9, 'L') SEP_lo
)
)
ЗАКАЗАТЬ ПО ГОДУ DESC
Этот запрос представляет нам другое расположение одних и тех же данных, с одной строкой для каждого года и двумя столбцами для каждого месяца.
| год | JUN_hi | JUN_lo | JUL_hi | JUL_lo | AUG_hi | SEP_hi | SEP_lo | |
|---|---|---|---|---|---|---|---|---|
| 2018 | 71,9 | 53,4 | 58,5 | 79,1 | 58,5 | NULL | NULL | |
| 2017 | 72,1 900 30 | 53,7 | 78,3 | 56,3 | 81,5 | 59,0 | 73,8 | 55,6 |
| 2016 | 52 .9 | |||||||
| 2015 | 78,9 | 56,4 | 82,6 | 59,9 | 79,0 | 58,5 | 68,5 | 52,5 |
Его также называют увеличительным стеклом ваших данных. Это позволяет пользователям преобразовывать строки в столбцы. В дополнение к этому, он также позволяет пользователю группировать данные на основе любого столбца, доступного в наборе данных.
[SalesTerritoryGroup] 
В этом примере я буду использовать производную таблицу для создания временного набора результатов.
Как я уже упоминал, оператор PIVOT состоит из трех частей.