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                   |
    

Надеемся, что эти несколько простых примеров помогли составить представление о содержимом демонстрационной базы данных.