5 演示数据库
探索航空订票演示数据库及其表和视图，使用贴近实际的航班数据编写 SQL 查询。
关于演示数据库
概览
要掌握更复杂的查询，我们需要创建一个规模更大的数据库（包含八张表，而不只是三张），并填入一些合理的数据。
我们选择航空公司的航班作为业务场景。数据库包含一家假想航空公司在特定时间段内所有航班的统计数据。坐过飞机的人应该都熟悉这些场景，不过我们还是会逐一说明。
数据库模式如第 63 页所示。我们力求取得平衡：既让数据库模式保持简单，避免不必要的复杂性，又保留足够的细节，以便编写有趣且有意义的查询。
模式中的主要实体是预订（对应 bookings 表）。每次预订可以包含多名旅客，每名旅客各有一张机票（tickets）。对于旅客本人（可能多次乘坐本航空公司的航班），我们没有可靠的唯一标识符，因此没有将旅客单独建模为实体。我们将假定所有旅客都互不相同。
每张机票总是包含一个或多个航段（flight segment，ticket_flights）。如果出发机场与到达机场之间没有直达航班，或者机票是往返机票，一张机票就可能包含多个航段。
假定同一次预订中的所有机票都包含相同的航段，不过数据库模式并未设置这一约束。
每个航班（flights）从一个机场（airports）飞往另一个机场。航班号相同的航班具有相同的出发地和目的地，但出发日期不同。
办理值机时，每名旅客都会获得一张登机牌（boarding_passes），上面注明座位号。旅客只有持有该航班的机票，才能办理值机。航班与座位的组合必须唯一，以免为同一个座位签发多张登机牌。
飞机上的座位数量及其在不同舱位等级之间的分配，取决于执飞飞机的具体型号。假定每种机型只有一种客舱布局。数据库模式并不检查登机牌上指定的座位是否确实存在于该架飞机上。
接下来的各小节中，我们将介绍每张表，以及附加的视图和函数。你随时可以使用 \d+ 命令获取任意表的确切定义，包括数据类型和列说明。
图 5.1 订票数据库中的关系
图 5.2 演示数据库中的机场与航线
预订
旅客必须提前订票。预订日期（book_date）必须在航班起飞前的一个月内。预订由预订编号标识（book_ref，由字母和数字组成的六位组合）。
total_amount 字段保存这次预订中所有旅客的机票总价。
机票
每张机票都有一个唯一编号（ticket_no），由 13 位数字组成。
机票包含旅客的身份证件号码（passenger_id）、名字和姓氏（passenger_name）以及联系信息（contact_data）。
由于旅客的证件号码或姓名可能发生变化（例如改名或换发护照），不一定能唯一识别属于同一名旅客的所有机票。为简单起见，我们假定所有旅客都互不相同。
航段
航段将机票与航班关联起来，由机票编号和航班编号共同标识。
每个航段都有价格（amount）和舱位等级（fare_conditions）。
航班
唯一标识符既可以是自然标识符（与现实中的对象相关），也可以是代理标识符（由系统生成，通常是递增的数字序列）。
flights 表的自然复合键由航班号（flight_no）和出发日期（scheduled_departure）组成。为了缩短引用该表的外键，表中使用代理键（surrogate key）flight_id 作为主键。
每个航班从一个机场（departure_airport）出发，到达另一个机场（arrival_airport）。
模式中没有“中转航班”这样的实体：如果两个机场之间没有直达航班，只需在机票中包含所有必要的航段。
每个航班都有计划起飞和到达的日期与时间（scheduled_departure 和 scheduled_arrival）。但由于延误，实际起飞和到达时间（actual_departure 和 actual_arrival）可能不同，延误可能从几分钟到几小时不等。
航班状态 status 可以取下列值之一：
机场
机场由三个字母组成的机场代码 airport_code 标识，并具有机场名称 airport_name。
城市 city 没有单独建模为实体；城市名称只是机场的一个属性，用于识别同一城市的所有机场。表中还包含坐标 coordinates（经纬度）和时区 timezone。
登机牌
值机在计划起飞前 24 小时开放，旅客办理值机时会获得登机牌。每张登机牌由机票编号与航班编号的组合唯一标识。
登机牌编号（boarding_no）按值机顺序依次分配，仅在特定航班内唯一。登机牌还注明座位号（seat_no）。
飞机
每种机型由三个字符组成的 aircraft_code 标识。表中还包含机型名称 model，以及以千米为单位的最大航程（range）。
座位
座位定义了每种机型的客舱布局。每个座位都有编号（seat_no）及指定的舱位等级（fare_conditions）：Economy（经济舱）、Comfort（舒适舱）或 Business（商务舱）。
航班视图
flights_v 视图建立在 flights 表之上。视图可以像表一样查询，但不存储数据，只是执行某个查询。以下 psql 命令显示该视图的定义和查询：
这个视图增加了以下信息：
出发机场的详细信息：departure_airport、departure_airport_name、departure_city
到达机场的详细信息：arrival_airport、arrival_airport_name、arrival_city
当地起飞时间：scheduled_departure_local、actual_departure_local
航线视图
flights 表包含一些冗余信息，可以从中提取不依赖具体航班日期的航线信息（航班号、出发和目的地机场、机型）。
这些信息构成了 routes 视图。此外，该视图还显示 days_of_week 数组，表示每周哪些天有航班，以及计划飞行时长 duration。
“now”函数
演示数据库包含一个数据快照，类似于在某个时刻为真实系统创建的备份。例如，如果某个航班的状态为 Departed，就表示备份时飞机正在空中飞行。
快照时间保存在 bookings.now 函数中。在通常需要调用 now 函数的场合，可以在演示查询中使用这个函数。
此外，这个函数的返回值还决定了演示数据库的版本。最新版本对应 2017 年 8 月 15 日，包含该日期的数据。
安装
从网站安装
演示数据库提供三个版本，只在数据量上有所不同：
学习编写查询时，小型数据库已经足够；但如果想研究查询优化的具体问题，请选择大型数据库，这样就能看到查询在大量数据上的表现。
这些文件包含使用 pg_dump 工具创建的 demo 数据库逻辑备份。注意，如果已有一个名为 demo 的数据库，它会被删除，然后从这个备份恢复。运行脚本的用户将成为该数据库的所有者。
要在 Linux 系统上安装演示数据库，请切换到 postgres 用户，下载相应文件。例如，下载小型数据库时执行以下操作：
$ wget https://edu.postgrespro.com/demo-small-en.zip
然后运行以下命令：
$ zcat demo-small-en.zip | psql
在 Windows 上，下载 edu.postgrespro.com/demo-small-en.zip 文件，双击打开压缩包，将 demo-small-en-20170815.sql 文件复制到 C:\Program Files\PostgreSQL\17 目录中。
pgAdmin 应用程序（见第 111 页）不支持从这种备份中恢复数据库。因此，应通过点击“SQL Shell (psql)”快捷方式启动 psql，运行以下命令：
postgres# \i demo-small-en-20170815.sql
如果找不到文件，请检查快捷方式的“起始位置”（Start in）属性；文件必须位于该目录中。
示例查询
略谈模式
安装完成后，启动 psql 并连接演示数据库：
postgres=# \c demo
You are now connected to database "demo" as user
"postgres".
bookings 模式包含所有必要的实体。连接数据库时会自动使用这个模式，因此无需显式指定：
demo=# 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 rows)
不过，调用 bookings.now 函数时必须指定模式，以便将它与标准 now 函数区分开来：
demo=# SELECT bookings.now();
now
------------------------
2017-08-15 18:00:00+03
(1 row)
以下查询返回城市和机场：
demo=# SELECT airport_code, city
FROM airports LIMIT 4;
airport_code | city
--------------+---------------
YKS | Yakutsk
MJZ | Mirnyj
KHV | Khabarovsk
PKC | Petropavlovsk
(4 rows)
数据库内容提供英语和俄语两个版本。将 bookings.lang 参数分别设置为 en 或 ru，即可在两种语言之间切换。默认选择英语，可以如下更改设置：
demo=# ALTER DATABASE demo SET bookings.lang = ru;
语言已在数据库级别修改，现在需要重新连接数据库。
You are now connected to database "demo" as user
"postgres".
demo=# SELECT airport_code, city
FROM airports LIMIT 4;
airport_code | city
--------------+--------------------------
YKS | Якутск
MJZ | Мирный
KHV | Хабаровск
PKC | Петропавловск-Камчатский
(4 rows)
要了解其工作原理，可以使用 \d+ psql 命令查看 aircrafts 或 airports 的定义。（译注：原版此处指 psql 的 \d+ 命令；英文网页将命令和程序名合入了同一代码片段。）
要了解更多模式管理方面的信息，请参阅文档：postgrespro.com/doc/ddl-schemas.
设置配置参数的更多信息见 postgrespro.com/doc/config-setting.
简单查询
我们用这个模式讨论几个问题，由浅入深。大多数问题后面都给出了解答，但要有效地学习 SQL，不妨先自己编写查询，再看答案。
问题。 前天，谁乘坐了从 Moscow（SVO）飞往 Novosibirsk（OVB）的航班，座位是 1A？机票是什么时候预订的？
解答。 “前天”应根据 booking.now 的值计算，而不是根据当前日期。（译注：原版正文此处写作 booking.now，下方 SQL 使用的是 bookings.now()。）
SELECT t.passenger_name,
b.book_date
FROM bookings b
JOIN tickets t
ON t.book_ref = b.book_ref
JOIN boarding_passes bp
ON bp.ticket_no = t.ticket_no
JOIN flights f
ON f.flight_id = bp.flight_id
WHERE f.departure_airport = 'SVO'
AND f.arrival_airport = 'OVB'
AND f.scheduled_departure::date =
bookings.now()::date - interval '2 day'
AND bp.seat_no = '1A';
问题。 昨天的 PG0404 航班有多少空座位？
解答。 这个问题有多种解法。其中一种是使用 NOT EXISTS 表达式，找出没有登机牌的座位：
SELECT count(*)
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
);
另一种方法是通过集合差运算查找空座位。不同解法会得到相同结果，但性能有时会有所不同；如果性能很重要，就需要考虑这一点。
SELECT count(*) FROM
(
SELECT s.seat_no
FROM seats s
WHERE s.aircraft_code = (
SELECT aircraft_code FROM flights
WHERE flight_no = 'PG0404'
AND scheduled_departure::date =
bookings.now()::date - interval '1 day'
)
EXCEPT
SELECT bp.seat_no
FROM boarding_passes bp
WHERE bp.flight_id = (
SELECT flight_id FROM flights
WHERE flight_no = 'PG0404'
AND scheduled_departure::date =
bookings.now()::date - interval '1 day'
)
) t;
问题。 哪些航班的延误时间最长？列出排名前十的“佼佼者”。
解答。 查询只能包含已经起飞的航班。
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 f.actual_departure - f.scheduled_departure
DESC
LIMIT 10;
也可以使用 status 列列出所有适用的状态，定义同样的条件。还可以指定 DESC NULLS LAST 排序，使未定义值排在结果末尾，从而完全省略 WHERE 条件。
聚合函数
问题。 从 Moscow 到 St. Petersburg 的各个航班，最短飞行时长是多少？延误超过一小时的次数各有多少？
解答。 解决这个问题时，使用现有的 flights_v 视图比自行连接表更方便。只能考虑已经到达的航班。
SELECT f.flight_no,
f.scheduled_duration,
min(f.actual_duration),
max(f.actual_duration),
sum(CASE WHEN f.actual_departure >
f.scheduled_departure +
interval '1 hour'
THEN 1 ELSE 0
END) delays
FROM flights_v f
WHERE f.departure_city = 'Moscow'
AND f.arrival_city = 'St. Petersburg'
AND f.status = 'Arrived'
GROUP BY f.flight_no,
f.scheduled_duration;
问题。 找出每次乘机都第一个办理值机的旅客。只考虑至少乘坐过两次航班的旅客。
解答。 利用登机牌编号按值机顺序分配这一事实。
SELECT t.passenger_name,
t.ticket_no
FROM tickets t
JOIN boarding_passes bp
ON bp.ticket_no = t.ticket_no
GROUP BY t.passenger_name,
t.ticket_no
HAVING max(bp.boarding_no) = 1
AND count(*) > 1;
问题。 一次预订可以包含多少名旅客？
解答。 我们先统计每次预订中的旅客人数，再按人数统计预订次数。
SELECT tt.cnt,
count(*)
FROM (
SELECT t.book_ref,
count(*) cnt
FROM tickets t
GROUP BY t.book_ref
) tt
GROUP BY tt.cnt
ORDER BY tt.cnt;
窗口函数
问题。 对于每张机票，显示它包含的所有航段以及转机时间。结果仅限一周前预订的机票。
解答。 使用窗口函数，避免两次访问同样的数据。
SELECT tf.ticket_no,
f.departure_airport,
f.arrival_airport,
f.scheduled_arrival,
lead(f.scheduled_departure) OVER w
AS next_departure,
lead(f.scheduled_departure) OVER w -
f.scheduled_arrival AS gap
FROM bookings b
JOIN tickets t
ON t.book_ref = b.book_ref
JOIN ticket_flights tf
ON tf.ticket_no = t.ticket_no
JOIN flights f
ON tf.flight_id = f.flight_id
WHERE b.book_date =
bookings.now()::date - interval '7 day'
WINDOW w AS (PARTITION BY tf.ticket_no
ORDER BY f.scheduled_departure);
可以看到，航班之间的间隔时间可能长达数天：往返机票和单程机票按同样方式处理，在目的地停留的时间也被当作转机时间。“数组”一节中有一道题的解法，可以帮助你在编写查询时考虑这一点。
问题。 哪些名字与姓氏的组合最常见？具有这些姓名的旅客占旅客总数的比例是多少？
解答。 使用窗口函数计算旅客总数。
SELECT passenger_name,
round( 100.0 * cnt / sum(cnt) OVER (), 2)
AS percent
FROM (
SELECT passenger_name,
count(*) cnt
FROM tickets
GROUP BY passenger_name
) t
ORDER BY percent DESC;
问题。 分别针对名字和姓氏求解上一道题。
解答。 我们来看看如何统计名字。统计姓氏的查询只在 p 子查询上有所不同。
从这个复杂的查询可以看出，如果需要分别使用不同的值，就应避免将它们放在同一个文本字段中；用学术术语来说，这叫第一范式。
WITH p AS (
SELECT left(passenger_name,
position(' ' IN passenger_name))
AS passenger_name
FROM tickets
)
SELECT passenger_name,
round( 100.0 * cnt / sum(cnt) OVER (), 2)
AS percent
FROM (
SELECT passenger_name,
count(*) cnt
FROM p
GROUP BY passenger_name
) t
ORDER BY percent DESC;
数组
问题。 数据库并未明确标注机票是单程还是往返。不过，可以通过比较最初的出发地和最后的目的地来判断。对于每张机票，忽略转机，显示出发机场和目的地机场，并标明是否为往返机票。
解答。 一种简单的解法是使用 array_agg 函数，将行程中的机场列表转换为数组。
我们选取数组的中间元素作为目的地机场，并假定去程和回程的停靠次数相同。
在这个示例中，tickets 表只扫描一次。显示机场数组是为了便于理解；数据量较大时，最好将它从查询中移除，因为额外的数据可能影响性能。
WITH t AS (
SELECT ticket_no,
a,
a[1] departure,
a[cardinality(a)] last_arrival,
a[cardinality(a)/2+1] middle
FROM (
SELECT t.ticket_no,
array_agg( f.departure_airport
ORDER BY f.scheduled_departure) ||
(array_agg( f.arrival_airport
ORDER BY f.scheduled_departure DESC)
)[1] AS a
FROM tickets t
JOIN ticket_flights tf
ON tf.ticket_no = t.ticket_no
JOIN flights f
ON f.flight_id = tf.flight_id
GROUP BY t.ticket_no
) t
)
SELECT t.ticket_no,
t.a,
t.departure,
CASE
WHEN t.departure = t.last_arrival
THEN t.middle
ELSE t.last_arrival
END arrival,
(t.departure = t.last_arrival) return_ticket
FROM t;
问题。 找出去程航线与回程航线不同的往返机票。
问题。 找出去程和回程航班在一周中不同日期出发的机场对。
解答。 题目中构建每周飞行日期数组的部分，实际上已经由 routes 视图解决了。只需使用 && 操作符检查数组是否相交，并确认交集为空。
SELECT r1.departure_airport,
r1.arrival_airport,
r1.days_of_week dow,
r2.days_of_week dow_back
FROM routes r1
JOIN routes r2
ON r1.arrival_airport = r2.departure_airport
AND r1.departure_airport = r2.arrival_airport
WHERE NOT (r1.days_of_week && r2.days_of_week);
递归查询
问题。 如何以最少的转机次数从 Ust-Kut（UKX）到达 Neryungri（CNN）？飞行时间是多少？
解答。 这个问题需要找出图中的最短路径。我们将使用递归查询来完成。
这个查询的详细分步说明已发布在 habr.com/en/company/postgrespro/blog/490228/. 下面简要介绍。
查询执行过程中会构建 hops 数组，检查该数组即可避免无限循环。
由于查询执行的是广度优先搜索，找到的第一条路径的转机次数最少。为了避免遍历其他路径（它们可能很多，而且肯定比已找到的路径更长），这里使用了 found 属性。它通过 bool_or 窗口函数计算。
WITH RECURSIVE p(
last_arrival, destination, hops,
flights, flight_time, found
) AS (
SELECT a_from.airport_code,
a_to.airport_code,
array[a_from.airport_code],
array[]::char(6)[],
interval '0',
a_from.airport_code = a_to.airport_code
FROM airports a_from,
airports a_to
WHERE a_from.airport_code = 'UKX'
AND a_to.airport_code = 'CNN'
UNION ALL
SELECT r.arrival_airport,
p.destination,
(p.hops || r.arrival_airport)::char(3)[],
(p.flights || r.flight_no)::char(6)[],
p.flight_time + r.duration,
bool_or(r.arrival_airport = p.destination)
OVER ()
FROM p JOIN routes r
ON r.departure_airport = p.last_arrival
WHERE NOT r.arrival_airport = ANY(p.hops)
AND NOT p.found
)
SELECT hops, flights, flight_time
FROM p
WHERE p.last_arrival = p.destination;
将这个查询与不使用 found 技巧的简化版本作比较，会很有帮助。
要了解更多递归查询方面的信息，请参阅文档：postgrespro.com/doc/queries-with.
问题。 从任意一个机场前往另一个机场，最多可能需要转机多少次？
解答。 我们可以在上一个查询的基础上求解。不过，第一次迭代现在必须包含所有可能的机场对，而不只是某一对：每个机场都要与其他所有机场配对。先求出每一对机场之间的最短路径，再从中选取最长的一条。
显然，只有航线图是连通的，才有可能这样做。我们的演示数据库满足这一条件。
WITH RECURSIVE p(
departure, last_arrival,
destination, hops, found
) AS (
SELECT a_from.airport_code,
a_from.airport_code,
a_to.airport_code,
array[a_from.airport_code],
a_from.airport_code = a_to.airport_code
FROM airports a_from,
airports a_to
UNION ALL
SELECT p.departure,
r.arrival_airport,
p.destination,
(p.hops || r.arrival_airport)::char(3)[],
bool_or(r.arrival_airport = p.destination)
OVER (PARTITION BY p.departure,
p.destination)
FROM p JOIN routes r
ON r.departure_airport = p.last_arrival
WHERE NOT r.arrival_airport = ANY(p.hops)
AND NOT p.found
)
SELECT max(cardinality(hops)-1)
FROM p
WHERE p.last_arrival = p.destination;
这个查询也使用了 found 属性，但这里需要对每一对机场分别计算。
问题。 从飞行时间的角度，找出 Ust-Kut（UKX）到 Neryungri（CNN）的最短航线（忽略转机时间）。
解答。 为避免无限循环，我们使用 PostgreSQL 14 引入的 CYCLE 子句。
WITH RECURSIVE p(
last_arrival, destination,
flights, flight_time,
min_time
) AS (
SELECT a_from.airport_code,
a_to.airport_code,
array[]::char(6)[],
interval '0',
NULL::interval
FROM airports a_from,
airports a_to
WHERE a_from.airport_code = 'UKX'
AND a_to.airport_code = 'CNN'
UNION ALL
SELECT r.arrival_airport,
p.destination,
(p.flights || r.flight_no)::char(6)[],
p.flight_time + r.duration,
least(
p.min_time, min(p.flight_time+r.duration)
FILTER (
WHERE r.arrival_airport = p.destination
) OVER ()
)
FROM p
JOIN routes r
ON r.departure_airport = p.last_arrival
WHERE p.flight_time + r.duration <
coalesce(p.min_time, interval '1 year')
)
CYCLE last_arrival SET is_cycle USING hops
SELECT hops,
flights,
flight_time
FROM (
SELECT hops,
flights,
flight_time,
min(min_time) OVER () min_time
FROM p
WHERE p.last_arrival = p.destination
) t
WHERE flight_time = min_time;
注意，这条航线的转机次数可能不是最少的。
函数与扩展
问题。 求 Kaliningrad（KGD）与 Petropavlovsk-Kamchatsky（PKC）之间的距离。
解答。 airports 表中包含机场坐标。要精确计算两点之间的距离，必须考虑地球曲率。最适合完成这项任务的是 PostGIS 扩展，它可以用大地水准面来近似地球表面。
不过，这个示例用简单的球体模型就足够了。我们使用 earthdistance，再将结果从英里转换为千米。
CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;
SELECT round(
(a_from.coordinates <@> a_to.coordinates) *
1.609344
)
FROM airports a_from,
airports a_to
WHERE a_from.airport_code = 'KGD'
AND a_to.airport_code = 'PKC';
问题。 画出所有机场之间的航班图。