J.5. Использование
J.5.1. Схема bookings
Все объекты демонстрационной базы данных находятся в схеме bookings. Это означает, что при обращении к объектам вам необходимо либо явно указывать имя схемы (например: bookings.flights), либо предварительно изменить конфигурационный параметр search_path (например: SET search_path = bookings, public;).
Однако для функции bookings.now в любом случае необходимо явно указывать схему, чтобы отличать её от стандартной функции now.
J.5.2. Примеры запросов
Чтобы лучше познакомиться с содержимым демонстрационной базы данных, посмотрим на результаты нескольких простых запросов.
Результаты, представленные ниже, были получены для версии с небольшой базой данных (demo_small) от 13 октября 2016. Если в вашей системе запросы выдают другие данные, проверьте версию демонстрационной базы (функция bookings.now). Незначительные отклонения могут быть связаны с местным временем, отличным от московского, и настройками локализации.
Все рейсы выполняются несколькими типами самолетов:
SELECT * FROM aircrafts;
aircraft_code | model | range
---------------+---------------------+-------
773 | Boeing 777-300 | 11100
763 | Boeing 767-300 | 7900
SU9 | Sukhoi SuperJet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
733 | Boeing 737-300 | 4200
CN1 | Cessna 208 Caravan | 1200
CR2 | Bombardier CRJ-200 | 2700
(9 строк)
Для каждого типа самолета поддерживается список мест в салоне. Например, вот где можно разместиться в небольшом самолете Cessna 208 Caravan:
SELECT a.aircraft_code,
a.model,
s.seat_no,
s.fare_conditions
FROM aircrafts a
JOIN seats s ON a.aircraft_code = s.aircraft_code
WHERE a.model = 'Cessna 208 Caravan'
ORDER BY s.seat_no;
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 строк)
Самолеты большего размера имеют больше посадочных мест с разными классами обслуживания:
SELECT s2.aircraft_code,
string_agg (s2.fare_conditions || '(' || s2.num::text || ')',
', ') as fare_conditions
FROM (
SELECT s.aircraft_code, s.fare_conditions, count(*) as num
FROM seats s
GROUP BY s.aircraft_code, s.fare_conditions
ORDER BY s.aircraft_code, s.fare_conditions
) s2
GROUP BY s2.aircraft_code
ORDER BY s2.aircraft_code;
aircraft_code | fare_conditions
---------------+-----------------------------------------
319 | Business(20), Economy(96)
320 | Business(20), Economy(120)
321 | Business(28), Economy(142)
733 | Business(12), Economy(118)
763 | Business(30), Economy(192)
773 | Business(30), Comfort(48), Economy(324)
CN1 | Economy(12)
CR2 | Economy(50)
SU9 | Business(12), Economy(85)
(9 строк)
База данных содержит список аэропортов практически всех крупных городов России. В большинстве городов есть только один аэропорт. Исключение составляют:
SELECT a.airport_code as code,
a.airport_name,
a.city,
a.longitude,
a.latitude,
a.timezone
FROM airports a
WHERE a.city IN (
SELECT aa.city
FROM airports aa
GROUP BY aa.city
HAVING COUNT(*) > 1
)
ORDER BY a.city, a.airport_code;
code | airport_name | city | longitude | latitude | timezone
------+---------------------+-----------+-----------+-----------+---------------
DME | Домодедово | Москва | 37.906111 | 55.408611 | Europe/Moscow
SVO | Шереметьево | Москва | 37.414589 | 55.972642 | Europe/Moscow
VKO | Внуково | Москва | 37.261486 | 55.591531 | Europe/Moscow
ULV | Баратаевка | Ульяновск | 48.2267 | 54.268299 | Europe/Samara
ULY | Ульяновск-Восточный | Ульяновск | 48.8027 | 54.401 | Europe/Samara
(5 строк)
Чтобы понять, откуда и куда можно улететь, удобно использовать материализованное представление routes, в котором агрегируется информация о всех рейсах. Вот, например, куда, в какие дни недели и за какое время можно долететь из Волгограда:
SELECT r.arrival_city as city,
r.arrival_airport as airport_code,
r.arrival_airport_name as airport_name,
r.days_of_week,
r.duration
FROM routes r
WHERE r.departure_city = 'Волгоград';
city | airport_code | airport_name | days_of_week | duration
----------------+--------------+----------------+-----------------+----------
Москва | SVO | Шереметьево | {1,2,3,4,5,6,7} | 01:15:00
Челябинск | CEK | Челябинск | {1,2,3,4,5,6,7} | 01:50:00
Ростов-на-Дону | ROV | Ростов-на-Дону | {1,2,3,4,5,6,7} | 00:30:00
Москва | VKO | Внуково | {1,2,3,4,5,6,7} | 01:10:00
Чебоксары | CSY | Чебоксары | {1,2,3,4,5,6,7} | 02:45:00
Томск | TOF | Богашёво | {3} | 03:50:00
(6 строк)
База данных была сформирована на момент времени, возвращаемый функцией bookings.now():
SELECT bookings.now() as now;
now
------------------------
2016-10-13 17:00:00+03
Относительно именно этого момента времени все рейсы делятся на прошедшие и будущие:
SELECT status,
count(*) as count,
min(scheduled_departure) as min_scheduled_departure,
max(scheduled_departure) as max_scheduled_departure
FROM flights
GROUP BY status
ORDER BY min_scheduled_departure;
status | count | min_scheduled_departure | max_scheduled_departure
-----------+-------+-------------------------+-------------------------
Arrived | 16707 | 2016-09-13 00:50:00+03 | 2016-10-13 16:25:00+03
Cancelled | 414 | 2016-09-16 10:35:00+03 | 2016-11-12 19:55:00+03
Departed | 58 | 2016-10-13 08:55:00+03 | 2016-10-13 16:50:00+03
Delayed | 41 | 2016-10-13 14:15:00+03 | 2016-10-14 16:25:00+03
On Time | 518 | 2016-10-13 16:55:00+03 | 2016-10-14 17:00:00+03
Scheduled | 15383 | 2016-10-14 17:05:00+03 | 2016-11-12 19:40:00+03
(6 строк)
Найдем ближайший рейс, вылетающий из Екатеринбурга в Москву. Использовать для такого запроса таблицу flight не очень удобно, так как в ней нет информации о городах отправления и прибытия. Поэтому воспользуемся представлением flights_v:
\x
SELECT f.*
FROM flights_v f
WHERE f.departure_city = 'Екатеринбург'
AND f.arrival_city = 'Москва'
AND f.scheduled_departure > bookings.now()
ORDER BY f.scheduled_departure
LIMIT 1;
-[ RECORD 1 ]-------------+-----------------------
flight_id | 10927
flight_no | PG0226
scheduled_departure | 2016-10-14 07:10:00+03
scheduled_departure_local | 2016-10-14 09:10:00
scheduled_arrival | 2016-10-14 08:55:00+03
scheduled_arrival_local | 2016-10-14 08:55:00
scheduled_duration | 01:45:00
departure_airport | SVX
departure_airport_name | Кольцово
departure_city | Екатеринбург
arrival_airport | SVO
arrival_airport_name | Шереметьево
arrival_city | Москва
status | On Time
aircraft_code | 773
actual_departure |
actual_departure_local |
actual_arrival |
actual_arrival_local |
actual_duration |
Обратите внимание, что в представлении flights_v указано не только московское время, но и местное время в аэропортах вылета и прилета.
J.5.3. Бронирования
Каждое бронирование может включать несколько билетов, по одному на каждого пассажира. Билет, в свою очередь, может включать несколько перелетов. Полная информация о бронировании находится в трёх таблицах: bookings, tickets и ticket_flights.
Найдём несколько бронирований с самой высокой стоимостью:
SELECT *
FROM bookings
ORDER BY total_amount desc
LIMIT 10;
book_ref | book_date | total_amount
----------+------------------------+--------------
3B54BB | 2016-09-02 16:08:00+03 | 1204500.00
3AC131 | 2016-09-28 00:06:00+03 | 1087100.00
65A6EA | 2016-08-31 05:28:00+03 | 1065600.00
D7E9AA | 2016-10-06 04:29:00+03 | 1062800.00
EF479E | 2016-09-30 14:58:00+03 | 1035100.00
521C53 | 2016-09-05 08:25:00+03 | 985500.00
514CA6 | 2016-09-24 04:07:00+03 | 955000.00
D70BD9 | 2016-09-02 11:47:00+03 | 947500.00
EC7EDA | 2016-08-30 15:13:00+03 | 946800.00
8E4370 | 2016-09-25 01:04:00+03 | 945700.00
(10 строк)
Посмотрим, из каких билетов состоит бронирование с кодом 521C53:
SELECT ticket_no,
passenger_id,
passenger_name
FROM tickets
WHERE book_ref = '521C53';
ticket_no | passenger_id | passenger_name
---------------+--------------+--------------------
0005432661914 | 8234 547529 | IVAN IVANOV
0005432661915 | 2034 201228 | ANTONINA KUZNECOVA
(2 строки)
Если нас интересует, какие перелеты включены в билет Антонины Кузнецовой, то это можно узнать запросом:
SELECT to_char(f.scheduled_departure, 'DD.MM.YYYY') as when,
f.departure_city || '(' || f.departure_airport || ')' as departure,
f.arrival_city || '(' || f.arrival_airport || ')' as arrival,
tf.fare_conditions as class,
tf.amount
FROM ticket_flights tf
JOIN flights_v f ON tf.flight_id = f.flight_id
WHERE tf.ticket_no = '0005432661915'
ORDER BY f.scheduled_departure;
when | departure | arrival | class | amount
------------+-------------------+-------------------+----------+-----------
26.09.2016 | Москва(SVO) | Анадырь(DYR) | Business | 185300.00
30.09.2016 | Анадырь(DYR) | Хабаровск(KHV) | Business | 92200.00
01.10.2016 | Хабаровск(KHV) | Благовещенск(BQS) | Business | 18000.00
06.10.2016 | Благовещенск(BQS) | Хабаровск(KHV) | Business | 18000.00
10.10.2016 | Хабаровск(KHV) | Анадырь(DYR) | Economy | 30700.00
15.10.2016 | Анадырь(DYR) | Москва(SVO) | Business | 185300.00
(6 строк)
Как видим, высокая стоимость бронирования объясняется большим количеством перелётов на дальние расстояния бизнес-классом.
Часть перелётов в этом билете имеет более ранние даты, чем значение bookings.now(): это значит, что они уже выполнены. А последний полет ещё предстоит. После регистрации на рейс выписывается посадочный талон с указанием места в самолете. Мы можем посмотреть какие именно места занимала Антонина (обратите внимание на внешнее левое соединение с таблицей boarding_passes):
SELECT to_char(f.scheduled_departure, 'DD.MM.YYYY') as when,
f.departure_city || '(' || f.departure_airport || ')' as departure,
f.arrival_city || '(' || f.arrival_airport || ')' as arrival,
f.status,
bp.seat_no
FROM ticket_flights tf
JOIN flights_v f ON tf.flight_id = f.flight_id
LEFT JOIN boarding_passes bp ON tf.flight_id = bp.flight_id
AND tf.ticket_no = bp.ticket_no
WHERE tf.ticket_no = '0005432661915'
ORDER BY f.scheduled_departure;
when | departure | arrival | status | seat_no
------------+-------------------+-------------------+-----------+---------
26.09.2016 | Москва(SVO) | Анадырь(DYR) | Arrived | 5C
30.09.2016 | Анадырь(DYR) | Хабаровск(KHV) | Arrived | 1D
01.10.2016 | Хабаровск(KHV) | Благовещенск(BQS) | Arrived | 2C
06.10.2016 | Благовещенск(BQS) | Хабаровск(KHV) | Arrived | 2D
10.10.2016 | Хабаровск(KHV) | Анадырь(DYR) | Arrived | 20B
15.10.2016 | Анадырь(DYR) | Москва(SVO) | Scheduled |
(6 строк)
J.5.4. Новое бронирование
Попробуем отправить Александра Николаевича Радищева по маршруту, который принес ему известность. Разумеется бесплатно и бизнес-классом. Предварительно найдем «завтрашний» рейс, а также позаботимся о возвращении через неделю.
BEGIN;
INSERT INTO bookings (book_ref, book_date, total_amount)
VALUES ('_QWE12', bookings.now(), 0);
INSERT INTO tickets (ticket_no, book_ref, passenger_id, passenger_name)
VALUES ('_000000000001', '_QWE12', '1749 051790', 'ALEKSANDR RADISHCHEV');
INSERT INTO ticket_flights (ticket_no, flight_id, fare_conditions, amount)
VALUES ('_000000000001', 9720, 'Business', 0),
('_000000000001', 6662, 'Business', 0);
COMMIT;
Мы начинаем идентификаторы с подчёркивания, чтобы не пересекаться с диапазоном значений, присутствующих в базе.
Сразу зарегистрируемся на завтрашний рейс:
INSERT INTO boarding_passes (ticket_no, flight_id, boarding_no, seat_no)
VALUES ('_000000000001', 9720, 1, '1A');
Проверим информацию о созданном бронировании:
SELECT b.book_ref,
t.ticket_no,
t.passenger_id,
t.passenger_name,
tf.fare_conditions,
tf.amount,
f.scheduled_departure_local,
f.scheduled_arrival_local,
f.departure_city || '(' || f.departure_airport || ')' as departure,
f.arrival_city || '(' || f.arrival_airport || ')' as arrival,
f.status,
bp.seat_no
FROM bookings b
JOIN tickets t ON b.book_ref = t.book_ref
JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
JOIN flights_v f ON tf.flight_id = f.flight_id
LEFT JOIN boarding_passes bp ON tf.flight_id = bp.flight_id
AND tf.ticket_no = bp.ticket_no
WHERE b.book_ref = '_QWE12'
ORDER BY t.ticket_no, f.scheduled_departure;
-[ RECORD 1 ]-------------+---------------------
book_ref | _QWE12
ticket_no | _000000000001
passenger_id | 1749 051790
passenger_name | ALEKSANDR RADISHCHEV
fare_conditions | Business
amount | 0.00
scheduled_departure_local | 2016-10-14 08:45:00
scheduled_arrival_local | 2016-10-14 09:35:00
departure | Санкт-Петербург(LED)
arrival | Москва(SVO)
status | On Time
seat_no | 1A
-[ RECORD 2 ]-------------+---------------------
book_ref | _QWE12
ticket_no | _000000000001
passenger_id | 1749 051790
passenger_name | ALEKSANDR RADISHCHEV
fare_conditions | Business
amount | 0.00
scheduled_departure_local | 2016-10-21 09:20:00
scheduled_arrival_local | 2016-10-21 10:10:00
departure | Москва(SVO)
arrival | Санкт-Петербург(LED)
status | Scheduled
seat_no |
Надеемся, что эти несколько простых примеров помогли составить представление о содержимом демонстрационной базы данных.