Язык Transact SQL является процедурным расширением языка SQL и используется в СУБД Microsoft SQL Server. В этой статье мы рассмотрим использование некоторых элементов T-SQL для анализа данных. Сначала мы рассмотрим оконные функции, а затем поговорим о методах сводки (pivoting) данных и его отмены. При сводки данные преобразуются из состояния строк в столбцы, а при отмене обработки данные преобразуются из столбцов в строки, аналогично Excel.

И здесь окна

Оконная функция – это функция, которая работает с окном, т.е. набором строк, и возвращает значение на основе неких вычислений. А окном называется подмножество строк, основанное на дескрипторе окна, который относится к текущей строке. Также познакомимся с OVER – это инструкция T-SQL, которая определяет окно для применения оконной функции.

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

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

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

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

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

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

Ниже приведен пример запроса к данным о продажах. Представление EmpOrders в базе данных TSQLV6, в котором используется оконная функция для вычисления текущих итоговых значений для каждого сотрудника по месяцам:

USE TSQLV6;
SELECT empid, ordermonth, val,
  SUM(val) OVER(PARTITION BY empid
    ORDER BY ordermonth
    ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW) AS runval
FROM Sales.EmpOrders;

Полученный вывод будет иметь следующий вид:

В определении оконной функции, которую вы указываете в запросе, вызываемом OVER, есть до трех частей: блок window-partition, блок window-order и window-frame. При этом, пустое предложение OVER() представляет весь результирующий набор базового запроса.

Использование блока WINDOW позволяет вам указать всю спецификацию окна или ее часть в запросе, а затем использовать имя окна в блоке OVER этого запроса. Его основная цель - сократить длину строки запроса, когда у вас есть повторяющиеся спецификации окна. Это доступно в SQL Server 2022 и более поздних версиях, а также в базе данных SQL Azure при условии, что уровень совместимости базы данных установлен на 160 или выше. Узнать уровень совместимости можно с помощью следующего запроса:

SELECT DATABASEPROPERTYEX(N'TSQLV6', N'CompatibilityLevel');

При рассмотрении всех основных команд запроса (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY) вы помещаете WINDOW между элементами запроса HAVING и ORDER BY.

Рассмотрим следующий запрос в качестве примера:

SELECT empid, ordermonth, val,
  SUM(val) OVER(PARTITION BY empid
    ORDER BY ordermonth
    ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW) AS runsum,
  MIN(val) OVER(PARTITION BY empid
    ORDER BY ordermonth
    ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW) AS runmin,
  MAX(val) OVER(PARTITION BY empid
    ORDER BY ordermonth
    ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW) AS runmax,
  AVG(val) OVER(PARTITION BY empid
    ORDER BY ordermonth
    ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW) AS runavg
FROM Sales.EmpOrders;

Pivoting данных

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

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

USE TSQLV6;
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders
(
  orderid INT NOT NULL
    CONSTRAINT PK_Orders PRIMARY KEY,
  orderdate DATE NOT NULL,
  empid INT NOT NULL,
  custid VARCHAR(5) NOT NULL,
  qty INT NOT NULL
);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20200802', 3, 'A', 10),
  (10001, '20201224', 2, 'A', 12),
  (10005, '20201224', 1, 'B', 20),
  (40001, '20210109', 2, 'A', 40),
  (10006, '20210118', 1, 'C', 14),
  (20001, '20210212', 2, 'B', 12),
  (40005, '20220212', 3, 'A', 10),
  (20002, '20220216', 1, 'C', 20),
  (30003, '20220418', 2, 'B', 15),
  (30004, '20200418', 3, 'C', 22),
  (30007, '20220907', 3, 'D', 30);
SELECT * FROM dbo.Orders;

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

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid;

Результат будет иметь следующий вид:

Но, у вас есть требование предcтавить результат в следующем виде:

Давайте рассмотрим эти три этапа в примере. Сначала вам нужно создать одну строку с результатами для каждого уникального идентификатора сотрудника. Это означает, что строки из dbo.Orders должна быть сгруппирована по атрибуту empid (элемент группировки).

В таблице dbo.Orders содержится один столбец, в котором находятся все значения идентификаторов клиентов, и один столбец, содержащий количество заказов. Задача сводного анализа требует, чтобы для каждого уникального идентификатора клиента создавался отдельный столбец результатов, содержащий агрегированные количества для этого клиента.

Наконец, поскольку сводный анализ включает группировку, вам необходимо объединить данные для получения результирующих значений в “пересечении” элементов группировки (сотрудник) и распределения (клиент). Вам необходимо определить агрегирующую функцию (в данном случае SUM) и элемент агрегирования (в данном случае атрибут qty).

Напомним, что pivoting включает в себя группировку, распространение и агрегирование. В этом примере вы группируете по empid, распространяете (количество) по custid и агрегируете по SUM (qty). После того, как вы определите элементы, участвующие в сводке, остальное - это просто включение этих элементов в нужные места в общем шаблоне запроса для сводки.

Вот запрос полного решения, который сводит данные о заказе, возвращая общее количество для каждого сотрудника (по строкам) и клиента (по столбцам):

SELECT empid,
  SUM(CASE WHEN custid = 'A' THEN qty END) AS A,
  SUM(CASE WHEN custid = 'B' THEN qty END) AS B,
  SUM(CASE WHEN custid = 'C' THEN qty END) AS C,
  SUM(CASE WHEN custid = 'D' THEN qty END) AS D
FROM dbo.Orders
GROUP BY empid;

Результат будет идентичен требованиям, представленным выше.

Но также для сводки можно воспользоваться оператором PIVOT.Вместо того, чтобы работать непосредственно с таблицей заказов dbo.Orders PIVOT работает с производной таблицей под названием D, которая содержит только элементы pivot empid, custid и qty.

SELECT empid, A, B, C, D
FROM dbo.Orders
  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;

Результат будет аналогичным.

У нас отмена

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

USE TSQLV6;
DROP TABLE IF EXISTS dbo.EmpCustOrders;
CREATE TABLE dbo.EmpCustOrders
(
  empid INT NOT NULL
    CONSTRAINT PK_EmpCustOrders PRIMARY KEY,
  A VARCHAR(5) NULL,
  B VARCHAR(5) NULL,
  C VARCHAR(5) NULL,
  D VARCHAR(5) NULL
);
INSERT INTO dbo.EmpCustOrders(empid, A, B, C, D)
  SELECT empid, A, B, C, D
  FROM (SELECT empid, custid, qty
    FROM dbo.Orders) AS D
    PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;
SELECT * FROM dbo.EmpCustOrders;

В таблице есть строка для каждого сотрудника, столбец для каждого из четырех клиентов A, B, C и D, а также количество заказов для каждого сотрудника и клиента. Обратите внимание, что нерелевантные пересечения (комбинации сотрудников и клиентов, в которых не было пересекающихся действий по заказу) представлены нулевыми значениями. Предположим, вы получаете запрос на отмену отправки данных, требующий, чтобы вы вернули строку для каждого сотрудника и клиента вместе с количеством заказа, если таковое существует. Результат должен выглядеть следующим образом:

Отмена сводки предполагает создание двух результирующих столбцов из любого количества исходных столбцов — один для хранения имен исходных столбцов в виде строк, а другой для хранения значений исходных столбцов. В примере ниже вам нужно открепить исходные столбцы A, B, C и D, чтобы в результате получить столбец имен custid и столбец значений qty. Аналогично оператору PIVOT, T-SQL также поддерживает оператор UNPIVOT, позволяющий откреплять данные. Общая форма запроса с оператором UNPIVOT выглядит следующим образом:

SELECT empid, custid, qty
FROM dbo.EmpCustOrders
  UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U;

По сути, оператор UNPIVOT выполняет те же шаги, что и оператор PIVOT, рассмотренный ранее.

Заключение

В этой статье мы рассмотрели некоторые аспекты использования оконных функций в T-SQL. На самом деле для обеспечения данного функционала можно использовать гораздо больше различных инструментов MS SQL Server.

Освоить MS SQL Server на профессиональном уровне можно на онлайн-курсе "MS SQL Server Developer" под руководством экспертов-практиков.

Transact SQL для анализа данных