SELECT aa.city, aa.airport_code, aa.airport_nameFROM (SELECT city, count( * )FROM airportsGROUP BY cityHAVING count( * ) > 1) AS aJOIN airports AS aa ON a.city = aa.cityORDER BY aa.city, aa.airport_name;182
6.4. Подзапросы
Благодаря использованию предложения HAVING, подзапрос выбирает города, в кото- рых имеется более одного аэропорта, и формирует временную таблицу в следующем виде:
city
| count
-----------+-------
Ульяновск |
2
Москва
|
3
(2 строки)
А в главном запросе выполняется соединение временной таблицы с таблицей «Аэро- порты» (airports).
city
| airport_code |
airport_name
-----------+--------------+---------------------
Москва
| VKO
| Внуково
Москва
| DME
| Домодедово
Москва
| SVO
| Шереметьево
Ульяновск | ULV
| Баратаевка
Ульяновск | ULY
| Ульяновск-Восточный
(5 строк)
Для иллюстрации использования подзапросов в предложении HAVING решим та- кую задачу: определить число маршрутов, исходящих из тех аэропортов, которые расположены восточнее географической долготы 150
◦
SELECT departure_airport, departure_city, count( * )
FROM routes
GROUP BY departure_airport, departure_city
HAVING departure_airport IN (
SELECT airport_code
FROM airports
WHERE longitude > 150
)
ORDER BY count DESC;
Подзапрос формирует список аэропортов, которые и будут отобраны с помощью предложения HAVING после выполнения группировки.
departure_airport |
departure_city
| count
-------------------+--------------------------+-------
DYR
| Анадырь
|
4
GDX
| Магадан
|
3
PKC
| Петропавловск-Камчатский |
1
(3 строки)
183
Глава 6. Запросы
В сложных запросах могут использоваться вложенные подзапросы. Это означает,
что один подзапрос находится внутри другого. Давайте в качестве примера рассмот- рим такую ситуацию: руководство авиакомпании хочет выяснить степень заполне- ния самолетов на всех рейсах, ведь отправлять полупустые самолеты не очень выгод- но. Таким образом, запрос должен не только выдавать число билетов, проданных на данный рейс, и общее число мест в самолете, но должен также вычислять отношение этих двух показателей.
Вот какой запрос получился:
SELECT ts.flight_id,
ts.flight_no,
ts.scheduled_departure_local,
ts.departure_city,
ts.arrival_city,
a.model,
ts.fact_passengers,
ts.total_seats,
round( ts.fact_passengers::numeric /
ts.total_seats::numeric, 2 ) AS fraction
FROM (
SELECT f.flight_id,
f.flight_no,
f.scheduled_departure_local,
f.departure_city,
f.arrival_city,
f.aircraft_code,
count( tf.ticket_no ) AS fact_passengers,
( SELECT count( s.seat_no )
FROM seats s
WHERE s.aircraft_code = f.aircraft_code
) AS total_seats
FROM flights_v f
JOIN ticket_flights tf ON f.flight_id = tf.flight_id
WHERE f.status = 'Arrived'
GROUP BY 1, 2, 3, 4, 5, 6
) AS ts
JOIN aircrafts AS a ON ts.aircraft_code = a.aircraft_code
ORDER BY ts.scheduled_departure_local;
Самый внутренний подзапрос — total_seats — выдает общее число мест в само- лете. Этот подзапрос — коррелированный, т. к. он выполняется для каждой строки,
184
6.4. Подзапросы
обрабатываемой во внешнем подзапросе, т. е. для каждой модели самолета. Для под- счета числа проданных билетов мы использовали соединение представления «Рей- сы» (flights_v) с таблицей «Перелеты» (ticket_flights) с последующей группи- ровкой строк и вызовом функции count. Конечно, можно было бы вместо такого ре- шения использовать еще один коррелированный подзапрос:
( SELECT count( tf.ticket_no )
FROM ticket_flights tf
WHERE tf.flight_id = f.flight_id
) AS fact_passengers
В таком случае уже не потребовалось бы соединять представление flights_v с таб- лицей ticket_flights и группировать строки, достаточно было бы сделать:
FROM flights_v
WHERE f.status = 'Arrived'
) AS ts JOIN aircrafts AS a
Внешний запрос вместо кода самолета выводит наименование модели, выбирает остальные столбцы из подзапроса без изменений и дополнительно производит вы- числение степени заполнения самолета пассажирами, а также сортирует результиру- ющие строки.
-[ RECORD 1 ]--------------+------------------------- flight_id
| 28205
flight_no
| PG0032
scheduled_departure_local | 2016-09-13 08:00:00
departure_city
| Пенза arrival_city
| Москва model
| Cessna 208 Caravan fact_passengers
| 2
total_seats
| 12
fraction
| 0.17
-[ RECORD 2 ]--------------+------------------------- flight_id
| 9467
flight_no
| PG0360
scheduled_departure_local | 2016-09-13 08:00:00
departure_city
| Санкт-Петербург arrival_city
| Оренбург model
| Bombardier CRJ-200
fact_passengers
| 6
total_seats
| 50
fraction
| 0.12 185
Глава 6. Запросы
Рассмотренный сложный запрос можно сделать более наглядным за счет выделения подзапроса в отдельную конструкцию, которая называется общее табличное выра-
жение (Common Table Expression — CTE)
WITH ts AS
( SELECT f.flight_id,
f.flight_no,
f.scheduled_departure_local,
f.departure_city,
f.arrival_city,
f.aircraft_code,
count( tf.ticket_no ) AS fact_passengers,
( SELECT count( s.seat_no )
FROM seats s
WHERE s.aircraft_code = f.aircraft_code
) AS total_seats
FROM flights_v f
JOIN ticket_flights tf ON f.flight_id = tf.flight_id
WHERE f.status = 'Arrived'
GROUP BY 1, 2, 3, 4, 5, 6
)
SELECT ts.flight_id,
ts.flight_no,
ts.scheduled_departure_local,
ts.departure_city,
ts.arrival_city,
a.model,
ts.fact_passengers,
ts.total_seats,
round( ts.fact_passengers::numeric /
ts.total_seats::numeric, 2 ) AS fraction
FROM ts
JOIN aircrafts AS a ON ts.aircraft_code = a.aircraft_code
ORDER BY ts.scheduled_departure_local;
Конструкция WITH ts AS (...) и представляет собой общее табличное выражение
(CTE). Такие конструкции удобны тем, что позволяют упростить основной запрос,
сделать его менее громоздким. В общем табличном выражении может присутство- вать больше одного подзапроса. Каждый подзапрос формирует временную таблицу с указанным именем. Если имена столбцов этой таблицы не заданы явным образом в виде списка, тогда они определяются на основе списка столбцов в предложении
SELECT. В нашем примере это будет именно так. Теперь мы можем в главном запро- се обращаться к временной таблице ts так, как если бы она существовала постоянно.
186
6.4. Подзапросы
Но важно учитывать, что временная таблица, создаваемая в общем табличном выра- жении, существует только во время выполнения запроса.
В этой главе мы уже решали задачу распределения сумм бронирований по диапазо- нам с шагом в 100 тысяч рублей. Тогда мы использовали предложение VALUES для формирования виртуальной таблицы. Можно решить эту задачу более рациональ- ным способом с использованием конструкции WITH ... AS (...).
Сначала покажем, как можно сформировать диапазоны сумм бронирований с помо- щью рекурсивного общего табличного выражения:
WITH RECURSIVE ranges ( min_sum, max_sum ) AS
( VALUES ( 0, 100000 )
UNION ALL
SELECT min_sum + 100000, max_sum + 100000
FROM ranges
WHERE max_sum <
( SELECT max( total_amount ) FROM bookings )
)
SELECT * FROM ranges;
В этом примере мы явно указали имена столбцов для временной таблицы ranges —
это min_sum и max_sum. Рекурсивный алгоритм работает следующим образом:
– сначала выполняется предложение VALUES (0, 100000) и результат записывает- ся во временную область памяти;
– затем к этой временной области памяти применяется запрос
SELECT min_sum + 100000, max_sum + 100000
...
и в результате его выполнения формируется только одна строка, поскольку в ис- ходном предложении VALUES была сформирована только одна строка и только одна строка была помещена во временную область памяти;
– вновь сформированная строка вместе с исходной строкой помещаются в другую временную область, в которой происходит накапливание результирующих строк;
– к той строке, которая была на предыдущем шаге сформирована с помощью коман- ды SELECT, опять применяется эта же команда и т. д.;
– работа завершится, когда перестанет выполняться условие
max_sum < ( SELECT max( total_amount ) FROM bookings )
187
Глава 6. Запросы
Важную роль в этом процессе играет предложение UNION ALL, благодаря которо- му происходит объединение сформированных строк в единую таблицу. Поскольку в нашем примере в рекурсивном алгоритме участвует только одна строка, то строк- дубликатов не возникает, поэтому мы используем не UNION, а UNION ALL. При ис- пользовании предложения UNION выполняется устранение строк-дубликатов, но в данном случае необходимости в выполнении этой операции нет, следовательно, це- лесообразно использовать именно UNION ALL.
Получим такую таблицу:
min_sum | max_sum
---------+--------
0 | 100000 100000 | 200000 200000 | 300000 1000000 | 1100000 1100000 | 1200000 1200000 | 1300000
(13 строк)
Здесь в предложении WHERE используется скалярный подзапрос. С результатом его выполнения сравнивается значение столбца max_sum:
WHERE max_sum < ( SELECT max( total_amount ) FROM bookings )
Теперь давайте скомбинируем рекурсивное общее табличное выражение с выборкой из таблицы bookings:
WITH RECURSIVE ranges ( min_sum, max_sum ) AS
( VALUES( 0, 100000 )
UNION ALL
SELECT min_sum + 100000, max_sum + 100000
FROM ranges
WHERE max_sum <
( SELECT max( total_amount ) FROM bookings )
)
SELECT r.min_sum, r.max_sum, count( b.* )
FROM bookings b
RIGHT OUTER JOIN ranges r
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;
188