Книжная полка Сохранить
Размер шрифта:
А
А
А
|  Шрифт:
Arial
Times
|  Интервал:
Стандартный
Средний
Большой
|  Цвет сайта:
Ц
Ц
Ц
Ц
Ц

PostgreSQL. Профессиональный SQL

Покупка
Новинка
Артикул: 890843.01.99
Доступ онлайн
1 899 ₽
В корзину
Учебное пособие представляет собой вторую часть курса по языку SQL, созданного при поддержке российской компании Postgres Professional. В книге рассмотрены такие расширенные возможности СУБД PostgreSQL, как общие табличные выражения, агрегатные и оконные функции, подзапросы LATERAL, создание пользовательских функций и процедур. Материал сопровождается многочисленными практическими примерами, заданиями и упражнениями, дополняющими основной текст каждой главы. Пособие заинтересует читателей, желающих повысить базовый уровень владения языком SQL. Изучать материал пособия можно как под руководством преподавателя, так и самостоятельно. Сайт книги: https://postgrespro.ru/education/books/advancedsql.
Моргунов, Е. П. PostgreSQL. Профессиональный SQL : учебное пособие / Е. П. Моргунов ; под ред. Е. В. Рогова. – Москва : ДМК Пресс, 2025. - 446 с. – ISBN 978-5-93700-373-7. - Текст : электронный. - URL: https://znanium.ru/catalog/product/2275253 (дата обращения: 18.11.2026). – Режим доступа: по подписке.
Фрагмент текстового слоя документа размещен для индексирующих роботов
Компания 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
Предметный указатель




Доступ онлайн
1 899 ₽
В корзину