6.4. Подзапросы
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 строк)
Обратите внимание, что для диапазона от 1 100 до 1 200 тысяч рублей значение числа бронирований равно нулю. Для того чтобы была выведена строка с нулевым значе- нием столбца count, мы использовали внешнее соединение.
В заключение рассмотрим команду для создания материализованного представле- ния «Маршруты» (routes), которое было описано в главе 5. Но тогда мы не стали рассматривать эту команду, т. к. еще не ознакомились с подзапросами, которые в ней используются.
Описание атрибута
Имя атрибута
Тип PostgreSQL
Номер рейса flight_no char(6)
Код аэропорта отправления departure_airport char(3)
Название аэропорта отправления departure_airport_name text
Город отправления departure_city text
Код аэропорта прибытия arrival_airport char(3)
Название аэропорта прибытия arrival_airport_name text
Город прибытия arrival_city text
Код самолета, IATA
aircraft_code char(3)
Продолжительность полета duration interval
Дни недели, когда выполняются рейсы days_of_week integer[ ]
189
Глава 6. Запросы
Эта команда выглядит так:
CREATE MATERIALIZED VIEW routes AS
WITH f3 AS
(
SELECT f2.flight_no,
f2.departure_airport,
f2.arrival_airport,
f2.aircraft_code,
f2.duration,
array_agg( f2.days_of_week ) AS days_of_week
FROM
(
SELECT f1.flight_no,
f1.departure_airport,
f1.arrival_airport,
f1.aircraft_code,
f1.duration,
f1.days_of_week
FROM
(
SELECT flights.flight_no,
flights.departure_airport,
flights.arrival_airport,
flights.aircraft_code,
( flights.scheduled_arrival -
flights.scheduled_departure
) AS duration,
( to_char( flights.scheduled_departure,
'ID'::text
)
)::integer AS days_of_week
FROM flights
) f1
GROUP BY f1.flight_no, f1.departure_airport,
f1.arrival_airport, f1.aircraft_code,
f1.duration, f1.days_of_week
ORDER BY f1.flight_no, f1.departure_airport,
f1.arrival_airport, f1.aircraft_code,
f1.duration, f1.days_of_week
) f2
GROUP BY f2.flight_no, f2.departure_airport,
f2.arrival_airport, f2.aircraft_code,
f2.duration
)
190
6.4. Подзапросы
SELECT f3.flight_no,
f3.departure_airport,
dep.airport_name AS departure_airport_name,
dep.city AS departure_city,
f3.arrival_airport,
arr.airport_name AS arrival_airport_name,
arr.city AS arrival_city,
f3.aircraft_code,
f3.duration,
f3.days_of_week
FROM f3,
airports dep,
airports arr
WHERE f3.departure_airport = dep.airport_code
AND f3.arrival_airport
= arr.airport_code;
Начнем ознакомление с запросом с его верхней части. Здесь мы видим конструкцию
WITH f3 AS (...), т. е. общее табличное выражение. В результате его выполнения будет сформирована временная таблица f3. Запрос, который ее формирует, содер- жит в предложении FROM подзапрос, формирующий временную таблицу f2. А этот подзапрос, в свою очередь, также содержит в предложении FROM подзапрос, форми- рующий временную таблицу f1. Таким образом, в этой команде используется вло- женный подзапрос.
Во вложенном подзапросе используется функция to_char. Второй ее параметр —
ID — указывает на то, что из значения даты/времени вылета будет извлечен номер дня недели. При этом нумерация дней недели соответствует стандарту ISO 8601: по- недельник — 1, воскресенье — 7. Поскольку номер дня недели представлен в виде символьной строки, он преобразуется в тип данных integer. Таким образом, вло- женный подзапрос вычисляет плановую длительность полета (столбец duration)
и извлекает номер дня недели из даты/времени вылета по расписанию (столбец days_of_week).
Подзапрос следующего, более высокого уровня, получив результат вложенного под- запроса, просто группирует строки, готовя столбец days_of_week к объединению отдельных номеров дней недели в массивы целых чисел. При этом в предложение
GROUP BY включен столбец days_of_week, чтобы заменить дубликаты дней недели одним значением. Ведь таблица flights содержит расписание рейсов на длитель- ный период. Поэтому рейс, который отправляется, скажем, по вторникам, появится в этом расписании несколько раз, следовательно, день недели с номером 2 также по- явится в столбце days_of_week для этого номера рейса несколько раз. В результате,
191
Глава 6. Запросы
если не прибегнуть к группировке по этому столбцу, то при формировании масси- ва дней недели в этом массиве будут многократные вхождения каждого дня недели,
когда этот рейс летает. В этом подзапросе присутствует и предложение ORDER BY,
в которое включен столбец days_of_week. Это необходимо для того, чтобы агре- гатная функция array_agg собрала номера дней недели в массив в возрастающем порядке этих номеров.
Во внешнем запросе вызывается функция array_agg, которая агрегирует номера дней недели, содержащиеся в сгруппированных строках, в массивы целых чисел.
На этом работа конструкции WITH f3 AS (...) завершается. В результате вместо нескольких строк в таблице flights, соответствующих вылетам конкретного рейса в различные дни недели, формируется одна строка в представлении routes, в этой строке все дни недели, в которые выполняется конкретный рейс, собраны в массив целых чисел.
И, наконец, главный запрос выполняет соединение временной таблицы f3 с таб- лицей «Аэропорты» (airports), причем дважды. Это нужно потому, что в таб- лице f3 есть столбец f3.departure_airport (аэропорт отправления) и столбец f3.arrival_airport (аэропорт прибытия), для каждого из них нужно выбрать на- именование аэропорта и наименование города из таблицы airports. О том, как нужно рассуждать при двукратном использовании одной и той же таблицы в соеди- нении, мы уже говорили ранее в разделе 5.4 «Представления».
Контрольные вопросы и задания
1. В документации сказано, что служебный символ «%» в шаблоне оператора LIKE
соответствует любой последовательности символов, в том числе и пустой после- довательности, однако ничего не сказано насчет правил обработки пробелов.
В таблице «Билеты» (tickets) столбец passenger_name содержит имя и фами- лию пассажира, записанные заглавными латинскими буквами и разделенные одним пробелом.
Выясните правила обработки пробелов самостоятельно, выполнив следующие команды и сравнив полученные результаты:
SELECT count( * ) FROM tickets;
SELECT count( * ) FROM tickets WHERE passenger_name LIKE '% %';
SELECT count( * ) FROM tickets WHERE passenger_name LIKE '% % %';
SELECT count( * ) FROM tickets WHERE passenger_name LIKE '% %%';
192
Контрольные вопросы и задания
2. Этот запрос выбирает из таблицы «Билеты» (tickets) всех пассажиров с име- нами, состоящими из трех букв (в шаблоне присутствуют три символа «_»):
SELECT passenger_name
FROM tickets
WHERE passenger_name LIKE '___ %';
Предложите шаблон поиска в операторе LIKE для выбора из этой таблицы всех пассажиров с фамилиями, состоящими из пяти букв.
3. В разделе документации 9.7.2 «Регулярные выражения SIMILAR TO» рассмат- ривается оператор SIMILAR TO. Он работает аналогично оператору LIKE, но использует шаблоны, соответствующие определению регулярных выражений,
приведенному в стандарте SQL. Регулярные выражения SQL представляют со- бой комбинацию синтаксиса LIKE с синтаксисом обычных регулярных выраже- ний. Самостоятельно ознакомьтесь с оператором SIMILAR TO.
4. В разделе документации 9.2 «Функция и операторы сравнения» представлены различные предикаты сравнения, кроме предиката BETWEEN, рассмотренного в этой главе. Самостоятельно ознакомьтесь с ними.
5. В разделе документации 9.17 «Условные выражения» представлены услов- ные выражения, которые поддерживаются в PostgreSQL. В тексте главы бы- ла рассмотрена конструкция CASE. Самостоятельно ознакомьтесь с функциями
COALESCE, NULLIF, GREATEST и LEAST.
6. Выясните, на каких маршрутах используются самолеты компании Boeing. В вы- борке вместо кода модели должно выводиться ее наименование, например,
вместо кода 733 должно быть Boeing 737-300.
Указание: можно воспользоваться соединением представления «Маршруты»
(routes) и таблицы «Самолеты» (aircrafts).
7. Самые крупные самолеты в нашей авиакомпании — это Boeing 777-300. Выяс- нить, между какими парами городов они летают, поможет запрос:
SELECT DISTINCT departure_city, arrival_city
FROM routes r
JOIN aircrafts a ON r.aircraft_code = a.aircraft_code
WHERE a.model = 'Boeing 777-300'
ORDER BY 1;
193
Глава 6. Запросы
departure_city | arrival_city
----------------+--------------
Екатеринбург
| Москва
Москва
| Екатеринбург
Москва
| Новосибирск
Москва
| Пермь
Москва
| Сочи
Новосибирск
| Москва
Пермь
| Москва
Сочи
| Москва
(8 строк)
К сожалению, в этой выборке информация дублируется. Пары городов приведе- ны по два раза: для рейса «туда» и для рейса «обратно». Модифицируйте запрос таким образом, чтобы каждая пара городов была выведена только один раз:
departure_city | arrival_city
----------------+--------------
Москва
| Екатеринбург
Новосибирск
| Москва
Пермь
| Москва
Сочи
| Москва
(4 строки)
8. В тексте главы мы рассматривали различные примеры использования левого и правого внешних соединений: LEFT OUTER JOIN и RIGHT OUTER JOIN. Напи- шите запрос, в котором использовалось бы полное внешнее соединение — FULL
OUTER JOIN.
9. Для ответа на вопрос, сколько рейсов выполняется из Москвы в Санкт-Петер- бург, можно написать совсем простой запрос:
SELECT count( * )
FROM routes
WHERE departure_city = 'Москва'
AND arrival_city
= 'Санкт-Петербург';
count
-------
12
(1 строка)
194
Контрольные вопросы и задания
А с помощью какого запроса можно получить результат в таком виде?
departure_city | arrival_city
| count
----------------+-----------------+-------
Москва
| Санкт-Петербург |
12
(1 строка)
10. Выяснить, сколько различных рейсов выполняется из каждого города, без уче- та частоты рейсов в неделю, можно с помощью обращения к представлению
«Маршруты» (routes):
SELECT departure_city, count( * )
FROM routes
GROUP BY departure_city
ORDER BY count DESC;
departure_city
| count
--------------------------+-------
Москва
|
154
Санкт-Петербург
|
35
Новосибирск
|
19
Благовещенск
|
1
Братск
|
1
(101 строка)
Модифицируйте этот запрос так, чтобы он выводил число направлений, по ко- торым летают самолеты из каждого города. Например, из Москвы в Санкт-
Петербург летает несколько различных рейсов, но все эти рейсы относятся к одному направлению.
Указание: нужно передать параметр в функцию count.
11. В материализованном представлении «Маршруты» (routes) имеется столбец days_of_week, который содержит списки (массивы) номеров дней недели, ко- гда выполняется каждый рейс.
Для оптимизации расписания вылетов из Москвы нужно выявить пять горо- дов, в которые из столицы отправляется наибольшее число ежедневных рейсов
(маршрутов). Строки в выборке следует расположить в убывающем порядке чис- ла выполняемых рейсов.
Указание: воспользуйтесь функцией array_length.
195
Глава 6. Запросы
12.* Предположим, что служба материального снабжения нашей авиакомпании за- просила информацию о числе рейсов, выполняющихся из Москвы в каждый день недели.
Результат можно получить путем выполнения семи аналогичных запросов: по одному для каждого дня недели. Начнем с понедельника:
SELECT 'Понедельник' AS day_of_week, count( * ) AS num_flights
FROM routes
WHERE departure_city = 'Москва'
AND days_of_week @> '{ 1 }'::integer[];
В этом запросе используется оператор @>, который проверяет, содержатся ли все элементы массива, стоящего справа от него, в том массиве, который нахо- дится слева. В правом массиве всего один элемент — номер интересующего нас дня недели.
day_of_week | num_flights
-------------+-------------
Понедельник |
131
(1 строка)
Запрос для вторника отличается лишь номером дня недели в массиве.
SELECT 'Вторник' AS day_of_week, count( * ) AS num_flights
FROM routes
WHERE departure_city = 'Москва'
AND days_of_week @> '{ 2 }'::integer[];
day_of_week | num_flights
-------------+-------------
Вторник
|
134
(1 строка)
Нужно выполнить еще пять аналогичных команд, чтобы получить результаты для всех дней недели. Очевидно, что это нерациональный способ.
Получить требуемый результат можно с помощью одного запроса:
SELECT unnest( days_of_week ) AS day_of_week,
count( * ) AS num_flights
FROM routes
WHERE departure_city = 'Москва'
GROUP BY day_of_week
ORDER BY day_of_week;
196
Контрольные вопросы и задания
day_of_week | num_flights
-------------+-------------
1 |
131 2 |
134 3 |
126 4 |
136 5 |
124 6 |
133 7 |
124
(7 строк)
Задание 1.
Самостоятельно разберитесь, как работает приведенный запрос.
Выясните, что делает функция unnest. Для того чтобы найти ее описание,
можно воспользоваться теми разделами документации, которые были указа- ны в главе 4. Однако можно воспользоваться и предметным указателем (Index),
ссылка на который находится в самом низу оглавления документации.
В качестве вспомогательного запроса, проясняющего работу функции unnest,
можно выполнить следующий:
SELECT flight_no, unnest( days_of_week ) AS day_of_week
FROM routes
WHERE departure_city = 'Москва'
ORDER BY flight_no;
Задание 2.
Использование номеров дней недели в предыдущей выборке не должно вызывать затруднений. Но все-таки предположим, что нас попросили модифицировать запрос, чтобы результат выводился в таком виде:
name_of_day | num_flights
-------------+-------------
Пн.
|
131
Вт.
|
134
Ср.
|
126
Чт.
|
136
Пт.
|
124
Сб.
|
133
Вс.
|
124
(7 строк)
Покажем одно из возможных решений задачи. Оно основано на использовании специальной табличной функции unnest в предложении FROM. Подробно об этом написано в документации в разделе 7.2.1.4 «Табличные функции». Функ- ция может принимать любое число параметров-массивов, а возвращает набор
197
Глава 6. Запросы
строк, которые могут использоваться в запросах как обычные таблицы. В этих наборах строк столбцы формируются из значений, содержащихся в массивах.
SELECT dw.name_of_day, count( * ) AS num_flights
FROM (
SELECT unnest( days_of_week ) AS num_of_day
FROM routes
WHERE departure_city = 'Москва'
) AS r,
unnest( '{ 1, 2, 3, 4, 5, 6, 7 }'::integer[],
'{ "Пн.", "Вт.", "Ср.", "Чт.", "Пт.", "Сб.", "Вс."}'::text[]
) AS dw( num_of_day, name_of_day )
WHERE r.num_of_day = dw.num_of_day
GROUP BY r.num_of_day, dw.name_of_day
ORDER BY r.num_of_day;
Этот запрос можно упростить. Предложение WITH ORDINALITY позволяет в на- шем примере избавиться от массива целых чисел, обозначающих дни неде- ли, поскольку автоматически формируется столбец целых чисел, нумерую- щих строки результирующего набора. По умолчанию этот столбец называется ordinality. Это имя можно использовать в запросе. Самостоятельно модифи- цируйте запрос с применением предложения WITH ORDINALITY.
13. Ответить на вопрос о том, каковы максимальные и минимальные цены билетов на все направления, может такой запрос:
SELECT f.departure_city, f.arrival_city,
max( tf.amount ), min( tf.amount )
FROM flights_v f
JOIN ticket_flights tf ON f.flight_id = tf.flight_id
GROUP BY 1, 2
ORDER BY 1, 2;
departure_city
|
arrival_city
|
max
|
min
---------------------+---------------------+-----------+----------
Абакан
| Москва
| 101000.00 | 33700.00
Абакан
| Новосибирск
|
5800.00 | 5800.00
Абакан
| Томск
|
4900.00 | 4900.00
Анадырь
| Москва
| 185300.00 | 61800.00
Анадырь
| Хабаровск
| 92200.00 | 30700.00
Якутск
| Мирный
|
8900.00 | 8100.00
Якутск
| Санкт-Петербург
| 145300.00 | 48400.00
(367 строк)
198
Контрольные вопросы и задания
А как выявить те направления, на которые не было продано ни одного билета?
Один из вариантов решения такой: если на рейсы, отправляющиеся по какому- то направлению, не было продано ни одного билета, то максимальная и мини- мальная цены будут равны NULL. Нужно получить выборку в таком виде:
departure_city
|
arrival_city
|
max
|
min
---------------------+---------------------+-----------+----------
Абакан
| Архангельск
|
|
Абакан
| Грозный
|
|
Абакан
| Кызыл
|
|
Абакан
| Москва
| 101000.00 | 33700.00
Абакан
| Новосибирск
|
5800.00 | 5800.00
Модифицируйте запрос, приведенный выше.
14. Предположим, что маркетологи нашей авиакомпании хотят знать, как часто встречаются различные имена среди пассажиров? Получить распределение ча- стот имен пассажиров в таблице «Билеты» (tickets) поможет такой запрос:
SELECT left( passenger_name, strpos( passenger_name, ' ' ) - 1 )
AS firstname, count( * )
FROM tickets
GROUP BY 1
ORDER BY 2 DESC;
firstname | count
-----------+-------
ALEKSANDR | 20328
SERGEY
| 15133
VLADIMIR | 12806
TATYANA
| 12058
ELENA
| 11291
OLGA
| 9998
MAGOMED
|
14
ASKAR
|
13
RASUL
|
11
(363 строки)
Напишите запрос для ответа на аналогичный вопрос насчет распределения ча- стот фамилий пассажиров.
Подробные сведения о других функциях для работы со строковыми данными приведены в документации в разделе 9.4 «Строковые функции и операторы».
199
Глава 6. Запросы
15.* В тексте главы были кратко рассмотрены оконные функции. Самостоятельно прочитайте разделы документации, которые рекомендуется изучить для более детального ознакомления с этим классом функций.
Подумайте, в какой ситуации, связанной с базой данных «Авиаперевозки», было бы полезно применить оконные функции, и напишите запрос.
16.* Вместе с агрегатными функциями может использоваться предложение FILTER.
Самостоятельно ознакомьтесь с этой темой, обратившись к разделу документа- ции 4.2.7 «Агрегатные выражения». Напишите запрос с использованием пред- ложения FILTER с агрегатной функцией.
17. В тексте главы в разделе 6.4 мы рассмотрели два способа получения ответа на вопрос: как распределяются места с разными классами обслуживания в самоле- тах всех типов?
А с помощью какого запроса можно получить результат в таком виде?
aircraft_code |
model
| fare_conditions | count
---------------+---------------------+-----------------+-------
319
| Airbus A319-100
| Business
|
20 319
| Airbus A319-100
| Economy
|
96
CR2
| Bombardier CRJ-200 | Economy
|
50
SU9
| Sukhoi SuperJet-100 | Business
|
12
SU9
| Sukhoi SuperJet-100 | Economy
|
85
(17 строк)
18. В разделе 6.2 мы находили ответ на вопрос: сколько маршрутов обслуживают са- молеты каждого типа? Но для повышения наглядности получаемых результатов необходимо еще рассчитывать относительные величины, т. е. доли от общего числа маршрутов.
Вот что требуется получить:
a_code |
model
| r_code | num_routes | fraction
--------+---------------------+--------+------------+----------
CR2
| Bombardier CRJ-200 | CR2
|
232 |
0.327
CN1
| Cessna 208 Caravan | CN1
|
170 |
0.239 773
| Boeing 777-300
| 773
|
10 |
0.014 320
| Airbus A320-200
|
|
0 |
0.000
(9 строк)
200