Taxa de Ocupação dos Voos por Classe em SQL

Lição 12.2 · Tempo de leitura: ~7 min · Tarefas para praticar ↓

Esta lição explica a solução da tarefa SQL Ocupação dos voos por classe tarifária no banco de dados Bookings (PostgreSQL). Você vai aprender a contar todos os assentos e os assentos ocupados numa só consulta, a separar as contagens por classe com FILTER, e por que a resposta precisa de dois níveis de agregação: primeiro por voo, depois por aeroporto.

A tarefa: para cada aeroporto de partida, calcular a ocupação média da classe executiva e da classe económica nos voos que partiram efetivamente em agosto de 2017. A ocupação de um voo é a percentagem de assentos ocupados entre todos os assentos dessa classe. O resultado tem três colunas: departure_airport, average_business_occupancy e average_economy_occupancy, em percentagem arredondada a duas casas decimais.

Tente resolver a tarefa sozinho primeiro; a consulta completa está escondida no fim da lição.

Que Tabelas São Necessárias

flights: o voo, o seu departure_airport, actual_departure e a aeronave (aircraft_code).
seats: todos os assentos de cada modelo de aeronave com a sua classe (fare_conditions).
boarding_passes: os assentos efetivamente ocupados num voo (flight_id, seat_no).

O plano: para cada voo, listar todos os assentos da sua aeronave e marcar quais têm cartão de embarque.

Solução Passo a Passo

1. Todos os Assentos e os Assentos Ocupados de Cada Voo

Junte cada voo aos assentos da sua aeronave e depois acrescente os cartões de embarque com um LEFT JOIN, para que os assentos vazios fiquem no 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 exemplo, o voo 2 a partir de DME tem 14 de 28 assentos executivos e 87 de 142 assentos económicos ocupados.

COUNT(seats.seat_no) conta todos os assentos; COUNT(boarding_passes.seat_no) conta só os assentos com cartão de embarque, porque para os assentos vazios o LEFT JOIN dá NULL.
Com um JOIN interno a boarding_passes, os assentos vazios desapareceriam e cada voo pareceria 100% cheio.
O cartão de embarque tem de corresponder ao assento e ao voo: o mesmo número de assento existe em todos os voos dessa aeronave.
COUNT(...) FILTER (WHERE ...) conta apenas as linhas de uma classe. Noutros bancos de dados escreve-se o mesmo como COUNT(CASE WHEN ... THEN 1 END) (Lição 4.4).

2. Ocupação de Cada Voo

Divida os assentos ocupados por todos os assentos. Cuidado com os tipos: COUNT devolve um inteiro, e no PostgreSQL um inteiro dividido por um inteiro dá um inteiro:

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

Resultado: integer_division é 0, numeric_division é 0.25.

Por isso a solução converte as contagens de assentos para numeric. Algumas aeronaves não têm classe executiva, por exemplo o Bombardier CRJ-200 e o Cessna 208 Caravan. Para elas, o número de assentos executivos é 0, e a divisão tem de ser evitada: CASE WHEN business_seats > 0 THEN ... END devolve NULL, que o AVG ignora.

3. Média por Aeroporto

Coloque o cálculo por voo numa CTE e agregue-o de novo, agora por departure_airport:

AVG(business_occupied / business_seats) * 100 é a percentagem média de ocupação;
::numeric(5, 2) arredonda-a a duas casas decimais.

Resultado: 104 aeroportos, a começar por AAQ (82.83% na executiva, 81.54% na económica).

Média de Percentagens vs. Percentagem Global

A tarefa pede a média das taxas de ocupação por voo: cada voo tem o mesmo peso, e um avião pequeno quase vazio conta tanto como um avião grande cheio. A outra métrica possível é a ocupação global: todos os assentos ocupados no aeroporto divididos por todos os assentos, SUM(occupied) / SUM(seats), em que os aviões grandes pesam mais. Ambas são válidas, mas dão números diferentes, por isso leia o enunciado com atenção. Aqui, a agregação em dois níveis é necessária precisamente porque a média é calculada sobre os voos.

Spoiler: a consulta SQL com a solução está escondida abaixo. Clique para revelar.


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;

Principais conclusões:

Conte todos os assentos a partir de seats e os assentos ocupados a partir de boarding_passes ligada com um LEFT JOIN; uma junção interna esconderia os assentos vazios.
COUNT(column) ignora NULL, por isso conta só as linhas encontradas; FILTER (WHERE ...) separa as contagens por categoria.
A divisão inteira no PostgreSQL descarta a parte decimal: converta para numeric antes de dividir.
Ignore as classes que a aeronave não tem com CASE WHEN seats > 0; o AVG ignora NULL.
Uma média de taxas por voo exige dois níveis de agregação: uma CTE por voo e depois GROUP BY por aeroporto.

Perguntas Frequentes

Por que o meu cálculo de percentagem devolve 0 em SQL?

Porque os dois operandos são inteiros, e o PostgreSQL, o SQL Server e o SQLite fazem divisão inteira: 3 / 12 = 0. Converta um dos operandos para numeric ou decimal, ou multiplique primeiro por 100.0. O MySQL devolve um resultado decimal para / e não tem este problema.

O que faz COUNT(...) FILTER (WHERE ...)?

Conta apenas as linhas que satisfazem a condição, por isso várias contagens com condições diferentes cabem num só GROUP BY. O FILTER é suportado pelo PostgreSQL e pelo SQLite; no MySQL e no SQL Server use COUNT(CASE WHEN condition THEN 1 END) ou SUM(CASE WHEN condition THEN 1 ELSE 0 END).

Quando preciso de dois níveis de agregação?

Quando a métrica é calculada por objeto e depois tirada a média por grupos: ocupação por voo, depois a média por aeroporto. Um só GROUP BY não faz as duas coisas, por isso o primeiro nível vai para uma CTE ou uma subconsulta.