Оконные функции в 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. Также Ицик пишет статьи для платформ sqlper- formance.com, ITProToday и SolidQ и участвует в различных конференциях, в числе которых PASS Summit и SQLBits. Является ведущим специалистом в области T-SQL и автором курсов Advanced T-SQL Querying, Programming and Tuning и T-SQL Fundamentals в компании SolidQ.
Структура книги 11 Введение Для меня лично оконные функции представляются наиболее важным элементом, поддерживаемым как стандартом SQL, так и его диалектом для Microsoft SQL Server, называемым T-SQL. Они позволяют выполнять вычисления применительно к целым наборам строк в очень гибкой, понятной и эффективной манере. Искусность принципов реализации оконных функций трудно переоценить, и у традиционных альтернатив со всеми присущими им недостатками нет против них ни шансов, ни аргументов. Спектр задач, которые легко решаются при помощи оконных функций, столь широк, что стоит задуматься о том, чтобы изучить эту концепцию. С момента своего появления оконные функции получили большое развитие как в SQL Server, так и в стандарте SQL. В этой книге мы будем говорить как о специфических для SQL Server свойствах применения оконных функций, так и об особенностях их использования в стандарте SQL, включая те, которые еще не реализованы в SQL Server. для кого эта книга Эта книга предназначена для разработчиков SQL Server, администраторов баз данных (DBA), специалистов в области обработки данных и бизнес-аналитики, а также для тех, кто пишет запросы и разрабатывает код на языке T-SQL. В книге мы будем предполагать, что у вас за плечами есть как минимум полугодовой опыт написания и отладки запросов на языке T-SQL. Структура книги В книге освещаются как логические аспекты оконных функций, так и вопросы их оптимизации и практического применения. Глава 1. Работа с окнами в SQL. В данной главе мы опишем концепцию оконных функций в языке SQL. Здесь вы познакомитесь с назначением оконных функций, их типами и элементами, участвующими в их создании и применении, включая секционирование, упорядочивание и определение границ окна. Глава 2. Детальное изучение оконных функций. В этой главе мы погрузимся в детали и специфику оконных функций, подробно разберем агрегат-
ВВеденИе ные и ранжирующие оконные функции, функции смещения и статистические функции (функции распределения). Глава 3. Функции упорядоченного набора. В третьей главе мы поговорим о поддержке в языке T-SQL и стандарте SQL функций для работы с упорядоченными наборами, включая конкатенацию строк, а также рассмотрим функции для работы с гипотетическими наборами, функции обратного распределения и др. Кроме того, для стандартных функций, пока не реализованных в языке T-SQL, мы приведем работающие решения. Глава 4. Распознавание шаблонов в строках. Здесь мы коснемся продвинутой концепции анализа данных, именуемой распознаванием шаблонов в строках (row-pattern recognition – RPR), которую можно рассматривать как следующий шаг развития оконных функций. В языке T-SQL данный функционал пока не реализован, но, как уже было упомянуто ранее, в этой книге мы представим вам все важнейшие аналитические концепции, даже не воплощенные на данный момент в T-SQL. Глава 5. Оптимизация оконных функций. В этой главе мы рассмотрим способы оптимизации оконных функций применительно к SQL Server и SQL Azure Database. Мы поговорим о применении индексации с целью ускорения выполнения запросов, затронем тему параллельных вычислений, сравним построчную и пакетную обработку запросов и узнаем другие способы оптимизации оконных функций. Глава 6. Практическое применение оконных функций в T-SQL. В заключительной главе книги мы обратимся к практическому применению оконных функций для решения распространенных бизнес-задач. Системные требования Оконные функции являются составной частью ядра баз данных Microsoft SQL Server и SQL Azure Database, в связи с чем их поддержка реализована во всех версиях продуктов. Для запуска примеров из этой книги вам необходимо иметь доступ к экземпляру установки SQL Server 2019 и старше (любой версии) или SQL Azure Database с установленной базой данных с уровнем совместимости ( compatibility level) 150 и выше. Также вам придется установить базу данных с названием TSQLV5, к которой мы будем обращаться в этой книге. Если у вас нет доступа к установленному экземпляру СУБД, на сайте Microsoft присутствуют бесплатные версии. Подробнее можно узнать по адресу https:// www.microsoft.com/en-us/sql-server/sql-server-downloads. В качестве клиентских инструментов для подключения к базам данных и выполнения представленных в книге примеров вы можете использовать SQL Server Management Studio (SSMS) или Azure Data Studio. Я использовал SSMS для создания графических представлений планов выполнения запросов, показанных в книге. Вы можете загрузить этот инструмент по ссылке https://docs. microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms. Azure Data Studio можно скачать по адресу https://docs.microsoft.com/en-us/sql/ azure-data-studio/download.
ВВедение 13 Примеры из книги Все примеры из этой книги, а также сопутствующие данные, ресурсы и многое другое вы можете загрузить со страницы книги на сайте издательства www.dmkpress.com. На странице книги присутствует ссылка на архив, включающий в себя все примеры кода из книги, а также файл TSQLV5.sql, содержащий инструкции для создания и наполнения базы данных TSQLV5. Благодарности Многие люди прямо или косвенно повлияли на выход в свет этой книги, и они, конечно, достойны упоминания и благодарностей. Спасибо Лилах за придание смысла всему, что я делаю, и за помощь с вычиткой текста. Благодарю всех разработчиков Microsoft SQL Server (бывших и нынешних), а в особенности: Конора Каннингема (Conor Cunningham), Джо Сака (Joe Sack), Василиса Пападимоса (Vassilis Papadimos), Марка Фридмана (Marc Friedman), Крейга Фридмана (Craig Freedman), Милана Ружича (Milan Ruzic), Милана Стои ча (Milan Stojic), Йована Поповича (Jovan Popovic), Борко Новаковича (Borko Novakovic), Тобиаса Тернстрёма (Tobias Ternström), Лубора Коллара (Lu- bor Kollar), Умачандара Джаячандрана (Umachandar Jayachandran), Педро Ло- песа (Ped ro Lopes), Архениса Фернандеса (Argenis Fernandez) и многих других. Я очень признателен вам за реализацию и поддержку оконных функций в SQL Server, а также за общение со мной и ответы на мои вопросы и просьбы что-то разъяснить. Кроме того, я благодарен всей редакторской команде издательства Pearson. Лоретта Йейтс (Loretta Yates), спасибо за то, что поверила в этот проект и позволила ему реализоваться! Моя признательность Чарви Ароре (Charvi Arora) за приложенные огромные усилия. Также я благодарен Рику Кугену (Rick Ku- ghen) и Трейси Крум (Tracey Croom). Спасибо Ашвини Кумар (Aswini Kumar) и ее команде за работу над PDF-версией книги. Помимо прочих, хочу выразить отдельную благодарность Адаму Маханику (Adam Machanic) за согласие стать техническим редактором книги. Существует не так много людей, понимающих архитектуру SQL Server лучше тебя. Для меня вопрос выбора человека на эту роль вообще не стоял. Спасибо Q2, Q3 и Q4. Для меня большое счастье иметь возможность обсуждать идеи, связанные с книгой, с такими профессионалами в области SQL, как вы. Кажется, я могу делиться с вами всем без опасений за возможные последствия. Также я хочу выразить благодарность моей компании SolidQ за последние два десятилетия. Быть частью такого коллектива и наблюдать за ростом компании – настоящее счастье. Для меня сотрудники этой компании – это больше, чем просто коллеги. Это партнеры, друзья и семья. Спасибо Фернандо Герреро (Fernando G. Guerrero), Антонио Сото (Antonio Soto) и многим другим.
ВВеденИе Отдельную признательность я выражаю Аарону Бертрану (Aaron Bertrand) и Грегу Гонзалесу (Greg Gonzales). Мне очень приятно вести колонку на сайте https://sqlperformance.com. SentryOne – прекрасная компания, создающая для сообщества отличные продукты и сервисы. Также я благодарен MVP в области SQL Server Алехандро Месе (Alejandro Mesa), Эрланду Соммарскогу (Erland Sommarskog), Аарону Бертрану (Aaron Ber- trand), Полу Уайту (Paul White) и многим другим. Я очень рад быть частью этой великолепной программы. Уровень знаний этих людей просто поражает, и мне всегда приятно встречаться с ними, разговаривать на профессиональные темы или просто попить пива. Я уверен, что в Microsoft решили уделить отдельное внимание развитию оконных функций в SQL Server именно под влиянием сообщества, и не последнюю роль в этом сыграли наши MVP. Очень приятно наблюдать за тем, как совместные усилия выливаются в нечто большее. Наконец, я хотел бы сказать спасибо своим студентам. Я обожаю преподавать SQL, это моя страсть, и я благодарен вам за возможность ее удовлетворять. А ваши бесконечные вопросы позволяют мне двигаться дальше, обретая новые знания. Отзывы и пожелания Мы всегда рады отзывам наших читателей. Расскажите нам, что вы думаете об этой книге – что понравилось или, может быть, не понравилось. Отзывы важны для нас, чтобы выпускать книги, которые будут для вас максимально полезны. Вы можете написать отзыв на нашем сайте www.dmkpress.com, зайдя на страницу книги и оставив комментарий в разделе «Отзывы и рецензии». Также можно послать письмо главному редактору по адресу dmkpress@gmail.com; при этом укажите название книги в теме письма. Если вы являетесь экспертом в какой-либо области и заинтересованы в написании новой книги, заполните форму на нашем сайте по адресу http://dmk- press.com/authors/publish_book/ или напишите в издательство по адресу dmk- press@gmail.com. Список опечаток Хотя мы приняли все возможные меры для того, чтобы обеспечить высокое качество наших текстов, ошибки все равно случаются. Если вы найдете ошибку в одной из наших книг, мы будем очень благодарны, если вы сообщите о ней главному редактору по адресу dmkpress@gmail.com. Сделав это, вы избавите других читателей от недопонимания и поможете нам улучшить последующие издания этой книги.