任务说明 查找航班占用率按票价
任务的主要目标
任务是计算每个服务等级（商务舱和经济舱）按出发机场划分的平均占用百分比。换句话说，您需要找出从每个机场出发的航班中，商务舱和经济舱的平均满座情况。
解决逻辑（逐步）
步骤 1：收集每个航班的数据。
- 首先，对于每个单独的航班，我们需要确定每个服务等级的两个关键指标：
- 飞机上的总座位数 (
total_seats)。 - 实际被占用的座位数 (
occupied_seats)。
- 飞机上的总座位数 (
- 为此，我们需要连接几个表：
flights- 航班的基本信息，包括flight_id和aircraft_code。seats- 每架飞机上的所有座位信息 (aircraft_code) 及其服务等级 (fare_conditions)。boarding_passes- 已发放登机牌的信息，告诉我们哪些座位在什么航班上被占用。
- 这里的关键是对
boarding_passes表使用LEFT JOIN。这使我们能够计算飞机上的所有座位，即使是没有发放登机牌的座位（即空座位）。
- 首先，对于每个单独的航班，我们需要确定每个服务等级的两个关键指标：
步骤 2：计算每个航班的占用率。
- 在这个阶段，通过按
flight_id对数据进行分组，我们可以计算每个等级的占用座位数和总座位数。 - 使用条件聚合（例如，
COUNT(...) FILTER (WHERE ...)），我们分别计算“商务舱”和“经济舱”的座位数。 - 单个航班和单个等级的占用百分比使用公式计算：
(occupied_seats / total_seats) * 100。 - 重要的是处理某架飞机没有某个等级座位的情况（例如，没有商务舱），以避免除以零的错误。
- 在这个阶段，通过按
步骤 3：按出发机场聚合。
- 上一步的结果（每个航班的占用情况）现在需要按出发机场 (
departure_airport) 进行分组。 - 使用
AVG()函数，我们找到从给定机场出发的所有航班的平均占用百分比。这将是最终结果。
- 上一步的结果（每个航班的占用情况）现在需要按出发机场 (
因此，解决方案建立在两个聚合层次上：首先按每个航班，然后按每个机场。
剧透：解决方案的 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;