Функции упорядоченных наборов

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

Еще в 2012 году я написал статью о функциях упорядоченных наборов. В ней отмечалось, что на тот момент в системе SQL Server возможность использования этих функций не была заложена. И вот начиная с версии SQL Server vNext (2017/2018?) CTP 1.1 мы наконец получили средства, обеспечивающие взаимодействие с первой функцией упорядоченных наборов — функцией STRING_AGG. Кроме того, эта функция уже реализована в Azure SQL Database. В настоящей статье я напомню о том, что же, собственно, представляют собой функции упорядоченных наборов, что нового содержит версия vNext и чего в ней пока нет.

Пример функции упорядоченных наборов

Агрегирующие функции широко используются в коде T-SQL, главным образом для решения задач аналитики. Но до сих пор язык T-SQL допускал применение только сгруппированных агрегирующих функций, не определяющих порядок важности. Рассмотрим для примера групповой запрос, приведенный в листинге 1, который рассчитывает значение максимального заказа для каждого клиента. Этот код генерирует выходные данные, показанные на рисунке 1.

Рисунок 1. Результаты работы кода в листинге 1

Агрегирующая функция MAX относится к категории общих функций наборов, и в ней упорядоченность по важности не актуальна, иначе говоря, порядок, в котором максимальное значение высчитывается внутри группы, не имеет значения. Вне зависимости от заданного порядка максимальное значение всегда будет одним и тем же. В противоположность этому функция упорядоченных наборов — это агрегирующая функция, предполагающая вычисление значений в определенном порядке. Давайте предположим, что нам требуется выяснить не стоимость максимального заказа для каждого клиента, а стоимость последнего заказа для каждого клиента на основе упорядочения таких показателей, как orderdate и orderid. В стандарте SQL определяется предложение WITHIN GROUP, в котором задается порядок важности для функции упорядоченных наборов. Если бы язык T-SQL обеспечивал возможность работы с функцией упорядоченных сгруппированных наборов LAST_VALUE (сейчас такая возможность отсутствует, но мы будем при рассмотрении данного примера считать ее реальной), задача выполнялась бы таким образом, как показано в листинге 2.

Этот запрос возвратил бы данные, приведенные на рисунке 2.

Рисунок 2. Результаты работы кода в листинге 2

В версии SQL Server 2016 язык T-SQL не имеет средств для работы с функциями упорядоченных сгруппированных наборов, но в версии vNext картина меняется.

Агрегация строк

До появления vNext самой востребованной функцией упорядоченных наборов в T-SQL была функция, выполняющая конкатенацию сгруппированных строк. Статья на сайте Connect (https://connect.microsoft.com/SQLServer/feedback/details/728969/feature-request-ordered-set-functions-within-group-clause), с запросом на эту функцию как часть более общего набора средств для работы с функциями упорядоченных наборов, оказалась очень востребованной. Конкатенировать сгруппированные строки полезно при решении множества задач, таких как формирование сводных запросов и запросов с обратным транспонированием для выполнения средствами динамического SQL, при конкатенировании индивидуальных элементов, таких как идентификаторы заказов по группам, например, клиенты по дате заказа и др.

До появления версии vNext в наиболее популярном решении для конкатенации упорядоченных строк использовался вариант FOR XML PATH. Я продемонстрирую, как применить его при формировании запроса к таблице Sales.Orders базы данных TSQLV4 и как создать для каждого клиента строку с разделителями в виде запятых, причем таким образом, чтобы идентификаторы заказов конкатенировались в порядке от самых новых к более давним.

Создадим индекс для получения решения (листинг 3). А в листинге 4 приведен код решения, примененный сначала к одному клиенту на входе.

Запрос возвращает идентификаторы заказов указанного клиента, отсортированные по времени размещения. Он преобразует целочисленные идентификаторы заказов в строки символов и конкатенирует запятую перед каждым идентификатором. Если использовать FOR XML PATH ('') и возвращать узлы в виде текстовых узлов, в результате получается простая строковая конкатенация. Наконец, с помощью функции STUFF код удаляет лишнюю предваряющую запятую. На выходе код генерирует данные, показанные на рисунке 3.

Рисунок 3. Результаты работы кода в листинге 4

Чтобы применить эту логику к каждому клиенту, нужно опросить таблицу Customers и задействовать указанную выше логику в коррелированном подзапросе, который коррелирует внутренние заказы с внешним клиентом, как показано в листинге 5. Этот код генерирует выходные данные, представленные на рисунке 4.

Рисунок 4. Результаты работы кода в листинге 5

Отмечу, что поскольку идентификаторы заказов состоят только из цифр, нет необходимости думать об обработке символов, которые имеют в языке XML специальное назначение (например, >) и обычно появляются в результирующих данных в виде >. Но если такие специальные символы все-таки могут появляться в ваших данных и вы хотите возвращать строки с исходными символами, можете применить обходной маневр. Добавьте директиву TYPE и с помощью метода. value извлеките из экземпляра XML значение в виде строки символов. Для этого в приведенном в листинге 5 решении вместо фрагмента (SELECT …FOR XML PATH ('')) используйте фрагмент (SELECT …FOR XML PATH (''), TYPE).value ('. [1]', 'VARCHAR (MAX)').

План выполнения этого запроса представлен на рисунке 5.

Рисунок 5. План агрегирования строки с использованием  XML

Данный план предусматривает сканирование индекса idx_nc_region в поисках Customers для получения идентификаторов клиентов. Далее с помощью оператора Nested Loops выявляются идентификаторы заказов текущего клиента; для этого применяются результаты поиска по клиентам в соответствующем индексе, созданном для таблицы Orders. После этого оператор UDX формирует строку XML с конкатенированными идентификаторами заказов. Я протестировал это решение на таблице Customers, содержащей данные по 20 000 клиентов, и на таблице Orders, содержащей 1 000 000 заказов (50 заказов на одного клиента). На своем ноутбуке я получил следующие статистические показатели по быстродействию: время использования процессора 1406 мс, время, затраченное на выполнение задания, — 1549 мс, логические операции считывания — 64 147. Как вы можете убедиться, время выполнения составило порядка 1,5 секунды.

Если же вы не формируете поддерживающий индекс, рабочие характеристики решения заметно ухудшаются. Работая без использования индекса, я получил следующие статистические данные: время использования процессора 9125 мс, время, затраченное на выполнение задания, — 10491 мс, логические операции считывания — 3 079 626. Затраты на выполнение операций ввода-вывода резко выросли до уровня миллионов операций считывания, а время выполнения составило 10,5 с. Пользователи SQL Server vNext не испытывают таких проблем. Разработчики Microsoft реализовали функцию упорядоченных наборов STRING_AGG. Первый входной элемент для этой функции — элемент, который вы хотите подвергнуть конкатенации, второй элемент — разделитель. С помощью стандартного факультативного предложения WITHIN GROUP пользователь задает порядок выполнения конкатенации. В листинге 6 показано, как можно использовать данную функцию для выполнения нашего задания.

Если у вас не возникает необходимости обеспечить тот или иной порядок конкатенации, можете опустить предложение WITHIN GROUP.

Функция допускает использование как символов в стандарте Unicode, так и строк, состоящих из обычных символов. Тип результирующих данных определяется типом входных данных. Если входные данные представлены не строкой символов, а, к примеру, целым числом, SQL Server по умолчанию в неявной форме преобразует их в строку символов Unicode. Вот почему в данном решении я явным образом преобразую столбец orderid в обычную строку символов.

План для этого решения представлен на рисунке 6.

Рисунок 6. План агрегирования строки с помощью функции STRING_AGG

Данный план сканирует поддерживающий индекс к таблице Orders по порядку ключа. Далее осуществляется конкатенация с использованием оператора Stream Aggregate, который выполняется в соответствии с порядком в группе. Вполне очевидно, что это решение намного проще и эффективнее решения, в котором используется FOR XML PATH. Обработав с помощью данного решения таблицу, содержащую 1 000 000 заказов, я получил на своем ноутбуке следующие статистические данные по быстродействию: время использования процессора 578 мс, время, затраченное на выполнение задания, — 673 мс, логические операции считывания — 3973. Без использования поддерживающего индекса я получил следующую статистику: время использования процессора 6890 мс, время, затраченное на выполнение задания, — 3711 мс, логические операции считывания — 4511. Удалить поддерживающий индекс вы можете с помощью фрагмента кода в листинге 7.

FIRST_VALUE, LAST_VALUE

В языке T-SQL добавление функции STRING_AGG — это важный шаг в процессе создания механизма для выполнения функций упорядоченных наборов. В то же время можно отметить отсутствие в продукте некоторых других важных функций упорядоченных наборов. Так, по состоянию на сегодня T-SQL поддерживает функции FIRST_VALUE и LAST_VALUE лишь в виде оконных, но не групповых функций. Допустим, к примеру, что нам нужно вычислить для каждого клиента значения первого и последнего заказов. На рисунке 7 показано, что мы хотим получить.

Рисунок 7. Значения первого и последнего заказов для клиентов

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

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

Однако здесь нужно напомнить, что в отличие от групповых запросов оконные запросы не предусматривают сокрытия деталей. Иначе говоря, результат выполнения функции повторяется для всех строк, ассоциированных с одним и тем же разделом (в нашем примере это custid). И код в листинге 9 генерирует выходные данные, представленные на рисунке 8.

Рисунок 8. Результаты работы кода в листинге 9

Чтобы удалить дубликаты, добавим предложение DISTINCT таким образом, как показано в листинге 10.

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

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

PERCENTILE_CONT, PERCENTILE_DISC

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

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

Рисунок 9. Желаемый результат расчета средней величины заказа для клиента

Если бы в языке T-SQL была предусмотрена возможность использования функций PERCENTILE_CONT и PERCENTILE_DISC как групповых функций упорядоченных наборов, для решения этой задачи мы использовали бы групповой запрос, подобный тому, который представлен в листинге 12, но пока такая возможность отсутствует.

Сегодня нам пришлось бы прибегнуть к маневру, который мы использовали при работе с функциями FIRST_VALUE и LAST_VALUE и предложением DISTINCT. В нашем случае код выглядел бы так, как показано в листинге 13.

После усовершенствований, реализованных недавно в процессе оптимизации оконных функций с помощью оператора пакетного режима Window Aggregate, использование для вычисления процентилей функций PERCENTILE_CONT и PERCENTILE_DISC (а не каких-либо альтернативных решений) представляется гораздо более эффективным. Будем надеяться, что, когда специалисты Microsoft обеспечат возможность применения таких методов в формате групповых функций, мы станем свидетелями появления аналогичных возможностей оптимизации.

Итак, реализовав наконец в своем продукте функцию STRING_AGG, которая осуществляет конкатенацию сгруппированных строк на основе указанного желаемого порядка, разработчики SQL Server сделали первый большой шаг на пути к внедрению функций упорядоченных наборов. Вне их поля зрения остаются еще несколько типов функций упорядоченных наборов — функции смещения, функция обратного распределения и др. Более того, пока что мы не можем пользоваться функциями упорядоченных наборов CLR. Было бы просто замечательно, если бы мы могли управлять порядком применения пользовательской агрегатной функции user defined aggregate (UDA) CLR с помощью предложения WITHIN GROUP. Если вы считаете, что подобные усовершенствования важны для вас, добавьте свой голос на сайте Сonnect с запросом на эти функции.

SET NOCOUNT ON;
USE TSQLV4; -- TSQLV4 database: http://tsql.solidq.com/SampleDatabases/TSQLV4.zip

SELECT custid, MAX(val) AS maxval
FROM Sales.OrderValues
GROUP BY custid;

SELECT custid,
  LAST_VALUE(val) WITHIN GROUP(ORDER BY orderdate, orderid) AS lastorderval
FROM Sales.OrderValues
GROUP BY custid;

CREATE UNIQUE INDEX idx_cid_odD_oidD
  ON Sales.Orders(custid, orderdate DESC, orderid DESC);

DECLARE @custid AS INT = 1;

SELECT
  STUFF(
    (SELECT ',' + CAST(orderid AS VARCHAR(20)) AS [text()]
     FROM Sales.Orders
     WHERE custid = @custid
     ORDER BY orderdate DESC, orderid DESC
     FOR XML PATH('')), 1, 1, NULL) AS orderids;

SELECT C.custid,
  STUFF(
    (SELECT ',' + CAST(orderid AS VARCHAR(20)) AS [text()]
     FROM Sales.Orders AS O
     WHERE O.custid = C.custid
     ORDER BY orderdate DESC, orderid DESC
     FOR XML PATH('')), 1, 1, NULL) AS orderids
FROM Sales.Customers AS C;

SELECT custid,
  STRING_AGG(CAST(orderid AS VARCHAR(20)), ',')
    WITHIN GROUP(ORDER BY orderdate DESC, orderid DESC) AS orderids
FROM Sales.Orders
GROUP BY custid;

DROP INDEX idx_cid_odD_oidD ON Sales.Orders;

SELECT custid,
  FIRST_VALUE(val) WITHIN GROUP(ORDER BY orderdate, orderid) AS firstorderval,
  LAST_VALUE(val) WITHIN GROUP(ORDER BY orderdate, orderid) AS lastorderval
FROM Sales.OrderValues
GROUP BY custid;

SELECT custid,
  FIRST_VALUE(val)
    OVER(PARTITION BY custid
         ORDER BY orderdate, orderid
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS firstorderval,
  LAST_VALUE(val)
    OVER(PARTITION BY custid
         ORDER BY orderdate, orderid
         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastorderval
FROM Sales.OrderValues;

SELECT DISTINCT custid,
  FIRST_VALUE(val)
    OVER(PARTITION BY custid
         ORDER BY orderdate, orderid
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS firstorderval,
  LAST_VALUE(val)
    OVER(PARTITION BY custid
         ORDER BY orderdate, orderid
         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastorderval
FROM Sales.OrderValues;

WITH C AS
(
  SELECT custid, val,
    ROW_NUMBER()
      OVER(PARTITION BY custid
           ORDER BY orderdate, orderid) AS rownumasc,
    ROW_NUMBER()
      OVER(PARTITION BY custid
           ORDER BY orderdate DESC, orderid DESC) AS rownumdesc
  FROM Sales.OrderValues
)
SELECT custid,
  MAX(CASE WHEN rownumasc = 1 THEN val END) AS firstorderval,
  MAX(CASE WHEN rownumdesc = 1 THEN val END) AS lastorderval
FROM C
WHERE rownumasc = 1 OR rownumdesc = 1
GROUP BY custid;

SELECT custid,
  PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY val) AS medianvalcont,
  PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY val) AS medianvaldisc
FROM Sales.OrderValues
GROUP BY custid;

SELECT DISTINCT custid,
  PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY val)
    OVER(PARTITION BY custid) AS medianvalcont,
  PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY val)
    OVER(PARTITION BY custid) AS medianvaldisc
FROM Sales.OrderValues;