L.5. Использование #
L.5.1. Схема bookings
#
Схема bookings
содержит все объекты демонстрационной базы. При подключении к базе параметр конфигурации search_path
автоматически принимает значение bookings, public
, так что указывать имя схемы явно необязательно.
Однако для функции bookings.now
в любом случае необходимо явно указывать схему, чтобы отличать её от стандартной функции now
.
L.5.2. Переводы #
По умолчанию значения различных переводимых полей выдаются на русском языке. Это поля airport_name
и city
представления airports
, а также поле model
представления aircrafts
.
Вы можете выбрать другой язык для отображения этих полей (хотя в данной базе представлен только английский перевод). Например, для переключения на английский установите для параметра bookings.lang
значение en
. Также может быть удобно выбрать язык на уровне базы данных:
ALTER DATABASE demo SET bookings.lang = en;
Чтобы это изменение вступило в силу, необходимо переподключиться к базе данных. Другие варианты установления параметров конфигурации описаны в Разделе 18.1.
В следующих примерах для переводимых полей выбран русский язык.
L.5.3. Примеры запросов #
Чтобы лучше познакомиться с содержимым демонстрационной базы данных, посмотрим на результаты нескольких простых запросов.
Результаты, представленные ниже, были получены для версии с небольшой базой данных (demo-small) от 15 августа 2017 г. Если в вашей системе запросы выдают другие данные, проверьте версию демонстрационной базы (функция 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.coordinates 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 | coordinates ------+------------------------------------+-----------+------------------------- DME | Домодедово | Москва | (37.9062995910645,55.4087982177734) SVO | Шереметьево | Москва | (37.4146,55.972599) VKO | Внуково | Москва | (37.2615013123,55.5914993286) ULV | Баратаевка | Ульяновск | (48.2266998291,54.2682991028) ULY | Ульяновск-Восточный | Ульяновск | (48.8027000427246,54.4010009765625) (5 строк)
Чтобы понять, откуда и куда можно улететь, удобно использовать материализованное представление routes
, в котором агрегируется информация о всех рейсах. Вот, например, куда, в какие дни недели и за какое время можно долететь из Волгограда:
SELECT r.arrival_city as city, r.arrival_airport as code, r.arrival_airport_name as airport_name, r.days_of_week, r.duration FROM routes r WHERE r.departure_city = 'Волгоград';
city | 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 | Богашёво | {1} | 03:50:00 (6 строк)
База данных была сформирована на момент времени, возвращаемый функцией bookings.now()
:
SELECT bookings.now() as now;
now ------------------------ 2017-08-15 18: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 | 2017-07-16 01:50:00+03 | 2017-08-15 17:25:00+03 Cancelled | 414 | 2017-07-19 11:35:00+03 | 2017-09-14 20:55:00+03 Departed | 58 | 2017-08-15 09:55:00+03 | 2017-08-15 17:50:00+03 Delayed | 41 | 2017-08-15 15:15:00+03 | 2017-08-16 17:25:00+03 On Time | 518 | 2017-08-15 17:55:00+03 | 2017-08-16 18:00:00+03 Scheduled | 15383 | 2017-08-16 18:05:00+03 | 2017-09-14 20: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 | 2017-08-16 08:10:00+03 scheduled_departure_local | 2017-08-16 10:10:00 scheduled_arrival | 2017-08-16 09:55:00+03 scheduled_arrival_local | 2017-08-16 09: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
указано не только московское время, но и местное время в аэропортах вылета и прилета.
L.5.4. Бронирования #
Каждое бронирование может включать несколько билетов, по одному на каждого пассажира. Билет, в свою очередь, может включать несколько перелетов. Полная информация о бронировании находится в трёх таблицах: bookings
, tickets
и ticket_flights
.
Найдём несколько бронирований с самой высокой стоимостью:
SELECT * FROM bookings ORDER BY total_amount desc LIMIT 10;
book_ref | book_date | total_amount ----------+------------------------+-------------- 3B54BB | 2017-07-05 17:08:00+03 | 1204500.00 3AC131 | 2017-07-31 01:06:00+03 | 1087100.00 65A6EA | 2017-07-03 06:28:00+03 | 1065600.00 D7E9AA | 2017-08-08 05:29:00+03 | 1062800.00 EF479E | 2017-08-02 15:58:00+03 | 1035100.00 521C53 | 2017-07-08 09:25:00+03 | 985500.00 514CA6 | 2017-07-27 05:07:00+03 | 955000.00 D70BD9 | 2017-07-05 12:47:00+03 | 947500.00 EC7EDA | 2017-07-02 16:13:00+03 | 946800.00 8E4370 | 2017-07-28 02: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 ------------+----------------------+----------------------+----------+----------- 29.07.2017 | Москва (SVO) | Анадырь (DYR) | Business | 185300.00 02.08.2017 | Анадырь (DYR) | Khabarovsk (KHV) | Business | 92200.00 03.08.2017 | Хабаровск (KHV) | Благовещенск (BQS) | Business | 18000.00 08.08.2017 | Благовещенск (BQS) | Хабаровск (KHV) | Business | 18000.00 12.08.2017 | Хабаровск (KHV) | Анадырь (DYR) | Economy | 30700.00 17.08.2017 | Анадырь (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 ------------+----------------------+----------------------+-----------+--------- 29.07.2017 | Москва (SVO) | Анадырь (DYR) | Arrived | 5C 02.08.2017 | Анадырь (DYR) | Хабаровск (KHV) | Arrived | 1D 03.08.2017 | Хабаровск (KHV) | Благовещенск (BQS) | Arrived | 2C 08.08.2017 | Благовещенск(BQS) | Хабаровск(KHV) | Arrived | 2D 12.08.2017 | Хабаровск(KHV) | Анадырь(DYR) | Arrived | 20B 17.08.2017 | Анадырь(DYR) | Москва(SVO) | Scheduled | (6 строк)
L.5.5. Новое бронирование #
Попробуем отправить Александра Николаевича Радищева по маршруту, который принес ему известность. Разумеется бесплатно и бизнес-классом. Предварительно найдем «завтрашний» рейс, а также позаботимся о возвращении через неделю.
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', 8525, 'Business', 0), ('_000000000001', 4967, 'Business', 0); COMMIT;
Мы начинаем идентификаторы с подчёркивания, чтобы не пересекаться с диапазоном значений, присутствующих в базе.
Сразу зарегистрируемся на завтрашний рейс:
INSERT INTO boarding_passes (ticket_no, flight_id, boarding_no, seat_no) VALUES ('_000000000001', 8525, 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 | 2017-08-16 09:45:00 scheduled_arrival_local | 2017-08-16 10: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 | 2017-08-23 10:20:00 scheduled_arrival_local | 2017-08-23 11:10:00 departure | Москва (SVO) arrival | Санкт-Петербург (LED) status | Scheduled seat_no |
Надеемся, что эти несколько простых примеров помогли составить представление о содержимом демонстрационной базы данных.