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

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

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

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