本章目录

第 1 章：示例数据库

1.1 概述

为了研究更复杂的查询，我们需要一个有一定规模的数据库。本例由九张表组成，填充了贴近真实情况的数据。

我们选择航空出行作为业务场景。这个数据库记录了一家虚构航空公司在一段时间内运营的航班信息。1

数据库模式如下图所示。我们尽量让它既不过于复杂，也不过于简单：足够简洁，避免不必要的细节；同时又足够丰富，能够支持有意义、有趣的查询。

示例数据库结构，表名、字段名和关系标记保留原文
示例数据库结构，表名、字段名和关系标记保留原文

示例数据库网站提供四种数据规模的版本。本书使用 2025 年 9 月 1 日版，包含六个月的航班数据，数据库大小约为 2.7 GB。

可以通过下面的函数查看版本：

SELECT bookings.version();

               version
-----------------------------------
 PostgresPro 2025-09-01 (181 days)
(1 row)

1.2 主要实体

数据库的核心实体是预订 (bookings)。

SELECT * FROM bookings b WHERE b.book_ref = '03RUKL';

 book_ref | book_date                     | total_amount
----------+-------------------------------+--------------
 03RUKL   | 2026-01-04 08:01:43.367932+03 |     84850.00
(1 row)

这一行给出了预订日期和总金额。随着实际时间的推移，这笔预订距今的时间每天都在增加：

SELECT now() - b.book_date
FROM bookings b
WHERE b.book_ref = '03RUKL';

          ?column?
---------------------------
 264 days 09:13:04.888436
(1 row)

不过，示例数据库有一个固定的“当前”时间：

SELECT bookings.now();

          now
------------------------
 2026-03-01 03:00:00+03
(1 row)

以这个固定时间为参照，预订距今的时间就短得多：

SELECT bookings.now() - b.book_date
FROM bookings b
WHERE b.book_ref = '03RUKL';

         ?column?
--------------------------
 55 days 18:58:16.632068
(1 row)

一次预订可以包含多名乘客。每位乘客都会获得一张单独的去程机票 (tickets)；如果需要返程，还会另外签发一张返程机票。

选中的这次预订包含以下机票：

SELECT t.ticket_no, t.passenger_id, t.passenger_name
FROM bookings b
  JOIN tickets t ON t.book_ref = b.book_ref
WHERE b.book_ref = '03RUKL';

 ticket_no     | passenger_id     | passenger_name
---------------+------------------+--------------------
 0005435998767 | RU 3761090132061 | Evgeniy Kondratyev
 0005435998770 | RU 3761090132061 | Evgeniy Kondratyev
 0005435998774 | RU 5595520101762 | Oleg Golubev
 0005435998776 | RU 5595520101762 | Oleg Golubev
(4 rows)

如果出发地与目的地之间有直达航班，一张机票只包含一个航段 (segments)；如果途中需要转机，则包含多个航段。

这些乘客的行程包含哪些航段？我们只需查看其中一名乘客，因为同一次预订中的所有乘客都采用相同的行程：

SELECT s.*
FROM tickets t
  JOIN segments s ON s.ticket_no = t.ticket_no
WHERE t.ticket_no = '0005435998767';

 ticket_no     | flight_id | fare_conditions | price
---------------+-----------+-----------------+----------
 0005435998767 |     17386 | Economy         |  6750.00
 0005435998767 |     17606 | Economy         | 14500.00
(2 rows)

上面是去程机票。再对比一下返程机票：

 ticket_no     | flight_id | fare_conditions | price
---------------+-----------+-----------------+---------
 0005435998770 |     26693 | Comfort         | 3575.00
 0005435998770 |     27013 | Comfort         | 5525.00
 0005435998770 |     27375 | Economy         | 2750.00
 0005435998770 |     27537 | Economy         | 8250.00
(4 rows)

去程与返程的行程不一定相同：Evgeniy 在返程途中需要转机三次，其中两个航班 (flights) 的舱位等级为 Comfort。

每个航班沿一条航线 (routes) 从一个机场飞往另一个机场。具有相同航线编号的记录，其出发机场和到达机场相同，而具体航班则因出发日期而不同。

要查出所选航班对应的航线，我们还必须考虑这些表之间的时间关联。航班时刻表会不时调整，因此连接时不仅要匹配航线编号，还要确保航班的出发时间落在该航线的有效期内。

SELECT r.route_no, f.scheduled_departure, f.status,
  r.departure_airport dep, r.arrival_airport arr
FROM tickets t
  JOIN segments s ON s.ticket_no = t.ticket_no
  JOIN flights f ON f.flight_id = s.flight_id
  JOIN routes r ON r.route_no = f.route_no
               AND r.validity @> f.scheduled_departure
WHERE t.ticket_no = '0005435998767'
ORDER BY f.scheduled_departure;

 route_no | scheduled_departure    | status  | dep | arr
----------+------------------------+---------+-----+-----
 PG0177   | 2026-01-05 15:05:00+03 | Arrived | SVO | AER
 PG0587   | 2026-01-06 19:00:00+03 | Arrived | AER | LHR
(2 rows)

航班时刻表 (timetable) 视图隐藏了这些复杂细节。它就像机场的航班信息显示屏，展示我们需要的信息，而不必让我们关心两张表之间的连接：

SELECT tt.route_no, tt.scheduled_departure, tt.status,
  tt.departure_airport dep, tt.arrival_airport arr
FROM tickets t
  JOIN segments s ON s.ticket_no = t.ticket_no
  JOIN timetable tt ON tt.flight_id = s.flight_id
WHERE t.ticket_no = '0005435998770'
ORDER BY tt.scheduled_departure;

 route_no | scheduled_departure    | status    | dep | arr
----------+------------------------+-----------+-----+-----
 PG0272   | 2026-02-27 06:25:00+03 | Arrived   | LHR | CDG
 PG0781   | 2026-03-01 02:50:00+03 | Departed  | CDG | MUC
 PG0358   | 2026-03-03 03:50:00+03 | Scheduled | MUC | VIE
 PG0165   | 2026-03-04 02:20:00+03 | Scheduled | VIE | SVO
(4 rows)

两个去程航班和一个返程航班都已到达，状态为 Arrived。在示例数据库的当前时间，乘客正从 CDG 飞往 MUC，状态为 Departed。其余航班尚未开始办理登机手续 (check-in)，状态为 Scheduled。

机场 (airports) 使用标准的三字母机场代码作为键。下面是一个机场的完整记录：

SELECT * FROM airports WHERE airport_code = 'CDG' \gx

-[ RECORD 1 ]+------------------
airport_code | CDG
airport_name | Charles de Gaulle
city         | Paris
country      | France
coordinates  | (2.55,49.0128)
timezone     | Europe/Paris

下面是我们关注的那条航线：

SELECT lower(r.validity) valid_from,
  upper(r.validity) valid_to, r.airplane_code,
  r.days_of_week, r.scheduled_time, r.duration
FROM routes r
WHERE r.route_no = 'PG0781'
  AND r.validity @> '2026-03-01 02:50:00+03'::timestamptz \gx

-[ RECORD 1 ]--+-----------------------
valid_from     | 2026-02-01 03:00:00+03
valid_to       | 2026-03-01 03:00:00+03
airplane_code  | CR7
days_of_week   | {1,2,3,4,5,6,7}
scheduled_time | 00:50:00
duration       | 01:25:00

在这个有效期内，该航线每周七天都有航班，由 CR7 型飞机 (airplanes) 执飞：

SELECT * FROM airplanes WHERE airplane_code = 'CR7';

 airplane_code | model             | range | speed
---------------+-------------------+-------+-------
 CR7           | Bombardier CRJ700 |  3100 |   829
(1 row)

飞机上的座位 (seats) 数量及各舱位等级的座位分布，取决于执飞机型。在示例数据库中，同一型号的飞机都采用相同的客舱布局。

例如，下面是第一排座位：

SELECT s.*
FROM airplanes a
  JOIN seats s ON s.airplane_code = a.airplane_code
WHERE a.airplane_code = 'CR7'
  AND s.seat_no ~ '^1.$';

 airplane_code | seat_no | fare_conditions
---------------+---------+-----------------
 CR7           | 1A      | Business
 CR7           | 1C      | Business
 CR7           | 1D      | Business
(3 rows)

这些都是商务舱 (Business) 座位。下面统计这架飞机各舱位等级的座位总数：

SELECT s.fare_conditions, count(*)
FROM seats s
WHERE s.airplane_code = 'CR7'
GROUP BY s.fare_conditions;

 fare_conditions | count
-----------------+-------
 Business        |     6
 Comfort         |    12
 Economy         |    52
(3 rows)

下面是所选航线上一个航班的完整记录。实际出发和到达时间可能与计划时间有所不同：通常相差不大 (本例的出发时间晚了六分钟)，但有时也会相差数小时：

SELECT * FROM flights f
WHERE f.flight_id = 27013 \gx

-[ RECORD 1 ]-------+----------------------------
flight_id           | 27013
route_no            | PG0781
status              | Departed
scheduled_departure | 2026-03-01 02:50:00+03
scheduled_arrival   | 2026-03-01 04:15:00+03
actual_departure    | 2026-03-01 02:56:02.8521+03
actual_arrival      |

乘客为首个航班办理登机手续时，会获得该张机票上所有航班的登机牌 (boarding_passes)。登机牌上注明了座位。航班与座位的组合是唯一的：两名乘客不能占用同一个座位。登机时，登机牌上会记录乘客的登机序号，以及通过登机口的时间。

这位乘客已经坐过或将要坐在哪些座位上？

SELECT f.status, f.flight_id, bp.seat_no, bp.boarding_no
FROM tickets t
  JOIN segments s ON s.ticket_no = t.ticket_no
  JOIN flights f ON f.flight_id = s.flight_id
  LEFT JOIN boarding_passes bp
         ON bp.ticket_no = s.ticket_no
        AND bp.flight_id = s.flight_id
WHERE t.ticket_no IN ('0005435998767','0005435998770')
ORDER BY f.scheduled_departure;

 status    | flight_id | seat_no | boarding_no
-----------+-----------+---------+-------------
 Arrived   |     17386 | 50A     |          41
 Arrived   |     17606 | 21D     |          16
 Arrived   |     26693 | 11A     |         176
 Departed  |     27013 | 7C      |          49
 Scheduled |     27375 | 45D     |
 Scheduled |     27537 | 13B     |
(6 rows)

示例数据库可以用不同语言显示机场、城市、国家和飞机名称。显示语言由在数据库级别设置的配置参数 bookings.lang 控制：

SHOW bookings.lang;

 bookings.lang
---------------
 en
(1 row)

将当前会话的显示语言切换为俄语：

SET bookings.lang = 'ru';
SELECT * FROM airports a WHERE a.airport_code = 'SVO' \gx

-[ RECORD 1 ]+------------------
airport_code | SVO
airport_name | Шереметьево
city         | Москва
country      | Россия
coordinates  | (37.4146,55.9726)
timezone     | Europe/Moscow

这一功能通过基表上的视图实现，多语言名称以 JSON 形式存储在基表中：

SELECT ml.airport_name, ml.country, ml.city
FROM airports_data ml
WHERE ml.airport_code = 'SVO' \gx

-[ RECORD 1 ]+--------------------------------------------
airport_name | {"en": "Sheremetyevo", "ru": "Шереметьево"}
country      | {"en": "Russia", "ru": "Россия"}
city         | {"en": "Moscow", "ru": "Москва"}

最后，汇总一下示例数据库中的表和视图：

\dtv

                 List of relations
 Schema   | Name            | Type  | Owner
----------+-----------------+-------+----------
 bookings | airplanes       | view  | postgres
 bookings | airplanes_data  | table | postgres
 bookings | airports        | view  | postgres
 bookings | airports_data   | table | postgres
 bookings | boarding_passes | table | postgres
 bookings | bookings        | table | postgres
 bookings | flights         | table | postgres
 bookings | routes          | table | postgres
 bookings | seats           | table | postgres
 bookings | segments        | table | postgres
 bookings | tickets         | table | postgres
 bookings | timetable       | view  | postgres
(12 rows)

  1. 示例数据库 。 ↩︎