# 演示数据库 > 探索航空订票演示数据库及其表和视图，使用贴近实际的航班数据编写 SQL 查询。 --- LLMS 索引： [llms.txt](/llms.txt) --- ## 关于演示数据库 {#intro-section-5-1} ### 概览 {#intro-subsection-5-1-1} 要掌握更复杂的查询，我们需要创建一个规模更大的数据库（包含八张表，而不只是三张），并填入一些合理的数据。 我们选择航空公司的航班作为业务场景。数据库包含一家假想航空公司在特定时间段内所有航班的统计数据。坐过飞机的人应该都熟悉这些场景，不过我们还是会逐一说明。 数据库模式如第 [63](/05-demo-database/#intro-subsection-5-1-1) 页所示。我们力求取得平衡：既让数据库模式保持简单，避免不必要的复杂性，又保留足够的细节，以便编写有趣且有意义的查询。 模式中的主要实体是**预订**（对应 `bookings` 表）。每次预订可以包含多名旅客，每名旅客各有一张**机票**（`tickets`）。对于旅客本人（可能多次乘坐本航空公司的航班），我们没有可靠的唯一标识符，因此没有将旅客单独建模为实体。我们将假定所有旅客都互不相同。 每张机票总是包含一个或多个**航段**（flight segment，`ticket_flights`）。如果出发机场与到达机场之间没有直达航班，或者机票是往返机票，一张机票就可能包含多个航段。 假定同一次预订中的所有机票都包含相同的航段，不过数据库模式并未设置这一约束。 每个**航班**（`flights`）从一个**机场**（`airports`）飞往另一个机场。航班号相同的航班具有相同的出发地和目的地，但出发日期不同。 办理值机时，每名旅客都会获得一张**登机牌**（`boarding_passes`），上面注明座位号。旅客只有持有该航班的机票，才能办理值机。航班与座位的组合必须唯一，以免为同一个座位签发多张登机牌。 飞机上的**座位**数量及其在不同舱位等级之间的分配，取决于执飞**飞机**的具体型号。假定每种机型只有一种客舱布局。数据库模式并不检查登机牌上指定的座位是否确实存在于该架飞机上。 接下来的各小节中，我们将介绍每张表，以及附加的视图和函数。你随时可以使用 `\d+` 命令获取任意表的确切定义，包括数据类型和列说明。 ![aircrafts、seats、airports、flights、bookings、tickets、ticket_flights 和 boarding_passes 之间的关系，图中标出了键和可选字段。](/images/intro/p064-01.svg) {#intro-fig-5-1 num="5.1" caption="订票数据库中的关系" .book-original-figure width=681 height=422} ![订票演示数据库中的机场位置和航线分布图。](/images/intro/p065-02.svg) {#intro-fig-5-2 num="5.2" caption="演示数据库中的机场与航线" .book-original-figure width=797 height=501} ### 预订 {#intro-subsection-5-1-2} 旅客必须提前订票。预订日期（`book_date`）必须在航班起飞前的一个月内。预订由预订编号标识（`book_ref`，由字母和数字组成的六位组合）。 `total_amount` 字段保存这次预订中所有旅客的机票总价。 ### 机票 {#intro-subsection-5-1-3} 每张机票都有一个唯一编号（`ticket_no`），由 13 位数字组成。 机票包含旅客的身份证件号码（`passenger_id`）、名字和姓氏（`passenger_name`）以及联系信息（`contact_data`）。 由于旅客的证件号码或姓名可能发生变化（例如改名或换发护照），不一定能唯一识别属于同一名旅客的所有机票。为简单起见，我们假定所有旅客都互不相同。 ### 航段 {#intro-subsection-5-1-4} 航段将机票与航班关联起来，由机票编号和航班编号共同标识。 每个航段都有价格（`amount`）和舱位等级（`fare_conditions`）。 ### 航班 {#intro-subsection-5-1-5} 唯一标识符既可以是自然标识符（与现实中的对象相关），也可以是代理标识符（由系统生成，通常是递增的数字序列）。 `flights` 表的自然复合键由航班号（`flight_no`）和出发日期（`scheduled_departure`）组成。为了缩短引用该表的外键，表中使用代理键（surrogate key）`flight_id` 作为主键。 每个航班从一个机场（`departure_airport`）出发，到达另一个机场（`arrival_airport`）。 模式中没有“中转航班”这样的实体：如果两个机场之间没有直达航班，只需在机票中包含所有必要的航段。 每个航班都有计划起飞和到达的日期与时间（`scheduled_departure` 和 `scheduled_arrival`）。但由于延误，实际起飞和到达时间（`actual_departure` 和 `actual_arrival`）可能不同，延误可能从几分钟到几小时不等。 航班状态 `status` 可以取下列值之一： - Scheduled（计划中）：可以预订该航班。这个值在计划出发日期前一个月设置；此时，航班信息被录入数据库。 - On Time（准点）：航班已开放值机（计划起飞前 24 小时），且没有延误。 - Delayed（延误）：航班已开放值机（计划起飞前 24 小时），但发生了延误。 - Departed（已起飞）：飞机已经起飞，正在空中飞行。 - Arrived（已到达）：飞机已经到达目的地。 - Cancelled（已取消）：航班已取消。 ### 机场 {#intro-subsection-5-1-6} 机场由三个字母组成的机场代码 `airport_code` 标识，并具有机场名称 `airport_name`。 城市 `city` 没有单独建模为实体；城市名称只是机场的一个属性，用于识别同一城市的所有机场。表中还包含坐标 `coordinates`（经纬度）和时区 `timezone`。 ### 登机牌 {#intro-subsection-5-1-7} 值机在计划起飞前 24 小时开放，旅客办理值机时会获得登机牌。每张登机牌由机票编号与航班编号的组合唯一标识。 登机牌编号（`boarding_no`）按值机顺序依次分配，仅在特定航班内唯一。登机牌还注明座位号（`seat_no`）。 ### 飞机 {#intro-subsection-5-1-8} 每种机型由三个字符组成的 `aircraft_code` 标识。表中还包含机型名称 `model`，以及以千米为单位的最大航程（`range`）。 ### 座位 {#intro-subsection-5-1-9} 座位定义了每种机型的客舱布局。每个座位都有编号（`seat_no`）及指定的舱位等级（`fare_conditions`）：Economy（经济舱）、Comfort（舒适舱）或 Business（商务舱）。 ### 航班视图 {#intro-subsection-5-1-10} `flights_v` 视图建立在 `flights` 表之上。视图可以像表一样查询，但不存储数据，只是执行某个查询。以下 psql 命令显示该视图的定义和查询： ```psql postgres=# \d+ flights_v ``` 这个视图增加了以下信息： - 出发机场的详细信息：`departure_airport`、`departure_airport_name`、`departure_city` - 到达机场的详细信息：`arrival_airport`、`arrival_airport_name`、`arrival_city` - 当地起飞时间：`scheduled_departure_local`、`actual_departure_local` - 当地到达时间：`scheduled_arrival_local`、`actual_arrival_local` - 飞行时长：`scheduled_duration`、`actual_duration` ### 航线视图 {#intro-subsection-5-1-11} `flights` 表包含一些冗余信息，可以从中提取不依赖具体航班日期的航线信息（航班号、出发和目的地机场、机型）。 这些信息构成了 `routes` 视图。此外，该视图还显示 `days_of_week` 数组，表示每周哪些天有航班，以及计划飞行时长 `duration`。 ### “now”函数 {#intro-subsection-5-1-12} 演示数据库包含一个数据快照，类似于在某个时刻为真实系统创建的备份。例如，如果某个航班的状态为 Departed，就表示备份时飞机正在空中飞行。 快照时间保存在 `bookings.now` 函数中。在通常需要调用 `now` 函数的场合，可以在演示查询中使用这个函数。 此外，这个函数的返回值还决定了演示数据库的版本。最新版本对应 2017 年 8 月 15 日，包含该日期的数据。 ## 安装 {#intro-section-5-2} ### 从网站安装 {#intro-subsection-5-2-1} 演示数据库提供三个版本，只在数据量上有所不同： - [edu.postgrespro.com/demo-small-en.zip]() 小型数据库，包含一个月的航班数据（21 MB，数据库大小为 280 MB）。 - [edu.postgrespro.com/demo-medium-en.zip]() 中型数据库，包含三个月的航班数据（62 MB，数据库大小为 702 MB）。 - [edu.postgrespro.com/demo-big-en.zip]() 大型数据库，包含一年的航班数据（232 MB，数据库大小为 2638 MB）。 学习编写查询时，小型数据库已经足够；但如果想研究查询优化的具体问题，请选择大型数据库，这样就能看到查询在大量数据上的表现。 这些文件包含使用 `pg_dump` 工具创建的 `demo` 数据库逻辑备份。注意，如果已有一个名为 `demo` 的数据库，它会被删除，然后从这个备份恢复。运行脚本的用户将成为该数据库的所有者。 要在 Linux 系统上安装演示数据库，请切换到 `postgres` 用户，下载相应文件。例如，下载小型数据库时执行以下操作： ```bash $ sudo su - postgres ``` ```bash $ wget https://edu.postgrespro.com/demo-small-en.zip ``` 然后运行以下命令： ```bash $ 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](/08-pgadmin/#intro-chapter-8) 页）不支持从这种备份中恢复数据库。因此，应通过点击“SQL Shell (psql)”快捷方式启动 psql，运行以下命令： ```text postgres# \i demo-small-en-20170815.sql ``` 如果找不到文件，请检查快捷方式的“起始位置”（Start in）属性；文件必须位于该目录中。 ## 示例查询 {#intro-section-5-3} ### 略谈模式 {#intro-subsection-5-3-1} 安装完成后，启动 `psql` 并连接演示数据库： ```psql postgres=# \c demo You are now connected to database "demo" as user "postgres". ``` `bookings` 模式包含所有必要的实体。连接数据库时会自动使用这个模式，因此无需显式指定： ```psql demo=# SELECT * FROM aircrafts; ``` ```text 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` 函数区分开来： ```psql demo=# SELECT bookings.now(); ``` ```text now ------------------------ 2017-08-15 18:00:00+03 (1 row) ``` 以下查询返回城市和机场： ```psql demo=# SELECT airport_code, city FROM airports LIMIT 4; ``` ```text airport_code | city --------------+--------------- YKS | Yakutsk MJZ | Mirnyj KHV | Khabarovsk PKC | Petropavlovsk (4 rows) ``` 数据库内容提供英语和俄语两个版本。将 `bookings.lang` 参数分别设置为 `en` 或 `ru`，即可在两种语言之间切换。默认选择英语，可以如下更改设置： ```psql demo=# ALTER DATABASE demo SET bookings.lang = ru; ``` ```sql ALTER DATABASE ``` 语言已在数据库级别修改，现在需要重新连接数据库。 ```psql demo=# \c ``` ```text You are now connected to database "demo" as user "postgres". ``` ```psql demo=# SELECT airport_code, city FROM airports LIMIT 4; ``` ```text 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.]() ### 简单查询 {#intro-subsection-5-3-2} 我们用这个模式讨论几个问题，由浅入深。大多数问题后面都给出了解答，但要有效地学习 SQL，不妨先自己编写查询，再看答案。 **问题。** 前天，谁乘坐了从 Moscow（SVO）飞往 Novosibirsk（OVB）的航班，座位是 1A？机票是什么时候预订的？ **解答。** “前天”应根据 `booking.now` 的值计算，而不是根据当前日期。（译注：原版正文此处写作 booking.now，下方 SQL 使用的是 bookings.now()。） ```sql 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` 表达式，找出没有登机牌的座位： ```sql 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 ); ``` 另一种方法是通过集合差运算查找空座位。不同解法会得到相同结果，但性能有时会有所不同；如果性能很重要，就需要考虑这一点。 ```sql 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; ``` **问题。** 哪些航班的延误时间最长？列出排名前十的“佼佼者”。 **解答。** 查询只能包含已经起飞的航班。 ```sql 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` 条件。 ### 聚合函数 {#intro-subsection-5-3-3} **问题。** 从 Moscow 到 St. Petersburg 的各个航班，最短飞行时长是多少？延误超过一小时的次数各有多少？ **解答。** 解决这个问题时，使用现有的 `flights_v` 视图比自行连接表更方便。只能考虑已经到达的航班。 ```sql 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; ``` **问题。** 找出每次乘机都第一个办理值机的旅客。只考虑至少乘坐过两次航班的旅客。 **解答。** 利用登机牌编号按值机顺序分配这一事实。 ```sql 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; ``` **问题。** 一次预订可以包含多少名旅客？ **解答。** 我们先统计每次预订中的旅客人数，再按人数统计预订次数。 ```sql 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; ``` ### 窗口函数 {#intro-subsection-5-3-4} **问题。** 对于每张机票，显示它包含的所有航段以及转机时间。结果仅限一周前预订的机票。 **解答。** 使用窗口函数，避免两次访问同样的数据。 ```sql 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); ``` 可以看到，航班之间的间隔时间可能长达数天：往返机票和单程机票按同样方式处理，在目的地停留的时间也被当作转机时间。“数组”一节中有一道题的解法，可以帮助你在编写查询时考虑这一点。 **问题。** 哪些名字与姓氏的组合最常见？具有这些姓名的旅客占旅客总数的比例是多少？ **解答。** 使用窗口函数计算旅客总数。 ```sql 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` 子查询上有所不同。 从这个复杂的查询可以看出，如果需要分别使用不同的值，就应避免将它们放在同一个文本字段中；用学术术语来说，这叫第一范式。 ```sql 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; ``` ### 数组 {#intro-subsection-5-3-5} **问题。** 数据库并未明确标注机票是单程还是往返。不过，可以通过比较最初的出发地和最后的目的地来判断。对于每张机票，忽略转机，显示出发机场和目的地机场，并标明是否为往返机票。 **解答。** 一种简单的解法是使用 `array_agg` 函数，将行程中的机场列表转换为数组。 我们选取数组的中间元素作为目的地机场，并假定去程和回程的停靠次数相同。 在这个示例中，`tickets` 表只扫描一次。显示机场数组是为了便于理解；数据量较大时，最好将它从查询中移除，因为额外的数据可能影响性能。 ```sql 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` 视图解决了。只需使用 `&&` 操作符检查数组是否相交，并确认交集为空。 ```sql 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); ``` ### 递归查询 {#intro-subsection-5-3-6} **问题。** 如何以最少的转机次数从 Ust-Kut（UKX）到达 Neryungri（CNN）？飞行时间是多少？ **解答。** 这个问题需要找出图中的最短路径。我们将使用递归查询来完成。 这个查询的详细分步说明已发布在 [habr.com/en/company/postgrespro/blog/490228/.]() 下面简要介绍。 查询执行过程中会构建 `hops` 数组，检查该数组即可避免无限循环。 由于查询执行的是广度优先搜索，找到的第一条路径的转机次数最少。为了避免遍历其他路径（它们可能很多，而且肯定比已找到的路径更长），这里使用了 `found` 属性。它通过 `bool_or` 窗口函数计算。 ```sql 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.]() **问题。** 从任意一个机场前往另一个机场，最多可能需要转机多少次？ **解答。** 我们可以在上一个查询的基础上求解。不过，第一次迭代现在必须包含所有可能的机场对，而不只是某一对：每个机场都要与其他所有机场配对。先求出每一对机场之间的最短路径，再从中选取最长的一条。 显然，只有航线图是连通的，才有可能这样做。我们的演示数据库满足这一条件。 ```sql 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` 子句。 ```sql 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 ``` ```sql 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; ``` 注意，这条航线的转机次数可能不是最少的。 ### 函数与扩展 {#intro-subsection-5-3-7} **问题。** 求 Kaliningrad（KGD）与 Petropavlovsk-Kamchatsky（PKC）之间的距离。 **解答。** `airports` 表中包含机场坐标。要精确计算两点之间的距离，必须考虑地球曲率。最适合完成这项任务的是 PostGIS 扩展，它可以用大地水准面来近似地球表面。 不过，这个示例用简单的球体模型就足够了。我们使用 `earthdistance`，再将结果从英里转换为千米。 ```sql CREATE EXTENSION IF NOT EXISTS cube; ``` ```sql CREATE EXTENSION IF NOT EXISTS earthdistance; ``` ```sql 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'; ``` **问题。** 画出所有机场之间的航班图。