Разработка
NewTechAudit
204

Как обобщить данные с использованием T‑SQL

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

В закладки

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

Select счет,период,sum(сумма_операций) as сумма from table group by счет,период

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

В таких случаях на помощь приходит оператор T‑SQL pivot. Данный оператор позволяет «развернуть» строки выгрузки в одну. Это удобно — если вам необходимо сравнить аналитику по разным счетам.

Как им пользоваться? Допустим, у нас есть таблица table, в ней следующие столбцы: счет, период, сумма и др. Как получить количество строк равное количеству счетов, а не периодов? Ваш pivot может выглядеть следующим образом:

Select * from table m pivot (sum ( сумма) for период in ([период1],[период2],[период3]….) )pvt

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

declare @periods nvarchar(max) select @periods = coalesce(@periods,'') + '['+ cast(период as nvarchar) +'],' from (select distinct период from table) t print(@periods)

Теперь немного модифицируем наш запрос, чтобы разработчик в дальнейшем смог бы подставить переменную со всеми периодами:

declare @sql nvarchar(max) select @sql = 'Select * from table m pivot (sum ( сумма) for период in ('+@periods+') )pvt' exec(@sql)

Готово. Теперь нам не нужно перечислять вручную все периоды.

Где мы использовали T—SQL pivot?

Во-первых — в повседневных задачах, во-вторых — в спринт проектах (это удобный способ собрать статистику). К примеру, с помощью него мы выводили информацию по одному клиенту одной строкой за полгода (с большим количеством признаков).

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

Материал опубликован пользователем.
Нажмите кнопку «Написать», чтобы поделиться мнением или рассказать о своём проекте.

Написать
{ "author_name": "NewTechAudit", "author_type": "editor", "tags": [], "comments": 0, "likes": -2, "favorites": 1, "is_advertisement": false, "subsite_label": "dev", "id": 110500, "is_wide": true, "is_ugc": false, "date": "Wed, 04 Mar 2020 13:41:29 +0300", "is_special": false }
0
Комментариев нет
Популярные
По порядку

Прямой эфир