Оконные функции в T-SQL. По ту сторону анализа данных

Ицик Бен-Ган

Оконные функции в T-SQL

T-SQL Window 
Functions

For data analysis and beyond

Second Edition

Itzik Ben-Gan

Оконные функции  
в T-SQL

По ту сторону анализа данных

Ицик Бен-Ган

Москва, 2022

УДК 004.424
ББК 32.372
Б46

Бен-Ган И.
Б46 
Оконные функции в T-SQL / пер. с англ. А. Ю. Гинько. – М.: ДМК Пресс, 
2022. – 344 с.: ил. 

ISBN 978-5-93700-139-9

В этой книге на конкретных примерах рассматриваются все типы оконных 
функций: агрегатные, ранжирующие, статистические, а также функции смещения 
и функции упорядоченного набора. Вы узнаете, как использовать оконные 
функции для повышения эффективности запросов, которые раньше писали с 
применением предикатов; освоить концепцию работы с окнами в SQL и строить 
запросы наиболее эффективным образом; умело использовать опции секционирования, 
упорядочивания и определения границ окна; оптимизировать оконные 
функции с использованием индексов и пакетного режима; применять оконные 
функции для решения распространенных бизнес-задач.
Издание предназначено для разработчиков, администраторов, специалистов 
в области бизнес-аналитики. Актуально для SQL Server 2019 и ниже, а также для 
Azure SQL Database.

УДК 004.424
ББК 32.372

Authorized translation from the English language edition, entitled T-SQL Window Functions: 
For Data Analysis and Beyond, 1st Edition by Itzik Ben-Gan, published by Pearson Education, Inc, 
publishing as Microsoft Press, Copyright © 2020.

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

ISBN 978-0-13-586144-8 (англ.) 
© 2020 by Itzik Ben-Gan
ISBN 978-5-93700-139-9 (рус.) 
© Перевод, оформление, издание, ДМК Пресс, 2022

В память о моих родителях,  
Миле и Габи Бен-Ган

СОдержанИе

Содержание

Об авторе ............................................................................................................................10

Введение ...............................................................................................................................11

Глава 1 работа с окнами в SQL ..............................................................................................16

Эволюция оконных функций .......................................................................................17

Основы оконных функций ............................................................................................18

Описание оконных функций ..................................................................................19
Программирование на основе наборов данных  
и курсоров/итераций .................................................................................................22
Недостатки альтернативных вариантов оконных функций ......................28
Ваши первые решения с применением оконных функций ............................34

Элементы оконных функций .......................................................................................39

Секционирование окна ............................................................................................39
Упорядочивание окна ...............................................................................................40
Определение границ окна ......................................................................................42
Элементы запросов с поддержкой оконных функций .....................................44

Логическая обработка запроса .............................................................................44
Инструкции с поддержкой оконных функций ................................................46
В обход ограничений ................................................................................................50
Возможности для использования дополнительных фильтров ......................52

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

Заключение ........................................................................................................................54

Глава 2 детальное изучение оконных функций .........................................................55

Агрегатные функции .......................................................................................................55

Описание агрегатных оконных функций ..........................................................56
Поддерживаемые элементы ..................................................................................56
Другие идеи по работе с окнами .........................................................................78
Агрегаты и DISTINCT ..................................................................................................84
Вложение группирующих функций в оконные ...............................................86
Ранжирующие функции .................................................................................................91

Поддерживаемые элементы ..................................................................................91

СОдержанИе 7

ROW_NUMBER ...............................................................................................................91
NTILE ................................................................................................................................97
RANK и DENSE_RANK ..............................................................................................102
Статистические функции ............................................................................................104

Поддерживаемые элементы ...............................................................................104
Функции распределения рангов .......................................................................105
Функции обратного распределения ................................................................ 107
Функции смещения ......................................................................................................110

Поддерживаемые элементы ...............................................................................111
LAG и LEAD .................................................................................................................111
FIRST_VALUE, LAST_VALUE и NTH_VALUE ......................................................114
RESPECT NULLS | IGNORE NULLS ......................................................................118
Заключение .....................................................................................................................121

Глава 3 Функции упорядоченного набора ..................................................................122

Функции гипотетического набора ..........................................................................123

RANK ..............................................................................................................................123
DENSE_RANK ..............................................................................................................125
PERCENT_RANK .........................................................................................................126
CUME_DIST ..................................................................................................................128
Обобщенное решение ...........................................................................................129
Функции обратного распределения ......................................................................131

Функции смещения ......................................................................................................135

Конкатенация строк .....................................................................................................140

Заключение .....................................................................................................................142

Глава 4 распознавание шаблонов в строках ..............................................................143

Предпосылки распознавания шаблонов в строках ........................................143

R010: «Распознавание шаблонов в строках: инструкция FROM» .............145

Описание задачи ......................................................................................................146
ONE ROW PER MATCH .............................................................................................152
ALL ROWS PER MATCH............................................................................................156
RUNNING и FINAL .....................................................................................................166
Вложение функций FIRST | LAST в PREV | NEXT .........................................168
R020: «Распознавание шаблонов в строках: инструкция WINDOW» .......170

Решения на основе распознавания шаблонов в строках ............................173

Возвращение верхних N строк по группам ..................................................174
Объединение интервалов .....................................................................................175
Поиск пропусков и островов ..............................................................................179
Вычисление нестандартных накопительных итогов .................................184
Заключение .....................................................................................................................189

СОдержанИе

Глава 5 Оптимизация оконных функций ......................................................................190

Исходные данные для примеров ...........................................................................191

Рекомендации по индексированию ......................................................................193

POC-индекс .................................................................................................................194
Merge Join (Concatenation) ...................................................................................196
Обратное сканирование .......................................................................................198
Эффективное имитирование опции NULLS LAST ............................................202

Улучшение параллелизма с использованием инструкции APPLY .............206

Пакетный режим обработки .....................................................................................209

Пакетный режим с индексами columnstore ..................................................210
Пакетный режим с индексами rowstore ......................................................... 217
Ранжирующие функции ..............................................................................................220

ROW_NUMBER ............................................................................................................221
NTILE .............................................................................................................................222
RANK и DENSE_RANK ..............................................................................................223
Пакетная обработка ................................................................................................225
Агрегатные функции и функции смещения .......................................................226

Без упорядочивания и указания границ окна ............................................. 227
С упорядочиванием и указанием границ окна............................................233
Функции распределения ............................................................................................245

Функции распределения рангов .......................................................................246
Функции обратного распределения ................................................................ 247
Пакетный режим обработки ................................................................................251
Заключение .....................................................................................................................252

Глава 6 Практическое применение оконных функций в T-SQL .....................253

Вспомогательные виртуальные таблицы с числами .......................................254

Последовательности значений даты и времени .............................................. 257

Последовательности ключей ....................................................................................259

Обновление столбца с заполнением уникальными значениями ........259
Получение диапазона значений последовательности .............................261
Разбиение на страницы ..............................................................................................264

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

Сведение ...........................................................................................................................269

Первые N элементов в группе .................................................................................272

Имитация IGNORE NULLS ..........................................................................................276

Моды ...................................................................................................................................282

Усеченное среднее .......................................................................................................286

Нарастающие итоги ...................................................................................................... 287

Решение на основе наборов данных с оконными функциями .............290

СОдержанИе 9

Решение на основе наборов данных с подзапросами 
и объединениями .....................................................................................................291
Решение на основе курсора ................................................................................293
Решение на основе общеязыковой среды выполнения (CLR) ..............294
Вложенные итерации .............................................................................................296
Многострочный UPDATE с переменными ......................................................298
Сравнительный анализ ..........................................................................................300
Максимальное количество пересекающихся интервалов ...........................302

Традиционный подход на основе набора данных .....................................304
Решения, основанные на оконных функциях...............................................306
Объединение интервалов ..........................................................................................312

Традиционный подход на основе набора данных .....................................314
Решения, основанные на оконных функциях...............................................315
Пропуски и острова ......................................................................................................321

Пропуски .....................................................................................................................322
Острова ........................................................................................................................323
Медианы ...........................................................................................................................328

Условные агрегаты ........................................................................................................332

Сортировка иерархий ..................................................................................................334

Заключение .....................................................................................................................338

Предметный указатель ..............................................................................................340

Об авторе

Ицик Бен-Ган (Itzik Ben-Gan) – сооснователь и преподаватель образовательной 
компании SolidQ, с 1999 года является обладателем статуса Microsoft MVP 
в области платформ обработки данных. Ицик провел бесчисленное количество 
обучающих мероприятий по всему миру, делясь опытом написания и оптимизации 
запросов на языке T-SQL. Автор нескольких книг, включая T-SQL 
Fundamentals и T-SQL Querying. Также Ицик пишет статьи для платформ sqlper-
formance.com, ITProToday и SolidQ и участвует в различных конференциях, 
в числе которых PASS Summit и SQLBits. Является ведущим специалистом в области 
T-SQL и автором курсов Advanced T-SQL Querying, Programming and Tuning 
и T-SQL Fundamentals в компании SolidQ.

Структура книги 11

Введение

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

для кого эта книга

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

Структура книги

В книге освещаются как логические аспекты оконных функций, так и вопросы 
их оптимизации и практического применения.
Глава 1. Работа с окнами в SQL. В данной главе мы опишем концепцию 
оконных функций в языке SQL. Здесь вы познакомитесь с назначением оконных 
функций, их типами и элементами, участвующими в их создании и применении, 
включая секционирование, упорядочивание и определение границ 
окна.
Глава 2. Детальное изучение оконных функций. В этой главе мы погрузимся 
в детали и специфику оконных функций, подробно разберем агрегат-

ВВеденИе

ные и ранжирующие оконные функции, функции смещения и статистические 
функции (функции распределения).
Глава 3. Функции упорядоченного набора. В третьей главе мы поговорим 
о поддержке в языке T-SQL и стандарте SQL функций для работы с упорядоченными 
наборами, включая конкатенацию строк, а также рассмотрим функции 
для работы с гипотетическими наборами, функции обратного распределения 
и др. Кроме того, для стандартных функций, пока не реализованных в языке 
T-SQL, мы приведем работающие решения.
Глава 4. Распознавание шаблонов в строках. Здесь мы коснемся продвинутой 
концепции анализа данных, именуемой распознаванием шаблонов 
в строках (row-pattern recognition – RPR), которую можно рассматривать как 
следующий шаг развития оконных функций. В языке T-SQL данный функционал 
пока не реализован, но, как уже было упомянуто ранее, в этой книге мы 
представим вам все важнейшие аналитические концепции, даже не воплощенные 
на данный момент в T-SQL.
Глава 5. Оптимизация оконных функций. В этой главе мы рассмотрим 
способы оптимизации оконных функций применительно к SQL Server и SQL 
Azure Database. Мы поговорим о применении индексации с целью ускорения 
выполнения запросов, затронем тему параллельных вычислений, сравним построчную 
и пакетную обработку запросов и узнаем другие способы оптимизации 
оконных функций.
Глава 6. Практическое применение оконных функций в T-SQL. В заключительной 
главе книги мы обратимся к практическому применению оконных 
функций для решения распространенных бизнес-задач.

Системные требования

Оконные функции являются составной частью ядра баз данных Microsoft SQL 
Server и SQL Azure Database, в связи с чем их поддержка реализована во всех 
версиях продуктов. Для запуска примеров из этой книги вам необходимо 
иметь доступ к экземпляру установки SQL Server 2019 и старше (любой версии) 
или SQL Azure Database с установленной базой данных с уровнем совместимости (
compatibility level) 150 и выше. Также вам придется установить базу 
данных с названием TSQLV5, к которой мы будем обращаться в этой книге. 
Если у вас нет доступа к установленному экземпляру СУБД, на сайте Microsoft 
присутствуют бесплатные версии. Подробнее можно узнать по адресу https://
www.microsoft.com/en-us/sql-server/sql-server-downloads.
В качестве клиентских инструментов для подключения к базам данных 
и выполнения представленных в книге примеров вы можете использовать SQL 
Server Management Studio (SSMS) или Azure Data Studio. Я использовал SSMS 
для создания графических представлений планов выполнения запросов, показанных 
в книге. Вы можете загрузить этот инструмент по ссылке https://docs.
microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.
Azure Data Studio можно скачать по адресу https://docs.microsoft.com/en-us/sql/
azure-data-studio/download.

ВВедение 13

Примеры из книги

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

Благодарности

Многие люди прямо или косвенно повлияли на выход в свет этой книги, и они, 
конечно, достойны упоминания и благодарностей.
Спасибо Лилах за придание смысла всему, что я делаю, и за помощь с вычиткой 
текста.
Благодарю всех разработчиков Microsoft SQL Server (бывших и нынешних), 
а в особенности: Конора Каннингема (Conor Cunningham), Джо Сака (Joe Sack), 
Василиса Пападимоса (Vassilis Papadimos), Марка Фридмана (Marc Friedman), 
Крейга Фридмана (Craig Freedman), Милана Ружича (Milan Ruzic), Милана 
Стои ча (Milan Stojic), Йована Поповича (Jovan Popovic), Борко Новаковича 
(Borko Novakovic), Тобиаса Тернстрёма (Tobias Ternström), Лубора Коллара (Lu-
bor Kollar), Умачандара Джаячандрана (Umachandar Jayachandran), Педро Ло-
песа (Ped ro Lopes), Архениса Фернандеса (Argenis Fernandez) и многих других. 
Я очень признателен вам за реализацию и поддержку оконных функций в SQL 
Server, а также за общение со мной и ответы на мои вопросы и просьбы что-то 
разъяснить.
Кроме того, я благодарен всей редакторской команде издательства Pearson. 
Лоретта Йейтс (Loretta Yates), спасибо за то, что поверила в этот проект и позволила 
ему реализоваться! Моя признательность Чарви Ароре (Charvi Arora) 
за приложенные огромные усилия. Также я благодарен Рику Кугену (Rick Ku-
ghen) и Трейси Крум (Tracey Croom). Спасибо Ашвини Кумар (Aswini Kumar) 
и ее команде за работу над PDF-версией книги.
Помимо прочих, хочу выразить отдельную благодарность Адаму Маханику 
(Adam Machanic) за согласие стать техническим редактором книги. Существует 
не так много людей, понимающих архитектуру SQL Server лучше тебя. Для 
меня вопрос выбора человека на эту роль вообще не стоял.
Спасибо Q2, Q3 и Q4. Для меня большое счастье иметь возможность обсуждать 
идеи, связанные с книгой, с такими профессионалами в области SQL, как 
вы. Кажется, я могу делиться с вами всем без опасений за возможные последствия.

Также я хочу выразить благодарность моей компании SolidQ за последние 
два десятилетия. Быть частью такого коллектива и наблюдать за ростом компании – 
настоящее счастье. Для меня сотрудники этой компании – это больше, 
чем просто коллеги. Это партнеры, друзья и семья. Спасибо Фернандо Герреро 
(Fernando G. Guerrero), Антонио Сото (Antonio Soto) и многим другим.

ВВеденИе

Отдельную признательность я выражаю Аарону Бертрану (Aaron Bertrand) 
и Грегу Гонзалесу (Greg Gonzales). Мне очень приятно вести колонку на сайте 
https://sqlperformance.com. SentryOne – прекрасная компания, создающая для сообщества 
отличные продукты и сервисы.
Также я благодарен MVP в области SQL Server Алехандро Месе (Alejandro 
Mesa), Эрланду Соммарскогу (Erland Sommarskog), Аарону Бертрану (Aaron Ber-
trand), Полу Уайту (Paul White) и многим другим. Я очень рад быть частью этой 
великолепной программы. Уровень знаний этих людей просто поражает, и мне 
всегда приятно встречаться с ними, разговаривать на профессиональные темы 
или просто попить пива. Я уверен, что в Microsoft решили уделить отдельное 
внимание развитию оконных функций в SQL Server именно под влиянием сообщества, 
и не последнюю роль в этом сыграли наши MVP. Очень приятно наблюдать 
за тем, как совместные усилия выливаются в нечто большее.
Наконец, я хотел бы сказать спасибо своим студентам. Я обожаю преподавать 
SQL, это моя страсть, и я благодарен вам за возможность ее удовлетворять. 
А ваши бесконечные вопросы позволяют мне двигаться дальше, обретая 
новые знания.

Отзывы и пожелания

Мы всегда рады отзывам наших читателей. Расскажите нам, что вы думаете об 
этой книге – что понравилось или, может быть, не понравилось. Отзывы важны 
для нас, чтобы выпускать книги, которые будут для вас максимально полезны.
Вы можете написать отзыв на нашем сайте www.dmkpress.com, зайдя на страницу 
книги и оставив комментарий в разделе «Отзывы и рецензии». Также 
можно послать письмо главному редактору по адресу dmkpress@gmail.com; при 
этом укажите название книги в теме письма. 
Если вы являетесь экспертом в какой-либо области и заинтересованы в написании 
новой книги, заполните форму на нашем сайте по адресу http://dmk-
press.com/authors/publish_book/ или напишите в издательство по адресу dmk-
press@gmail.com.

Список опечаток

Хотя мы приняли все возможные меры для того, чтобы обеспечить высокое 
качество наших текстов, ошибки все равно случаются. Если вы найдете ошибку 
в одной из наших книг, мы будем очень благодарны, если вы сообщите о ней 
главному редактору по адресу dmkpress@gmail.com. Сделав это, вы избавите 
других читателей от недопонимания и поможете нам улучшить последующие 
издания этой книги.