Оконные функции в T-SQL. По ту сторону анализа данных
Покупка
Тематика:
Проектирование баз и банков данных
Издательство:
ДМК Пресс
Автор:
Бен-Ган Ицик
Перевод:
Гинько Александр Юрьевич
Год издания: 2022
Кол-во страниц: 344
Дополнительно
Вид издания:
Практическое пособие
Уровень образования:
Профессиональное образование
ISBN: 978-5-93700-139-9
Артикул: 817220.01.99
В этой книге на конкретных примерах рассматриваются все типы оконных функций: агрегатные, ранжирующие, статистические, а также функции смещения и функции упорядоченного набора. Вы узнаете, как использовать оконные функции для повышения эффективности запросов, которые раньше писали с применением предикатов; освоить концепцию работы с окнами в SQL и строить запросы наиболее эффективным образом; умело использовать опции секционирования, упорядочивания и определения границ окна; оптимизировать оконные функции с использованием индексов и пакетного режима; применять оконные функции для решения распространенных бизнес-задач.
Издание предназначено для разработчиков, администраторов, специалистов в области бизнес-аналитики. Актуально для SQL Server 2019 и ниже, а также для Azure SQL Database.
Тематика:
ББК:
УДК:
ОКСО:
- ВО - Бакалавриат
- 02.03.02: Фундаментальная информатика и информационные технологии
- 09.03.01: Информатика и вычислительная техника
- 09.03.02: Информационные системы и технологии
- 09.03.04: Программная инженерия
ГРНТИ:
Скопировать запись
Фрагмент текстового слоя документа размещен для индексирующих роботов
Ицик Бен-Ган Оконные функции в 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.