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

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

1 ... 8 9 10 11 12 13 14 15 ... 20

SELECT aa.city, aa.airport_code, aa.airport_name
FROM (
SELECT city, count( * )
FROM airports
GROUP BY city
HAVING count( * ) > 1
) AS a
JOIN airports AS aa ON a.city = aa.city
ORDER 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

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

Контрольные вопросы и задания
19.* В разделе 6.4 мы использовали рекурсивный алгоритм в общем табличном вы- ражении. Изучите этот пример, чтобы лучше понять работу рекурсивного алго- ритма:
WITH RECURSIVE ranges ( min_sum, max_sum )
AS (
VALUES( 0,
100000 ),
( 100000, 200000 ),
( 200000, 300000 )
UNION ALL
SELECT min_sum + 100000, max_sum + 100000
FROM ranges
WHERE max_sum < ( SELECT max( total_amount ) FROM bookings )
)
SELECT * FROM ranges;
min_sum | max_sum
---------+---------
0 | 100000
исходные строки
100000 | 200000 200000 | 300000 100000 | 200000
результат первой итерации
200000 | 300000 300000 | 400000 200000 | 300000
результат второй итерации
300000 | 400000 400000 | 500000 300000 | 400000 400000 | 500000 500000 | 600000 1000000 | 1100000
результат (n-3)-й итерации
1100000 | 1200000 1200000 | 1300000 1100000 | 1200000
результат (n-2)-й итерации
1200000 | 1300000 1200000 | 1300000
результат (n-1)-й итерации (предпоследней)
(36 строк)
201

Глава 6. Запросы
Здесь мы с помощью предложения VALUES специально создали виртуальную таблицу из трех строк, хотя для получения требуемого результата достаточно только одной строки (0, 100000). Еще важно то, что предложение UNION ALL не удаляет строки-дубликаты, поэтому мы можем видеть весь рекурсивный про- цесс порождения новых строк.
При рекурсивном выполнении запроса
SELECT min_sum + 100000, max_sum + 100000
FROM ranges
WHERE max_sum < ( SELECT max( total_amount ) FROM bookings )
каждый раз выполняется проверка в условии WHERE. И на (n − 2)-й итерации это условие отсеивает одну строку, т. к. после (n − 3)-й итерации значение атрибута max_sum в третьей строке было равно 1 300 000.
Ведь запрос
SELECT max( total_amount ) FROM bookings;
выдаст значение max
------------
1204500.00
(1 строка)
Таким образом, после (n − 2)-й итерации во временной области остается всего две строки, после (n−1)-й итерации во временной области остается только одна строка.
Заключительная итерация уже не добавляет строк в результирующую таблицу,
поскольку единственная строка, поданная на вход команде SELECT, будет от- клонена условием WHERE. Работа алгоритма завершается.
Задание 1.
Модифицируйте запрос, добавив в него столбец level (можно на- звать его и iteration). Этот столбец должен содержать номер текущей итера- ции, поэтому нужно увеличивать его значение на единицу на каждом шаге. Не забудьте задать начальное значение для добавленного столбца в предложении
VALUES.
Задание 2.
Для завершения экспериментов замените UNION ALL на UNION и выполните запрос. Сравните этот результат с предыдущим, когда мы исполь- зовали UNION ALL.
202

Контрольные вопросы и задания
20.* В тексте главы есть такой запрос, вычисляющий распределение сумм брониро- ваний по диапазонам в 100 тысяч рублей:
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;
Как вы думаете, почему функция count получает в качестве параметра выра- жение b.*, а не просто *? Что изменится, если оставить только *, и почему?
21. В тексте главы был приведен запрос, выводящий список городов, в которые нет рейсов из Москвы.
SELECT DISTINCT a.city
FROM airports a
WHERE NOT EXISTS (
SELECT * FROM routes r
WHERE r.departure_city = 'Москва'
AND r.arrival_city = a.city
)
AND a.city <> 'Москва'
ORDER BY city;
Можно предложить другой вариант, в котором используется одна из операций над множествами строк: объединение, пересечение или разность.
Вместо знака «?» поставьте в приведенном ниже запросе нужное ключевое сло- во — UNION, INTERSECT или EXCEPT — и обоснуйте ваше решение.
203

Глава 6. Запросы
SELECT city
FROM airports
WHERE city <> 'Москва'
?
SELECT arrival_city
FROM routes
WHERE departure_city = 'Москва'
ORDER BY city;
22. В тексте главы мы рассматривали такой запрос: получить перечень аэропортов в тех городах, в которых больше одного аэропорта.
SELECT aa.city, aa.airport_code, aa.airport_name
FROM (
SELECT city, count( * )
FROM airports
GROUP BY city
HAVING count( * ) > 1
) AS a
JOIN airports AS aa ON a.city = aa.city
ORDER BY aa.city, aa.airport_name;
Как вы думаете, обязательно ли наличие функции count в подзапросе в пред- ложении SELECT или можно написать просто
SELECT city FROM airports
Сначала попробуйте дать ответ теоретически, а потом проверьте вашу гипотезу на компьютере.
23. Предположим, что департамент развития нашей авиакомпании задался вопро- сом: каким будет общее число различных маршрутов, которые теоретически можно проложить между всеми городами?
Если в каком-то городе имеется более одного аэропорта, то это учитывать не будем, т. е. маршрутом будем считать путь между городами, а не между аэро-
портами
. Здесь мы используем соединение таблицы с самой собой на основе неравенства значений атрибутов.
SELECT count( * )
FROM ( SELECT DISTINCT city FROM airports ) AS a1
JOIN ( SELECT DISTINCT city FROM airports ) AS a2
ON a1.city <> a2.city;
204

Контрольные вопросы и задания
count
-------
10100
(1 строка)
Задание.
Перепишите этот запрос с общим табличным выражением.
24. В тексте главы мы рассмотрели использование подзапросов в предикатах
EXISTS и IN. Существуют также предикаты многократного сравнения ANY и
ALL. Они представлены в документации в разделе 9.22 «Выражения подзапро- сов». Самостоятельно ознакомьтесь с этими предикатами и напишите несколь- ко запросов с их применением.
Предикаты ANY и ALL имеют некоторую связь с предикатом IN. В частности,
использование IN эквивалентно использованию конструкции = ANY, а исполь- зование NOT IN эквивалентно использованию конструкции <> ALL.
Пример двух эквивалентных запросов, выбирающих аэропорты в часовых поя- сах Asia/Novokuznetsk и Asia/Krasnoyarsk:
SELECT * FROM airports
WHERE timezone IN ( 'Asia/Novokuznetsk', 'Asia/Krasnoyarsk' );
SELECT * FROM airports
WHERE timezone = ANY (
VALUES ( 'Asia/Novokuznetsk' ), ( 'Asia/Krasnoyarsk' )
);
Еще один пример. В тексте главы мы рассматривали запрос, подсчитывающий количество маршрутов, проложенных из самых восточных аэропортов.
SELECT departure_city, count( * )
FROM routes
GROUP BY departure_city
HAVING departure_city IN (
SELECT city
FROM airports
WHERE longitude > 150
)
ORDER BY count DESC;
В этом запросе можно заменить IN на ANY таким образом:
HAVING departure_city = ANY ( ... )
205

Глава 6. Запросы
25.* При планировании новых маршрутов и оценке экономической эффективности уже существующих может потребоваться информация о том, какова усреднен- ная степень заполнения самолетов на всех направлениях.
Будем учитывать только уже прибывшие рейсы.
WITH tickets_seats
AS (
SELECT f.flight_id,
f.flight_no,
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
)
SELECT ts.departure_city,
ts.arrival_city,
sum( ts.fact_passengers ) AS sum_pass,
sum( ts.total_seats ) AS sum_seats,
round( sum( ts.fact_passengers )::numeric /
sum( ts.total_seats )::numeric, 2 ) AS frac
FROM tickets_seats ts
GROUP BY ts.departure_city, ts.arrival_city
ORDER BY ts.departure_city;
departure_city | arrival_city
| sum_pass | sum_seats | frac
----------------+-----------------+----------+-----------+------
Абакан
| Tomsk
|
258 |
360 | 0.72
Абакан
| Novosibirsk
|
217 |
348 | 0.62
Абакан
| Moscow
|
466 |
1044 | 0.45
Якутск
| Санкт-Петербург |
352 |
3596 | 0.10
(361 строка)
Для того чтобы лучше уяснить, как работает запрос в целом, вычлените из него отдельные подзапросы и выполните их, посмотрите, что они выводят.
206

Контрольные вопросы и задания
Как вы считаете, равносильно ли в данном запросе
SELECT count( s.seat_no )
и
SELECT count( s.* )
Почему?
Задание.
Модифицируйте этот запрос, чтобы он выводил те же отчетные дан- ные, но с учетом классов обслуживания, т. е. Business, Comfort и Economy.
26.* Предположим, что некая контролирующая организация потребовала информа- цию о размещении пассажиров одного из рейсов Кемерово — Москва в салоне самолета. Для определенности выберем конкретный рейс из тех рейсов, кото- рые уже прибыли на момент времени, соответствующий текущему моменту.
Текущий момент времени в базе данных «Авиаперевозки» определяется с по- мощью функции bookings.now.
Выполним запрос:
SELECT *
FROM flights_v
WHERE departure_city = 'Кемерово'
AND arrival_city = 'Москва'
AND actual_arrival < bookings.now();
Выберем для дальнейшей работы рейс, у которого значения атрибутов flight_id — 27584, aircraft_code — SU9.
Получим список пассажиров этого рейса с местами, которые им были назначе- ны в салоне самолета.
SELECT t.passenger_name, b.seat_no
FROM (
ticket_flights tf
JOIN tickets t ON tf.ticket_no = t.ticket_no
)
JOIN boarding_passes b
ON tf.ticket_no = b.ticket_no
AND tf.flight_id = b.flight_id
WHERE tf.flight_id = 27584
ORDER BY t.passenger_name;
207

Контрольные вопросы и задания
Получим список мест в салоне самолета и пассажиров, которые сидели на этих местах. При этом незанятые места также должны быть выведены (поэтому ис- пользуем левое внешнее соединение LEFT OUTER JOIN).
SELECT s.seat_no, p.passenger_name
FROM seats s
LEFT OUTER JOIN (
SELECT t.passenger_name, b.seat_no
FROM (
ticket_flights tf
JOIN tickets t ON tf.ticket_no = t.ticket_no
)
JOIN boarding_passes b
ON tf.ticket_no = b.ticket_no
AND tf.flight_id = b.flight_id
WHERE tf.flight_id = 27584
) AS p
ON s.seat_no = p.seat_no
WHERE s.aircraft_code = 'SU9'
ORDER BY s.seat_no;
seat_no | passenger_name
---------+---------------------
10A
|
10C
|
10D
| NATALYA POPOVA
10E
|
10F
| YURIY ZAKHAROV
11A
| VLADIMIR POPOV
11C
| ANNA KUZMINA
8F
|
9A
| MAKSIM CHERNOV
9C
|
9D
| LYUDMILA IVANOVA
9E
|
9F
| SOFIYA KULIKOVA
(97 строк)
Предположим, что нас попросили отсортировать места в порядке их расположе- ния в салоне самолета и вывести также адреса электронной почты пассажиров
(у кого они были указаны при бронировании). Для выполнения второго требова- ния воспользуемся столбцом contact_data. В нем содержатся JSON-объекты,
209

Глава 6. Запросы
содержащие контактные данные пассажиров. Ряд из них имеет ключ email. Мо- дифицированный запрос будет таким:
SELECT s.seat_no, p.passenger_name, p.email
FROM seats s
LEFT OUTER JOIN (
SELECT t.passenger_name, b.seat_no,
t.contact_data->'email' AS email
FROM (
ticket_flights tf
JOIN tickets t ON tf.ticket_no = t.ticket_no
)
JOIN boarding_passes b
ON tf.ticket_no = b.ticket_no
AND tf.flight_id = b.flight_id
WHERE tf.flight_id = 27584
) AS p
ON s.seat_no = p.seat_no
WHERE s.aircraft_code = 'SU9'
ORDER BY
left( s.seat_no, length( s.seat_no ) - 1 )::integer,
right( s.seat_no, 1 );
seat_no |
passenger_name | email
---------+-------------------+------------------------------------
1A
| ALEKSANDR ABRAMOV |
1C
|
|
1D
| DENIS PETROV
|
1F
| LEONID BARANOV
| "baranov.l.1967@postgrespro.ru"
2A
|
|
2C
|
|
9F
| SOFIYA KULIKOVA
| "sofiya.kulikova_041963@postgre..."
10A
|
|
10C
|
|
10D
| NATALYA POPOVA
| "popova.n_13031976@postgrespro.ru"
20E
| ELENA ZOTOVA
|
20F
| LILIYA OSIPOVA
|
(97 строк)
Задание.
Перепишите последний запрос с использованием общего табличного выражения и добавьте столбец «Класс обслуживания» (fare_conditions).
210

1 ... 8 9 10 11 12 13 14 15 ... 20