6.2 Соединения
В тех случаях, когда информации, содержащейся в одной таблице, недостаточно для получения требуемого результата, используют соединение (join) таблиц. Покажем способ выполнения соединения на примере следующего запроса: выбрать все места,
предусмотренные компоновкой салона самолета Cessna 208 Caravan.
Сначала приведем 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, необходимо указывать либо имя таблицы, из которой выбирается значение этого атрибута, либо ее псевдоним, но псевдоним может быть коротким, что удобнее при написании команды. Псевдоним
116
и атрибут соединяются символом «.». Псевдонимы используются и в предложениях
WHERE, GROUP BY, ORDER BY, HAVING, т. е. во всех частях команды SELECT.
Итак, как мы рассуждали? Если бы в качестве исходных сведений мы получили сра- зу код самолета — «CN1», то запрос свелся бы к выборке из одной таблицы «Места»
(seats). Он был бы таким:
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_conditionsFROM seats sJOIN aircrafts a ON s.aircraft_code = a.aircraft_codeWHERE a.model '^Cessna'ORDER BY s.seat_no;seat_no | fare_conditions
---------+-----------------
1A
| Economy
1B
| Economy
(12 строк)
Если подвести итог, то можно упрощенно объяснить механизм построения соедине- ния следующим образом. Сначала формируются все попарные комбинации строк из обеих таблиц, т. е. декартово произведение множеств строк этих таблиц. Эти комби- нированные строки включают в себя все атрибуты обеих таблиц. Затем в дело вступа- ет условие s.aircraft_code = a.aircraft_code. Это означает, что в результирующем мно- жестве строк останутся только те из них, в которых значения атрибута aircraft_code,
взятые из таблицы aircrafts и из таблицы seats, одинаковые. Строки, не удовлетворя- ющие этому критерию, отфильтровываются. Это
означает на практике, что каждой строке из таблицы «Места» мы сопоставили только одну конкретную строку из таб- лицы «Самолеты», из которой мы теперь можем взять значение атрибута «Модель самолета», чтобы включить ее в итоговый вывод данных.
117
На практике описанный механизм не реализуется буквально. Специальная подсисте- ма 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 указывают критерии, которым должны удовлетворять результирующие строки.
Изучая язык 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,
118
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;
В этом представлении используется не только таблица «Рейсы» (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;
Это означает, что при соединении таблиц flights и airports PostgreSQL будет пытать- ся для каждой строки из таблицы flights найти такую строку в таблице airports, в которой значение атрибута airport_code будет равно не только значению атрибута departure_airport, но также и значению атрибута arrival_airport в таблице flights. Полу- чается, что данное условие будет выполнено, если только аэропорт вылета и аэропорт назначения будет одним и тем же. Однако в сфере пассажирских авиаперевозок та- ких рейсов не бывает. Конечно, иногда самолеты возвращаются в пункт вылета, но это уже совсем другая ситуация, которая в нашей учебной базе данных не учитыва- ется.
Таким образом, приходим к выводу о том, что каждую строку из таблицы «Рейсы»
(flights) необходимо соединять с двумя различными строками из таблицы «Аэропор- ты»: ведь аэропорт вылета и аэропорт назначения — это различные аэропорты. Одна- ко при однократном включении таблицы «Аэропорты» (airports) в предложение FROM
сделать это невозможно, поэтому поступают так: к таблице airports в предложении
FROM обращаются дважды, как будто это две копии одной и той же таблицы. Конеч- но, на самом деле никаких копий не создается, а просто в результате поиск строк в ней будет производиться дважды: один раз для атрибута departure_airport, а второй раз — для атрибута arrival_airport. Но поскольку необходимо обеспечить однознач- ную идентификацию, то каждой «копии» (экземпляру) таблицы airports присваива- ют уникальный псевдоним, в нашем случае это dep и arr, т. е. departure и arrival. Эти псевдонимы указывают, из какой «копии» (экземпляра) таблицы airports нужно брать значение атрибута airport_code для сопоставления с атрибутами departure_airport и arrival_airport.
Рассмотрев этот пример, вновь обратимся к соединениям такого типа и покажем три способа выполнения соединения таблицы с самой собой, отличающиеся синтак- сически, но являющиеся функционально эквивалентными. Наш запрос-иллюстрация должен выяснить: сколько всего маршрутов нужно было бы сформировать, если бы требовалось соединить каждый город со всеми остальными городами? Если в городе
119
имеется более одного аэропорта, то договоримся рейсы из каждого из них (в каждый из них) считать отдельными маршрутами. Поэтому правильнее было бы говорить не о маршрутах из каждого города, а о маршрутах из каждого аэропорта во все другие аэропорты. Конечно, рейсов из любого города в тот же самый город быть не долж- но.
Первый вариант запроса использует обычное перечисление имен таблиц в предложе- нии 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 строка)
Третий вариант предусматривает явное использование декартова произведения
таблиц
. Для этого служит предложение CROSS JOIN. Лишние строки, как и в первом варианте, отсеиваем с помощью предложения WHERE:
SELECT count( * )
FROM airports a1 CROSS JOIN airports a2
WHERE a1.city <> a2.city;
count
-------
10704
(1 строка)
120
С точки зрения СУБД эти три варианта эквивалентны, они отличаются лишь синтак- сисом. Для них 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 строк)
Обратите внимание, что в таблице «Самолеты» (aircrafts) представлено 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 121
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) не находится ни одной
соответствующей строки в правой таблице, тогда в результиру- ющую строку вместо значений столбцов правой таблицы будут помещены значения
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,count( r.aircraft_code ) AS num_routes...Но если бы нам это было нужно, то мы могли бы поменять столбцы местами:
SELECT r.aircraft_code AS r_code,a.model,122
a.aircraft_code AS a_code,count( r.aircraft_code ) AS num_routes...Комбинацией этих двух видов внешних соединений является
полное внешнее со-единение— FULL OUTER JOIN. В этом случае в выборку включаются строки из левой таблицы, для которых не нашлось соответствующих строк в правой таблице, и строки из правой таблицы, для которых не нашлось соответствующих строк в левой табли- це.
В практической работе при выполнении выборок зачастую выполняются
многотаб-личные запросы,включающие три таблицы и более. В качестве примера рассмот- рим такую задачу: определить число пассажиров, не пришедших на регистрацию би- летов и, следовательно, не вылетевших в пункт назначения. Будем учитывать только рейсы, у которых фактическое время вылета не пустое, т. е. рейсы, имеющие статус
«Departed» или «Arrived».
SELECT count( * )FROM (ticket_flights tJOIN flights f ON t.flight_id = f.flight_id)LEFT OUTER JOIN boarding_passes bON t.ticket_no = b.ticket_no AND t.flight_id = b.flight_idWHERE 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.
В предложении WHERE второе условие — b.flight_id IS NULL. Оно как раз и позволяет выявить те комбинированные строки, в которых столбцам таблицы boarding_passes были назначены значения NULL из-за того, что в ней не нашлось строки, для кото- рой выполнялось бы условие t.ticket_no = b.ticket_no AND t.flight_id = b.flight_id. Ко- нечно, мы могли использовать любой столбец таблицы boarding_passes, а не только b.flight_id, для проверки на NULL.
123
При формировании соединений подключение таблиц выполняется слева направо,
т. е. берется самая первая таблица в предложении FROM и с ней соединяется вторая таблица, затем с полученным набором строк соединяется третья таблица и т. д. Если требуется изменить порядок соединения таблиц, то могут использоваться круглые скобки. В приведенном запросе мы использовали круглые скобки для наглядности,
однако в данном случае они не были обязательными. Необходимо различать описан- ный выше логический порядок соединения таблиц, т. е. взгляд с позиции програм- миста, пишущего запрос, и тот фактический порядок выполнения запроса, который будет сформирован планировщиком. Они могут различаться. Подробно о планах вы- полнения запросов сказано в главе 10.
Теперь рассмотрим более сложный пример. Известно, что в компьютерных системах бывают сбои. Предположим, что возможна такая ситуация: при бронировании биле- та пассажир выбрал один класс обслуживания, например, «Business», а при регистра- ции на рейс ему выдали посадочный талон на то место в салоне самолета, где класс обслуживания — «Economy». Необходимо выявить все случаи несовпадения классов обслуживания.
Сведения о классе обслуживания, который пассажир выбрал при бронировании биле- та, содержатся в таблице «Перелеты» (ticket_flights). Однако в таблице «Посадочные талоны» (boarding_passes), которая «отвечает» за посадку на рейс, сведений о классе обслуживания, который пассажир получил при регистрации, нет. Эти сведения мож- но получить только из таблицы «Места» (seats). Причем, сделать это можно, зная код модели самолета, выполняющего рейс, и номер места в салоне самолета. Номер места можно взять из таблицы boarding_passes, а код модели самолета можно получить из таблицы «Рейсы» (flights), связав ее с таблицей boarding_passes. Для полноты инфор- мационной картины необходимо получить еще фамилию и имя пассажира из табли- цы «Билеты» (tickets), связав ее с таблицей ticket_flights по атрибуту «Номер билета»
(ticket_no). При формировании запроса выберем в качестве, условно говоря, базовой таблицы таблицу boarding_passes, а затем будем поэтапно подключать остальные таб- лицы. В предложении WHERE будет только одно условие: несовпадение требуемого и фактического классов обслуживания.
В результате получим запрос, включающий пять таблиц.
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
124
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
Выполним запрос еще раз. Теперь он выдаст одну строку.
--[ RECORD 1 ]-------+----------------------- flight_no
| PG0405
scheduled_departure | 2016-09-13 13:35:00+08
flight_id
| 1
departure_airport
| DME
arrival_airport
| LED
aircraft_code
| 321
passenger_name
| PAVEL AFANASEV
fc_to_be
| Economy fc_fact
| Business seat_no
| 1A
В предложении FROM можно использовать виртуальные таблицы, сформированные с помощью ключевого слова VALUES.
Предположим, что для выработки финансовой стратегии нашей авиакомпании тре- буется следующая информация: распределение количества бронирований по диапа- зонам сумм с шагом в сто тысяч рублей. Максимальная сумма в одном бронировании составляет 1 204 500 рублей. Учтем это при формировании диапазонов стоимостей.
Виртуальной таблице, создаваемой с помощью ключевого слова VALUES, присваива- ют имя с помощью ключевого слова AS. После имени в круглых скобках приводится список имен столбцов этой таблицы.
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;
125
В этом запросе мы использовали внешнее соединение. Сделано это для того, чтобы в случаях, когда в каком-то диапазоне не окажется ни одного бронирования, резуль- тирующая строка выборки все же была бы сформирована. А правое соединение было выбрано только потому, что в качестве первой, базовой, таблицы мы выбрали табли- цу «Бронирования» (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 строк)
Обратите внимание, что для диапазона от 1100 до 1200 тысяч рублей значение счет- чика бронирований равно нулю. Если бы мы не использовали внешнее соединение,
то эта строка вообще не попала бы в выборку. Конечно, информация была бы полу- чена та же самая, но воспринимать ее было бы сложнее.
В команде SELECT предусмотрены средства для выполнения операций с выборками,
как с множествами, а именно:
– предложение UNION предназначено для вычисления объединения множеств строк из двух выборок;
– предложение INTERSECT предназначено для вычисления пересечения множеств строк из двух выборок;
– предложение EXCEPT предназначено для вычисления разности множеств строк из двух выборок.
Запросы должны возвращать одинаковое число столбцов, типы данных у столбцов также должны совпадать.
Рассмотрим эти операции, используя материализованное представление «Маршру- ты» (routes).
Начнем с операции объединения множеств строк — UNION. Строка включается в итоговое множество (выборку), если она присутствует хотя бы в одном из них.
126
Строки-дубликаты в результирующее множество не включаются. Для их включения нужно использовать 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.
Вопрос: в какие города можно улететь как из Москвы, так и из Санкт-Петербурга?
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.
Вопрос: в какие города можно улететь из Санкт-Петербурга, но нельзя из Москвы?
127
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.
6.3 Агрегирование и группировка
Среди множества функций, имеющихся в PostgreSQL, важное место занимают агре- гатные функции. С одной из них, функцией count, мы уже работали довольно много.
Давайте рассмотрим еще ряд функций из этой группы и сделаем это на примере таб- лицы «Бронирования» (bookings).
Для расчета среднего значения по столбцу используется функция 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;
128
min
---------
3400.00
(1 строка)
Мы уже много раз выполняли
группировку строк в выборке при помощи предложе- ния GROUP BY, поэтому рассмотрим только два примера.
Первый будет таким: давайте подсчитаем, сколько маршрутов предусмотрено из
Москвы в другие города. При формировании запроса не будем учитывать частоту рейсов в неделю, т. е. независимо от того, выполняется какой-то
рейс один раз в неделю или семь раз, он учитывается только однократно. Воспользуемся материа- лизованным представлением «Маршруты» (routes).
SELECT arrival_city, count( * )FROM routesWHERE departure_city = 'Москва'GROUP BY arrival_cityORDER BY count DESC;arrival_city
| count
--------------------------+-------
Санкт-Петербург
|
12
Брянск
|
9
Ульяновск
|
5
Йошкар-Ола
|
4
Петрозаводск
|
4
В качестве второго примера рассмотрим ситуацию, когда руководству компании потребовалась обобщенная информация по частоте выполнения рейсов, а именно:
сколько рейсов выполняется ежедневно, сколько рейсов — шесть дней в неделю, пять и т. д. Опять обратимся к материализованному представлению «Маршруты» (routes).
Но теперь при формировании запроса, в отличие от первого примера, воспользуем- ся столбцом days_of_week, в котором содержатся
массивы номеров дней недели, когда выполняется данный рейс.
SELECT array_length( days_of_week, 1 ) AS days_per_week,count( * ) AS num_routesFROM routesGROUP BY days_per_weekORDER BY 1 desc;days_per_week | num_routes
---------------+------------
7 |
482 3 |
54 2 |
88 1 |
86
(4 строки)
В этом запросе используется функция array_length, возвращающая количество эле- ментов в указанном измерении массива. Поскольку массив одномерный, то вторым параметром функции будет число 1 — первое измерение.
129
При выполнении выборок можно с помощью условий, заданных в предложении
WHERE, сузить множество выбираемых строк. Аналогичная возможность существу- ет и при выполнении группировок: можно включить в результирующее множество не все строки, а лишь те, которые удовлетворяют некоторому условию. Это условие можно задать в предложении 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). Сначала объясним эти понятия на примере. Предпо- ложим, что руководство нашей компании хочет усовершенствовать тарифную поли- тику и с этой целью просит нас предоставить сведения о распределении количества проданных билетов на некоторые рейсы во времени. Количество проданных билетов должно выводиться в виде накопленного показателя, суммирование должно произ- водиться в пределах каждого календарного месяца. Форма отчета предположительно должна быть такой:
130
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.
В столбцах book_ref и book_date приводятся номер бронирования и момент времени,
когда оно было произведено. В столбцах month и day указываются порядковый номер месяца и день этого месяца. В столбце count содержатся суммарные (накопленные)
количества билетов, проданных на каждый момент времени. С первого дня нового месяца подсчет числа проданных билетов начинается сначала. Таким образом, в на- шем примере в качестве раздела (partition) будет выступать множество строк, у ко- торых даты продажи билета (т. е. даты бронирования) относятся к одному и тому же месяцу. В результате в полученной выборке будет сформировано два раздела.
Понятие оконного кадра (window frame) является важным, поскольку многие окон- ные функции работают не со всеми строками раздела, а только с теми, которые обра- зуют оконный кадр текущей строки. Если строки в разделе не упорядочены, то окон- ным кадром текущей строки по умолчанию считается множество всех строк раздела.
Однако в том случае, когда строки в
разделе упорядочены по какому-то критерию,
тогда в состав оконного кадра по умолчанию включаются строки, начиная с первой строки раздела и заканчивая текущей строкой. Если же существуют строки, имеющие такое же значение критерия сортировки, что и текущая строка, и расположенные
по-сленее, то они также включаются в состав оконного кадра текущей строки.
Обратите внимание на первые строки в представленной выборке. В строках с тре- тьей по пятую значения в столбце count одинаковые и равны 5. Равенство значений имеет следующее объяснение. В рамках одного бронирования с номером «854C4C»
были проданы сразу три билета на этот рейс, поэтому в этих трех строках значения в столбце book_date одинаковые. Строки в выборке упорядочены по значениям столбца book_date. Таким образом, для каждой из этих трех строк, т. е. для третьей, четвер- той и пятой, значения критерия сортировки одинаковые, поэтому оконным кадром для каждой из них будут являться первые пять строк первого раздела выборки. Под- счет числа проданных билетов выполняется в пределах оконного кадра. В результате и появляется значение 5 в каждой из этих трех строк, а значений 3 и 4 нет вообще.
131
В приведенной выборке отражены также и случаи одновременного бронирования двух билетов на данный рейс. Вы можете найти соответствующие строки самосто- ятельно.
Теперь посмотрим, с помощью какого запроса был получен этот результат, и на его примере объясним синтаксические конструкции, используемые для работы с окон- ными функциями.
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 ( 
Скачать 0.9 Mb.