Вложенные операторы в агрегатных функциях T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Вложенные операторы в агрегатных функциях

Дополнительные варианты фильтрации

Как вы помните, различные элементы определения окна (секционирование, упорядочение и кадрирование) по сути являются различными вариантами фильтрации. Существуют другие потребности в фильтрации, которые эти определения не в состоянии удовлетворить. Некоторые из этих потребностей удается удовлетворить с помощью предложения FILTER, которое не было реализовано в SQL Server 2012. Есть также попытки решить эту проблему за счет внесения предложений по расширению стандарта, которые, я надеюсь, так или иначе появятся в стандарте и SQL Server.

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

<функция агрегирования>(<входное выражение>) FILTER (WHERE <условие поиска>)

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

-- Не работает в T-SQL
SELECT empid, ordermonth, qty,
  qty - AVG(qty)
          FILTER (WHERE ordermonth <= DATEADD(month, -3, CURRENT_TIMESTAMP))
          OVER(PARTITION BY empid) AS diff
FROM Sales.EmpOrders;

SQL Server 2012 пока не поддерживает предложение FILTER. Честно говоря, я не знаю СУБД, которая поддерживала его. Если вам нужна такая возможность, существует довольно простое альтернативное решение — использовать в качестве входных данных для функции агрегирования выражение CASE:

<функция агрегирования>(CASE WHEN <условие поиска> THEN <входное выражение> END)

Вот полный запрос, который решает ту же задачу:

SELECT empid, ordermonth, qty,
  qty - AVG(CASE WHEN ordermonth <= DATEADD(month, -3, CURRENT_TIMESTAMP) THEN qty END)
          OVER(PARTITION BY empid) AS diff
FROM Sales.EmpOrders;

Чего все еще не хватает в стандарте (начиная с версии SQL 2008) и SQL Server 2012, так это возможности ссылаться на элементы текущей строки для целей фильтрации. Это можно было бы применять в предложении FILTER, в альтернативном решении с использованием выражения CASE, а также в других случаях, в которых нужна фильтрация.

Для демонстрации этой потребности представьте на секундочку, что на элемент текущей строки можно ссылаться с помощью префикса $current_row. А теперь представим себе, что нужно написать запрос представления Sales.OrderValues, который бы вычислял для каждого заказа разницу между значением текущего заказа и средним значением для определенного сотрудника для всех клиентов кроме того клиента, которому принадлежит этот заказ. Эта задача решается следующим запросом с предложением FILTER:

-- Не работает в T-SQL
SELECT orderid, orderdate, empid, custid, val,
  val - AVG(val)
          FILTER (WHERE custid <> $current_row.custid)
          OVER(PARTITION BY empid) AS diff
FROM Sales.OrderValues;

В качестве альтернативы можно воспользоваться выражением CASE:

-- Не работает в T-SQL
SELECT orderid, orderdate, empid, custid, val,
  val - AVG(CASE WHEN custid <> $current_row.custid THEN val END)
          OVER(PARTITION BY empid) AS diff
FROM Sales.OrderValues;

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

Предложение по улучшению

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

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

Паттерны в последовательностях строк определяются с применением семантики, похожей на регулярные выражения. Этот механизм может применяться для определения табличного выражения, а также для фильтрации строк в определении окна. Он также может использоваться в технологиях потоковой передачи данных, например с StreamInsight в SQL Server, а также в запросах, которые работают с неперемещаемыми данными. Вот ссылка на предоставленный для всеобщего доступа документ: http://www.softwareworkshop.com/h2/SQL-RPR-review-paper.pdf. Прежде чем читать этот документ, я предлагаю освободить голову от лишних мыслей и хорошенько взбодриться кофе. Это непросто чтение, но идея исключительно интересна и я надеюсь, что она пробьет себе путь в стандарт SQL и будет использоваться не только для данных в движении, но и для неактивных данных.

Ключевое слово DISTINCT в функциях агрегирования

SQL Server 2012 не поддерживает параметр DISTINCT в оконных функциях агрегирования. Представьте, что вам нужно запрашивать представление Sales.OrderValues и получить для каждого заказа число конкретных клиентов, с которыми работал текущий сотрудник с начала и до текущей даты. Вам нужно выполнить такой запрос:

-- Не работает в T-SQL
SELECT empid, orderdate, orderid, val,
  COUNT(DISTINCT custid) OVER(PARTITION BY empid
                              ORDER BY orderdate) AS numcusts
FROM Sales.OrderValues;

Но поскольку этот запрос не поддерживается, нужно искать обходное решение. Один из вариантов — прибегнуть к помощи функции ROW_NUMBER. Я расскажу о ней подробнее чуть попозже, а пока достаточно будет сказать, что она возвращает уникальное целое значение для каждой строки секции, начиная с единицы и с шагом 1, в соответствии с определением упорядочения в окне. С помощью функции ROW_NUMBER можно назначить строкам номера, секционированные по empid и custid и упорядоченные по orderdate. Это означает, что строки с номером 1 относятся к первому случаю работы сотрудника с данным клиентом при упорядочении заказов по датам. Используя выражение CASE можно вернуть значение custid, только если номер строки равен 1, а в противном случае вернуть NULL. Вот запрос, реализующий описанную логику, с результатом его работы:

SELECT empid, orderdate, orderid, custid, val,
  CASE 
    WHEN ROW_NUMBER() OVER(PARTITION BY empid, custid
                           ORDER BY orderdate) = 1
      THEN custid
  END AS distinct_custid
FROM Sales.OrderValues;

Заметьте, что для каждого сотрудника возвращается только первое значение custid при условии упорядочения по дате, а для последующих значений возвращаются NULL. Следующий шаг заключается в определении обобщенного табличного значения (CTE) на основе предыдущего запроса, а затем применении агрегирования текущего числа строк к результату выражения CASE:

WITH C AS
(
  SELECT empid, orderdate, orderid, custid, val,
    CASE 
      WHEN ROW_NUMBER() OVER(PARTITION BY empid, custid
                             ORDER BY orderdate) = 1
        THEN custid
    END AS distinct_custid
  FROM Sales.OrderValues
)
SELECT empid, orderdate, orderid, val,
  COUNT(distinct_custid) OVER(PARTITION BY empid
                              ORDER BY orderdate) AS numcusts
FROM C;

Вложенные агрегаты

На данный момент вы знаете, что есть групповые и оконные агрегаты. Как уже говорилось, функции при этом используются одинаковые, но контекст разный. Групповые агрегаты работают на основе групп строк, определенных предложением GROUP BY и возвращают одно значение на группу. Оконные агрегаты действуют на основе окон строк и возвращают одно значение для каждой строки в базовом запросе. Вспомните рассказ о логической обработке запросов из статьи Запросы. Напомню порядок, в котором в соответствии с концепцией должны обрабатываться различные предложения запросов:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

Групповые агрегаты используются, когда запрос является групповым, и они разрешены в фазах, которые обрабатываются после определения групп, а именно, начиная с фазы 4 и далее. Помните, что в результате запроса каждая группа представлена только одной строкой. Оконные агрегаты разрешены, начиная с фазы 5 и последующих, потому что они работают на основе строк базового запроса — после фазы HAVING.

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

Это совершенно легальный, но на первый взгляд странный подход — применять оконный агрегат к окну, содержащему строки с атрибутами, полученными с применением групповых агрегатов. Я сказал «странный», потому что на первый взгляд выражение SUM(SUM(val)) в запросе выглядит неуместным. Но оно имеет право на существование. Посмотрите на запрос, который решает поставленную задачу:

SELECT empid,
  SUM(val) AS emptotal,
  SUM(val) / SUM(SUM(val)) OVER() * 100. AS pct
FROM Sales.OrderValues
GROUP BY empid;

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

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

SELECT empid,
  SUM(val) AS emptotal
FROM Sales.OrderValues
GROUP BY empid;

Этот результат можно считать начальной точкой для дальнейшего оконной агрегации. Таким образом, можно применить агрегат SUM к выражению, представленному псевдонимом emptotal. К сожалению нельзя применить его непосредственно к псевдониму по причинам, изложенным ранее (помните принцип «все сразу»?). Но его можно применить к базовому выражению так: SUM(SUM(val)) OVER(...) и можно считать, что это SUM(emptotal) OVER(...). Таким образом получаем следующее:

SELECT empid,
  SUM(val) AS emptotal,
  SUM(val) / SUM(SUM(val)) OVER() * 100. AS pct
FROM Sales.OrderValues
GROUP BY empid;

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

WITH C AS
(
  SELECT empid,
    SUM(val) AS emptotal
  FROM Sales.OrderValues
  GROUP BY empid
)
SELECT empid, emptotal,
  emptotal / SUM(emptotal) OVER() * 100. AS pct
FROM C;

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

WITH C AS
(
  SELECT empid, orderdate,
    CASE 
      WHEN ROW_NUMBER() OVER(PARTITION BY empid, custid
                             ORDER BY orderdate) = 1
        THEN custid
    END AS distinct_custid
  FROM Sales.Orders
)
SELECT empid, orderdate,
  COUNT(distinct_custid) OVER(PARTITION BY empid
                              ORDER BY orderdate) AS numcusts
FROM C
GROUP BY empid, orderdate;

Но при выполнении запроса вы получаете следующую ошибку:

Column 'C.distinct_custid' is invalid in the select list because it 
is not contained in either an aggregate function or the GROUP BY clause.

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

SELECT empid, orderdate, distinct_custid
FROM C
GROUP BY empid, orderdate;

Ясно, что ответ отрицательный. Атрибут distinct_custid в списке SELECT неверен, потому что не содержится ни в агрегирующей функции, ни в предложении GROUP BY, и примерно об этом говорится в сообщении об ошибке. Что вам нужно сделать, так это применить оконный агрегат SUM с кадром, реализующим принцип нарастающего итога, к групповому агрегату COUNT, который считает конкретные вхождения:

WITH C AS
(
  SELECT empid, orderdate,
    CASE 
      WHEN ROW_NUMBER() OVER(PARTITION BY empid, custid
                             ORDER BY orderdate) = 1
        THEN custid
    END AS distinct_custid
  FROM Sales.Orders
)
SELECT empid, orderdate,
  SUM(COUNT(distinct_custid)) OVER(PARTITION BY empid
                                   ORDER BY orderdate) AS numcusts
FROM C
GROUP BY empid, orderdate;

Ясно, что это не единственный способ получения нужного результата, но моей задачей было проиллюстрировать принцип вложения групповых агрегатов в оконные. Как вы помните, в соответствии с порядком логической обработки запросов оконные функции обрабатываются на этапе SELECT или ORDER BY, то есть после GROUP BY. По этой причине групповые агрегаты видны в качестве входных выражений оконных агрегатов. Также вспомните, что если код становится сложным для понимания, всегда можно задействовать табличные выражения, чтобы избежать прямого сложения функций и повысить читабельность кода.