Оконные функции в 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
В память о моих родителях, Миле и Габи Бен-Ган
СОдержанИе Содержание Об авторе ............................................................................................................................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.