SQLtest Aulas de SQL

Explicação da Tarefa Mapa de Assentos da Aeronave

Objetivo Principal da Tarefa

A tarefa consiste em criar um mapa visual de assentos para um modelo específico de aeronave (neste caso, 'Boeing 777-300'). O resultado deve ser uma lista de fileiras, onde para cada fileira e classe de serviço ('Business', 'Economy', 'Comfort') é mostrado um array de assentos disponíveis.

Por exemplo, para a 1ª fileira da classe executiva, pode parecer assim:

• Fileira: 1
• Assentos: ["A", "C", "D", "F"]
• Classe: Business

Lógica da Solução (Passo a Passo)

1. Passo 1: Selecionar a Aeronave Correta.

   • Primeiramente, é necessário filtrar os dados para trabalhar apenas com os assentos pertencentes ao modelo 'Boeing 777-300'.
   • Para isso, é preciso juntar a tabela seats (assentos) com a tabela aircrafts_data (dados das aeronaves) pelo campo comum aircraft_code.
   • Em seguida, na cláusula WHERE, especificar o modelo de aeronave desejado.

2. Passo 2: Dividir o Número do Assento em Fileira e Letra.

   • O número do assento na tabela seats tem um formato como 12A, 30K, onde 12 é o número da fileira, e A é a letra do assento na fileira.
   • O número da fileira pode ser obtido removendo a parte da letra da string seat_no. Por exemplo, usando a função rtrim ou expressões regulares.
   • A letra do assento pode ser obtida pegando o último caractere da string seat_no (por exemplo, com a função right).

3. Passo 3: Agrupar os Assentos.

   • A ideia principal é agrupar todos os assentos que estão na mesma fileira e pertencem à mesma classe de serviço (fare_conditions).
   • Portanto, a cláusula GROUP BY deve incluir tanto o número da fileira calculado quanto a classe de serviço.

4. Passo 4: Agregar as Letras dos Assentos em um Array.

   • Para cada grupo (por exemplo, "fileira 10, classe econômica"), precisamos coletar todas as letras dos assentos (A, B, C, etc.) em uma única lista ou array.
   • A função de agregação json_agg (no PostgreSQL) é ideal para isso, pois cria um array JSON a partir dos valores.
   • Para garantir que os assentos no array estejam na ordem correta (A, B, C, e não C, A, B), é necessário adicionar uma ordenação dentro da função json_agg.

5. Passo 5: Ordenação Final.

   • Para que o mapa de assentos pareça lógico (fileiras indo da frente para trás da aeronave), o resultado final precisa ser ordenado pelo número da fileira.

        select 
            rtrim(seat_no, 'ABCDEFGHKJ')::int "row", 
            json_agg(right(seat_no, 1) order by right(seat_no, 1)) seats , 
            fare_conditions 
        from seats
        join aircrafts_data using(aircraft_code)
        where aircrafts_data.model->>'en' = 'Boeing 777-300'
        group by rtrim(seat_no, 'ABCDEFGHKJ'), fare_conditions
        order by "row";