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

Данная статья завершает серию публикаций о логической обработке запросов. В ней будут описаны последние шаги в логической обработке запросов, связанные с предложениями SELECT и ORDER BY и фильтрами TOP и OFFSET-FETCH.

Логическая обработка запросов определяет концептуальную интерпретацию запросов. Глубокое понимание этой темы — ключевое условие для подготовки корректных и эффективных запросов. В предыдущих статьях серии я представил обзор темы и описал некоторые важнейшие предложения запроса. Мы рассмотрели предложение FROM и табличные операторы, предложение WHERE, а также предложения GROUP BY и HAVING. В первой части (см. Windows IT Pro/RE № 3 за 2016 год) был дан обзор темы, приведена тестовая база данных TSQLV4 (http://tsql.solidq.com/SampleDatabases/TSQLV4.zip) и показаны примеры запросов, которые я назвал простым и сложным.

Полная блок-схема логической обработки запросов

На рисунке показана полная блок-схема для логической обработки запросов, содержащая шаг 5, на котором обрабатывается предложение SELECT, шаг 6, на котором обрабатывается предложение ORDER BY, и шаг 7, на котором применяются фильтры TOP и OFFSET-FETCH.

Рисунок. Полная блок-схема логической обработки запросов

Шаг 5, на котором выполняется предложение SELECT, обрабатывает результат шага 4, на котором применяется фильтр HAVING. Его можно разделить на два промежуточных шага: на шаге 5.1 вычисляются выражения в списке SELECT, а на шаге 5.2 обрабатывается предложение DISTINCT, если оно присутствует.

Шаг 6, на котором обрабатывается предложение ORDER BY, принимает в качестве входных данных результат шага 5. Без предложения ORDER BY во внешнем запросе нет гарантии упорядоченности представления результата запроса, так как результат считается реляционным. Поскольку во внешнем запросе имеется предложение ORDER BY, результат считается нереляционным (курсор), и упорядоченность представления гарантируется.

Кроме того, на рисунке показана обработка фильтров TOP и OFFSET-FETCH, которую можно рассматривать как шаг 7. Переключатель TOP фильтрует запрошенное количество или процент строк в указанном порядке, при наличии предложения ORDER BY. Если предложение ORDER BY отсутствует, порядок следует считать произвольным. Для фильтра OFFSET-FETCH необходимо наличие предложения ORDER BY. Он пропускает число строк, указанное в предложении OFFSET, и фильтрует число строк, указанное в предложении FETCH, на основе определенного порядка.

Применим шаги 5, 6 и 7 к нашим тестовым запросам. В статье «Логическая обработка запросов: GROUP BY и HAVING», опубликованной в предыдущем номере, описаны выходные данные шага 4 в обоих тестовых запросах. Напомню, что выходные данные шага 4 используются как входные данные шага 5. В листинге 1 содержится полный простой тестовый запрос.

На экране 1 приводится результат предложения HAVING (как описано в предыдущей статье), который используется как входные данные для шага 5.

Экран 1. Результат предложения HAVING как входные данные для шага 5

Поскольку запрос сгруппированный, строки в результатах шага 4 организованы в группах. На шаге 5 вычисляются выражения из списка SELECT для группы, что приведет к двум строкам для двух групп, а на шаге 6 эти строки упорядочены по numorders. На экране 2 приводится окончательный результат этого запроса.

Экран 2. Окончательный результат простого запроса для шага 5

Предложение ORDER BY появляется во внешнем запросе.

В листинге 2 представлен полный сложный тестовый запрос.

Как выглядит состояние данных перед применением шага 5, показано на экране 3.

Экран 3. Состояние данных перед применением шага 5 для сложного запроса

Строки в результатах шага 4 организованы в группы, так как запрос является сгруппированным. На данном этапе у нас пять групп. На шаге 5 вычисляются выражения в списке SELECT, что дает пять строк. Обратите внимание на любопытное сочетание группирования и окон в этом запросе. Выражение SUM (SUM (A.val)) OVER () вычисляет оконный общий итог сгруппированных итоговых значений. В предыдущей статье об этой функции было рассказано подробно.

На шаге 6 определено упорядочение на основе numorders (конкретное число заказов). Переключатель TOP фильтрует первые четыре строки на основе порядка numorders. Также имеется параметр WITH TIES, который включает связи с последней строкой, если они существуют. В данном случае связей с последней строкой нет, поэтому окончательный результат запроса содержит четыре строки, как показано на экране 4.

Экран 4. Окончательный результат сложного запроса

Предложение ORDER BY находится во внешнем запросе, поэтому порядок представления гарантирован.

В следующих разделах приводятся дополнительные сведения о шагах 5, 6 и 7.

Обработка предложения SELECT

Как уже отмечалось, на шаге 5 обрабатывается предложение SELECT. Он может быть разделен на два промежуточных шага. На шаге 5.1 вычисляется выражение в списке SELECT, а на шаге 5.2 обрабатывается предложение DISTINCT.

Шаг 5.1. Вычисление выражений

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

В реляционной модели заголовок отношения представляет собой набор атрибутов. Поскольку набор не упорядочен и не имеет дубликатов, атрибут идентифицируется по имени, а не по порядковому номеру. Кроме того, вы не можете дублировать имена атрибутов. В зависимости от контекста это условие не всегда действует в T-SQL (то же относится к SQL). Например, T-SQL позволяет запросу создать неименованный столбец, который является результатом вычислений, как показано в листинге 3.

Этот запрос формирует данные, показанные на экране 5.

Экран 5. Результат выполнения запроса с неименованным столбцом

Однако T-SQL применяет оба требования, если вы попытаетесь определить табличное выражение, такое как производная таблица, обобщенные табличные выражения (CTE), представление или встроенная функция, возвращающая табличное значение, на основе запроса. Например, программный код в листинге 4 выдаст ошибку.

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

Чтобы исправить ошибку, убедитесь, что имена назначены всем столбцам и что имена всех столбцов уникальны (см. листинг 5).

На этот раз представление будет построено успешно.

Выполните следующую команду для очистки:

DROP VIEW dbo.CustOrders;

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

Если вы попытаетесь выполнить этот запрос, будет получена ошибка, как показано на экране 6.

Экран 6. Ошибка при выполнении листинга 6

Чтобы сделать псевдоним, созданный одним из вычислений, доступным другому вычислению, необходимо определить псевдоним на шаге, предшествующем тому, на котором он применяется. Например, можно создать псевдоним с использованием оператора CROSS APPLY и задействовать его во втором операторе CROSS APPLY. Затем вы можете использовать результаты первого и второго операторов на всех последующих шагах, в том числе WHERE, GROUP BY, HAVING и SELECT. В листинге 7 приведен программный код, в котором реализован этот метод.

Другая важная особенность шага 5.1, о которой следует помнить, состоит в том, что на данном шаге вычисляется оконная функция. Она должна работать с набором результатов базового запроса (перед удалением дубликатов), и набор результатов базового запроса формируется, когда вы добираетесь до шага 5. До этого шага результат запроса только формируется. Не разрешается использовать оконные функции непосредственно на предыдущих шагах логической обработки запросов. Это означает, что если вам нужно воспользоваться оконной функцией на любом шаге, предшествующем шагу 5, делать это необходимо с помощью табличного выражения. Например, если вы хотите фильтровать позиции заказов в строках с номерами 21-30 по orderid, вам не удастся напрямую обратиться к оконной функции ROW_NUMBER в предложении WHERE; сделать это нужно косвенно с помощью табличного выражения (см. листинг 8).

Внутренний запрос вычисляет номера строк в предложении SELECT и назначает результирующему столбцу псевдоним rownum. Затем внешний запрос ссылается на псевдоним rownum в предложении WHERE.

Кстати, вы не можете использовать этот прием с оператором CROSS APPLY и предложением VALUES для назначения псевдонимов вычислениям на основе оконных функций. Дело в том, что оператор APPLY показывает на правой стороне только одну строку слева, а оконные функции должны увидеть результат запроса целиком. Поэтому при использовании оконных функций необходимо избрать более длинный путь с полным табличным выражением, как в последнем примере.

Шаг 5.2. Обработка предложения DISTINCT

Если в предложении SELECT присутствует предложение DISTINCT, то на шаге 5.2 удаляются дубликаты из результатов шага 5.1. Примечательно, что по реляционной теории тело отношения представляет собой набор и потому не может иметь дубликатов. Поэтому, например, запрос, который проецирует только атрибут страны на отношение Employee, должен возвращать конкретные страны, в которых находятся сотрудники. T-SQL, как и SQL, отклоняется от реляционной модели и допускает дубликаты в таблице. Реляционная модель отчасти основывается на теории множеств, а T-SQL — на теории мультимножеств. Мультимножество похоже на множество в том смысле, что оно не упорядочено, но отличается от множества тем, что допускает дубликаты. В частности, вы можете создать таблицу без ключа и таким образом разрешить дублированные строки. А запрос, возвращающий подмножество столбцов, может возвращать дубликаты. Рассмотрим следующий запрос:

SELECT country FROM HR.Employees;

В таблице девять сотрудников, и потому запрос возвращает девять строк с дублированными странами (см. экран 7).

Экран 7. Строки с дублированными данными

Если вы хотите удалить дубликаты, необходимо добавить явное предложение DISTINCT:

SELECT DISTINCT country
   FROM HR.Employees;

Этот запрос возвращает только две страны, как показано на экране 8.

Экран 8. Дубликаты убраны

Напомню, что это предложение DISTINCT обработано на шаге 5.2 и применяется после вычисления всех выражений на шаге 5.1. Это означает, что при наличии оконных функций они вычисляются до того, как применяется DISTINCT. В качестве примера попытайтесь определить, сколько строк будет возвращено запросом, приведенным в листинге 9, прежде чем выполнить его.

Если вы предположили, что две, то вы ошиблись. Перед применением шага 5.1 во входной таблице насчитывается 9 строк. Это означает, что функция ROW_NUMBER формирует 9 отдельных номеров строк в этих 9 строках. Дубликаты, которые могли бы быть удалены предложением DISTINCT, отсутствуют, и вы получаете выходные данные, как показано на экране 9.

Экран 9. Результат использования предложения DISTINCT с оконной функцией

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

Этот запрос формирует выходные данные, как на экране 10.

Экран 10. Результат вычисления номера строк для конкретных стран

Другой вариант — использовать GROUP BY вместо DISTINCT. Помните, что предложение GROUP BY обрабатывается на шаге 3, намного раньше шага 5, на котором обрабатывается предложение SELECT. Это означает, что любые оконные функции применяются на шаге 5.1, после группирования. В листинге 11 приводится полный запрос.

В результатах содержится две строки с двумя уникальными странами и соответствующими им номерами строк.

Обработка предложения ORDER BY

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

Предложение ORDER BY обрабатывается на шаге 6, после предложения SELECT, которое обрабатывается на шаге 5, поэтому разрешается ссылаться на псевдонимы, которые были созданы в предложении SELECT, в предложении ORDER BY. Это можно увидеть как в простом тестовом запросе, так и в сложном. Предложение SELECT подсчитывает число заказов, обозначая его псевдонимом numorders, а затем предложение ORDER BY ссылается на псевдоним numorders.

Обычно разрешается в предложении ORDER BY ссылаться на выражения, даже если они не появляются в предложении SELECT. Другими словами, можно выполнять упорядочение по величинам, которые вы не обязательно хотите возвращать. Однако, как правило, делать это разрешается при условии, что выражение было бы действительным, если бы было указано в предложении SELECT. Правила строже, если используется предложение DISTINCT. В этом случае предложение ORDER BY ограничено только выражениями, которые появляются в предложении SELECT. Например, запрос в листинге 12 неверен.

Такое ограничение основано на том, что одно уникальное значение может представлять несколько исходных строк, а выражение в предложении ORDER BY может иметь различные результаты для разных исходных строк, связанных с одной целевой строкой. Например, представьте себе запрос, который возвращает конкретные страны и заказы по идентификатору сотрудника. С одной страной может быть связано несколько различных идентификаторов. Поэтому T-SQL просто не поддерживает такие запросы. Но что если существует соответствие «один к одному» между результатами выражений SELECT и ORDER BY, как в листинге 12? Можно применить DISTINCT без упорядочения в одном запросе, определить табличное выражение на основе этого запроса, а затем выполнить упорядочение во внешнем запросе (см. листинг 13).

Этот запрос формирует выходные данные, показанные в сокращенном виде на экране 11.

Экран 11. Результаты использования обходного приема

ORDER BY, табличные выражения, TOP и OFFSET-FETCH

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

Если вы выполните программный код листинга 14, то будет получена ошибка, как на экране 12.

Экран 12. Ошибка при выполнении листинга 14

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

Если вы внимательно прочитаете сообщение об ошибке, то заметите, что требование отсутствия ORDER BY во внутреннем запросе снимается в исключительных случаях, например когда указаны фильтры OFFSET-FETCH или TOP. Эти фильтры применяются к результату шага 5.2 и полагаются на предложение ORDER BY, как будто оно часть спецификации фильтра. Теоретически эти фильтры могли быть спроектированы с собственными спецификациями упорядочения, которые не следует путать с порядком представления. Но, к сожалению, в действительности это не так. Поэтому, когда вы используете эти фильтры во внутреннем запросе, разрешается добавить предложение ORDER BY для поддержки фильтра. Например, действительно определение представления, как в листинге 15.

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

SELECT *
FROM Sales.MyView;

Вы гарантированно получаете три заказа с высшими значениями, но, поскольку внешний запрос не имеет предложения ORDER BY, строки не обязательно следуют в каком-то определенном порядке. Есть вероятность, что строки будут упорядочены, так как SQL Server использует алгоритм с упорядочением для обработки TOP и OFFSET-FETCH, чтобы выяснить, какие строки следует фильтровать. Порядок строк не перестраивается просто ради того, чтобы представить строки сортированными, так как для этого потребуется больше усилий. Любой порядок строк в выходных данных считается приемлемым. Поэтому, выполнив этот запрос на своем компьютере, я получил выходные данные, в которых строки, похоже, были упорядочены по убыванию значений (см. экран 13).

Экран 13. Пример выходных данных с упорядочением вследствие оптимизации

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

Распространенная ошибка — попытаться создать «сортированное представление», применив фильтр TOP (100) PERCENT и предложение ORDER BY во внутреннем запросе (см. листинг 16).

Эта попытка неверна, поскольку, как уже отмечалось, представление является отношением и поэтому не может быть упорядоченным. Кроме того, когда SQL Server оптимизирует запрос для представления, выясняется, что комбинация TOP (100) PERCENT и ORDER BY во внутреннем запросе бессмысленна, и исключает ее. Например, выполните следующий запрос:

SELECT *
FROM Sales.MyView;

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

Экран 14. Сокращенный вариант выходных данных с интеллектуальной оптимизацией

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

Если вы сочетаете использование оконных функций и фильтра TOP или OFFSET-FETCH в одном запросе, помните, что оконные функции применяются на шаге 5.1 перед фильтрами TOP и OFFSET-FETCH, но не наоборот. Рассмотрим пример в листинге 17.

Функция ROW_NUMBER нумерует строки, а оконная функция COUNT (*) подсчитывает их перед применением фильтра OFFSET-FETCH. На экране 15 показано, как выглядят выходные данные этого запроса.

Экран 15. Выходные данные запроса в листинге 17

Обратите внимание, что результат состоит из строк с номерами от 21 до 30, а не от 1 до 10, а столбец totalrows содержит значение 830, а не 10. Если так и требовалось, это хорошо. Однако, если нужно применить оконные функции к результату фильтра TOP или OFFSET-FETCH, используйте табличное выражение на основе запроса, который применяет фильтр, а затем используйте оконные функции во внешнем запросе.

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

USE TSQLV4; -- http://tsql.solidq.com/SampleDatabases/TSQLV4.zip

SELECT C.custid, COUNT( O.orderid ) AS numorders
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid
WHERE C.country = N'Spain'
GROUP BY C.custid
HAVING COUNT( O.orderid ) <= 3
ORDER BY numorders;

SELECT TOP (4) WITH TIES
  C.custid,
  A.custlocation,
  COUNT( DISTINCT O.orderid ) AS numorders,
  SUM( A.val ) AS totalval,
  SUM( A.val ) / SUM( SUM( A.val ) ) OVER() AS pct
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)
WHERE A.custlocation IN (N'Spain.Madrid', N'France.Paris', N'USA.WA.Seattle')
GROUP BY C.custid, A.custlocation
HAVING COUNT( DISTINCT O.orderid ) <= 3
ORDER BY numorders;

SELECT C.custid, O.custid, O.orderid, YEAR(O.orderdate)
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid;

CREATE VIEW dbo.CustOrders
AS

SELECT C.custid, O.custid, O.orderid, YEAR(O.orderdate)
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid;
GO

CREATE VIEW dbo.CustOrders
AS

SELECT C.custid AS ccustid, O.custid AS ocustid, O.orderid, YEAR(O.orderdate) AS orderyear
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid;
GO

SELECT orderid, YEAR(Orderdate) AS orderyear,
  DATEFROMPARTS(orderyear, 12, 31) AS endofyear
FROM Sales.Orders;

SELECT orderid, orderyear, endofyear
FROM Sales.Orders
  CROSS APPLY ( VALUES( YEAR(Orderdate) ) ) AS A1(orderyear)
  CROSS APPLY ( VALUES( DATEFROMPARTS(orderyear, 12, 31) ) ) AS A2(endofyear);

WITH C AS
(
  SELECT orderid, orderdate, custid, empid,
    ROW_NUMBER() OVER(ORDER BY orderid) AS rownum
  FROM Sales.Orders
)
SELECT orderid, orderdate, custid, empid
FROM C
WHERE rownum BETWEEN 21 AND 30;

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

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

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

SELECT DISTINCT
  QUOTENAME(CONCAT(MONTH(orderdate), ‘/’, YEAR(orderdate))) AS monthyear
FROM Sales.Orders
ORDER BY YEAR(orderdate), MONTH(orderdate);

WITH C AS
(
  SELECT DISTINCT MONTH(orderdate) AS ordermonth, YEAR(orderdate) AS orderyear
  FROM Sales.Orders
)
SELECT QUOTENAME(CONCAT(ordermonth, ‘/’, orderyear)) AS monthyear
FROM CORDER BY orderyear, ordermonth;

CREATE VIEW Sales.MyView
AS

SELECT orderid, val
FROM Sales.Ordervalues
ORDER BY val DESC;
GO

CREATE VIEW Sales.MyView
AS

SELECT TOP (3) orderid, val
FROM Sales.Ordervalues
ORDER BY val DESC;
GO

ALTER VIEW Sales.MyView
AS

SELECT TOP (100) PERCENT orderid, val
FROM Sales.Ordervalues
ORDER BY val DESC;
GO

SELECT orderid, orderdate, custid, empid,
  ROW_NUMBER() OVER(ORDER BY orderdate, orderid) AS rownum,
  COUNT(*) OVER() AS totalrows
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;