set search_path to bookings;
-- 1 В каких городах больше одного аэропорта?
/*
* Группирую таблицу аэропортов по городу и вывожу только те, у которых количество airport_code больше 1
*/
select city "Город"
from airports a
group by city
having count(airport_code) > 1;
-- 2 В каких аэропортах есть рейсы, выполняемые самолетом с максимальной дальностью перелета?
-- - Подзапрос
/*
* Подзапрос получает код самолета с самыой большой дальностью (с помощью сортировки и ограничения вывода).
* Далее в основном запросе указывается условие соответствия самолета.
* Основной запрос получает имя аэропорта по джойну с таблицей перелётов
*/
select distinct
a.airport_name "Аэропорт"
from airports a
join flights f on a.airport_code = f.departure_airport
where f.aircraft_code = (
select a.aircraft_code
from aircrafts a
order by a."range" desc limit 1
);
-- 3 Вывести 10 рейсов с максимальным временем задержки вылета - Оператор LIMIT
/*
* Отбираю только те рейсы, которые вылетели (actual_departure заполнено)
* Задержка считается простым вычитанием.
* Наконец, сортировка по убыванию и ограничение вывода
*/
select
f.flight_id,
f.scheduled_departure,
f.actual_departure,
f.actual_departure - f.scheduled_departure "Задержка"
from flights f
where f.actual_departure is not null
order by "Задержка" desc
limit 10;
-- 4 Были ли брони, по которым не были получены посадочные талоны? - Верный тип JOIN
/*
* Left join, т.к. нужно полное множество броней.
* Джойню таблицу tickets т.к. таблица броней связывается с талонами через билет.
*/
select
case when count(b.book_ref) > 0 then 'Да'
else 'Нет'
end "Наличие броней без пт",
count(b.book_ref) "Их количество"
from bookings b
join tickets t on t.book_ref = b.book_ref
left join boarding_passes bp on bp.ticket_no = t.ticket_no
where bp.boarding_no is null;
-- 5 Найдите свободные места для каждого рейса, их % отношение к общему количеству мест в самолете.
-- Добавьте столбец с накопительным итогом - суммарное накопление количества вывезенных пассажиров из каждого аэропорта на каждый день.
-- Т.е. в этом столбце должна отражаться накопительная сумма - сколько человек уже вылетело из данного аэропорта на этом или более ранних рейсах за день.
-- - Оконная функция
-- - Подзапросы или cte
/*
* CTE boarded получает количество выданных посадочных талонов по каждому рейсу
* Ограничение actual_departure is not null для того, чтобы отслеживать уже вылетевшие рейсы
* CTE max_seats_by_aircraft получает количество мест в самолёте
* В итоговом запросе оба CTE джойнятся по aircraft_code
* Для подсчета накопительной суммы использется оконная функция c разделением по аэропорту отправления и времени вылета приведенному к формату date.
*/
with boarded as (
select
f.flight_id,
f.flight_no,
f.aircraft_code,
f.departure_airport,
f.scheduled_departure,
f.actual_departure,
count(bp.boarding_no) boarded_count
from flights f
join boarding_passes bp on bp.flight_id = f.flight_id
where f.actual_departure is not null
group by f.flight_id
),
max_seats_by_aircraft as(
select
s.aircraft_code,
count(s.seat_no) max_seats
from seats s
group by s.aircraft_code
)
select
b.flight_no,
b.departure_airport,
b.scheduled_departure,
b.actual_departure,
b.boarded_count,
m.max_seats - b.boarded_count free_seats,
round((m.max_seats - b.boarded_count) / m.max_seats :: dec, 2) * 100 free_seats_percent,
sum(b.boarded_count) over (partition by (b.departure_airport, b.actual_departure::date) order by b.actual_departure) "Накопительно пассажиров"
from boarded b
join max_seats_by_aircraft m on m.aircraft_code = b.aircraft_code;
-- 6 Найдите процентное соотношение перелетов по типам самолетов от общего количества. - Подзапрос
-- - Оператор ROUND
/*
* Используется подзапрос для получения общего числа полетов (проверяем, вылетел ли самолет при подсчете)
* В основном запросе используется группировка по полю model
*/
select
a.model "Модель самолета",
count(f.flight_id) "Количество рейсов",
round(count(f.flight_id) /
(select
count(f.flight_id)
from flights f
where f.actual_departure is not null
)::dec * 100, 4) "В процентах от общего числа"
from aircrafts a
join flights f on f.aircraft_code = a.aircraft_code
where f.actual_departure is not null
group by a.model;
-- 7 Были ли города, в которые можно добраться бизнес - классом дешевле, чем эконом-классом в рамках перелета?
-- - CTE
/*
* В CTE prices собираются стоимости билетов на рейс: максимальная для Эконома и минимальная для бизнеса.
* Затем из него отбираются эти стоимости и группируются в одну строку по каждому аэропорту - это внешний
* CTE eco_busi. Результаты фильтруются по сравнению полей b_min_amount и e_max_amount
* Далее этот CTE джойнится с таблицами рейсов и аэропортов, чтобы достать из них города отправления и прибытия.
* Судя по тому, что результат пустой, таких рейсов нет
*/
with eco_busi as (
with prices as(
select
f.flight_id,
case when tf.fare_conditions = 'Business' then min(tf.amount) end b_min_amount,
case when tf.fare_conditions = 'Economy' then max(tf.amount) end e_max_amount
from ticket_flights tf
join flights f on tf.flight_id = f.flight_id
group by
f.flight_id, tf.fare_conditions
)
select
p.flight_id,
min(p.b_min_amount),
max(p.e_max_amount)
from prices p
group by p.flight_id
having min(p.b_min_amount) < max(p.e_max_amount)
)
select
e.flight_id,
a.city depatrure_city,
a2.city arrival_city
from eco_busi e
join flights f on e.flight_id = f.flight_id
join airports a on f.departure_airport = a.airport_code
join airports a2 on f.arrival_airport = a2.airport_code
/*
* Этот вариант смотрит стоимость билета между городами без учета рейса
* CTE max_min_by_city формирует минимальную стоимость по бизнес классу и муксимальную по эконому
* с группировкой по городу отправления и прибытия и по классу билета.
* результаты его отправляются во внешний запрос, который собирает минимум и максимум по двум городам
* в одну строку. В итоговом условии выводятся только те строки, в которых min(b_min_amount) < max(e_max_amount).
* Таких строк нет, так что и в этом случае бизнес всегда дороже эконома
*/
with max_min_by_city as(
select
a.city dep_city,
a2.city arr_city,
tf.fare_conditions,
case when tf.fare_conditions = 'Business' then min(tf.amount) end b_min_amount,
case when tf.fare_conditions = 'Economy' then max(tf.amount) end e_max_amount
from flights f
join ticket_flights tf on tf.flight_id = f.flight_id
join airports a on f.departure_airport = a.airport_code
join airports a2 on f.arrival_airport = a2.airport_code
group by (1, 2), 3
)
select
dep_city "Из",
arr_city "В",
min(b_min_amount) "Минимум за бизнес",
max(e_max_amount) "Максимум за эконом"
from max_min_by_city
group by (1, 2)
having min(b_min_amount) < max(e_max_amount);
-- 8 Между какими городами нет прямых рейсов?
-- - Декартово произведение в предложении FROM
-- - Самостоятельно созданные представления
-- - Оператор EXCEPT
/*
* Создаю представление для получения городов, между которыми есть рейсы
* Два джойна в представлении для получения города отправления и города прибытия
* В основном запросе получаю декартово произведение всех городов, с условием их неравенства
* Затем из него убираю данные, которые есть в представлении.
*/
create view dep_arr_city as
select distinct
a.city departure_city,
a2.city arrival_city
from flights f
join airports a on f.departure_airport = a.airport_code
join airports a2 on f.arrival_airport = a2.airport_code;
select distinct
a.city departure_city,
a2.city arrival_city
from airports a, airports a2
where a.city != a2.city
except
select * from dep_arr_city
-- 9 Вычислите расстояние между аэропортами, связанными прямыми рейсами, сравните с допустимой максимальной дальностью перелетов
-- в самолетах, обслуживающих эти рейсы * - Оператор RADIANS или использование sind/cosd
-- - CASE
/*
* Опять два раза джойн таблицы аэропортов.
* Поле "Долетит?" заполняется по условию того, что рассчитанная дальность между городами меньше дальности самолета.
* Расстояние между городами делал по формуле из задания не особо задумываясь об этом
*/
select distinct
ad.airport_name "Из",
aa.airport_name "В",
a."range" "Дальность самолета",
round((acos(sind(ad.latitude) * sind(aa.latitude) + cosd(ad.latitude) * cosd(aa.latitude) * cosd(ad.longitude - aa.longitude)) * 6371)::dec, 2) "Расстояние",
case when
a."range" <
acos(sind(ad.latitude) * sind(aa.latitude) + cosd(ad.latitude) * cosd(aa.latitude) * cosd(ad.longitude - aa.longitude)) * 6371
then 'Нет!'
else 'Да!'
end "Долетит?"
from flights f
join airports ad on f.departure_airport = ad.airport_code
join airports aa on f.arrival_airport = aa.airport_code
join aircrafts a on a.aircraft_code = f.aircraft_code
