Запросы к оконным функциям T-SQL

167 Исходник базы данных

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

Логическая обработка запросов

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

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

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

На следующем рисунке представлена схема логической обработки запроса в SQL Server 2012:

Обработка запросов T-SQL

Заметьте, что при написании запроса предложение SELECT всегда пишется первым, но в процессе логической обработки оно находится практически в самом конце - непосредственно перед обработкой предложения ORDER BY.

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

  1. FROM

  2. WHERE

  3. GROUP BY

  4. HAVING

  5. SELECT

    1. Вычисление выражений

    2. Удаление дубликатов

  6. ORDER BY

  7. OFFSET-FETCH/TOP

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

Предложения, поддерживающие оконные функции

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

Я попытаюсь продемонстрировать эту неоднозначность на примере. Сначала выполните следующий код, чтобы создать таблицу T1 и наполнить ее данными:

SET NOCOUNT ON;
USE TSQL2012;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
GO

CREATE TABLE dbo.T1
(
  col1 VARCHAR(10) NOT NULL
    CONSTRAINT PK_T1 PRIMARY KEY
);

INSERT INTO dbo.T1(col1) 
  VALUES('A'),('B'),('C'),('D'),('E'),('F');
GO

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

SELECT col1
FROM dbo.T1
WHERE col1 > 'B'
  AND ROW_NUMBER() OVER(ORDER BY col1) <= 3;

Прежде чем говорить, что это очевидно, что это должны быть значения C, D и Е, вспомните о принципе «все сразу» в SQL. Этот принцип подразумевает, что с точки зрения концепции все выражения одного логического этапа выполняются одновременно. Это значит, что порядок следования выражений не должен влиять на результат. Если так, то следующий запрос должен быть семантически эквивалентен такому:

SELECT col1
FROM dbo.T1
WHERE ROW_NUMBER() OVER(ORDER BY col1) <= 3
  AND col1 > 'B';

Сможете ли вы на этот раз определить, какое выражение правильное? Это C, D и Е или только C?

Это пример неоднозначности, о которой я говорил. Разрешение использовать оконные функции только в предложениях SELECT и ORDER BY позволяет избавиться от этой неоднозначности.

При анализе блок-схемы на рисунке выше вы могли заметить, что на этапе SELECT оконные функции поддерживает шаг 5-1 (Вычисление выражений) и он выполняет перед шагом 5-2 (Удаление дубликатов). Если вы спросите, почему так важно знать такие детали, я продемонстрирую, зачем это нужно.

Вот вопрос, возвращающий атрибуты empid и country всех сотрудников из таблицы сотрудников Employees:

SELECT empid, country
FROM HR.Employees;
Простой оператор SELECT

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

SELECT DISTINCT country, ROW_NUMBER() OVER(ORDER BY country) AS rownum
FROM HR.Employees;

Некоторые будут ожидать такой результат:

Ожидаемый результат вызова ROW_NUMBER в предложении SELECT

Но на самом деле вы получите это:

Настоящий результат вызова ROW_NUMBER в предложении SELECT

А теперь вспомните, что в этом запросе функция ROW_NUMBER вычисляется на шаге 5-1, на котором вычисляются выражения списка SELECT - до удаления дубликатов на шаге 5-2. Функция ROW_NUMBER назначает девять уникальных номеров строк, содержащих информацию о сотрудниках, поэтому предложению DISTINCT нечего удалять.

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

WITH EmpCountries AS
(
  SELECT DISTINCT country FROM HR.Employees
)
SELECT country, ROW_NUMBER() OVER(ORDER BY country) AS rownum
FROM EmpCountries;

Можете ли вы представить себе другие способы решения задачи, по крайней мере проще, чем это?

Тот факт, что оконные функции оцениваются на этапе SELECT или ORDER BY означает, что окно, определенное для вычисления, - до применения последующих ограничений - является промежуточной формой строк, полученной после всех предшествующих фаз, то есть после применения FROM со всеми табличными операторами (например, соединениями), а также фильтрации с применением WHERE, группировки и фильтрации групп. Такой запрос можно считать примером:

SELECT O.empid,
  SUM(OD.qty) AS qty,
  RANK() OVER(ORDER BY SUM(OD.qty) DESC) AS rnk
FROM Sales.Orders AS O
  JOIN Sales.OrderDetails AS OD ON O.orderid = OD.orderid
WHERE O.orderdate >= '20070101' AND O.orderdate < '20080101'
GROUP BY O.empid;
Порядок вычисления оконной функции

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

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

В обход ограничений

Я объяснил, почему запретили использование оконных функций на этапах логической обработки запроса, предшествующих предложению SELECT. Но что, если нужно выполнять фильтрацию или группировку на основе вычислений, выполненных в оконных функциях? Решение заключается в использовании табличного выражения, такого как CTE или производная таблица. Заставьте запрос вызывать оконную функцию в его списке SELECT, назначив выражению псевдоним. Определите на основе этого запроса табличное выражение, после чего сошлитесь на него в запросе по псевдониму.

Вот пример, демонстрирующий, как можно фильтровать на основе результатов оконной функции с использованием CTE:

WITH C AS
(
  SELECT orderid, orderdate, val,
    RANK() OVER(ORDER BY val DESC) AS rnk
  FROM Sales.OrderValues
)
SELECT *
FROM C
WHERE rnk <= 5;
Вызов оконной функции с предложением WHERE

В инструкциях, изменяющих данные, оконные функции полностью запрещены, потому что в этих инструкциях не поддерживаются предложения SELECT и ORDER BY. Но есть случаи, когда оконные функции нужны в изменяющих данные инструкциях. Табличные выражения позволяют решить и эту проблему, потому что T-SQL позволяет менять данные через табличные выражения. Продемонстрирую это поведение на примере UPDATE. Сначала выполните следующий код, чтобы создать таблицу T1 со столбцами col1 и col2 и наполнить ее данными:

SET NOCOUNT ON;
USE TSQL2012;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
GO

CREATE TABLE dbo.T1
(
  col1 INT NULL,
  col2 VARCHAR(10) NOT NULL
);

INSERT INTO dbo.T1(col2) 
  VALUES('C'),('A'),('B'),('A'),('C'),('B');
GO

Значения столбца col2 определены явно, a col1 был заполнен значениями NULL.

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

UPDATE dbo.T1
   SET col1 = ROW_NUMBER() OVER(ORDER BY col2);

Но, как вы помните, в такой инструкции это запрещено. Обходной способ заключается в создании запроса по отношению к T1, который возвращает col1, и выражения, основанного на функции ROW_NUMBER (назовем ее rownum); определите табличное выражение, основанное на этом запросе, и, наконец, примените инструкцию UPDATE к CTE для присвоения значения rownum столбцу col1:

WITH C AS
(
  SELECT col1, col2,
    ROW_NUMBER() OVER(ORDER BY col2) AS rownum
  FROM dbo.T1
)
UPDATE C
  SET col1 = rownum;

SELECT col1, col2
FROM dbo.T1;

Получите данные из T1 - вы увидите, что все строки получили уникальное значение в столбце col1:

Обновление таблицы с помощью оконной функции

Возможность создания дополнительных фильтров

Я показал, как в T-SQL можно прибегнуть к обходному решению и косвенным образом использовать оконные функции в элементах, которые не поддерживают их напрямую. Это обходное решение основано на применении табличного выражения в форме CTE или производной таблицы. Приятно иметь дополнительный вариант, но в табличном выражении используется дополнительный уровень запроса и все немного усложняется. Приведенные мной примеры просты, но как насчет длинных и сложных запросов. Возможно ли более простое решение без этого дополнительного уровня?

Если говорить об оконных функциях, то в SQL Server на текущий момент нет другого решения. Вместе с тем, интересно посмотреть, как другие справляются с этой проблемой. Например, в Teradata создали фильтрующее предложение, которое называется QUALIFY и принципиально вычисляется после предложения SELECT. Это означает, что в нем можно напрямую обращаться к оконным функциям, как в следующем примере:

-- Не работает в SQL Server 2012
SELECT orderid, orderdate, val
FROM Sales.OrderValues
QUALIFY RANK() OVER(ORDER BY val DESC) <= 5;

Более того, можно ссылаться на псевдонимы столбцов, определенных в списке SELECT, так:

-- Не работает в SQL Server 2012
SELECT orderid, orderdate, val,
  RANK() OVER(ORDER BY val DESC) AS rnk
FROM Sales.OrderValues
QUALIFY rnk <= 5;

Предложения QUALIFY нет в стандартном SQL - оно поддерживается только в продуктах Teradata. Но оно кажется очень интересным решением, и было бы неплохо, если бы и стандарт, и в SQL Server удовлетворили такую потребность.

Повторное использование определений окон

Представьте, что вам нужно вызвать несколько оконных функций в одном запросе, при этом часть определения окна (или все определение) у нескольких функций совпадает. Если указать определение окна во всех функциях, код может сильно увеличиться в объеме, как в этом примере:

SELECT empid, ordermonth, qty,
  SUM(qty) OVER (PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS run_sum_qty,
  AVG(qty) OVER (PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS run_avg_qty,
  MIN(qty) OVER (PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS run_min_qty,
  MAX(qty) OVER (PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS run_max_qty
FROM Sales.EmpOrders;

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

SQL Server пока не поддерживает предложение WINDOW. В стандартном SQL можно сократить предыдущий запрос с использованием предложения WINDOW так:

-- Не работает в SQL Server 2012
SELECT empid, ordermonth, qty,
  SUM(qty) OVER W1 AS run_sum_qty,
  AVG(qty) OVER W1 AS run_avg_qty,
  MIN(qty) OVER W1 AS run_min_qty,
  MAX(qty) OVER W1 AS run_max_qty
FROM Sales.EmpOrders
WINDOW W1 AS ( PARTITION BY empid
               ORDER BY ordermonth
               ROWS BETWEEN UNBOUNDED PRECEDING
                        AND CURRENT ROW );

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

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

Пройди тесты
Лучший чат для C# программистов