PostgreSQL. Профессиональный SQL
Покупка
Новинка
Тематика:
Программирование и алгоритмизация
Издательство:
ДМК Пресс
Автор:
Моргунов Евгений Павлович
Под ред.:
Рогов Егор Валерьевич
Год издания: 2025
Кол-во страниц: 446
Дополнительно
Вид издания:
Учебное пособие
Уровень образования:
ВО - Бакалавриат
ISBN: 978-5-93700-373-7
Артикул: 890843.01.99
Учебное пособие представляет собой вторую часть курса по языку SQL, созданного при поддержке российской компании Postgres Professional. В книге рассмотрены такие расширенные возможности СУБД PostgreSQL, как общие табличные выражения, агрегатные и оконные функции, подзапросы LATERAL, создание пользовательских функций и процедур. Материал сопровождается многочисленными практическими примерами, заданиями и упражнениями, дополняющими основной текст каждой главы. Пособие заинтересует читателей, желающих повысить базовый уровень владения языком SQL. Изучать материал пособия можно как под руководством преподавателя, так и самостоятельно.
Сайт книги: https://postgrespro.ru/education/books/advancedsql.
Тематика:
ББК:
УДК:
ОКСО:
- ВО - Бакалавриат
- 09.03.01: Информатика и вычислительная техника
- 09.03.02: Информационные системы и технологии
ГРНТИ:
Скопировать запись
Фрагмент текстового слоя документа размещен для индексирующих роботов
Компания Postgres Professional Е. П. Моргунов PostgreSQL. Профессиональный SQL Учебное пособие Москва, 2025
УДК 004.65 ББК 32.972.134 М79 Моргунов, Е. П. М79 PostgreSQL. Профессиональный SQL : учеб. пособие / Е. П. Моргунов; под ред. Е. В. Рогова. — М. : ДМК Пресс, 2025. — 444 с. ISBN 978-5-93700-373-7 Учебное пособие представляет собой вторую часть курса по языку SQL, созданного при поддержке российской компании Postgres Professional. В книге рассмотрены такие расширенные возможности СУБД PostgreSQL, как общие табличные выражения, агрегатные и оконные функции, подзапросы LATERAL, создание пользовательских функций и процедур. Материал сопровождается многочисленными практическими примерами, заданиями и упражнениями, дополняющими основной текст каждой главы. Пособие заинтересует читателей, желающих повысить базовый уровень владения языком SQL. Изучать материал пособия можно как под руководством преподавателя, так и самостоятельно. Сайт книги: https://postgrespro.ru/education/books/advancedsql. УДК 004.65 ББК 32.972.134 © Текст, оформление. ООО «ППГ», 2025 ISBN 978-5-93700-373-7 © Издание. ДМК Пресс, 2025
Оглавление Глава 1. Введение 11 1.1. О книге . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 1.2. Учебная база данных . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 1.3. Благодарности автора . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Глава 2. Общие табличные выражения 19 2.1. Запросы с несколькими общими табличными выражениями . . . 19 2.2. Рекурсивные общие табличные выражения . . . . . . . . . . . . . . 28 2.3. Массивы в общих табличных выражениях . . . . . . . . . . . . . . 49 2.4. Модификация данных в общем табличном выражении . . . . . . . 69 2.5. Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . 82 Глава 3. Аналитические возможности PostgreSQL 109 3.1. Агрегатные функции . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 3.2. Статистические функции . . . . . . . . . . . . . . . . . . . . . . . . 126 3.3. GROUPING SETS, CUBE и ROLLUP . . . . . . . . . . . . . . . . . . . . 137 3.4. Оконные функции . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 3.5. Гипотезирующие агрегатные функции . . . . . . . . . . . . . . . . 186 3.6. Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . 187 Глава 4. Конструкция LATERAL команды SELECT 225 4.1. Подзапросы в предложении FROM . . . . . . . . . . . . . . . . . . . 225 4.2. Вызовы функций в предложении FROM . . . . . . . . . . . . . . . . 234 4.3. Тип JSON и конструкция LATERAL . . . . . . . . . . . . . . . . . . . 236 4.4. Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . 244 Глава 5. Подпрограммы 263 5.1. Базовые сведения о функциях . . . . . . . . . . . . . . . . . . . . . 263 5.2. Функции и зависимости между объектами базы данных . . . . . . 288 5.3. Функции, возвращающие множества строк . . . . . . . . . . . . . . 298 5.4. Функции с переменным числом аргументов . . . . . . . . . . . . . 305 5.5. Конструкция LATERAL и функции . . . . . . . . . . . . . . . . . . . 309 5.6. Категории изменчивости функций . . . . . . . . . . . . . . . . . . . 314 5.7. Дополнительные сведения о функциях . . . . . . . . . . . . . . . . 347
Оглавление 5.8. Элементы теории принятия решений . . . . . . . . . . . . . . . . . 357 5.9. Процедуры . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367 5.10. Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . 372 Предметный указатель
Содержание Глава 1. Введение 11 1.1. О книге . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 1.2. Учебная база данных . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 1.3. Благодарности автора . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Глава 2. Общие табличные выражения 19 2.1. Запросы с несколькими общими табличными выражениями . . . 19 2.2. Рекурсивные общие табличные выражения . . . . . . . . . . . . . . 28 2.3. Массивы в общих табличных выражениях . . . . . . . . . . . . . . 49 2.3.1. Выявление циклов . . . . . . . . . . . . . . . . . . . . . . . . 49 2.3.2. Выявление множественных путей . . . . . . . . . . . . . . . 60 2.3.3. Поиск маршрута между двумя городами . . . . . . . . . . . 64 2.4. Модификация данных в общем табличном выражении . . . . . . . 69 2.5. Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . 82 1. Материализация общего табличного выражения может влиять на скорость выполнения всего запроса . . . . . . . . . . . . . . . . . . . . . . . . . 82 2. А если главный запрос использует не все строки, порождаемые конструкцией WITH? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 3. Изменчивая функция random: неожиданные эффекты . . . . . . . . . . . . . 87 4. Не является ли промежуточная таблица лишним звеном? . . . . . . . . . . . 89 5. Имя таблицы одно и то же, а совпадают ли наборы строк? . . . . . . . . . . 89 6. Почему рекурсивный запрос может зациклиться и что с этим делать? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 7. Разница между UNION и UNION ALL в рекурсивном запросе . . . . . . . . 91 8. Порядком обхода иерархии управлять нельзя, а порядком вывода — можно . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 9. Для поиска циклов в графе есть встроенный синтаксис . . . . . . . . . . . . 95 10. В распавшейся на части иерархии тоже могут быть дефекты . . . . . . . . 95 11. Можно ли ускорить поиск маршрута между двумя городами? . . . . . . . . 98 12. Пути к вершинам-листьям можно найти, не создавая их список заранее . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 13. В массив можно «собрать» не только путь, но и стоимости ребер вдоль этого пути . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 14. Повторим все эксперименты на «большой» двоичной иерархии . . . . . 101
Содержание 15. Как выбрать альтернативные пути, имеющиеся в иерархии? . . . . . . . . . 102 16. С помощью запросов, разработанных для иерархий, можно исследовать и граф общего вида . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 17. Главный запрос и все общие табличные выражения в WITH работают с одним и тем же снимком данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 18. Процедуру архивирования таблиц можно ускорить . . . . . . . . . . . . . . . . 106 19. В тексте главы была первая часть процедуры архивирования таблиц, а это — ее завершение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 Глава 3. Аналитические возможности PostgreSQL 109 3.1. Агрегатные функции . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 3.1.1. Краткий обзор . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 3.1.2. Агрегирование в параллельном режиме . . . . . . . . . . . . 114 3.1.3. Агрегирование данных типа JSON . . . . . . . . . . . . . . . 121 3.2. Статистические функции . . . . . . . . . . . . . . . . . . . . . . . . 126 3.3. GROUPING SETS, CUBE и ROLLUP . . . . . . . . . . . . . . . . . . . . 137 3.3.1. Группировка с помощью GROUPING SETS . . . . . . . . . . . 137 3.3.2. Группировка с помощью ROLLUP . . . . . . . . . . . . . . . . 142 3.3.3. Группировка с помощью CUBE . . . . . . . . . . . . . . . . . 145 3.4. Оконные функции . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 3.4.1. Использование агрегатных функций в качестве оконных . 156 3.4.2. Способы формирования оконного кадра . . . . . . . . . . . 164 3.4.3. Совместное использование оконных и агрегатных функций . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 3.4.4. Оконные функции общего назначения . . . . . . . . . . . . 176 3.5. Гипотезирующие агрегатные функции . . . . . . . . . . . . . . . . 186 3.6. Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . 187 1. Битовые строки тоже можно агрегировать: функции bit_and и bit_or . . 187 2. Битовые строки тоже можно агрегировать: функция bit_xor . . . . . . . . . 190 3. В каких частях запроса можно использовать агрегатные выражения? . 190 4. Агрегирование в параллельном режиме . . . . . . . . . . . . . . . . . . . . . . . . 191 5. Агрегирование числовых данных, содержащихся в JSON-объектах . . . . 192 6. Как первичный ключ влияет на выбор группируемых столбцов? . . . . . 193 7. Эксперимент с процентилями . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 8. Аргумент-массив функции можно сгенерировать, но есть нюансы . . . . 196 9. Небольшое статистическое исследование . . . . . . . . . . . . . . . . . . . . . . . 197 10. Сопоставление конструкции UNION с конструкцией GROUPING SETS . . 199
Содержание 11. Влияет ли порядок следования групп столбцов в конструкции GROUPING SETS на работу запроса? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 12. Конструкция ROLLUP и ее отражение в плане запроса . . . . . . . . . . . . . 201 13. Конструкция CUBE, предложение HAVING и функция GROUPING . . . . . . 203 14. Сопоставление конструкции UNION с конструкцией CUBE . . . . . . . . . . 206 15. Комбинирование конструкций GROUPING SETS, CUBE и ROLLUP . . . . . 208 16. Оконная функция в предложении ORDER BY: можно ли обойтись без нее? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211 17. В запросе может быть несколько разных определений окна . . . . . . . . . 213 18. Использование условия в определении раздела . . . . . . . . . . . . . . . . . . 215 19. Эксперименты с определениями окон и оконными функциями . . . . . . . 217 20. Функции lead и lag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221 21. Оконные функции и родственные строки . . . . . . . . . . . . . . . . . . . . . . . . 223 22. Сравнение режимов формирования оконного кадра RANGE, ROWS и GROUPS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223 Глава 4. Конструкция LATERAL команды SELECT 225 4.1. Подзапросы в предложении FROM . . . . . . . . . . . . . . . . . . . 225 4.2. Вызовы функций в предложении FROM . . . . . . . . . . . . . . . . 234 4.3. Тип JSON и конструкция LATERAL . . . . . . . . . . . . . . . . . . . 236 4.4. Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . 244 1. Оконная функция вместо конструкции LATERAL при отборе пассажиров для поощрения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244 2. Еще один вариант решения задачи отбора пассажиров для поощрения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245 3. Наглядное представление планировок салонов . . . . . . . . . . . . . . . . . . . 247 4. Напоминает ли конструкция LATERAL коррелированный подзапрос? . . 250 5. Выявление пропусков в нумерации . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250 6. Конструкция LATERAL вместо оконных функций . . . . . . . . . . . . . . . . . . 253 7. Использование конструкций LATERAL и WITH для многошаговых вычислений . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255 8. Использование LATERAL во вложенных подзапросах . . . . . . . . . . . . . . 259 9. Функция JSON_TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260 Глава 5. Подпрограммы 263 5.1. Базовые сведения о функциях . . . . . . . . . . . . . . . . . . . . . 263 5.1.1. Создание функций . . . . . . . . . . . . . . . . . . . . . . . . 264 5.1.2. Перегрузка функций . . . . . . . . . . . . . . . . . . . . . . . 275 5.1.3. Удаление функций . . . . . . . . . . . . . . . . . . . . . . . . 279
Содержание 5.1.4. Функции, включающие несколько SQL-команд . . . . . . . 280 5.1.5. Функции в стиле стандарта SQL . . . . . . . . . . . . . . . . 283 5.1.6. Значения NULL в качестве аргументов функции . . . . . . . 285 5.2. Функции и зависимости между объектами базы данных . . . . . . 288 5.2.1. Зависимость объектов базы данных от функций . . . . . . . 291 5.2.2. Зависимость функций от объектов базы данных . . . . . . . 296 5.3. Функции, возвращающие множества строк . . . . . . . . . . . . . . 298 5.4. Функции с переменным числом аргументов . . . . . . . . . . . . . 305 5.5. Конструкция LATERAL и функции . . . . . . . . . . . . . . . . . . . 309 5.6. Категории изменчивости функций . . . . . . . . . . . . . . . . . . . 314 5.6.1. Влияние изменчивости на выбор оптимального плана . . . 316 5.6.2. Видимость изменений . . . . . . . . . . . . . . . . . . . . . . 338 5.7. Дополнительные сведения о функциях . . . . . . . . . . . . . . . . 347 5.7.1. Подстановка кода функций в запрос . . . . . . . . . . . . . . 347 5.7.2. Функции и параллельный режим выполнения запросов . . 351 5.8. Элементы теории принятия решений . . . . . . . . . . . . . . . . . 357 5.9. Процедуры . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367 5.10. Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . 372 1. Совместное использование параметров с модификатором OUT и предложения RETURNS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372 2. Значение параметра функции в качестве идентификатора в ее коде — это возможно? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372 3. Если имена параметров функции совпадают с именами столбцов таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372 4. Символ одинарной кавычки в теле функции . . . . . . . . . . . . . . . . . . . . . 373 5. Перегруженные функции и значения параметров по умолчанию . . . . . 374 6. Аргументом функции может быть и значение NULL . . . . . . . . . . . . . . . . 377 7. Взаимосвязи объектов в базе данных . . . . . . . . . . . . . . . . . . . . . . . . . . 377 8. Могут ли параметры с модификаторами INOUT и OUT идти вперемежку? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378 9. Параметр VARIADIC идет последним. Почему? . . . . . . . . . . . . . . . . . . . . 379 10. Псевдонимы таблиц и столбцов: есть некоторая свобода . . . . . . . . . . . 380 11. Когда в предложении FROM несколько табличных функций . . . . . . . . . 381 12. Табличные функции в списке SELECT? Иногда можно, но все же лучше в предложении FROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388 13. Стабильные функции могут зависеть от настроек сервера . . . . . . . . . . . 396 14. Функция random. Как получить одни и те же значения при многократном вызове? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Содержание 15. Функция random. Неожиданные результаты . . . . . . . . . . . . . . . . . . . . . . 398 16. Поиск корней квадратного уравнения . . . . . . . . . . . . . . . . . . . . . . . . . . 399 17. Параметр конфигурации сервера можно изменить на время выполнения функции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402 18. Видит ли изменчивая функция изменения, произведенные конкурентной транзакцией? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 19. А если вызвать изменчивую функцию из стабильной или постоянной? . 405 20. Не только багаж, но и питание . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406 21. Подведение итогов по операции бронирования: подстановка кода функции в запрос . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406 22. Подстановка в запрос кода скалярной функции . . . . . . . . . . . . . . . . . . 408 23. Пользовательские функции в индексных выражениях . . . . . . . . . . . . . . 411 24. Иллюстрация использования системного каталога pg_depend . . . . . . . 414 25. Подстановка кода функций в запрос: детальные эксперименты . . . . . . 417 26. Как отобрать из Парето-оптимального множества единственную альтернативу? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418 27. Функция pareto: результат попарного сравнения альтернатив в другой форме . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419 28. Составные значения в качестве аргументов функций . . . . . . . . . . . . . . 419 29. ROWS — характеристика количества строк, возвращаемых функцией . . 421 30. COST— характеристика стоимости вычисления функции . . . . . . . . . . . 426 31. Вычисляемые столбцы, сохраняемые в таблице . . . . . . . . . . . . . . . . . . . 427 32. Сортировка массивов разной размерности . . . . . . . . . . . . . . . . . . . . . . 430 33. Формирование русского или английского алфавита . . . . . . . . . . . . . . . 432 34. Функция для определения степени заполнения самолетов . . . . . . . . . . 433 Предметный указатель