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