SQL 按舱位等级计算航班上座率

Q: 为什么我的 SQL 百分比计算结果是 0？

因为两个操作数都是整数，而 PostgreSQL、SQL Server 和 SQLite 执行的是整数除法：3 / 12 = 0。把其中一个操作数转换为 numeric 或 decimal，或者先乘以 100.0。MySQL 中 / 返回小数结果，不存在这个问题。

Q: COUNT(...) FILTER (WHERE ...) 有什么作用？

它只统计满足条件的行，因此可以在同一个 GROUP BY 中进行多个条件不同的计数。PostgreSQL 和 SQLite 支持 FILTER；在 MySQL 和 SQL Server 中，请使用 COUNT(CASE WHEN condition THEN 1 END) 或 SUM(CASE WHEN condition THEN 1 ELSE 0 END)。

Q: 什么时候需要两级聚合？

当指标先按每个对象计算、再按分组求平均时：先算每个航班的上座率，再算每个机场的平均值。一个 GROUP BY 无法同时完成这两步，所以第一级要放在 CTE 或子查询中。

第 12.2 课 · 阅读时间：约 7 分钟 · 练习题 ↓

本课讲解 Bookings 数据库（PostgreSQL）上的 SQL 题目按舱位等级统计航班上座率的解法。你将学习如何在一条查询中同时统计全部座位和已占用座位，如何用 FILTER 按舱位等级分别计数，以及为什么答案需要两级聚合：先按航班，再按机场。

题目：对每个出发机场，计算 2017 年 8 月实际起飞的航班中商务舱和经济舱的平均上座率。一个航班的上座率是该舱位已占用座位占全部座位的百分比。结果包含三列：departure_airport、average_business_occupancy 和 average_economy_occupancy，以百分比表示并保留两位小数。

请先尝试自己解答；完整的查询隐藏在本课末尾。

需要哪些表

flights：航班、其 departure_airport、actual_departure 以及机型（aircraft_code）。
seats：每种机型的全部座位及其舱位等级（fare_conditions）。
boarding_passes：航班上实际被占用的座位（flight_id、seat_no）。

思路是：对每个航班，列出其机型的全部座位，并标记哪些座位有登机牌。

分步解答

1. 每个航班的全部座位和已占用座位

把每个航班与其机型的座位连接起来，再用 LEFT JOIN 连接登机牌，这样空座位也会保留在结果中：

SELECT
    flights.flight_id,
    flights.departure_airport,
    COUNT(boarding_passes.seat_no) FILTER (WHERE seats.fare_conditions = 'Business') AS business_occupied,
    COUNT(seats.seat_no) FILTER (WHERE seats.fare_conditions = 'Business') AS business_seats,
    COUNT(boarding_passes.seat_no) FILTER (WHERE seats.fare_conditions = 'Economy') AS economy_occupied,
    COUNT(seats.seat_no) FILTER (WHERE seats.fare_conditions = 'Economy') AS economy_seats
FROM flights
JOIN seats USING (aircraft_code)
LEFT JOIN boarding_passes
    ON boarding_passes.seat_no = seats.seat_no
   AND boarding_passes.flight_id = flights.flight_id
WHERE flights.actual_departure BETWEEN '2017-08-01' AND '2017-09-01'
GROUP BY flights.flight_id, flights.departure_airport
ORDER BY flights.flight_id
LIMIT 3;

结果：例如，从 DME 出发的 2 号航班，商务舱 28 个座位中有 14 个被占用，经济舱 142 个座位中有 87 个被占用。

COUNT(seats.seat_no) 统计全部座位；COUNT(boarding_passes.seat_no) 只统计有登机牌的座位，因为对于空座位，LEFT JOIN 得到的是 NULL。
如果用内连接 JOIN 连接 boarding_passes，空座位就会消失，每个航班看起来都是 100% 满座。
登机牌必须同时匹配座位和航班：同一个座位号在该机型的每个航班上都存在。
COUNT(...) FILTER (WHERE ...) 只统计某一舱位的行。在其他数据库中，同样的逻辑写作 COUNT(CASE WHEN ... THEN 1 END)（第 4.4 课）。

2. 每个航班的上座率

用已占用座位数除以全部座位数。注意类型：COUNT 返回整数，而在 PostgreSQL 中，整数除以整数的结果仍是整数：

SELECT 3 / 12 AS integer_division, 3::numeric / 12 AS numeric_division;

结果：integer_division 为 0，numeric_division 为 0.25。

因此，解答中把座位数转换为 numeric。有些机型根本没有商务舱，例如 Bombardier CRJ-200 和 Cessna 208 Caravan。它们的商务舱座位数为 0，必须跳过除法：CASE WHEN business_seats > 0 THEN ... END 返回 NULL，而 AVG 会忽略 NULL。

3. 按机场求平均值

把按航班的计算放进 CTE，再按 departure_airport 进行第二次聚合：

AVG(business_occupied / business_seats) * 100 是平均上座率百分比；
::numeric(5, 2) 将其保留两位小数。

结果：104 个机场，从 AAQ 开始（商务舱 82.83%，经济舱 81.54%）。

百分比的平均值与总体百分比

题目要求的是各航班上座率的平均值：每个航班权重相同，一架几乎空着的小飞机与一架满座的大飞机分量一样。另一种可能的指标是总体上座率：机场所有已占用座位除以所有座位，即 SUM(occupied) / SUM(seats)，其中大飞机的权重更大。两种指标都合理，但结果不同，所以请仔细阅读题目。这里之所以需要两级聚合，正是因为平均值是按航班计算的。

剧透：解答的 SQL 查询隐藏在下方，点击展开。


with occupancy as (
    select
        flights.flight_id, departure_airport,
        count(boarding_passes.seat_no) filter (where seats.fare_conditions = 'Business') business_occupancy,
        (count(seats.seat_no) filter (where seats.fare_conditions = 'Business'))::numeric business_seats,
        count(boarding_passes.seat_no) filter (where seats.fare_conditions = 'Economy') economy_occupancy,
        (count(seats.seat_no) filter (where seats.fare_conditions = 'Economy'))::numeric economy_seats
    from flights
    join seats using (aircraft_code)
    left join boarding_passes on
        boarding_passes.seat_no = seats.seat_no and
        boarding_passes.flight_id = flights.flight_id
    where flights.actual_departure between '2017-08-01' and '2017-09-01'
    group by flights.flight_id, departure_airport
)
select
    departure_airport,
    (avg(case when business_seats > 0 then business_occupancy / business_seats end) * 100)::numeric(5, 2) average_business_occupancy,
    (avg(case when economy_seats > 0 then economy_occupancy / economy_seats end) * 100)::numeric(5, 2) average_economy_occupancy
from occupancy
group by departure_airport
order by departure_airport;

要点：

从 seats 统计全部座位，从通过 LEFT JOIN 连接的 boarding_passes 统计已占用座位；内连接会把空座位隐藏掉。
COUNT(column) 会跳过 NULL，因此只统计匹配到的行；FILTER (WHERE ...) 按类别拆分计数。
PostgreSQL 中的整数除法会舍弃小数部分：除法之前先转换为 numeric。
用 CASE WHEN seats > 0 跳过机型中不存在的舱位；AVG 会忽略 NULL。
按航班计算比率后再求平均，需要两级聚合：先用 CTE 按航班汇总，再按机场 GROUP BY。

常见问题

为什么我的 SQL 百分比计算结果是 0？

因为两个操作数都是整数，而 PostgreSQL、SQL Server 和 SQLite 执行的是整数除法：3 / 12 = 0。把其中一个操作数转换为 numeric 或 decimal，或者先乘以 100.0。MySQL 中 / 返回小数结果，不存在这个问题。

COUNT(...) FILTER (WHERE ...) 有什么作用？

它只统计满足条件的行，因此可以在同一个 GROUP BY 中进行多个条件不同的计数。PostgreSQL 和 SQLite 支持 FILTER；在 MySQL 和 SQL Server 中，请使用 COUNT(CASE WHEN condition THEN 1 END) 或 SUM(CASE WHEN condition THEN 1 ELSE 0 END)。

什么时候需要两级聚合？

当指标先按每个对象计算、再按分组求平均时：先算每个航班的上座率，再算每个机场的平均值。一个 GROUP BY 无法同时完成这两步，所以第一级要放在 CTE 或子查询中。