Логическая обработка запросов: предложение FROM и оператор APPLY

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

В предыдущей статье мы начали рассматривать предложения FROM, сделав акцент на объединениях. На этот раз я продолжу тему, но теперь основное внимание будет уделено оператору APPLY. В этой статье я буду пользоваться той же демонстрационной базой данных TSQLV4, которая использовалась в предыдущих статьях. Исходный код для создания и заполнения этой базы вы можете найти в предыдущей статье, опубликованной в этом же номере журнала. Для использования демонстрационной базы данных в вашем сеансе запустите следующий код:

USE TSQLV4;

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

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

Оператор APPLY используется как средство языка T-SQL; подобно оператору JOIN, он является табличным оператором. В чем-то он подобен объединению, а в чем-то отличается от него. Сначала я покажу, в чем состоят ограничения объединений и подзапросов. Затем мы рассмотрим, как оператор APPLY помогает преодолевать эти ограничения. В заключение я объясню, каким образом мы можем задействовать оператор APPLY для определения псевдонимов столбцов и обеспечить их использование на первых этапах логической обработки запросов

Ограничения объединений и подзапросов

Причина, побудившая разработчиков Microsoft включить в T-SQL оператор APPLY, состояла в том, что подзапросы и объединения, альтернативой которым должен был стать этот оператор, имеют ряд ограничений. Приведу пример. Допустим, вам необходимо написать запрос к базе TSQLV4, который возвращает последний по времени заказ от каждого клиента. В запросе будут использоваться таблицы Sales.Customers и Sales.Orders. Из таблицы Customers вы должны извлечь идентификатор клиента и название компании, а из квалифицирующих строк таблицы Orders — идентификатор заказа, дату заказа, а также идентификатор служащего.

Эта задача известна как задача выделения в группе N элементов с максимальными показателями. Следующий код создает рекомендуемый индекс в таблице Orders для типичных запросных решений для данной задачи:

CREATE INDEX idx_poc
  ON Sales.Orders(custid, orderdate DESC,
  orderid DESC)
  INCLUDE(empid);

Для себя этот индекс я называю «индексом POC», где аббревиатура POC означает partitioning, ordering and covering, или выделение элементов, их упорядочение и наполнение данными. Он определяет список ключей на основе элемента выделения (custid), за которым следует элемент упорядочения (orderdate DESC, orderid DESC). Для наполнения в список включаются оставшиеся элементы запроса (empid).

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

SELECT C.custid, C.companyname,
  ( SELECT TOP (1) O.orderid,
  O.orderdate, O.empid
    FROM Sales.Orders AS O
    WHERE O.custid = C.custid
    ORDER BY O.orderdate DESC,
    O.orderid DESC )
FROM Sales.Customers AS C;

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

Msg 116, Level 16, State 1, Line 24

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

Мы можем выполнять для каждого столбца особый подзапрос таким, например, образом, как показано в листинге 1.

Это довольно громоздкое решение. Почти вся логика запроса повторяется трижды, что затрудняет его обслуживание. Кроме того, по состоянию на сегодня оптимизатор SQL Server не пытается внутренними средствами свести логику трех подзапросов к одной операции физического доступа к данным. Это явно просматривается в плане выполнения запроса, представленного на рисунке 1 (с помощью средства Plan Explorer программы SQL Sentry).

Рисунок 1. План выполнения трех подзапросов

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

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

Возможно, вы полагаете, что эта задача решается с использованием перекрестного объединения, как, например, в листинге 2.

Однако надо отметить, что в процессе объединения два набора входных данных рассматриваются как множество, а множество не имеет атрибута упорядочения. Это означает, что на одной стороне объединения мы не имеем возможности ссылаться на элементы с другой его стороны. Здесь производный табличный запрос к таблице Orders коррелируется через фильтр с элементом из таблицы Customers (C.custid). И только на последующих этапах логической обработки запроса будет предоставляться доступ к элементам с обеих сторон объединения. Если вы попытаетесь выполнить данный запрос, то получите следующее сообщение об ошибке:

Msg 4104, Level 16, State 1, Line 52
The multi-part identifier "C.custid"
   could not be bound

То есть состоящий из нескольких частей идентификатор C.custid не может быть связан.

APPLY идет на помощь

Оператор APPLY как раз создавался для решения описанных мною проблем, имеющих отношение к подзапросам и объединениям. Существует два вида таких операторов; один из них именуется CROSS APPLY; он реализует только один логический шаг в обработке. Второй вид — OUTER APPLY, он реализует два шага.

Оператор CROSS APPLY во многом подобен оператору CROSS JOIN, только он не рассматривает два входящих набора данных как множества. Вместо этого данный оператор прежде всего оценивает левый входной набор, а затем применяет правую сторону к каждой строке в левой части. Что же из этого следует? Если правая часть представляет собой табличное выражение, такое как производная таблица, внутри этого выражения вы можете обращаться к элементам левой стороны. Таким образом, если в последнем запросе вы замените оператор CROSS JOIN оператором CROSS APPLY, запрос станет допустимым и решит нашу задачу. Код, которым нужно воспользоваться, указан в листинге 3.

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

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

Рисунок 2. План выполнения для CROSS APPLY

Следует отметить, что для каждого клиента существует лишь одна возможность доступа к индексу POC.

Как уже говорилось, оператор APPLY применяется только в среде T-SQL. Любопытно, что стандарт SQL включает в себя аналогичное средство, так называемую вторичную производную таблицу, lateral derived table. Стандартное решение, параллельное применению CROSS APPLY, состоит в использовании оператора CROSS JOIN, однако перед производной таблицей следует в виде префикса указывать ключевое слово LATERAL, которое будет квалифицировать производную таблицу как вторичную. Оператор начинает обработку с левой стороны и применяет вторичную производную таблицу к каждой строке слева. Язык T-SQL не предоставляет возможности работать со вторичными производными таблицами, поскольку предусматривает собственное решение с помощью оператора APPLY. Но если бы такая возможность была, соответствующий код выглядел бы так, как в листинге 4. Не пытайтесь запускать его, так как этот код не поддерживается системой SQL Server.

Как уже отмечалось, оператор CROSS APPLY реализует только один этап логической обработки запроса: применение выражения из правой таблицы к каждой строке с левой стороны. Если правая сторона возвращает пустой набор для данной строки слева, оператор CROSS APPLY не будет возвращать эту строку. Это напоминает случай с перекрестным объединением таблицы с одной строкой и таблицы с нулевым количеством строк: в ответ вы получаете пустой набор данных. Если вы хотите сохранить все левые строки, используйте второй тип оператора, а именно OUTER APPLY. Этот оператор включает в себя второй логический шаг, сохраняющий внешние строки. Подобно оператору LEFT OUTER JOIN, оператор OUTER APPLY использует значения NULL в качестве заполнителей на правой стороне, поскольку соответствий нет.

В результате выполнения предыдущего запроса, в котором применяется оператор CROSS APPLY, мы получаем 263 строки (см. рисунок 3).

Рисунок 3. Результат выполнения запроса с оператором CROSS APPLY

В полученный результат не входят два клиента, представленные в таблице Customers, но не в таблице Orders.

Во фрагменте кода в листинге 5 оператор CROSS APPLY из предыдущего примера заменяется оператором OUTER APPLY.

Этот запрос возвращает 265 строк, в которые включены два клиента, не имеющие соответствующих заказов (см. рисунок 4).

Рисунок 4. В результаты включены два утерянных ранее клиента

Если вас интересует, какова стандартная альтернатива применению оператора OUTER APPLY, могу сказать, что она состоит в использовании оператора LEFT OUTER JOIN со вторичной производной таблицей и с предикатом объединения, всегда имеющим значение true, как в листинге 6 (опять-таки не запускайте этот код, ибо он не поддерживается системой SQL Server).

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

Рисунок 5. Блок-схема логической обработки запроса — APPLY

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

Далее внешний запрос может использовать эту функцию в качестве правого потока входных данных оператора APPLY вместо производной таблицы и передать C.custid, а также число недавних заказов в качестве входных данных:

SELECT C.custid, C.companyname,
   O.orderid, O.orderdate, O.empid
FROM Sales.Customers AS C
  CROSS APPLY Sales.GetTopOrders
  ( C.custid, 3 ) AS O;

Эта функция встраивается до этапа оптимизации, так что получаемый вами план запроса идентичен плану, показанному на рисунке 2.

Повторное использование псевдонимов столбцов

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

SELECT
  YEAR(orderdate) AS orderyear,
  MONTH(orderdate) AS ordermonth,
  COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY orderyear, ordermonth;

Возникает у вас ощущение, что здесь что-то не так? Если нет, попробуйте запустить этот код. Вы получите сообщение об ошибке (см. рисунок 6).

Рисунок 6. Сообщение об ошибке при использовании псевдонимов

В рамках логической обработки запроса предложение SELECT анализируется после предложения GROUP, а не наоборот. Псевдонимы, назначенные внутри предложения SELECT, недоступны выражениям, которые появляются на более ранних этапах логической обработки запросов. Следовательно, мы не можем ссылаться на такие псевдонимы в предложении GROUP BY. По той же причине на эти псевдонимы нельзя ссылаться в предложении WHERE.

Этот вопрос мы рассмотрим более подробно в следующих статьях, где речь пойдет о предложениях WHERE и GROUP BY. Пока же я хочу остановиться на операторе APPLY. Поскольку APPLY является табличным оператором, он оценивается как часть предложения FROM, которое анализируется прежде всех остальных предложений запроса. Отсюда следует, что, если вы определяете псевдонимы столбцов с помощью APPLY, эти псевдонимы, естественно, будут доступны всем выражениям в остальной части предложений запроса. Вы можете разрешить проблемы, возникшие при выполнении предыдущего запроса, с помощью оператора APPLY и производной таблицы, основывающейся на предложении VALUES (известном как конструктор значений), используя код из листинга 8.

Предложение VALUES определяет таблицу, состоящую из одной строки с двумя столбцами. Оно присваивает псевдоним столбца orderyear выражению YEAR (orderdate) и псевдоним ordermonth выражению MONTH (orderdate). Обратите внимание: APPLY обеспечивает доступ к выражениям на правой стороне столбцу orderdate с левой стороны. Как уже отмечалось, поскольку псевдонимы создаются на первых этапах процесса логической обработки запроса, они становятся доступными для предложений запроса, которые анализируются в ходе последующих этапов, например для предложения GROUP BY в данном запросе.

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

Обратите внимание на попытку сослаться на псевдоним orderyear в выражении, которое определяет столбец endofyear. Поскольку orderyear определяется на том же шаге логической обработки запроса, что и endofyear, ссылка на orderyear в этом выражении недопустима. Ссылка на псевдоним, созданный на том или ином этапе, допускается только из выражений, появляющихся на последующих этапах, но не на том же самом этапе и тем более не на предшествующих этапах. Если вы попытаетесь запустить помещенный выше запрос, то получите сообщение об ошибке (см. рисунок 7).

Рисунок 7. Ошибка при использовании псевдонима, определенного на том же этапе обработки

Чтобы устранить эту проблему, в случаях, когда у вас возникает зависимость между выражениями, определяйте последние с помощью операторов APPLY в нужном порядке. В нашем случае столбец endofyear следует определить с помощью оператора APPLY, который помещается после оператора, определяющего orderyear. Это может выглядеть так, как в листинге 10.

Итак, вы получили представление о функционировании операторов JOIN и APPLY в процессе логической обработки запросов и теперь можете сами разобраться с тем, какую роль играет предложение FROM при выполнении сложного демонстрационного запроса. В листинге 11 показана часть этого запроса с полным предложением FROM и с упрощенным предложением SELECT.

Используя внутреннее объединение между таблицами Orders и OrderDetails, запрос сопоставляет заголовки заказов с соответствующими строками заказов и сохраняет только те заказы, которые были размещены за период с начала 2016 года. Поскольку это внутреннее объединение, заказы, размещенные до начала 2016 года, не сохраняются.

Запрос выполняет левое внешнее объединение между таблицей Customers и результатом внутреннего объединения между таблицами Orders и OrderDetails. Объединение является левым внешним, поэтому сохраняются все клиенты, даже те, которые не размещали заказы в период с начала 2016 года. У этих клиентов атрибуты с несохраненной стороны объединения будут иметь значение NULL.

Далее запрос создает с помощью оператора CROSS APPLY псевдонимы столбцов custlocation и val. Первый из них представляет собой составную строку, состоящую из атрибутов страны, региона и города с разделителями между элементами. Функция CONCAT объединяет входные данные в цепочку, заменяя значения NULL пустыми строками. Второй псевдоним — это значение строки заказов, рассчитанное как количество, умноженное на цену единицы продукции; при этом учитывается скидка. Поскольку эти псевдонимы столбцов создаются в предложении FROM, они доступны для всех остающихся предложений в запросе.

Предложение SELECT в данном примере просто возвращает некоторые из атрибутов, включая те, что были рассчитаны с помощью оператора CROSS APPLY. Мы получаем на выходе рассматриваемого запроса данные, показанные на рисунке 8.

Рисунок 8. Результаты, полученные с помощью оператора CROSS APPLY

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

DROP INDEX idx_poc ON Sales.Orders;
DROP FUNCTION Sales.GetTopOrders;

Что дальше?

В данной статье я уделил основное внимание использованию табличного оператора APPLY в процессе логической обработки запросов. Я описал два вида этого оператора: CROSS APPLY и OUTER APPLY. Первый из них применяет выражение из правой таблицы к каждой строке с левой стороны, а второй к тому же сохраняет все левые строки. Мы рассмотрели преимущества, которые применение оператора APPLY дает по сравнению с использованием объединений и подзапросов. Кроме того, я объяснил, как оператор APPLY используется для создания псевдонимов столбцов для выражений на первых этапах процесса логической обработки запроса, так чтобы эти псевдонимы были доступны для всех остальных предложений запроса. В следующей статье я продолжу описание предложения FROM. Речь пойдет об использовании в процессе логической обработки запросов операторов PIVOT и UNPIVOT.

SELECT C.custid, C.companyname,
  ( SELECT TOP (1) O.orderid
    FROM Sales.Orders AS O
    WHERE O.custid = C.custid
    ORDER BY O.orderdate DESC, O.orderid DESC ) AS orderid,
  ( SELECT TOP (1) O.orderdate
    FROM Sales.Orders AS O
    WHERE O.custid = C.custid
    ORDER BY O.orderdate DESC, O.orderid DESC ) AS orderdate,
  ( SELECT TOP (1) O.empid
    FROM Sales.Orders AS O
    WHERE O.custid = C.custid
    ORDER BY O.orderdate DESC, O.orderid DESC ) AS empid
FROM Sales.Customers AS C;

SELECT C.custid, C.companyname, O.orderid, O.orderdate, O.empid
FROM Sales.Customers AS C
  CROSS JOIN ( SELECT TOP (3) O.orderid, O.orderdate, O.empid
               FROM Sales.Orders AS O
               WHERE O.custid = C.custid
               ORDER BY O.orderdate DESC, O.orderid DESC ) AS O;

SELECT C.custid, C.companyname, O.orderid, O.orderdate, O.empid
FROM Sales.Customers AS C
  CROSS APPLY ( SELECT TOP (3) O.orderid, O.orderdate, O.empid
                FROM Sales.Orders AS O
                WHERE O.custid = C.custid
                ORDER BY O.orderdate DESC, O.orderid DESC ) AS O;

SELECT C.custid, C.companyname, O.orderid, O.orderdate, O.empid
FROM Sales.Customers AS C
  CROSS JOIN LATERAL ( SELECT TOP (3) O.orderid, O.orderdate, O.empid
                       FROM Sales.Orders AS O
                       WHERE O.custid = C.custid
                       ORDER BY O.orderdate DESC, O.orderid DESC ) AS O;

SELECT C.custid, C.companyname, O.orderid, O.orderdate, O.empid
FROM Sales.Customers AS C
  OUTER APPLY ( SELECT TOP (3) O.orderid, O.orderdate, O.empid
                FROM Sales.Orders AS O
                WHERE O.custid = C.custid
                ORDER BY O.orderdate DESC, O.orderid DESC ) AS O;

SELECT C.custid, C.companyname, O.orderid, O.orderdate, O.empid
FROM Sales.Customers AS C
  LEFT OUTER JOIN LATERAL ( SELECT TOP (3) O.orderid, O.orderdate, O.empid
                            FROM Sales.Orders AS O
                            WHERE O.custid = C.custid
                            ORDER BY O.orderdate DESC, O.orderid DESC ) AS O
    ON 1 = 1;

CREATE FUNCTION Sales.GetTopOrders(@custid AS INT, @n AS BIGINT) RETURNS TABLE
AS
RETURN
  SELECT TOP (@n) O.orderid, O.orderdate, O.empid
  FROM Sales.Orders AS O
  WHERE O.custid = @custid
  ORDER BY O.orderdate DESC, O.orderid DESC;
GO

SELECT orderyear, ordermonth, COUNT(*) AS numorders
FROM Sales.Orders
  CROSS APPLY ( VALUES( YEAR(orderdate), MONTH(orderdate) ) )
    AS A(orderyear, ordermonth)
GROUP BY orderyear, ordermonth;

SELECT orderyear, ordermonth, COUNT(*) AS numorders
FROM Sales.Orders
  CROSS APPLY ( VALUES( YEAR(orderdate), MONTH(orderdate),
                        DATEFROMPARTS(orderyear, 12, 31) ) )
    AS A(orderyear, ordermonth, endofyear)
WHERE orderdate <> endofyear
GROUP BY orderyear, ordermonth;

SELECT orderyear, ordermonth, COUNT(*) AS numorders
FROM Sales.Orders
  CROSS APPLY ( VALUES( YEAR(orderdate), MONTH(orderdate) ) )
    AS A1(orderyear, ordermonth)
  CROSS APPLY ( VALUES( DATEFROMPARTS(orderyear, 12, 31) ) )
    AS A2(endofyear)
WHERE orderdate <> endofyear
GROUP BY orderyear, ordermonth;

SELECT
  C.custid,
  A.custlocation,
  O.orderid,
  OD.productid,
  A.val
FROM Sales.Customers AS C
  LEFT OUTER JOIN
      ( Sales.Orders AS O
          INNER JOIN Sales.OrderDetails AS OD
            ON O.orderid = OD.orderid
            AND O.orderdate >= '20160101' )
    ON C.custid = O.custid
  CROSS APPLY ( VALUES( CONCAT(C.country, N'.' + C.region, N'.' + C.city),
                        OD.qty * OD.unitprice * (1 - OD.discount) )
              ) AS A(custlocation, val);