Учебное пособие СанктПетербург бхвпетербург

• Название: Учебное пособие СанктПетербург бхвпетербург
• Дата: 12.02.2023
• Размер: 1.88 Mb.
• Имя файла: sql_primer.pdf
• Тип: Учебное пособие; #933464
• страница: 10 из 20

Глава 6
Запросы
Эта глава будет самой насыщенной и интересной, поскольку умение писать SQL-запросы — это не только ремесло, но, пожалуй, и искусство тоже.
В предыдущих главах мы уже не раз использовали команду
SELECT и формировали с ее помощью различные запросы. Эти запросы строились как на основе одной таблицы, так и на основе двух и более таблиц. Мы рассмотрели простые способы сортировки и группировки строк в полученных вы- борках из таблиц, использовали функцию count для подсчета числа выбранных строк. Таким образом,
вы уже получили элементарное представление о том, как формировать выборки из базы данных.
В этой главе мы покажем более сложные способы их получения.
С целью приведения в систему тех знаний о формировании выборок, что были получены в предыду- щих главах, в этой главе мы повторим некоторые сведения, но сделаем это уже на новых примерах.
6.1. Дополнительные возможности команды SELECT
Основой для экспериментов в этом разделе будут самые маленькие (по числу строк)
таблицы базы данных «Авиаперевозки»: «Самолеты» (aircrafts) и «Аэропорты»
(airports).
Прежде чем перейти к конкретным запросам, просто просмотрите содержимое этих двух таблиц. Таблица «Самолеты» совсем маленькая, а таблица «Аэропорты» содер- жит чуть больше ста строк. Для ее просмотра можно включить расширенный режим вывода данных \x.
SELECT * FROM aircrafts;
SELECT * FROM airports;
Начнем с различных условий отбора строк в предложении WHERE. Эти условия мо- гут конструироваться с использованием следующих операторов сравнения: =, < >,
>
, > =, <, < =. В предыдущих главах мы уже использовали ряд таких операторов,
поэтому сейчас рассмотрим некоторые другие способы осуществления отбора строк.
145

Глава 6. Запросы
Для начала поставим перед собой такую задачу: выбрать все самолеты компании
Airbus. В этом нам поможет оператор поиска шаблонов LIKE:
SELECT * FROM aircrafts WHERE model LIKE 'Airbus%';
Обратите внимание на символ «%», имеющий специальное значение. Он соответ- ствует любой последовательности символов, т. е. вместо него могут быть подстав- лены любые символы в любом количестве, а может и не быть подставлено ни одного символа. В результате будут выбраны строки, в которых значения атрибута model начинаются с символов «Airbus»:
aircraft_code |
model
| range
---------------+-----------------+-------
320
| Airbus A320-200 | 5700 321
| Airbus A321-200 | 5600 319
| Airbus A319-100 | 6700
(3 строки)
Шаблон в операторе LIKE всегда покрывает всю анализируемую строку. Поэтому если требуется отыскать некоторую последовательность символов где-то внутри строки,
то шаблон должен начинаться и завершаться символом «%». Однако в этом случае нужно учитывать следующие соображения. Если по тому столбцу, к которому при- меняется оператор LIKE, создан индекс для ускорения доступа к данным, то при наличии символа «%» в начале шаблона этот индекс использоваться не будет. Из-за этого может ухудшиться производительность, т. е. запрос будет выполняться медлен- нее. Индексы подробно рассматриваются в главе 8, а вопросы производительности —
в главе 10.
Конечно, существует и оператор NOT LIKE. Например, если мы захотим узнать, каки- ми самолетами, кроме машин компаний Airbus и Boeing, располагает наша авиаком- пания, то придется усложнить условие:
SELECT * FROM aircrafts
WHERE model NOT LIKE 'Airbus%'
AND model NOT LIKE 'Boeing%';
aircraft_code |
model
| range
---------------+---------------------+-------
SU9
| Sukhoi SuperJet-100 | 3000
CN1
| Cessna 208 Caravan | 1200
CR2
| Bombardier CRJ-200 | 2700
(3 строки)
146

6.1. Дополнительные возможности команды SELECT
Кроме символа «%» в шаблоне может использоваться и символ подчеркивания — «_»,
который соответствует в точности одному любому символу. В качестве примера най- дем в таблице «Аэропорты» те из них, которые имеют названия длиной три символа
(буквы). С этой целью зададим в качестве шаблона строку, состоящую из трех симво- лов «_».
SELECT * FROM airports WHERE airport_name LIKE '___';
-[ RECORD 1 ]-+------------------- airport_code | UFA
airport_name | Уфа city
| Уфа longitude
| 55.874417
latitude
| 54.557511
timezone
| Asia/Yekaterinburg
Существует ряд операторов для работы с регулярными выражениями POSIX. Эти операторы имеют больше возможностей, чем оператор LIKE. Для того чтобы вы- брать, например, самолеты компаний Airbus и Boeing, можно сделать так:
SELECT * FROM aircrafts WHERE model

'^(A|Boe)';
aircraft_code |
model
| range
---------------+-----------------+-------
773
| Boeing 777-300 | 11100 763
| Boeing 767-300 | 7900 320
| Airbus A320-200 | 5700 321
| Airbus A321-200 | 5600 319
| Airbus A319-100 | 6700 733
| Boeing 737-300 | 4200
(6 строк)
Оператор ищет совпадение с шаблоном с учетом регистра символов. Символ «^»
в начале регулярного выражения означает, что поиск совпадения будет привязан к началу строки. Если же требуется проверить наличие такого символа в составе
строки, то перед ним нужно поставить символ обратной косой черты «\». Выражение в круглых скобках означает альтернативный выбор между значениями, разделяемы- ми символом «|». Поэтому в выборку попадут значения, начинающиеся либо на «A»,
либо на «Boe».
Для инвертирования смысла оператора нужно перед ним добавить знак «!». В каче- стве примера отыщем модели самолетов, которые не завершаются числом 300.
SELECT * FROM aircrafts WHERE model ! '300$';
147

Глава 6. Запросы
В этом регулярном выражении символ «$» означает привязку поискового шаблона к концу строки. Если же требуется проверить наличие такого символа в составе стро- ки, то перед ним нужно поставить символ обратной косой черты «\».
aircraft_code |
model
| range
---------------+---------------------+-------
SU9
| Sukhoi SuperJet-100 | 3000 320
| Airbus A320-200
| 5700 321
| Airbus A321-200
| 5600 319
| Airbus A319-100
| 6700
CN1
| Cessna 208 Caravan | 1200
CR2
| Bombardier CRJ-200 | 2700
(6 строк)
Использование регулярных выражений подробно рассматривается в разделе доку- ментации 9.7.3 «Регулярные выражения POSIX».
В качестве замены традиционных операторов сравнения могут использоваться пре-
дикаты сравнения
, которые ведут себя так же, как и операторы, но имеют другой синтаксис.
Давайте ответим на вопрос: какие самолеты имеют дальность полета в диапазоне от
3 000 км до 6 000 км? Ответ получим с помощью предиката BETWEEN.
SELECT * FROM aircrafts WHERE range BETWEEN 3000 AND 6000;
aircraft_code |
model
| range
---------------+---------------------+-------
SU9
| Sukhoi SuperJet-100 | 3000 320
| Airbus A320-200
| 5700 321
| Airbus A321-200
| 5600 733
| Boeing 737-300
| 4200
(4 строки)
Обратите внимание, что граничное значение 3 000 включено в полученную выборку.
При выборке данных можно проводить вычисления и получать в результирующей таблице вычисляемые столбцы. Если мы захотим представить дальность полета не только в километрах, но и в милях, то нужно вычислить это выражение и для удобства присвоить новому столбцу псевдоним с помощью ключевого слова AS.
SELECT model, range, range / 1.609 AS miles FROM aircrafts;
148

6.1. Дополнительные возможности команды SELECT
model
| range |
miles
---------------------+-------+-----------------------
Boeing 777-300
| 11100 | 6898.6948415164698571
Boeing 767-300
| 7900 | 4909.8819142324425109
(9 строк)
По всей вероятности, такая высокая точность представления значений в милях не требуется, поэтому мы можем уменьшить ее до разумного предела в два десятичных знака:
SELECT model, range, round( range / 1.609, 2 ) AS miles
FROM aircrafts;
model
| range | miles
---------------------+-------+---------
Boeing 777-300
| 11100 | 6898.69
Boeing 767-300
| 7900 | 4909.88
Теперь обратимся к такому вопросу, как упорядочение строк при выводе. Если не принять специальных мер, то СУБД не гарантирует никакого конкретного поряд- ка строк в результирующей выборке. Для упорядочения строк служит предложение
ORDER BY, которое мы уже использовали ранее. Однако мы не говорили, что можно задать не только возрастающий, но также и убывающий порядок сортировки. Напри- мер, если мы захотим разместить самолеты в порядке убывания дальности их полета,
то нужно сделать так:
SELECT * FROM aircrafts ORDER BY range DESC;
aircraft_code |
model
| range
---------------+---------------------+-------
773
| Boeing 777-300
| 11100 763
| Boeing 767-300
| 7900 319
| Airbus A319-100
| 6700 320
| Airbus A320-200
| 5700 321
| Airbus A321-200
| 5600 733
| Boeing 737-300
| 4200
SU9
| Sukhoi Superjet-100 | 3000
CR2
| Bombardier CRJ-200 | 2700
CN1
| Cessna 208 Caravan | 1200
(9 строк)
149

Глава 6. Запросы
Мы детально разобрались с таблицей «Самолеты» и теперь обратим наше внимание на таблицу «Аэропорты»). В ней есть столбец «Часовой пояс» (timezone). Давайте по- смотрим, в каких различных часовых поясах располагаются аэропорты. Если сделать традиционную выборку
SELECT timezone FROM airports;
то мы получим список значений, среди которых будет много повторяющихся. Ко- нечно, это неудобно. Для того чтобы оставить в выборке только неповторяющиеся
значения
, служит ключевое слово DISTINCT:
SELECT DISTINCT timezone FROM airports ORDER BY 1;
Обратите внимание, что столбец, по значениям которого будут упорядочены строки,
указан не с помощью его имени, а с помощью его порядкового номера в предложении
SELECT.
Получим такой результат:
timezone
--------------------
Asia/Anadyr
Asia/Chita
Asia/Irkutsk
Asia/Kamchatka
Asia/Krasnoyarsk
Asia/Magadan
Asia/Novokuznetsk
Asia/Novosibirsk
Asia/Omsk
Asia/Sakhalin
Asia/Vladivostok
Asia/Yakutsk
Asia/Yekaterinburg
Europe/Kaliningrad
Europe/Moscow
Europe/Samara
Europe/Volgograd
(17 строк)
Таким образом, аэропорты располагаются в семнадцати различных часовых поясах.
Они описаны в базе данных часовых поясов, поддерживаемой международной орга- низацией IANA (Internet Assigned Numbers Authority), и отличаются от традиционных
150

6.1. Дополнительные возможности команды SELECT
географических и административных часовых поясов, число которых в России равно одиннадцати.
В таблице «Аэропорты» более ста записей. Если мы поставим задачу найти три самых восточных аэропорта, то для ее решения подошел бы такой алгоритм: отсортировать строки в таблице по убыванию значений столбца «Долгота» (longitude) и включить в выборку только первые три строки. Как отсортировать строки по убыванию значе- ний какого-либо столбца, вы уже знаете, а для того чтобы ограничить число строк,
включаемых в результирующую выборку, служит предложение LIMIT.
SELECT airport_name, city, longitude
FROM airports
ORDER BY longitude DESC
LIMIT 3;
airport_name |
city
| longitude
--------------+--------------------------+------------
Анадырь
| Анадырь
| 177.741483
Елизово
| Петропавловск-Камчатский | 158.453669
Магадан
| Магадан
| 150.720439
(3 строки)
А как найти еще три аэропорта, которые находятся немного западнее первой трой- ки, т. е. занимают места с четвертого по шестое? Алгоритм будет почти таким же,
как в первой задаче, но он будет дополнен еще одним шагом: нужно пропустить три первые строки, прежде чем начать вывод. Для пропуска строк служит предложение
OFFSET.
SELECT airport_name, city, longitude
FROM airports
ORDER BY longitude DESC
LIMIT 3
OFFSET 3;
airport_name
|
city
| longitude
-----------------+----------------------+------------
Хомутово
| Южно-Сахалинск
| 142.717531
Хурба
| Комсомольск-на-Амуре |
136.934
Хабаровск-Новый | Хабаровск
| 135.188361
(3 строки)
В дополнение к вычисляемым столбцам, когда выводимые значения получают пу- тем вычислений, при выборке данных из таблиц можно использовать условные вы-
ражения
, позволяющие вывести то или иное значение в зависимости от условий.
151

Глава 6. Запросы
В таблице «Самолеты» есть столбец «Максимальная дальность полета» (range). Мы можем дополнить вывод данных из этой таблицы столбцом «Класс самолета», имея в виду принадлежность каждого самолета к классу дальнемагистральных, среднема- гистральных или ближнемагистральных судов.
Для этого подойдет конструкция
CASE WHEN условие THEN выражение
[ WHEN ... ]
[ ELSE выражение ]
END
Воспользовавшись этой конструкцией в предложении SELECT и назначив новому столбцу имя с помощью ключевого слова AS, получим следующий запрос:
SELECT model, range,
CASE WHEN range < 2000 THEN 'Ближнемагистральный'
WHEN range < 5000 THEN 'Среднемагистральный'
ELSE 'Дальнемагистральный'
END AS type
FROM aircrafts
ORDER BY model;
model
| range | type
---------------------+-------+---------------------
Airbus A319-100
| 6700 | Дальнемагистральный
Airbus A320-200
| 5700 | Дальнемагистральный
Airbus A321-200
| 5600 | Дальнемагистральный
Boeing 737-300
| 4200 | Среднемагистральный
Boeing 767-300
| 7900 | Дальнемагистральный
Boeing 777-300
| 11100 | Дальнемагистральный
Bombardier CRJ-200 | 2700 | Среднемагистральный
Cessna 208 Caravan | 1200 | Ближнемагистральный
Sukhoi SuperJet-100 | 3000 | Среднемагистральный
(9 строк)
6.2. Соединения
В тех случаях, когда информации, содержащейся в одной таблице, недостаточно для получения требуемого результата, используют соединение (join) таблиц. Покажем способ выполнения соединения на примере следующего запроса: выбрать все места,
предусмотренные компоновкой салона самолета Cessna 208 Caravan.
152

6.2. Соединения
Сначала приведем SQL-команду для выполнения запроса, а потом объясним, как мы ее придумали.
SELECT a.aircraft_code, a.model, s.seat_no, s.fare_conditions
FROM seats AS s
JOIN aircrafts AS a
ON s.aircraft_code = a.aircraft_code
WHERE a.model '^Cessna'
ORDER BY s.seat_no;
В предложении WHERE мы применили регулярное выражение, хотя в данном случае можно было с таким же успехом воспользоваться и оператором LIKE или функцией substr.
aircraft_code |
model
| seat_no | fare_conditions
---------------+--------------------+---------+-----------------
CN1
| Cessna 208 Caravan | 1A
| Economy
CN1
| Cessna 208 Caravan | 1B
| Economy
CN1
| Cessna 208 Caravan | 2A
| Economy
CN1
| Cessna 208 Caravan | 2B
| Economy
CN1
| Cessna 208 Caravan | 3A
| Economy
CN1
| Cessna 208 Caravan | 3B
| Economy
CN1
| Cessna 208 Caravan | 4A
| Economy
CN1
| Cessna 208 Caravan | 4B
| Economy
CN1
| Cessna 208 Caravan | 5A
| Economy
CN1
| Cessna 208 Caravan | 5B
| Economy
CN1
| Cessna 208 Caravan | 6A
| Economy
CN1
| Cessna 208 Caravan | 6B
| Economy
(12 строк)
Данная команда иллюстрирует соединение двух таблиц на основе равенства зна-
чений атрибутов
В этой команде в предложении FROM указаны две таблицы — aircrafts и seats,
причем каждая из них получила еще и псевдоним с помощью ключевого слова AS
(заметим, что оно не является обязательным). Конечно, псевдонимы могут состоять не только из одной буквы, как в нашем примере. Псевдонимы удобны в тех случаях,
когда в соединяемых таблицах есть одноименные атрибуты. В таких случаях в спис- ке атрибутов, следующих за ключевым словом SELECT, необходимо указывать либо имя таблицы, из которой выбирается значение этого атрибута, либо ее псевдоним, но псевдоним может быть коротким, что удобнее при написании команды. Псевдоним и атрибут соединяются символом «.». Псевдонимы используются и в предложениях
WHERE, GROUP BY, ORDER BY, HAVING, т. е. во всех частях команды SELECT.
153

Глава 6. Запросы
Итак, как мы рассуждали? Если бы в качестве исходных сведений мы получили сразу код самолета — CN1, то запрос свелся бы к выборке из одной таблицы «Места». Он был бы таким:
SELECT * FROM seats WHERE aircraft_code = 'CN1';
Но нам дано название модели, а не ее код, поэтому придется подключить к работе и таблицу «Самолеты» (aircrafts), в которой хранятся наименования моделей. Для того чтобы решить, удовлетворяет ли строка таблицы seats поставленному условию,
нужно узнать, какой модели самолета соответствует эта строка.
Как это можно узнать? В каждой строке таблицы seats есть атрибут aircraft_code,
такой же атрибут есть и в каждой строке таблицы aircrafts. Если с каждой строкой таблицы seats соединить такую строку таблицы aircrafts, в которой значение ат- рибута aircraft_code такое же, как и в строке таблицы seats, то сформированная комбинированная строка, составленная из атрибутов обеих таблиц, будет содержать не только номер места, класс обслуживания и код модели, но — что важно — и наиме- нование модели. Поэтому с помощью условия WHERE можно будет отобрать только те результирующие строки, в которых значение атрибута model будет «Cessna 208
Caravan».
А какие столбцы оставлять в списке столбцов предложения SELECT, решать нам. Даже если мы соединяем две таблицы (или более), то совершенно не обязательно в резуль- тирующий список столбцов включать столбцы всех таблиц, перечисленных в предло- жении FROM. Мы могли бы оставить только атрибуты таблицы seats:
SELECT s.seat_no, s.fare_conditions
FROM seats s
JOIN aircrafts a ON s.aircraft_code = a.aircraft_code
WHERE a.model '^Cessna'
ORDER BY s.seat_no;
seat_no | fare_conditions
---------+-----------------
1A
| Economy
1B
| Economy
2A
| Economy
2B
| Economy
3A
| Economy
3B
| Economy
4A
| Economy
4B
| Economy
5A
| Economy
154

6.2. Соединения
5B
| Economy
6A
| Economy
6B
| Economy
(12 строк)
Если подвести итог, то можно упрощенно объяснить механизм построения соедине- ния следующим образом.
Сначала формируются все попарные комбинации строк из обеих таблиц, т. е. декар- тово произведение множеств строк этих таблиц. Эти комбинированные строки вклю- чают в себя все атрибуты обеих таблиц.
Затем в дело вступает условие s.aircraft_code = a.aircraft_code. Это означает,
что в результирующем множестве строк останутся только те из них, в которых значе- ния атрибута aircraft_code, взятые из таблицы aircrafts и из таблицы seats,
одинаковые. Строки, не удовлетворяющие этому критерию, отфильтровываются.
Это означает на практике, что каждой строке из таблицы «Места» мы сопоставили только одну конкретную строку из таблицы «Самолеты», из которой мы теперь мо- жем взять значение атрибута «Модель самолета», чтобы включить ее в итоговый вы- вод данных.
На практике описанный механизм не реализуется буквально. Специальная подсисте- ма PostgreSQL, называемая планировщиком, строит план выполнения запроса, кото- рый является гораздо более эффективным, чем упрощенный план, представленный здесь. Детально вопросы планирования запросов рассматриваются в главе 10.
Запрос, который мы рассмотрели, можно записать немного по-другому, без исполь- зования предложения JOIN (обратите внимание, что мы не использовали ключевое слово AS для назначения псевдонимов таблицам).
SELECT a.aircraft_code, a.model, s.seat_no, s.fare_conditions
FROM seats s, aircrafts a
WHERE s.aircraft_code = a.aircraft_code
AND a.model '^Cessna'
ORDER BY s.seat_no;
В этом варианте условие соединения таблиц s.aircraft_code = a.aircraft_code перешло из предложения FROM в предложение WHERE, а таблицы просто перечислены в предложении FROM через запятую. Простые запросы зачастую записывают именно в такой форме, без предложения JOIN, а в предложении WHERE указывают критерии,
которым должны удовлетворять результирующие строки.
155

Глава 6. Запросы
Изучая язык SQL вообще и способы выполнения соединений в частности, нужно иметь в виду, что результатом любых реляционных операций над отношениями (таб-
лицами, представлениями) также является отношение
. Поэтому такие операции мож- но произвольно комбинировать друг с другом.
В соединении одна и та же таблица может участвовать дважды, т. е. формировать
соединение таблицы с самой собой
. В качестве примера рассмотрим запрос для создания представления «Рейсы» (flights_v), о котором шла речь в главе 5.
Этот запрос выглядит так:
CREATE OR REPLACE VIEW flights_v AS
SELECT f.flight_id,
f.flight_no,
f.scheduled_departure,
timezone( dep.timezone, f.scheduled_departure )
AS scheduled_departure_local,
f.scheduled_arrival,
timezone( arr.timezone, f.scheduled_arrival )
AS scheduled_arrival_local,
f.scheduled_arrival - f.scheduled_departure
AS scheduled_duration,
f.departure_airport,
dep.airport_name AS departure_airport_name,
dep.city AS departure_city,
f.arrival_airport,
arr.airport_name AS arrival_airport_name,
arr.city AS arrival_city,
f.status,
f.aircraft_code,
f.actual_departure,
timezone( dep.timezone, f.actual_departure )
AS actual_departure_local,
f.actual_arrival,
timezone( arr.timezone, f.actual_arrival )
AS actual_arrival_local,
f.actual_arrival - f.actual_departure AS actual_duration
FROM flights f,
airports dep,
airports arr
WHERE f.departure_airport = dep.airport_code
AND f.arrival_airport = arr.airport_code;
156

6.2. Соединения
В этом представлении используется не только таблица «Рейсы» (flights), но также и таблица «Аэропорты» (airports). Причем она используется, условно говоря, два- жды. Поясним, что мы имеем в виду.
Как вы уже знаете из главы 3, при соединении двух таблиц в результирующую выбор- ку попадают те комбинации строк из первой и второй таблиц, которые удовлетво- ряют условию, указанному в предложении WHERE. Будем рассуждать от противного.
Пусть в предложение FROM таблица «Аэропорты» (airports) будет указана только один раз, тогда предложения FROM и WHERE будут выглядеть так:
FROM flights f, airports a
WHERE f.departure_airport = a.airport_code
AND f.arrival_airport = a.airport_code;
Это означает, что при соединении двух таблиц PostgreSQL будет пытаться для каж- дой строки из таблицы flights найти такую строку в таблице airports, в кото- рой значение атрибута airport_code будет равно не только значению атрибута departure_airport, но также и значению атрибута arrival_airport в таблице flights. Получается, что данное условие будет выполнено, если только аэропорт вылета и аэропорт назначения будет одним и тем же. Однако в сфере пассажирских авиаперевозок таких рейсов не бывает. Конечно, иногда самолеты возвращаются в пункт вылета, но это уже совсем другая ситуация, которая в нашей учебной базе дан- ных не учитывается.
Таким образом, приходим к выводу о том, что каждую строку из таблицы «Рейсы»
необходимо соединять с двумя различными строками из таблицы «Аэропорты»: ведь аэропорт вылета и аэропорт назначения — это различные аэропорты. Однако при од- нократном включении таблицы «Аэропорты» в предложение FROM сделать это невоз- можно, поэтому поступают так: к таблице airports в предложении FROM обращают- ся дважды, как будто это две копии одной и той же таблицы.
Конечно, на самом деле никаких копий не создается. Просто в результате поиск строк в ней будет производиться дважды: один раз для атрибута departure_airport,
а второй раз — для атрибута arrival_airport. Но поскольку необходимо обес- печить однозначную идентификацию, то каждой «копии» (экземпляру) таблицы airports присваивают уникальный псевдоним, в нашем случае это dep и arr, т. е.
departure и arrival. Эти псевдонимы указывают, из какой «копии» (экземпляра)
таблицы airports нужно брать значение атрибута airport_code для сопоставле- ния с атрибутами departure_airport и arrival_airport.
157

Глава 6. Запросы
Рассмотрев этот пример, вновь обратимся к соединениям такого типа и покажем три способа выполнения соединения таблицы с самой собой, отличающиеся синтак- сически, но являющиеся функционально эквивалентными. Наш запрос-иллюстрация должен выяснить: сколько всего маршрутов нужно было бы сформировать, если бы требовалось соединить каждый город со всеми остальными городами? Если в городе имеется более одного аэропорта, то договоримся рейсы из каждого из них (в каждый из них) считать отдельными маршрутами. Поэтому правильнее было бы говорить не о маршрутах из каждого города, а о маршрутах из каждого аэропорта во все другие аэропорты. Конечно, рейсов из любого города в тот же самый город быть не должно.
Первый вариант запроса использует обычное перечисление имен таблиц в предло- жении FROM. Поскольку имена таблиц совпадают, используются псевдонимы. В таком случае СУБД обращается к таблице дважды, как если бы это были различные таблицы.
SELECT count( * )
FROM airports a1, airports a2
WHERE a1.city <> a2.city;
Как мы уже говорили ранее, СУБД соединяет каждую строку первой таблицы с каж- дой строкой второй таблицы, т. е. формирует декартово произведение таблиц — все попарные комбинации строк из двух таблиц. Затем СУБД отбрасывает те комбини- рованные строки, которые не удовлетворяют условию, приведенному в предложении
WHERE. В нашем примере условие как раз и отражает требование о том, что рейсов из одного города в тот же самый город быть не должно.
count
-------
10704
(1 строка)
Во втором варианте запроса мы используем соединение таблиц на основе нера-
венства значений атрибутов
. Тем самым мы перенесли условие отбора результи- рующих строк из предложения WHERE в предложение FROM.
SELECT count( * )
FROM airports a1
JOIN airports a2 ON a1.city <> a2.city;
count
-------
10704
(1 строка)
158

6.2. Соединения
Третий вариант предусматривает явное использование декартова произведения
таблиц
. Для этого служит предложение CROSS JOIN. Лишние строки, как и в первом варианте, отсеиваем с помощью предложения WHERE:
SELECT count( * )
FROM airports a1 CROSS JOIN airports a2
WHERE a1.city <> a2.city;
count
-------
10704
(1 строка)
С точки зрения СУБД эти три варианта эквивалентны и отличаются лишь синтакси- сом. Для них PostgreSQL выберет один и тот же план (порядок) выполнения запроса.
Теперь обратимся к так называемым внешним соединениям. Зададимся вопросом:
сколько маршрутов обслуживают самолеты каждого типа? Если не требовать вывода наименований моделей самолетов, тогда всю необходимую информацию можно по- лучить из материализованного представления «Маршруты» (routes). Но мы все же будем выводить и наименования моделей, поэтому обратимся также к таблице «Са- молеты» (aircrafts). Соединим эти таблицы на основе атрибута aircraft_code,
сгруппируем строки и просто воспользуемся функцией count. В этом запросе внеш- нее соединение еще не используется.
SELECT r.aircraft_code, a.model, count( * ) AS num_routes
FROM routes r
JOIN aircrafts a ON r.aircraft_code = a.aircraft_code
GROUP BY 1, 2
ORDER BY 3 DESC;
aircraft_code |
model
| num_routes
---------------+---------------------+------------
CR2
| Bombardier CRJ-200 |
232
CN1
| Cessna 208 Caravan |
170
SU9
| Sukhoi SuperJet-100 |
158 319
| Airbus A319-100
|
46 733
| Boeing 737-300
|
36 321
| Airbus A321-200
|
32 763
| Boeing 767-300
|
26 773
| Boeing 777-300
|
10
(8 строк)
159

Глава 6. Запросы
Обратите внимание, что таблица «Самолеты» содержит 9 моделей, а в этой выборке лишь 8 строк. Значит, какая-то модель самолета не участвует в выполнении рейсов.
Как ее выявить?
С помощью такого запроса:
SELECT a.aircraft_code AS a_code,
a.model,
r.aircraft_code AS r_code,
count( r.aircraft_code ) AS num_routes
FROM aircrafts a
LEFT OUTER JOIN routes r ON r.aircraft_code = a.aircraft_code
GROUP BY 1, 2, 3
ORDER BY 4 DESC;
a_code |
model
| r_code | num_routes
--------+---------------------+--------+------------
CR2
| Bombardier CRJ-200 | CR2
|
232
CN1
| Cessna 208 Caravan | CN1
|
170
SU9
| Sukhoi SuperJet-100 | SU9
|
158 319
| Airbus A319-100
| 319
|
46 733
| Boeing 737-300
| 733
|
36 321
| Airbus A321-200
| 321
|
32 763
| Boeing 767-300
| 763
|
26 773
| Boeing 777-300
| 773
|
10 320
| Airbus A320-200
|
|
0
(9 строк)
В данном запросе используется левое внешнее соединение — об этом говорит пред- ложение LEFT OUTER JOIN.
В качестве базовой таблицы выбирается таблица aircrafts, указанная в запросе слева от предложения LEFT OUTER JOIN, и для каждой строки, находящейся в ней, из таблицы routes подбираются строки, в которых значение атрибута aircraft_code такое же, как и в текущей строке таблицы aircrafts. Если в таблице routes нет ни одной соответствующей строки, то при отсутствии ключевых слов LEFT OUTER ре- зультирующая комбинированная строка просто не будет сформирована и не попадет в выборку. Но при наличии ключевых слов LEFT OUTER результирующая строка все равно будет сформирована.
Это происходит таким образом: если для строки из левой таблицы (левой относитель- но предложения LEFT OUTER JOIN) не находится ни одной соответствующей строки
160

6.2. Соединения
в правой таблице, тогда в результирующую строку вместо значений столбцов пра- вой таблицы будут помещены значения NULL. Получается, что для строки из табли- цы aircrafts, в которой значение атрибута aircraft_code равно 320, в таблице routes нет ни одной строки с таким же значением этого атрибута. В результате при выводе выборки в столбце a_code, взятом из таблицы aircrafts, будет значение
320, а в столбце r_code, взятом из таблицы routes, будет значение NULL. Этот стол- бец включен в выборку лишь для повышения наглядности, в реальном запросе он не нужен.
Обратите внимание, что параметром функции count является столбец из таблицы routes, поэтому count и выдает число 0 для самолета с кодом 320. Если заменить его на одноименный столбец из таблицы aircrafts, тогда count выдаст 1, что бу- дет противоречить цели нашей задачи — подсчитать число рейсов, выполняемых на самолетах каждого типа. Напомним, что если функция count в качестве параметра получает не символ «∗», а имя столбца, тогда она подсчитывает число строк, в кото- рых значение в этом столбце определено (не равно NULL).
Кроме левого внешнего соединения существует также и правое внешнее соедине-
ние
— RIGHT OUTER JOIN.
В этом случае в качестве базовой выбирается таблица, имя которой указано справа от предложения RIGHT OUTER JOIN, а механизм получения результирующих строк в слу- чае, когда для строки базовой таблицы не находится пары во второй таблице, точно такой же, как и для левого внешнего соединения. Как сказано в документации, пра- вое внешнее соединение является лишь синтаксическим приемом, поскольку всегда можно заменить его левым внешним соединением, поменяв при этом имена таблиц местами.
Важно учитывать, что порядок следования таблиц в предложениях LEFT (RIGHT)
OUTER JOIN никак не влияет на порядок столбцов в предложении SELECT. В выше- приведенном запросе мы написали
SELECT a.aircraft_code AS a_code,
a.model,
r.aircraft_code AS r_code,
...
Но если бы нам это было нужно, то мы могли бы поменять столбцы местами:
SELECT r.aircraft_code AS r_code,
a.model,
a.aircraft_code AS a_code,
...
161

Глава 6. Запросы
Комбинацией этих двух видов внешних соединений является полное внешнее со-
единение
— FULL OUTER JOIN.
В этом случае в выборку включаются строки из левой таблицы, для которых не на- шлось соответствующих строк в правой таблице, и строки из правой таблицы, для которых не нашлось соответствующих строк в левой таблице.
В практической работе при выполнении выборок зачастую выполняются многотаб-
личные запросы
, включающие три таблицы и более. В качестве примера рассмот- рим такую задачу: определить число пассажиров, не пришедших на регистрацию би- летов и, следовательно, не вылетевших в пункт назначения. Будем учитывать только рейсы, у которых фактическое время вылета не пустое, т. е. рейсы, имеющие статус
Departed или Arrived.
SELECT count( * )
FROM ( ticket_flights t
JOIN flights f ON t.flight_id = f.flight_id
)
LEFT OUTER JOIN boarding_passes b
ON t.ticket_no = b.ticket_no AND t.flight_id = b.flight_id
WHERE f.actual_departure IS NOT NULL AND b.flight_id IS NULL;
Оказывается, таких пассажиров нет.
count
-------
0
(1 строка)
При формировании запроса надо вспомнить, что таблица «Посадочные талоны»
(boarding_passes) связана с таблицей «Перелеты» (ticket_flights) по внешне- му ключу, а тип связи — 1:1, т. е. каждой строке из таблицы ticket_flights соот- ветствует не более одной строки в таблице boarding_passes: ведь строка в таблицу boarding_passes добавляется только тогда, когда пассажир прошел регистрацию на рейс. Однако теоретически, да и практически тоже, пассажир может на регистра- цию не явиться, тогда строка в таблицу boarding_passes добавлена не будет.
Поскольку нас интересуют только рейсы с непустым временем вылета, нам придется обратиться к таблице «Рейсы» (flights) и соединить ее с таблицей ticket_flights по атрибуту flight_id. А затем для подключения таблицы boarding_passes мы используем левое внешнее соединение, т. к. в этой таблице может не оказаться стро- ки, соответствующей строке из таблицы ticket_flights.
162

6.2. Соединения
В предложении WHERE второе условие — b.flight_id IS NULL. Оно и позволяет вы- явить те комбинированные строки, в которых столбцам таблицы boarding_passes были назначены значения NULL из-за того, что в ней не нашлось строки, для ко- торой выполнялось бы условие t.ticket_no = b.ticket_no AND t.flight_id =
b.flight_id. Конечно, для проверки на NULL мы могли использовать любой стол- бец таблицы boarding_passes, а не только b.flight_id.
При формировании соединений подключение таблиц выполняется слева направо,
т. е. берется самая первая таблица в предложении FROM и с ней соединяется вторая таблица, затем с полученным набором строк соединяется третья таблица и т. д. Если требуется изменить порядок соединения таблиц, то могут использоваться круглые скобки. В приведенном запросе мы использовали круглые скобки для наглядности,
однако в данном случае они не были обязательными. Необходимо различать описан- ный выше логический порядок соединения таблиц, т. е. взгляд с позиции програм- миста, пишущего запрос, и тот фактический порядок выполнения запроса, который будет сформирован планировщиком. Они могут различаться. Подробно о планах вы- полнения запросов сказано в главе 10.
Теперь рассмотрим более сложный пример. Известно, что в компьютерных системах бывают сбои. Предположим, что возможна такая ситуация: при бронировании билета пассажир выбрал один класс обслуживания, например, Business, а при регистра- ции на рейс ему выдали посадочный талон на то место в салоне самолета, где класс обслуживания — Economy. Необходимо выявить все случаи несовпадения классов об- служивания.
Сведения о классе обслуживания, который пассажир выбрал при бронировании би- лета, содержатся в таблице «Перелеты» (ticket_flights). Однако в таблице «По- садочные талоны» (boarding_passes), которая «отвечает» за посадку на рейс, све- дений о классе обслуживания, который пассажир получил при регистрации, нет. Эти сведения можно получить только из таблицы «Места» (seats). Причем сделать это можно, зная код модели самолета, выполняющего рейс, и номер места в салоне самолета. Номер места можно взять из таблицы boarding_passes, а код моде- ли самолета можно получить из таблицы «Рейсы» (flights), связав ее с таблицей boarding_passes.
Для полноты информационной картины необходимо получить еще фамилию и имя пассажира из таблицы «Билеты» (tickets), связав ее с таблицей ticket_flights по атрибуту «Номер билета» (ticket_no). При формировании запроса выберем в ка- честве, условно говоря, базовой таблицы таблицу boarding_passes, а затем будем поэтапно подключать остальные таблицы. В предложении WHERE будет только одно условие: несовпадение требуемого и фактического классов обслуживания.
163

Глава 6. Запросы
В результате получим запрос, включающий пять таблиц. Он не выдаст ни одной стро- ки, значит, пассажиров, получивших неправильный класс обслуживания, не было.
SELECT f.flight_no,
f.scheduled_departure,
f.flight_id,
f.departure_airport,
f.arrival_airport,
f.aircraft_code,
t.passenger_name,
tf.fare_conditions AS fc_to_be,
s.fare_conditions AS fc_fact,
b.seat_no
FROM boarding_passes b
JOIN ticket_flights tf
ON b.ticket_no = tf.ticket_no AND b.flight_id = tf.flight_id
JOIN tickets t ON tf.ticket_no = t.ticket_no
JOIN flights f ON tf.flight_id = f.flight_id
JOIN seats s
ON b.seat_no = s.seat_no AND f.aircraft_code = s.aircraft_code
WHERE tf.fare_conditions <> s.fare_conditions
ORDER BY f.flight_no, f.scheduled_departure;
Чтобы все же удостовериться в работоспособности этого запроса, можно в таблице boarding_passes изменить в одной строке номер места таким образом, чтобы этот пассажир переместился из салона экономического класса в салон бизнес-класса.
UPDATE boarding_passes
SET seat_no = '1A'
WHERE flight_id = 1 AND seat_no = '17A';
UPDATE 1
Выполним запрос еще раз, и теперь он выдаст одну строку.
В предложении FROM можно использовать виртуальные таблицы, сформированные с помощью ключевого слова VALUES. Предположим, что для выработки финансовой стратегии нашей авиакомпании требуется распределение количества бронирований по диапазонам сумм с шагом в 100 тысяч рублей. Максимальная сумма в одном бро- нировании составляет 1 204 500 рублей. Учтем это при формировании диапазонов.
Виртуальной таблице, создаваемой с помощью ключевого слова VALUES, присваива- ют имя с помощью ключевого слова AS. После имени в круглых скобках приводится список имен столбцов этой таблицы.
164

6.2. Соединения
SELECT r.min_sum, r.max_sum, count( b.* )
FROM bookings b
RIGHT OUTER JOIN
( VALUES (
0, 100000 ), ( 100000, 200000 ),
( 200000, 300000 ), ( 300000, 400000 ),
( 400000, 500000 ), ( 500000, 600000 ),
( 600000, 700000 ), ( 700000, 800000 ),
( 800000, 900000 ), ( 900000, 1000000 ),
( 1000000, 1100000 ), ( 1100000, 1200000 ),
( 1200000, 1300000 )
) AS r ( min_sum, max_sum )
ON b.total_amount >= r.min_sum AND b.total_amount < r.max_sum
GROUP BY r.min_sum, r.max_sum
ORDER BY r.min_sum;
В этом запросе мы использовали внешнее соединение. Сделано это для того, чтобы в случаях, когда в каком-то диапазоне не окажется ни одного бронирования, резуль- тирующая строка выборки все же была бы сформирована. А правое соединение было выбрано только потому, что в качестве первой, базовой, таблицы мы выбрали таб- лицу «Бронирования» (bookings), но именно в ней может не оказаться ни одной строки для соединения с какой-либо строкой виртуальной таблицы. А все строки вир- туальной таблицы, стоящей справа от предложения RIGHT OUTER JOIN, должны быть обязательно представлены в выборке: это позволит сразу увидеть «пустые» диапазо- ны, если они будут.
Можно было использовать и левое внешнее соединение, поменяв таблицы местами.
min_sum | max_sum | count
---------+---------+--------
0 | 100000 | 198314 100000 | 200000 | 46943 200000 | 300000 | 11916 300000 | 400000 |
3260 400000 | 500000 |
1357 500000 | 600000 |
681 600000 | 700000 |
222 700000 | 800000 |
55 800000 | 900000 |
24 900000 | 1000000 |
11 1000000 | 1100000 |
4 1100000 | 1200000 |
0 1200000 | 1300000 |
1
(13 строк)
165

Глава 6. Запросы
Обратите внимание, что для диапазона от 1 100 до 1 200 тысяч рублей значение счет- чика бронирований равно нулю. Если бы мы не использовали внешнее соединение,
то эта строка вообще не попала бы в выборку. Конечно, информация была бы полу- чена та же самая, но воспринимать ее было бы сложнее.
В команде SELECT предусмотрены средства для выполнения операций с выборками,
как с множествами, а именно:
– UNION для вычисления объединения множеств строк из двух выборок;
– INTERSECT для вычисления пересечения множеств строк из двух выборок;
– EXCEPT для вычисления разности множеств строк из двух выборок.
Запросы должны возвращать одинаковое число столбцов, типы данных у столбцов также должны совпадать.
Рассмотрим эти операции, используя материализованное представление «Маршру- ты» (routes).
Начнем с операции объединения множеств строк — UNION. Строка включается в итоговое множество (выборку), если она присутствует хотя бы в одном из них.
Строки-дубликаты в результирующее множество не включаются. Для их включения нужно использовать UNION ALL.
Вопрос: в какие города можно улететь либо из Москвы, либо из Санкт-Петербурга?
SELECT arrival_city FROM routes
WHERE departure_city = 'Москва'
UNION
SELECT arrival_city FROM routes
WHERE departure_city = 'Санкт-Петербург'
ORDER BY arrival_city;
arrival_city
--------------------------
Абакан
Анадырь
Анапа
(87 строк)
Рассмотрим операцию пересечения множеств строк — INTERSECT. Строка вклю- чается в итоговое множество (выборку), если она присутствует в каждом из них.
Строки-дубликаты в результирующее множество не включаются. Для их включения нужно использовать INTERSECT ALL.
166

6.2. Соединения
Вопрос: в какие города можно улететь как из Москвы, так и из Санкт-Петербурга?
SELECT arrival_city FROM routes
WHERE departure_city = 'Москва'
INTERSECT
SELECT arrival_city FROM routes
WHERE departure_city = 'Санкт-Петербург'
ORDER BY arrival_city;
arrival_city
-----------------
Воркута
Воронеж
Казань
(15 строк)
В завершение рассмотрим операцию разности множеств строк — EXCEPT. Строка включается в итоговое множество (выборку), если она присутствует в первом мно- жестве (выборке), но отсутствует во втором. Строки-дубликаты в результирующее множество не включаются. Для их включения нужно использовать EXCEPT ALL.
Вопрос: в какие города можно улететь из Санкт-Петербурга, но нельзя из Москвы?
SELECT arrival_city FROM routes
WHERE departure_city = 'Санкт-Петербург'
EXCEPT
SELECT arrival_city FROM routes
WHERE departure_city = 'Москва'
ORDER BY arrival_city;
arrival_city
--------------
Иркутск
Калуга
Москва
(7 строк)
Конечно, при выполнении этих операций можно соединять не только две таблицы, но и большее их число. При этом нужно либо учитывать приоритеты выполнения опера- ций, либо использовать скобки. Согласно документации INTERSECT связывает свои подзапросы сильнее, чем UNION, а EXCEPT связывает свои подзапросы так же сильно,
как UNION.
167

Глава 6. Запросы
6.3. Агрегирование и группировка
Среди множества функций, имеющихся в PostgreSQL, важное место занимают агре- гатные функции. С одной из них, функцией count, мы уже работали довольно много.
Давайте рассмотрим еще ряд функций из этой группы и сделаем это на примере таб- лицы «Бронирования».
Для расчета среднего значения по столбцу используется функция avg (от слова average).
SELECT avg( total_amount ) FROM bookings;
avg
--------------------
79025.605811528685
(1 строка)
Для получения максимального значения по столбцу используется функция max.
SELECT max( total_amount ) FROM bookings;
max
------------
1204500.00
(1 строка)
Для получения минимального значения по столбцу используется функция min.
SELECT min( total_amount ) FROM bookings;
min
---------
3400.00
(1 строка)
Мы уже много раз выполняли группировку строк в выборке при помощи предложе- ния GROUP BY, поэтому рассмотрим только два примера.
Первый будет таким: давайте подсчитаем, сколько маршрутов предусмотрено из
Москвы в другие города. При формировании запроса не будем учитывать частоту рейсов в неделю, т. е. независимо от того, выполняется какой-то рейс один раз в неде- лю или семь раз, он учитывается только однократно. Воспользуемся материализо- ванным представлением «Маршруты».
168

6.3. Агрегирование и группировка
SELECT arrival_city, count( * )
FROM routes
WHERE departure_city = 'Москва'
GROUP BY arrival_city
ORDER BY count DESC;
arrival_city
| count
--------------------------+-------
Санкт-Петербург
|
12
Брянск
|
9
Ульяновск
|
5
В качестве второго примера рассмотрим ситуацию, когда руководству компании потребовалась обобщенная информация по частоте выполнения рейсов, а именно:
сколько рейсов выполняется ежедневно, сколько рейсов — шесть дней в неделю,
пять и т. д. Опять обратимся к материализованному представлению «Маршруты».
Но теперь при формировании запроса, в отличие от первого примера, воспользуемся столбцом days_of_week, в котором содержатся массивы номеров дней недели, когда выполняется данный рейс.
SELECT array_length( days_of_week, 1 ) AS days_per_week,
count( * ) AS num_routes
FROM routes
GROUP BY days_per_week
ORDER BY 1 desc;
days_per_week | num_routes
---------------+------------
7 |
482 3 |
54 2 |
88 1 |
86
(4 строки)
В этом запросе используется функция array_length, возвращающая количество элементов в указанном измерении массива. Поскольку массив одномерный, то вто- рым параметром функции будет число 1 — первое измерение.
При выполнении выборок можно с помощью условий, заданных в предложении
WHERE, сузить множество выбираемых строк. Аналогичная возможность существу- ет и при выполнении группировок: можно включить в результирующее множество не все строки, а лишь те, которые удовлетворяют некоторому условию. Это условие
169

Глава 6. Запросы
можно задать в предложении HAVING. Важно помнить, что предложение WHERE ра- ботает с отдельными строками еще до выполнения группировки с помощью GROUP
BY, а предложение HAVING — уже после выполнения группировки.
В качестве примера приведем такой запрос: определить, сколько существует марш- рутов из каждого города в другие города, и вывести названия городов, из которых в другие города существует не менее 15 маршрутов.
SELECT departure_city, count( * )
FROM routes
GROUP BY departure_city
HAVING count( * ) >= 15
ORDER BY count DESC;
departure_city | count
-----------------+-------
Москва
|
154
Санкт-Петербург |
35
Новосибирск
|
19
Екатеринбург
|
15
(4 строки)
В подавляющем большинстве городов только один аэропорт, но есть и такие города,
в которых более одного аэропорта. Давайте их выявим.
SELECT city, count( * )
FROM airports
GROUP BY city
HAVING count( * ) > 1;
city
| count
-----------+-------
Ульяновск |
2
Москва
|
3
(2 строки)
Кроме обычных агрегатных функций существуют и так называемые оконные функ-
ции (window functions)
, технология использования которых описана в документа- ции в разделе 3.5 «Оконные функции». Эти функции предоставляют возможность производить вычисления на множестве строк, логически связанных с текущей стро- кой, т. е. имеющих то или иное отношение к ней.
При работе с оконными функциями используются концепции раздела (partition) и
оконного кадра
(window frame). Сначала объясним эти понятия на примере.
170

6.3. Агрегирование и группировка
Предположим, что руководство нашей компании хочет усовершенствовать тарифную политику и с этой целью просит нас предоставить сведения о распределении коли- чества проданных билетов на некоторые рейсы во времени. Количество проданных билетов должно выводиться в виде накопленного показателя, суммирование должно производиться в пределах каждого календарного месяца.
Более детально, в столбцах book_ref и book_date выборки должны приводиться но- мер и время бронирования соответственно. В столбцах month и day должны указы- ваться порядковый номер месяца и день этого месяца. Столбец count должен содер- жать суммарные (накопленные) количества билетов, проданных на каждый момент времени. С первого дня нового месяца подсчет числа проданных билетов начинается сначала.
Таким образом, в нашем примере в качестве раздела (partition) будет выступать мно- жество строк, у которых даты продажи билета (т. е. даты бронирования) относятся к одному и тому же месяцу. В результате в полученной выборке, пример которой при- веден ниже, будет сформировано два раздела.
book_ref |
book_date
| month | day | count
----------+------------------------+-------+-----+-------
A60039
| 2016-08-22 12:02:00+08 |
8 | 22 |
1 554340
| 2016-08-23 23:04:00+08 |
8 | 23 |
2 854C4C
| 2016-08-24 10:52:00+08 |
8 | 24 |
5 854C4C
| 2016-08-24 10:52:00+08 |
8 | 24 |
5 854C4C
| 2016-08-24 10:52:00+08 |
8 | 24 |
5 81D8AF
| 2016-08-25 10:22:00+08 |
8 | 25 |
6 8D6873
| 2016-08-31 17:09:00+08 |
8 | 31 |
59
E82829
| 2016-08-31 20:56:00+08 |
8 | 31 |
60
ECA0D7
| 2016-09-01 00:48:00+08 |
9 |
1 |
1
E3BD32
| 2016-09-01 04:44:00+08 |
9 |
1 |
2
EB11BB
| 2016-09-03 12:02:00+08 |
9 |
3 |
14 19FE38
| 2016-09-03 17:42:00+08 |
9 |
3 |
16 19FE38
| 2016-09-03 17:42:00+08 |
9 |
3 |
16 536A3D
| 2016-09-03 19:19:00+08 |
9 |
3 |
18 536A3D
| 2016-09-03 19:19:00+08 |
9 |
3 |
18 02E6B6
| 2016-09-04 01:39:00+08 |
9 |
4 |
19
(79 строк)
Здесь для примера был выбран рейс с идентификатором 1.
171

Глава 6. Запросы
Понятие оконного кадра (window frame) является важным, поскольку многие окон- ные функции работают не со всеми строками раздела, а только с теми, которые обра- зуют оконный кадр текущей строки. Если строки в разделе не упорядочены, то окон- ным кадром текущей строки по умолчанию считается множество всех строк раздела.
Однако в том случае, когда строки в разделе упорядочены по какому-то критерию,
тогда в состав оконного кадра по умолчанию включаются строки, начиная с первой строки раздела и заканчивая текущей строкой. Если же существуют строки, имеющие такое же значение критерия сортировки, что и текущая строка, и расположенные по-
сле
нее, то они также включаются в состав оконного кадра текущей строки.
Обратите внимание на первые строки в представленной выборке. В строках с третьей по пятую значения в столбце count одинаковые и равны 5. Равенство значений име- ет следующее объяснение. В рамках одного бронирования с номером 854C4C были проданы сразу три билета на этот рейс, поэтому в этих трех строках значения в столб- це book_date одинаковые. Строки в выборке упорядочены по значениям столбца book_date. Таким образом, для каждой из этих трех строк, т. е. для третьей, четвер- той и пятой, значения критерия сортировки одинаковые, поэтому оконным кадром для каждой из них будут являться первые пять строк первого раздела выборки. Под- счет числа проданных билетов выполняется в пределах оконного кадра. В результате и появляется значение 5 в каждой из этих трех строк, а значений 3 и 4 нет вообще.
В приведенной выборке отражены также и случаи одновременного бронирования двух билетов на данный рейс. Вы можете найти соответствующие строки самосто- ятельно.
Теперь посмотрим, с помощью какого запроса был получен этот результат, и на его примере объясним синтаксические конструкции, используемые для работы с окон- ными функциями.
SELECT b.book_ref,
b.book_date,
extract( 'month' from b.book_date ) AS month,
extract( 'day'
from b.book_date ) AS day,
count( * ) OVER (