Основы оконных функций T-SQL

197

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Основы оконных функций

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

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

Описание оконных функций

Оконная функция применяется к набору строк. Окно - стандартный термин SQL, служащий для описания контекста в котором работает функция. Для указания окна в SQL используется предложение OVER. Вот пример запроса:

USE TSQL2012;

SELECT orderid, orderdate, val,
  RANK() OVER(ORDER BY val DESC) AS 'rank'
FROM Sales.OrderValues
ORDER BY 'rank';

А это сокращенный результат выполнения этого запроса:

Простой пример использования оконной функции

Предложение OVER определяет окно (window), или точный набор строк по отношению к текущей строке, указание об упорядочении (если нужно) и другие элементы. Отсутствуют элементы, которые ограничивают набор строк в окне - как в данном примере, потому что набор строк окна является окончательным набором для запроса.

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

Упорядочение естественным образом необходимо для целей ранжирования. В данном примере оно основано на столбце val и обеспечивает ранжирование по убыванию.

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

Выберем одну из строк в результатах примера запроса, например с рангом 5. Этот ранг определен как 5, потому что в соответствии с заданным порядком сортировки (по убыванию val) в окончательном наборе результатов есть четыре строки, у которых значение атрибута val больше текущего значения (11188,40), а ранг определяется, как число этих строк плюс один.

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

Поддержка оконных функций в SQL описана в стандарте SQL:1999, где они назвались «OLAP functions». С того времени в каждой новой редакции поддержка оконных функций только укреплялась. Я имею в виду редакции SQL:2003, SQL:2008 и SQL:2011. В последнем стандарте SQL предусмотрена очень широкая поддержка оконных функций - нужно ли других доказательств, что в комитете стандартизации верят в них и, по-видимому, стандарт будет расширяться за счет увеличения числа оконных функций и сопутствующей функциональности.

Документы стандартов можно приобрести в организации ISO или ANSI. Например, по следующему URL-адресу можно приобрести документ фонда ANSI описанием стандарта SQL:2011, в котором описаны конструкции языка.

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

В агрегатных оконных функциях вы найдете привычные функции агрегирования, такие как SUM, COUNT, MIN, МАХ и другие, однако вы, скорее всего, привыкли к использованию их в контексте групп запросов. Функция агрегирования должна работать на наборе, который определен групповым запросом или определением окна. В SQL Server 2005 была реализована частичная поддержка оконных функций, а в SQL Server 2012 эта функциональность была расширена.

Реализованы следующие функции ранжирования: RANK, DENSE_RANK, ROW_NUMBER и NTILE. В стандарте первая и вторая пары функций относятся к разным категориям, и позже я объясню, почему. Я предпочитаю объединять эту четверку функций в одну категорию для простоты - точно так же, как это делается в официальной документации по SQL Server. В SQL Server 2005 эти четыре функции ранжирования уже обладают полной функциональностью.

К аналитическим функциям относятся PERCENT_RANK, CUME_DIST, PERCENTILE_CONT и PERCENTILE_DISC. Поддержка этих функций появилась в SQL Server 2012.

К функциям сдвига относятся LAG, LEAD, FIRST_VALUE, LAST, VALUE и NTH_VALUE. Поддержка эти функций также появилась в SQL Server 2012. Поддержки функции NTH_VALUE в SQL Server нет, и SQL Server 2012 не исключение.

Ниже рассказывается о назначении, целях и особенностях работы различных функций.

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

Оконные функции позволяют решать множество задач извлечения данных. Я бы сказал, что эту возможность сложно переоценить. Как я уже говорил, сейчас я использую оконные функции в большинстве ситуаций, когда нужно запрашивать данные. После рассказа о принципах работы и оптимизации функций будут показаны примеры практического их применения. Но чтобы вы уже сейчас понимали, как их можно использовать, скажу, что средствами оконных функций можно решать следующие задачи:
1. Разбиение на страницы.
2. Устранение дублирования данных.
3. Возвращение первых n строк в каждой группе.
4. Вычисление нарастающих итогов.
5. Выполнение операций в интервалах, например в интервалах упаковки, а также вычисление максимального числа параллельных сеансов.
6. Нахождение пробелов и диапазонов.
7. Вычисление процентилей.
8. Вычисление режима распределения.
9. Иерархии сортировки.
10. Сведение.
11. Определение новизны.
Я занимаюсь созданием SQL-запросов уже почти десять лет и на протяжении последних нескольких лет активно использую оконные функции. Могу сказать, что на освоение оконных функций требуется определенное время, но во многих случая оконные функции оказываются проще и более «интуитивными», чем обычные методы.
Оконные функции хорошо поддаются оптимизации. Почему это происходит, вы узнаете позже.

Декларативный язык и оптимизация

Вас наверное удивит, почему в таком декларативном языке, как SQL, где вы просто заявляете, что хотите получить, а не описываете, как получить искомое, две формы одного и того же запроса - одна с оконными функциями, а другая без - дают разную производительность. Как так происходит, что в одной реализации SQL, такой как SQL Server, с собственным диалектом T-SQL, СУБД не всегда «догадывается», что две формы практически идентичны, и не создает одинаковые планы выполнения.

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

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

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

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

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

Два типа программирования: наборы и курсоры с итеративным проходом

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

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

Поэтому сначала объясню, что представляет собой основанный на наборах подход к решению задач получения данных средствами T-SQL. T-SQL является диалектом стандартного языка SQL (стандартов как ISO, так и ANSI). SQL основан (или является попыткой реализации) на базе реляционной модели, которая представляет собой математическую модель управления данными, изначально сформулированными и предложенными Е.Ф. Коддом в конце 1960-х.

Реляционная модель основана на двух математических принципах: теории множеств и логике предикатов. Многие аспекты компьютерных вычислений основаны на интуиции, при этом они очень быстро меняются - так быстро, что иногда кажется, что ты сам похож на кота, который гоняется за своим хвостом. Реляционная модель является островом в мире компьютерных вычислений, потому что основана на существенно более надежном основании - на математике. Некоторые считают математику истиной в последней инстанции. Строгие математические основания обеспечивают надежность и стабильность реляционной модели. Она развивается, но не так быстро, как другие области компьютерных вычислений. Вот уже несколько десятилетий реляционная модель оставалась незыблемой и сейчас она лежит в основе ведущих платформ баз данных, которые называют реляционными системами управления базами данных (РСУБД).

SQL представляет собой попытку создания языка, основанного на реляционной модели. SQL неидеален и, честно говоря, в ряде нюансов отклоняется от реляционной модели, но вместе с тем он предоставляет достаточно средств, чтобы человек, понимающий реляционную модель, мог использовать реляционные возможности средствами SQL. Этот язык - бесспорный ведущий де-факто язык современных РСУБД.

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

open file
fetch first record 
while not end of file 
begin 
	process record 
	fetch next record 
end

Данные в файлах (или, если быть точнее, в файлах с индексированным последовательным доступом, или ISAM-файлах) хранятся в определенном порядке. И вы гарантировано можете получать записи из файла именно в таком порядке. Также записи можно получать по одной за раз. Поэтому вы привыкаете, что доступ к данным осуществляется именно так: по порядку и по одной записи за раз. Это похоже на работу с курсором в T-SQL. По этой причине для разработчиков с навыками процедурного программирования использование курсоров или других итеративных механизмов соответствует их опыту и представляется логичным способом работы с данными.

Реляционный, основанный на наборах подход к работе с данными фундаментально отличается от подобных навыков. Чтобы лучше понять его начнем с определения набора, или множества, принадлежащего создателю теории множеств Георгу Кантору: «Под "множеством" мы понимаем соединение в некое целое М определённых хорошо различимых предметов m нашего созерцания или нашего мышления (которые будут называться «элементами» множества М)».

Это очень емкое определение набора - чтобы сформулировать эту мысль мне бы пришлось потратить много времени. Но для целей нашей дискуссии я сосредоточусь на двух аспектах - один сформулирован в определении явно, а второй - неявно:

Целое

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

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

Порядок

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

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

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

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

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

На рисунке ниже я пытаюсь показать, что как входные, так и выходные данные оконной функции являются реляционными, несмотря на то, что в определении оконной функции задано упорядочивание. Овалами и разным порядком строк во входных и выходных данных я пытаюсь выразить тот факт, что порядок строк не имеет значения:

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

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

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

SELECT orderid, orderdate, val,
  RANK() OVER(ORDER BY val DESC) AS 'rank'
FROM Sales.OrderValues;

Вот сокращенный результат выполнения этого запроса (учтите, что нет никаких гарантий именно такого порядка строк):

Вот пример базового понимания того, как вычисляются значения ранга в псевдокоде:

сортировать строки по значению val
итеративный проход строк 
for each строка 
	if текущая строка является первой в секции, вернуть 1 
	if значение val равно предыдущему значению val, вернуть предыдущий ранг
	else вернуть текущее число обработанных строк

Следующий рисунок иллюстрирует такой способ понимания:

Хотя такой способ мышления дает правильный результат, он не совсем корректный. Честно говоря, моя задача усложняется еще и тем, что такой процесс очень похож на то, как вычисление ранга физически реализовано в SQL Server. Но на данном этапе моя цель не физическая реализация, а концептуальный уровень - язык и логическая модель. Под «неправильным мышлением» я имею в виду, что концептуально, с точки зрения языка, вычисление мыслится иначе - в терминах наборов, а не итераций. Помните, что язык никак не связан с конкретной физической реализацией в ядре СУБД. Задача физического уровня - определить, как выполнить логический запрос, и максимально быстро вернуть правильный результат.

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

Глубокое понимание вычисления значений ранга с помощью наборов

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

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

Недостатки альтернатив оконным функциям

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

Я начну с традиционных групповых запросов. Они дают новую информацию в виде агрегатов, но кое-что при этом теряется - детали.

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

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

use TSQL2012;

WITH Aggregates AS
(
    SELECT custid, SUM(val) AS sumval, AVG(val) AS avgval
    FROM Sales.OrderValues
    GROUP BY custid
)
SELECT O.orderid, O.custid, O.val,
    CAST(100. * O.val / A.sumval AS NUMERIC(5, 2)) AS pctcust,
    O.val - A.avgval AS diffcust
FROM Sales.OrderValues AS O
    JOIN Aggregates AS A ON O.custid = A.custid;

Вот сокращенный результат выполнения этого запроса:

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

use TSQL2012;

WITH CustAggregates AS
(
	SELECT custid, SUM(val) AS sumval, AVG(val) AS avgval
	FROM Sales.OrderValues
	GROUP BY custid
),
GrandAggregates AS
(
	SELECT SUM(val) AS sumval, AVG(val) AS avgval
	FROM Sales.OrderValues
)
SELECT O.orderid, O.custid, O.val,
    CAST(100. * O.val / CA.sumval AS NUMERIC(5, 2)) AS pctcust,
	    O.val - CA.avgval AS diffcust,
    CAST(100. * O.val / GA.sumval AS NUMERIC(5, 2)) AS pctall,
	    O.val - GA.avgval AS diffall
FROM Sales.OrderValues AS O
	JOIN CustAggregates AS CA ON O.custid = CA.custid
	CROSS JOIN GrandAggregates AS GA;

Вот результат выполнения этого запроса:

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

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

use TSQL2012;

-- Вложенные запросы с подробными данными и агрегатами по отдельным клиентам
SELECT orderid, custid, val,
    CAST(100. * val /
        (SELECT SUM(O2.val)
         FROM Sales.OrderValues AS O2
         WHERE O2.custid = O1.custid) AS NUMERIC(5, 2)) AS pctcust,
    val - (SELECT AVG(O2.val)
         FROM Sales.OrderValues AS O2
         WHERE O2.custid = O1.custid) AS diffcust
FROM Sales.OrderValues AS O1;

-- Вложенные запросы с подробными данными и агрегатами по всем и отдельным клиентам
SELECT orderid, custid, val,
    CAST(100. * val /
        (SELECT SUM(O2.val)
         FROM Sales.OrderValues AS O2
         WHERE O2.custid = O1.custid) AS NUMERIC(5, 2)) AS pctcust,
    val - (SELECT AVG(O2.val)
         FROM Sales.OrderValues AS O2
         WHERE O2.custid = O1.custid) AS diffcust,
    CAST(100. * val /
        (SELECT SUM(O2.val)
         FROM Sales.OrderValues AS O2) AS NUMERIC(5, 2)) AS pctall,
    val - (SELECT AVG(O2.val)
         FROM Sales.OrderValues AS O2) AS diffall
FROM Sales.OrderValues AS O1;

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

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

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

SUM(val) OVER()

Если не ограничить окно (в скобках пусто), исходной точкой считается результирующий набор запроса.

Чтобы вычислить сумму всех значений результирующего набора, у которых идентификатор клиента (customer ID) такой же, как в текущей строке, используйте возможности секционирования в оконных функциях (о нем я расскажу попозже) и выполните секционирование окна по custid следующим образом:

SUM(val) OVER(PARTITION BY custid)

Заметьте, что термин секционирование подразумевает фильтрацию, а не группировку.

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

use TSQL2012;

SELECT orderid, custid, val,
    CAST(100. * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) AS pctcust,
    val - AVG(val) OVER(PARTITION BY custid) AS diffcust
FROM Sales.OrderValues;

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

SELECT orderid, custid, val,
	CAST(100. * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) AS pctcust,
	val - AVG(val) OVER(PARTITION BY custid) AS diffcust,
	CAST(100. * val / SUM(val) OVER() AS NUMERIC(5, 2)) AS pctall,
	val - AVG(val) OVER() AS diffall
FROM Sales.OrderValues;

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

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

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

use TSQL2012;

SELECT orderid, custid, val,
	CAST(100. * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) AS pctcust,
	val - AVG(val) OVER(PARTITION BY custid) AS diffcust,
	CAST(100. * val / SUM(val) OVER() AS NUMERIC(5, 2)) AS pctall,
	val - AVG(val) OVER() AS diffall
FROM Sales.OrderValues
WHERE orderdate >= '20070101'
	AND orderdate < '20080101';

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

use TSQL2012;

SELECT orderid, custid, val,
    CAST(100. * val /
        (SELECT SUM(O2.val)
         FROM Sales.OrderValues AS O2
         WHERE O2.custid = O1.custid
           AND orderdate >= '20070101'
           AND orderdate < '20080101') AS NUMERIC(5, 2)) AS pctcust,
    val - (SELECT AVG(O2.val)
         FROM Sales.OrderValues AS O2
         WHERE O2.custid = O1.custid
           AND orderdate >= '20070101'
           AND orderdate < '20080101') AS diffcust,
    CAST(100. * val /
        (SELECT SUM(O2.val)
         FROM Sales.OrderValues AS O2
         WHERE orderdate >= '20070101'
           AND orderdate < '20080101') AS NUMERIC(5, 2)) AS pctall,
    val - (SELECT AVG(O2.val)
         FROM Sales.OrderValues AS O2
         WHERE orderdate >= '20070101'
           AND orderdate < '20080101') AS diffall
FROM Sales.OrderValues AS O1
WHERE orderdate >= '20070101'
	AND orderdate < '20080101';

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

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