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                   |
    

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