Практикум demo — авиакомпания PostgreSQL

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Архитектору

Контекст: учебная база demo — открытый логический дамп для практики SQL. Предметная область — воображаемая авиакомпания: бронирования, билеты, рейсы, аэропорты. После 101 и 888 — лучший следующий шаг для JOIN, агрегатов и оконных функций на осмысленных данных.

См. также: JOIN — четыре типа на одном примере · Практикум shop_data · мета-команды psql

---

Установка базы demo

Официальные архивы — логический дамп pg_dump, восстанавливается через psql. Три размера (данные о полётах за 1 / 3 / 12 месяцев):

Архив	Объём архива	Размер БД после restore
demo-small.zip	~21 МБ	~280 МБ
demo-medium.zip	~62 МБ	~702 МБ
demo-big.zip	~232 МБ	~2,6 ГБ

Для первых запросов достаточно small; для экспериментов с планами и индексами — big.

Linux (от пользователя postgres):

sudo su - postgres
wget https://edu.postgrespro.ru/demo-small.zip
zcat demo-small.zip | psql

Windows. Скачайте архив, распакуйте SQL-файл (имя с датой среза, например demo-small-20170815.sql), положите в рабочую папку psql и выполните:

\i demo-small-20170815.sql

Существующая база demo

Скрипт восстановления пересоздаёт базу demo. Владельцем станет роль, под которой выполнялся restore.

Подключение:

psql -U postgres -d demo

\c demo
SELECT bookings.now();
SELECT count(*) FROM flights;

---

Схема данных (схема bookings)

Все учебные объекты лежат в схеме bookings. При подключении к demo она в search_path, поэтому в запросах можно писать flights, а не bookings.flights.

Таблица	Назначение
bookings	Бронирование (book_ref, дата, total_amount)
tickets	Билет на пассажира (ticket_no, passenger_name, …)
ticket_flights	Связь билет ↔ рейс, класс, стоимость перелёта
flights	Конкретный рейс (расписание, статус, фактические времена)
airports	Код IATA, город, coordinates (point), timezone
aircrafts	Модель ВС и дальность полёта
seats	Места в салоне по модели и классу обслуживания
boarding_passes	Посадочный талон (рейс, место, порядок посадки)

Представления

• flights_v — рейсы с расшифровкой аэропортов, локальным временем и длительностью полёта (\d+ flights_v).
• routes — «маршрут без даты»: номер рейса, аэропорты, модель, массив дней недели days_of_week, плановая duration.

Служебное

• bookings.now() — фиксированный «момент среза» дампа (как now() в учебных запросах). В small-дампе срез — 2017-08-15.
• Параметр bookings.lang (ru / en) — язык названий городов и моделей: ALTER DATABASE demo SET bookings.lang = 'en'; затем \c.

Подробное описание столбца — \d+ имя_таблицы в psql.

---

Базовые запросы

Справочники

SELECT airport_code, city FROM airports LIMIT 5;

SELECT aircraft_code, model, range FROM aircrafts ORDER BY range DESC;

JOIN — рейсы с городами

SELECT f.flight_no,
       f.scheduled_departure,
       da.city AS from_city,
       aa.city AS to_city
FROM flights f
JOIN airports da ON da.airport_code = f.departure_airport
JOIN airports aa ON aa.airport_code = f.arrival_airport
WHERE f.scheduled_departure::date = bookings.now()::date
LIMIT 10;

Тот же смысл короче через представление:

SELECT flight_no, departure_city, arrival_city, scheduled_departure
FROM flights_v
WHERE scheduled_departure::date = bookings.now()::date
LIMIT 10;

Агрегаты и GROUP BY

Сколько билетов в каждом бронировании:

SELECT cnt, count(*) AS bookings_count
FROM (
  SELECT book_ref, count(*) AS cnt
  FROM tickets
  GROUP BY book_ref
) tt
GROUP BY cnt
ORDER BY cnt;

Доля пассажиров по фамилии (оконная функция):

SELECT passenger_name,
       round(100.0 * cnt / sum(cnt) OVER (), 2) AS percent
FROM (
  SELECT passenger_name, count(*) AS cnt
  FROM tickets
  GROUP BY passenger_name
) t
ORDER BY percent DESC
LIMIT 10;

Задержки рейсов

SELECT f.flight_no,
       f.scheduled_departure,
       f.actual_departure,
       f.actual_departure - f.scheduled_departure AS delay
FROM flights f
WHERE f.actual_departure IS NOT NULL
ORDER BY delay DESC
LIMIT 10;

NOT EXISTS — свободные места на рейсе

SELECT count(*) AS free_seats
FROM flights f
JOIN seats s ON s.aircraft_code = f.aircraft_code
WHERE f.flight_no = 'PG0404'
  AND f.scheduled_departure::date = bookings.now()::date - INTERVAL '1 day'
  AND NOT EXISTS (
    SELECT NULL
    FROM boarding_passes bp
    WHERE bp.flight_id = f.flight_id
      AND bp.seat_no = s.seat_no
  );

---

Расстояние между аэропортами

Задача — расстояние KGD (Калининград) и PKC (Петропавловск-Камчатский). Координаты в airports.coordinates; для сферической модели достаточно расширений cube и earthdistance:

CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;

SELECT round(
  (a_from.coordinates <@> a_to.coordinates) * 1.609344
) AS km
FROM airports a_from,
     airports a_to
WHERE a_from.airport_code = 'KGD'
  AND a_to.airport_code = 'PKC';

Оператор <@> даёт расстояние в милях; множитель 1.609344 переводит в километры. Для продакшена с геодезической точностью смотрите PostGIS — в 888 и справочнике.

---

Задания для самопроверки

1. Список аэропортов России (city или код страны в данных) с числом исходящих рейсов за «сегодня» (bookings.now()).
2. Топ-5 маршрутов (departure_airport → arrival_airport) по числу рейсов за последние 7 дней от bookings.now().
3. Пассажиры, зарегистрировавшиеся первыми (boarding_no = 1) более чем на одном рейсе (подсказка — GROUP BY + HAVING).
4. Граф рейсов — пары аэропортов, между которыми есть хотя бы один рейс в routes или flights (можно начать с SELECT DISTINCT departure_airport, arrival_airport FROM routes).
5. Переключите bookings.lang на en, переподключитесь (\c) и сравните вывод SELECT city FROM airports LIMIT 5.

Дальше

Сложные маршруты с пересадками решаются через рекурсивный CTE — см. 551. Оптимизацию планов — 881. Администрирование сервера — справочник PostgreSQL.

---