Книжная полка Сохранить
Размер шрифта:
А
А
А
|  Шрифт:
Arial
Times
|  Интервал:
Стандартный
Средний
Большой
|  Цвет сайта:
Ц
Ц
Ц
Ц
Ц

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

Покупка
Артикул: 817220.01.99
В этой книге на конкретных примерах рассматриваются все типы оконных функций: агрегатные, ранжирующие, статистические, а также функции смещения и функции упорядоченного набора. Вы узнаете, как использовать оконные функции для повышения эффективности запросов, которые раньше писали с применением предикатов; освоить концепцию работы с окнами в SQL и строить запросы наиболее эффективным образом; умело использовать опции секционирования, упорядочивания и определения границ окна; оптимизировать оконные функции с использованием индексов и пакетного режима; применять оконные функции для решения распространенных бизнес-задач. Издание предназначено для разработчиков, администраторов, специалистов в области бизнес-аналитики. Актуально для SQL Server 2019 и ниже, а также для Azure SQL Database.
253
282
Бен-Ган, И. Оконные функции в T-SQL. По ту сторону анализа данных : практическое руководство / И. Бен-Ган ; пер. с англ. А. Ю. Гинько. - Москва : ДМК Пресс, 2022. - 344 с. - ISBN 978-5-93700-139-9. - Текст : электронный. - URL: https://znanium.com/catalog/product/2109498 (дата обращения: 18.11.2026). – Режим доступа: по подписке.
Фрагмент текстового слоя документа размещен для индексирующих роботов
Ицик Бен-Ган
Оконные функции в 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


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


6  Содержание
Содержание
Об авторе............................................................................................................................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


8  Содержание
Глава 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. Также Ицик пишет статьи для платформ sqlperformance.com, ITProToday и  SolidQ и  участвует в  различных конференциях, 
в числе которых PASS Summit и SQLBits. Является ведущим специалистом в области T-SQL и автором курсов Advanced T-SQL Querying, Programming and Tuning 
и T-SQL Fundamentals в компании SolidQ.