Ocupación de vuelos por clase de tarifa en SQL

Lección 12.2 · Tiempo de lectura: ~7 min · Tareas de práctica ↓

Esta lección explica la solución del ejercicio SQL Ocupación de vuelos por clase de tarifa en la base de datos Bookings (PostgreSQL). Aprenderás a contar todos los asientos y los asientos ocupados en una sola consulta, a separar los conteos por clase con FILTER, y por qué la respuesta necesita dos niveles de agregación: primero por vuelo y luego por aeropuerto.

El ejercicio: para cada aeropuerto de salida, calcular la ocupación media de la clase ejecutiva y de la clase económica en los vuelos que salieron efectivamente en agosto de 2017. La ocupación de un vuelo es el porcentaje de asientos ocupados entre todos los asientos de esa clase. El resultado tiene tres columnas: departure_airport, average_business_occupancy y average_economy_occupancy, en porcentaje redondeado a dos decimales.

Primero intenta resolver el ejercicio por tu cuenta; la consulta completa está oculta al final de la lección.

Qué tablas se necesitan

flights: el vuelo, su departure_airport, actual_departure y el avión (aircraft_code).
seats: todos los asientos de cada modelo de avión con su clase (fare_conditions).
boarding_passes: los asientos realmente ocupados en un vuelo (flight_id, seat_no).

El plan: para cada vuelo, enumerar todos los asientos de su avión y marcar cuáles tienen pase de abordar.

Solución paso a paso

1. Todos los asientos y los asientos ocupados de cada vuelo

Une cada vuelo con los asientos de su avión y luego agrega los pases de abordar con un LEFT JOIN, para que los asientos vacíos se queden en el resultado:

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;

Resultado: por ejemplo, el vuelo 2 desde DME tiene ocupados 14 de 28 asientos de ejecutiva y 87 de 142 de económica.

COUNT(seats.seat_no) cuenta todos los asientos; COUNT(boarding_passes.seat_no) cuenta solo los asientos con pase de abordar, porque para los asientos vacíos el LEFT JOIN da NULL.
Con un JOIN interno a boarding_passes, los asientos vacíos desaparecerían y cada vuelo parecería lleno al 100%.
El pase de abordar debe coincidir con el asiento y con el vuelo: el mismo número de asiento existe en todos los vuelos de ese avión.
COUNT(...) FILTER (WHERE ...) cuenta solo las filas de una clase. En otras bases de datos lo mismo se escribe COUNT(CASE WHEN ... THEN 1 END) (lección 4.4).

2. Ocupación de cada vuelo

Divide los asientos ocupados entre todos los asientos. Cuidado con los tipos: COUNT devuelve un entero, y en PostgreSQL un entero dividido entre un entero da un entero:

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

Resultado: integer_division es 0 y numeric_division es 0.25.

Por eso la solución convierte los conteos de asientos a numeric. Algunos aviones no tienen clase ejecutiva, por ejemplo el Bombardier CRJ-200 y el Cessna 208 Caravan. Para ellos el número de asientos de ejecutiva es 0, y hay que saltarse la división: CASE WHEN business_seats > 0 THEN ... END devuelve NULL, que AVG ignora.

3. Promedio por aeropuerto

Pon el cálculo por vuelo en una CTE y vuelve a agregarlo, ahora por departure_airport:

AVG(business_occupied / business_seats) * 100 es el porcentaje medio de ocupación;
::numeric(5, 2) lo redondea a dos decimales.

Resultado: 104 aeropuertos, empezando por AAQ (82.83% en ejecutiva, 81.54% en económica).

Promedio de porcentajes vs. porcentaje global

El ejercicio pide el promedio de las tasas de ocupación por vuelo: cada vuelo tiene el mismo peso, y un avión pequeño casi vacío cuenta igual que uno grande lleno. La otra métrica posible es la ocupación global: todos los asientos ocupados en el aeropuerto divididos entre todos los asientos, SUM(occupied) / SUM(seats), donde los aviones grandes pesan más. Ambas son válidas, pero dan números distintos, así que lee el enunciado con atención. Aquí la agregación en dos niveles es necesaria precisamente porque el promedio se calcula sobre los vuelos.

Spoiler: la consulta SQL con la solución está oculta abajo. Haz clic para verla.


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;

Conclusiones clave:

Cuenta todos los asientos a partir de seats y los ocupados a partir de boarding_passes unida con un LEFT JOIN; una unión interna ocultaría los asientos vacíos.
COUNT(column) ignora los NULL, así que cuenta solo las filas encontradas; FILTER (WHERE ...) separa los conteos por categoría.
La división entera en PostgreSQL descarta la parte decimal: convierte a numeric antes de dividir.
Sáltate las clases que el avión no tiene con CASE WHEN seats > 0; AVG ignora los NULL.
Un promedio de tasas por vuelo necesita dos niveles de agregación: una CTE por vuelo y luego GROUP BY por aeropuerto.

Preguntas frecuentes

¿Por qué mi cálculo de porcentaje devuelve 0 en SQL?

Porque ambos operandos son enteros, y PostgreSQL, SQL Server y SQLite hacen división entera: 3 / 12 = 0. Convierte uno de los operandos a numeric o decimal, o multiplica primero por 100.0. MySQL devuelve un resultado decimal para / y no tiene este problema.

¿Qué hace COUNT(...) FILTER (WHERE ...)?

Cuenta solo las filas que cumplen la condición, así que varios conteos con condiciones distintas caben en un solo GROUP BY. FILTER lo admiten PostgreSQL y SQLite; en MySQL y SQL Server usa COUNT(CASE WHEN condition THEN 1 END) o SUM(CASE WHEN condition THEN 1 ELSE 0 END).

¿Cuándo necesito dos niveles de agregación?

Cuando la métrica se calcula por objeto y luego se promedia por grupos: ocupación por vuelo y después el promedio por aeropuerto. Un solo GROUP BY no puede hacer ambas cosas, así que el primer nivel va en una CTE o una subconsulta.