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

Если бы меня попросили назвать фундаментальный вопрос в T-SQL, наиболее важный для практической работы, то, несомненно, я бы выбрал логическую обработку запросов. Необходимо с самого начала отметить, что этот термин не взят из какого-то формального источника. Я придумал его, чтобы объяснить студентам и читателям логическую, или концептуальную, интерпретацию запроса. Глубокое понимание этой темы — ключ к созданию правильного и надежного программного кода. Всегда на занятиях по T-SQL, будь то курсы для начинающих или для опытных специалистов, я начинаю с этой темы, поскольку она одинаково важна для всех.

Что означает «логическая обработка запросов»?

Чтобы понять, что здесь означает слово «логическая», нужно начать с основ T-SQL. Это широко известные базовые принципы, их взаимосвязь показана на рисунке 1.

 

Основы T-SQL
Рисунок 1. Основы T-SQL

 

T-SQL, или Transact-SQL, — диалект стандартного языка SQL, используемый Microsoft в нескольких продуктах, которые являются частью платформы обработки данных компании, таких как Microsoft SQL Server и Microsoft Azure SQL Database. Используя T-SQL, можно управлять данными в этих продуктах. T-SQL — собственный диалект Microsoft — базируется в основном на стандартном языке SQL с некоторыми расширениями. Язык SQL (Structured Query Language) поддерживается как Международной организацией по стандартизации (ISO), так и Американским национальным институтом стандартов (ANSI).

SQL, в свою очередь, основывается на реляционной модели — семантической модели представления данных, созданной Эдгаром Ф. Коддом в 1969 году. Реляционная же модель основана на двух областях математики: теории множеств и логике предикатов.

В реляционной модели определен важный принцип независимости физических данных. Это означает, что модель и основанный на ней язык определяют логические аспекты данных и манипуляций или, другими словами, смысл. Авторы модели избегают вдаваться в детали физической реализации (способы физической организации, хранения и доступа к данным, а также методы физической обработки — оптимизации и выполнения — запросов). Физическая часть зависит от используемой платформы базы данных. Не предполагается, что пользователь осмысливает информацию, основываясь на физических данных. Для этого служит логическая модель.

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

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

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

Порядок логической обработки запросов

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

 

Порядок ввода предложений запросов с номерами шагов логической обработки запросов
Рисунок 2. Порядок ввода предложений запросов с номерами шагов логической обработки запросов

 

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

Я поясню, что стоит за такой структурой. Дело в том, что разработчики SQL хотели, чтобы язык напоминал английский. Первоначальное название языка было SEQUEL (сокращение от Structured English QUEry Language), но из-за спора об авторских правах оно было изменено на SQL. Теперь вспомним, как структурированы инструкции в английском языке. Например, рассмотрим запрос Bring me the T-SQL Querying book from the shelf in my office (принесите мне книгу T-SQL Querying с полки в моем офисе). Обратите внимание, что инструкция начинается не с указания местонахождения объекта (офис), а с самого объекта (книга). Но если задуматься о порядке, в котором необходимо выполнять такую инструкцию, то сначала требуется зайти в комнату, потом найти полку, а затем уже взять книгу и принести ее.

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

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

На рисунке 2 показан требуемый порядок ввода предложений запроса. На рисунке 3 мы видим те же предложения на основе порядка логической обработки запроса.

 

Порядок логической обработки предложений запроса
Рисунок 3. Порядок логической обработки предложений запроса

 

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

 

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

 

Если вам нравится решать задачи, рассмотрите другие шаги в блок-схеме. Впрочем, я буду их подробно разбирать в следующих статьях; пока они представлены для справки. При чтении следующих статей серии держите под рукой рисунки 2, 3 и 4. Они вам пригодятся.

Та же база данных и тестовые запросы

В моих примерах будет использоваться тестовая база данных с именем TSQLV4. Исходный текст, с помощью которого можно создать и заполнить базу данных, находится по адресу: http://tsql.solidq.com/SampleDatabases/TSQLV4.zip. Эта база данных представляет собой простую систему ввода информации о заказах с таблицами Sales.Customers, Sales.Orders (с заголовками заказов), Sales.OrderDetails (со строками заказов) и т. д. База данных совместима со всеми версиями, начиная с SQL Server 2008 и до 2016, а также с базой данных SQL Azure. Перед запуском программного кода из статей данной серии убедитесь, что эта база данных установлена и доступна. После установки используйте следующую команду для переключения контекста на эту базу данных:

USE TSQLV4;

Для объяснения логической обработки запросов будут использоваться два тестовых запроса, простой и сложный. В статье они именуются «простой тестовый запрос» и «сложный тестовый запрос». В листинге 1 показан простой тестовый запрос. Он возвращает данные заказчиков из Испании, разместивших не более трех заказов. Для подходящих заказчиков возвращаются идентификатор (customer ID) и число заказов. Запрос выдает строки, сортированные по числу заказов в возрастающем порядке.

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

  1. FROM: запрос объединяет таблицы Customers и Orders на основании совпадений между идентификаторами customer ID заказчика и заказа. В запросе используется левое внешнее соединение, чтобы сохранить заказчиков, не разместивших заказы.
  2. WHERE: запрос фильтрует только строки, в которых указана страна заказчика — Испания (Spain).
  3. GROUP BY: запрос группирует оставшиеся строки по идентификатору customer ID заказчика.
  4. HAVING: запрос фильтрует только группы заказчиков, имеющие не более трех заказов
  5. SELECT: для остальных групп запрос возвращает идентификатор customer ID заказчика и число заказов, назначая столбцу имя numorders.
  6. Запрос представляет строку результатов, сортированную по numorders.

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

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

Что дальше

Понимание логической обработки запросов — ключ к разработке производительного кода SQL. Кроме того, чрезвычайно важно понимать определенные ограничения языка и уметь искать обходные пути. В следующих статьях серии мы углубимся в подробности, рассматривая отдельные предложения. А пока предлагаю вам домашнее задание — ответить на следующие вопросы:

  1. В чем разница между предложением ON и предложением WHERE?
  2. Существует ли гарантия, что выражения в предложении WHERE будут вычисляться в определенном порядке?
  3. Для чего служат NULL-значения и какие сложности они вносят в язык?
  4. При объединении таблицы с производной таблицей может ли запрос к производной таблице ссылаться на столбцы из другой таблицы в объединении и почему?
  5. Можно ли использовать псевдоним столбца, который был определен в предложении SELECT, в предложении WHERE и почему?
  6. Можно ли использовать псевдоним, который был определен в предложении SELECT, в других выражениях в предложении SELECT и почему?
  7. Можно ли использовать псевдоним, который был определен в предложении SELECT, в предложении ORDER BY и почему?
  8. Как сделать псевдоним столбца доступным для таких предложений, как WHERE, GROUP BY, HAVING, SELECT?
  9. В чем разница между природой результата запроса, когда в запросе имеется предложение ORDER BY представления и когда оно отсутствует?
  10. В чем разница между групповой агрегатной функцией и оконной агрегатной функцией?
  11. Если во внутреннем запросе присутствует предложение ORDER BY, гарантирован ли порядок представления внешнего запроса?
  12. Если в запросе отсутствует предложение ORDER BY представления, существуют ли какие-нибудь обстоятельства, при которых гарантирован порядок представления запроса?
Листинг 1. Простой тестовый запрос
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;
Листинг 2. Сложный тестовый запрос
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;