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 |
Надеемся, что эти несколько простых примеров помогли составить представление о содержимом демонстрационной базы данных.