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

Стр.5

Стр.7

Стр.8

Стр.9

Стр.10

УДК 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 (англ.) ISBN 978-5-93700-139-9 (рус.) © 2020 by Itzik Ben-Gan © Перевод, оформление, издание, ДМК Пресс, 2022

Стр.5

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

Стр.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 СОдержанИе 7

Стр.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 8 СОдержанИе

Стр.9

Решение на основе наборов данных с подзапросами и объединениями .....................................................................................................291 Решение на основе курсора ................................................................................293 Решение на основе общеязыковой среды выполнения (CLR) ..............294 Вложенные итерации .............................................................................................296 Многострочный UPDATE с переменными ......................................................298 Сравнительный анализ ..........................................................................................300 Максимальное количество пересекающихся интервалов ...........................302 Традиционный подход на основе набора данных .....................................304 Решения, основанные на оконных функциях...............................................306 Объединение интервалов ..........................................................................................312 Традиционный подход на основе набора данных .....................................314 Решения, основанные на оконных функциях...............................................315 Пропуски и острова ......................................................................................................321 Пропуски .....................................................................................................................322 Острова ........................................................................................................................323 Медианы ...........................................................................................................................328 Условные агрегаты ........................................................................................................332 Сортировка иерархий ..................................................................................................334 Заключение .....................................................................................................................338 Предметный указатель ..............................................................................................340 СОдержанИе 9

Стр.10