https://pastein.ru/t/bzB
Загрузка данных
/*
ШАБЛОНЫ КОДА ДЛЯ ПРАКТИКИ НА ЭКЗАМЕНЕ
Как пользоваться этим файлом:
1. Сначала прочитай условие.
2. Найди в условии ключевые слова:
- CTE
- подзапрос
- оконные функции
- функция
- процедура
- триггер
3. Найди ниже шаблон с таким же типом.
4. В каждом шаблоне есть блок "КАК ПОНЯТЬ ИЗ УСЛОВИЯ".
5. В каждом шаблоне есть блок "ЧТО МЕНЯТЬ В КОДЕ".
6. Меняй только строки, которые подписаны как ZAMENIT.
Важно:
Если запрос вернул 0 строк - это может быть нормально.
Например, в базе может не быть рейсов за нужную дату или пассажиров под условие.
Как читать SQL в этом файле:
SELECT - какие столбцы показать в результате.
SELECT * - показать все столбцы. Это удобно для проверки, но в чистовом ответе лучше выводить только нужные поля.
FROM - из какой таблицы брать строки.
WHERE - какие строки оставить до группировки.
JOIN - как соединить строки из разных таблиц.
ON - по какому условию соединять таблицы.
GROUP BY - как разбить строки на группы.
HAVING - какие группы оставить после GROUP BY.
ORDER BY - как отсортировать результат.
LIMIT - сколько строк оставить в конце.
AS - дать столбцу или таблице короткое имя.
Псевдонимы:
flights f означает: таблица flights дальше называется коротко f.
Это нужно, чтобы писать f.flight_id и не путать одинаковые поля из разных таблиц.
CTE:
WITH name AS (...) - это общее табличное выражение.
Проще: временный результат внутри одного запроса.
Сначала SQL получает строки внутри скобок, дает им имя name, а потом ниже можно писать FROM name.
CTE удобно, когда задачу проще разбить на шаги.
Подзапрос:
SELECT внутри другого SELECT называется подзапросом.
Он может возвращать одно значение, список значений или временную таблицу.
Оконная функция:
Функция с OVER (...).
Она считает значение по группе строк, но не уменьшает количество строк.
GROUP BY сворачивает строки, а оконная функция оставляет строки как были.
Триггер:
Триггер - это код, который автоматически запускается при INSERT, UPDATE или DELETE.
NEW - новая строка при INSERT или UPDATE.
OLD - старая строка при UPDATE или DELETE.
Подробная механика, чтобы не путаться на экзамене:
1. Порядок, в котором логически работает SELECT:
FROM - сначала выбирается таблица или несколько таблиц.
JOIN ON - потом таблицы соединяются по условию.
WHERE - потом отбрасываются лишние строки.
GROUP BY - потом оставшиеся строки собираются в группы.
HAVING - потом отбрасываются лишние группы.
SELECT - потом считаются и выводятся выбранные столбцы.
ORDER BY - потом результат сортируется.
LIMIT - потом оставляется нужное количество строк.
Важно:
В коде SELECT написан первым, но логически он выполняется не первым.
Поэтому в WHERE нельзя использовать псевдоним из SELECT.
Если нужно фильтровать по вычисленному столбцу, лучше вынести вычисление в CTE или подзапрос.
2. Когда писать SELECT *
SELECT * показывает все столбцы.
Это удобно:
- когда изучаешь таблицу
- когда делаешь проверочный SELECT перед UPDATE или DELETE
- когда читаешь уже подготовленный CTE, где ты сам выбрал только нужные поля
- когда функция или представление уже возвращает ровно нужные столбцы
В чистовом ответе лучше не писать SELECT * от большой таблицы, если задание просит конкретные поля.
3. Что значит FROM table
FROM указывает источник строк.
Если написано FROM flights, то PostgreSQL берет строки из flights.
Если написано FROM flights f, то f - короткое имя таблицы.
4. Что значит JOIN
JOIN соединяет строки из двух таблиц.
PostgreSQL берет строку из одной таблицы и ищет подходящие строки во второй таблице по ON.
Если нашел несколько подходящих строк, строка из первой таблицы размножится.
5. JOIN и LEFT JOIN
JOIN показывает только строки, где связь нашлась в обеих таблицах.
LEFT JOIN сохраняет все строки из левой таблицы, даже если справа ничего не нашлось.
6. WHERE, GROUP BY, HAVING
WHERE фильтрует обычные строки до группировки.
GROUP BY собирает строки в группы.
HAVING фильтрует уже готовые группы после count, sum, avg, max или min.
7. EXPLAIN
EXPLAIN показывает план, но не выполняет запрос.
EXPLAIN ANALYZE выполняет запрос и показывает реальное время.
Seq Scan - последовательное чтение таблицы.
Index Scan - чтение через индекс.
Index Only Scan - чтение только из индекса.
Bitmap Index Scan + Bitmap Heap Scan - сначала индекс находит строки, потом таблица читается пачками.
Hash Join - соединение через хеш-таблицу.
Nested Loop - вложенный цикл.
Merge Join - соединение двух отсортированных потоков.
8. Если что-то не работает
relation does not exist - таблица или представление называется иначе.
column does not exist - поле называется иначе или забыт псевдоним таблицы.
Если airports не работает, попробуй airports_data.
Если aircrafts не работает, попробуй aircrafts_data.
Если city, airport_name или model выглядят как JSON, используй ->> 'ru'.
*/
/* ============================================================
1. CTE: НАЙТИ МАКСИМУМ ДЛЯ КАЖДОЙ ГРУППЫ
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "для каждого аэропорта найти рейс с максимальной задержкой"
- "для каждого месяца найти рейс с максимальным количеством пассажиров"
- "для каждого самолета найти максимальную продолжительность"
Значит:
- "для каждого ..." - это группа
- "максимальной ..." - это значение, по которому ищем максимум
ПРИМЕР РАЗБОРА:
Условие: найти все рейсы с максимальной задержкой для каждого аэропорта отправления.
Тогда:
- группа - аэропорт отправления - f.departure_airport
- значение - задержка - f.actual_departure - f.scheduled_departure
- что выводим - рейсы
ЧТО МЕНЯТЬ В КОДЕ:
1. в SELECT base замени group_col на нужную группу
2. в SELECT base замени value_col на нужное значение
3. в FROM и JOIN добавь нужные таблицы, если они нужны
4. в WHERE добавь условия из задания
============================================================ */
WITH base AS (
SELECT
f.departure_airport AS group_col, -- ZAMENIT: группа. пример: f.departure_airport, f.aircraft_code, date_trunc('month', f.scheduled_departure)
f.flight_id,
f.flight_no,
f.actual_departure - f.scheduled_departure AS value_col -- ZAMENIT: значение для максимума. пример: задержка, количество пассажиров, выручка
FROM flights f -- ZAMENIT: основная таблица
WHERE f.actual_departure IS NOT NULL -- ZAMENIT: условие из задания
),
mx AS (
SELECT group_col,
max(value_col) AS max_value
FROM base
GROUP BY group_col
)
SELECT b.*
FROM base b
JOIN mx m
ON m.group_col = b.group_col
AND m.max_value = b.value_col;
/*
КОММЕНТАРИЙ К КОДУ 1
WITH base AS (...) создает CTE. CTE - это временный результат внутри одного SQL-запроса. Он не создает постоянную таблицу в базе, а просто дает имя промежуточному SELECT. Здесь base нужен, чтобы сначала подготовить данные: группу, id рейса и значение для максимума.
SELECT внутри base выбирает нужные поля. Мы не пишем SELECT *, потому что для поиска максимума нужны только конкретные поля: группа, flight_id, flight_no и значение value_col. Так запрос понятнее.
f.departure_airport AS group_col - это поле группировки. В примере группа - аэропорт отправления. Если в условии написано "для каждого самолета", сюда надо поставить f.aircraft_code.
f.flight_id и f.flight_no нужны, чтобы в ответе было понятно, какой именно рейс найден.
f.actual_departure - f.scheduled_departure AS value_col считает задержку. Это значение, по которому ищется максимум.
FROM flights f означает, что основная таблица - flights. Буква f - короткий псевдоним, чтобы дальше писать f.flight_id, а не flights.flight_id.
WHERE f.actual_departure IS NOT NULL убирает рейсы, где фактического вылета нет. Без этого задержка могла бы быть NULL.
CTE mx группирует base по group_col и считает max(value_col). То есть для каждой группы находит максимальное значение.
GROUP BY group_col нужен, потому что max считается отдельно для каждой группы. Без GROUP BY max нашел бы один максимум по всей таблице, а не максимум внутри каждой группы.
SELECT b.* в конце выводит все столбцы из base. Здесь * нормален, потому что base уже маленький и специально собранный: там только нужные столбцы. Это не то же самое, что SELECT * FROM flights.
JOIN mx m соединяет исходные строки с максимумами. Так мы получаем не просто число максимума, а сами рейсы, у которых значение равно максимуму.
Механика JOIN здесь такая: SQL берет строку из base b и ищет строку из mx m, где совпадает группа и значение. Если максимум одинаковый у нескольких рейсов, JOIN вернет все такие рейсы.
Почему тут CTE удобен: без WITH пришлось бы либо повторять один и тот же подзапрос, либо писать более сложный вложенный SELECT. WITH разбивает задачу на понятные шаги.
Условие m.group_col = b.group_col нужно, чтобы сравнивать максимум внутри своей группы.
Условие m.max_value = b.value_col оставляет только строки с максимальным значением.
*/
/* ============================================================
2. CTE: НАЙТИ ТОП ПО КОЛИЧЕСТВУ ПАССАЖИРОВ
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "рейсы с максимальным количеством пассажиров для каждого месяца"
- "рейсы, где пассажиров больше всего"
Значит:
- нужны flights
- нужны boarding_passes
- количество пассажиров считается через count(bp.ticket_no)
ЧТО МЕНЯТЬ В КОДЕ:
1. если нужно "для каждого месяца" - оставь month
2. если нужно "для каждого аэропорта" - замени month на f.departure_airport или f.arrival_airport
3. если нужно не максимум, а минимум - поменяй ORDER BY passenger_count DESC на ASC
============================================================ */
WITH counts AS (
SELECT
date_trunc('month', f.scheduled_departure)::date AS group_col, -- ZAMENIT: группа. месяц, аэропорт, самолет
f.flight_id,
f.flight_no,
count(bp.ticket_no) AS passenger_count
FROM flights f
LEFT JOIN boarding_passes bp
ON bp.flight_id = f.flight_id
GROUP BY date_trunc('month', f.scheduled_departure)::date, f.flight_id, f.flight_no
),
ranked AS (
SELECT *,
rank() OVER (
PARTITION BY group_col
ORDER BY passenger_count DESC
) AS rnk
FROM counts
)
SELECT *
FROM ranked
WHERE rnk = 1;
/*
КОММЕНТАРИЙ К КОДУ 2
WITH counts AS (...) создает первый CTE. CTE работает как именованный промежуточный SELECT: сначала считаем количество пассажиров по рейсам, потом используем этот результат ниже как обычную таблицу.
date_trunc('month', f.scheduled_departure)::date AS group_col превращает дату рейса в месяц. Это нужно для задач "для каждого месяца".
f.flight_id и f.flight_no выводятся, чтобы понимать, какой рейс попал в результат.
count(bp.ticket_no) AS passenger_count считает количество посадочных талонов. В этой базе посадочный талон означает, что пассажир реально связан с рейсом.
FROM flights f берет список рейсов.
LEFT JOIN boarding_passes bp нужен, чтобы рейсы без посадочных талонов тоже не потерялись. Если использовать обычный JOIN, рейсы без пассажиров исчезнут.
ON bp.flight_id = f.flight_id связывает посадочные талоны с конкретным рейсом.
GROUP BY нужен, потому что count считается по каждому рейсу и месяцу. Механика такая: SQL собирает строки с одинаковыми значениями GROUP BY в одну группу, а потом count считает строки внутри этой группы.
WITH ranked AS (...) добавляет ранг к уже посчитанным строкам.
rank() OVER (...) - это оконная функция. Она считает место рейса внутри группы, но не сворачивает строки как GROUP BY. Если несколько рейсов имеют одинаковое количество пассажиров, они получат одинаковый ранг.
PARTITION BY group_col означает "считать ранг отдельно внутри каждого месяца".
Механика PARTITION BY: SQL делит строки ranked на отдельные группы по group_col. Внутри каждой группы rank начинается заново с 1.
ORDER BY passenger_count DESC внутри OVER означает, что первое место получает рейс с самым большим количеством пассажиров.
WHERE rnk = 1 выполняется уже после того, как ранг посчитан в CTE ranked. Поэтому фильтр по rnk вынесен во внешний SELECT.
ORDER BY passenger_count DESC означает, что большее количество пассажиров получает первое место.
SELECT * FROM ranked WHERE rnk = 1 выводит все столбцы из ranked только для победителей. Здесь SELECT * удобен, потому что ranked уже содержит только подготовленные полезные поля и rnk.
*/
/* ============================================================
3. ПОДЗАПРОС: НАЙТИ ПАССАЖИРОВ ПО УСЛОВИЮ НА РЕЙС
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "найти пассажиров, которые летали на рейсах ..."
- "пассажиры, летавшие более чем ..."
Значит:
- снаружи берем passengers из tickets
- внутри подзапроса проверяем рейсы через ticket_flights и flights
ЧТО МЕНЯТЬ В КОДЕ:
1. меняй только условие в нижнем WHERE
2. если нужно не задержка, а статус - ставь f.status = 'Cancelled'
3. если нужно город или аэропорт - добавь JOIN airports
============================================================ */
SELECT DISTINCT t.passenger_id,
t.passenger_name
FROM tickets t
WHERE t.ticket_no IN (
SELECT tf.ticket_no
FROM ticket_flights tf
JOIN flights f
ON f.flight_id = tf.flight_id
WHERE f.actual_departure IS NOT NULL
AND f.actual_departure - f.scheduled_departure > interval '2 hours' -- ZAMENIT: условие из задания
);
/*
КОММЕНТАРИЙ К КОДУ 3
Внешний SELECT выбирает пассажиров из tickets.
SELECT DISTINCT нужен, потому что один пассажир может иметь несколько билетов и несколько перелетов. DISTINCT убирает повторы.
t.passenger_id - идентификатор пассажира, t.passenger_name - имя пассажира.
FROM tickets t означает, что начинаем с таблицы билетов. Именно там есть данные пассажира.
WHERE t.ticket_no IN (...) проверяет, входит ли билет пассажира в список билетов, найденных подзапросом. Механика такая: подзапрос внутри скобок возвращает список ticket_no, а внешний запрос оставляет только tickets с такими ticket_no.
Подзапрос внутри IN ищет ticket_no из ticket_flights. Таблица ticket_flights связывает билет с конкретным рейсом.
JOIN flights f ON f.flight_id = tf.flight_id нужен, чтобы проверить условие по рейсу: задержка, статус, аэропорт и т.д.
WHERE f.actual_departure IS NOT NULL убирает рейсы без фактического вылета.
f.actual_departure - f.scheduled_departure > interval '2 hours' - условие задержки больше 2 часов. Это место чаще всего меняется под задание.
Почему не SELECT *: нам нужны только passenger_id и passenger_name. Если написать *, выведется много лишних полей из tickets.
Механика IN: внутренний SELECT возвращает список ticket_no. Внешний SELECT проходит по tickets и оставляет только те строки, где t.ticket_no есть в этом списке.
Если подзапрос вернул пустой список, внешний запрос тоже вернет пусто. Это не ошибка, просто подходящих билетов нет.
*/
/* ============================================================
4. ПОДЗАПРОС: ЗНАЧЕНИЕ БОЛЬШЕ СРЕДНЕГО
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "больше среднего"
- "меньше минимального"
- "превышает среднее значение"
Значит:
- нужен подзапрос с avg, min или max
ПРИМЕР:
рейсы, длительность которых больше средней длительности за месяц.
ЧТО МЕНЯТЬ В КОДЕ:
1. duration - что сравниваем
2. avg(duration) - с чем сравниваем
3. условие месяца - если оно есть в задании
============================================================ */
WITH durations AS (
SELECT
f.flight_id,
f.flight_no,
f.scheduled_departure,
f.scheduled_arrival - f.scheduled_departure AS duration
FROM flights f
)
SELECT d.*
FROM durations d
WHERE d.duration > (
SELECT avg(d2.duration)
FROM durations d2
WHERE date_trunc('month', d2.scheduled_departure) = date_trunc('month', d.scheduled_departure)
);
/*
КОММЕНТАРИЙ К КОДУ 4
WITH durations AS (...) заранее считает продолжительность каждого рейса. Это CTE: временный результат внутри одного запроса. Он нужен, чтобы не повторять выражение scheduled_arrival - scheduled_departure много раз.
f.scheduled_arrival - f.scheduled_departure AS duration - длительность рейса по расписанию.
FROM flights f нужен, потому что даты вылета и прилета хранятся в flights.
Основной SELECT берет строки из durations.
WHERE d.duration > (...) оставляет только те рейсы, где длительность больше значения из подзапроса.
Подзапрос SELECT avg(d2.duration) считает среднюю длительность.
FROM durations d2 использует тот же подготовленный набор данных, но с другим псевдонимом.
WHERE date_trunc('month', d2.scheduled_departure) = date_trunc('month', d.scheduled_departure) делает подзапрос коррелированным: среднее считается для того же месяца, что и текущий рейс d. Коррелированный подзапрос зависит от строки внешнего запроса, поэтому логически он проверяется для каждой строки d отдельно.
Механика коррелированного подзапроса: внешний запрос берет одну строку d, потом внутренний запрос считает avg только для месяца этой строки. Потом SQL сравнивает d.duration с этим avg. Затем переходит к следующей строке d и повторяет проверку.
Здесь SELECT d.* удобен, потому что durations содержит только подготовленные поля: flight_id, flight_no, scheduled_departure и duration.
*/
/* ============================================================
5. ОКОННАЯ ФУНКЦИЯ: RANK И DENSE_RANK
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "ранжировать"
- "ранг"
- "плотный и неплотный ранг"
Значит:
- rank() - обычный ранг с пропусками
- dense_rank() - плотный ранг без пропусков
ПРИМЕР:
плотный и неплотный ранг самолетов по убыванию дальности.
ЧТО МЕНЯТЬ В КОДЕ:
1. FROM aircrafts ac - если ранжируем самолеты
2. ORDER BY ac.range DESC - если ранжируем по дальности
3. если ранжируем пассажиров по сумме - сначала сделай CTE с суммой
============================================================ */
SELECT ac.aircraft_code,
ac.model,
ac.range,
rank() OVER (ORDER BY ac.range DESC) AS rnk,
dense_rank() OVER (ORDER BY ac.range DESC) AS dense_rnk
FROM aircrafts ac;
/*
КОММЕНТАРИЙ К КОДУ 5
Этот запрос показывает обычный и плотный ранг.
SELECT ac.aircraft_code, ac.model, ac.range выводит код самолета, модель и дальность.
rank() OVER (ORDER BY ac.range DESC) AS rnk считает неплотный ранг по убыванию дальности. Если два самолета делят 1 место, следующий может получить 3 место.
dense_rank() OVER (ORDER BY ac.range DESC) AS dense_rnk считает плотный ранг. Если два самолета делят 1 место, следующий получит 2 место.
OVER означает, что функция оконная. Механика такая: SQL берет текущий набор строк, задает для каждой строки окно и считает значение внутри этого окна. Строки не сворачиваются, а к каждой строке добавляется новое вычисленное значение.
Чем rank отличается от GROUP BY: GROUP BY сделал бы одну строку на группу, а rank оставляет каждый самолет отдельной строкой и просто добавляет его место.
ORDER BY ac.range DESC задает порядок: сначала самые дальние самолеты.
FROM aircrafts ac берет данные из таблицы или представления самолетов.
Почему не SELECT *: для ответа нужны только код, модель, дальность и ранги. Лишние поля лучше не выводить.
*/
/* ============================================================
6. ОКОННАЯ ФУНКЦИЯ: ПЕРВОЕ МЕСТО В КАЖДОЙ ГРУППЕ
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "максимальный для каждого ..."
- "аэропорты с максимальным количеством рейсов за каждый месяц"
- "рейсы с максимальной продолжительностью для каждого самолета"
Значит:
- PARTITION BY - это "для каждого"
- ORDER BY ... DESC - это "максимальный"
- WHERE rnk = 1 - оставить только победителей
ЧТО МЕНЯТЬ В КОДЕ:
1. group_col - то, после слов "для каждого"
2. value_col - то, по чему ищем максимум
============================================================ */
WITH base AS (
SELECT
f.aircraft_code AS group_col, -- ZAMENIT: для каждого самолета
f.flight_id,
f.flight_no,
f.scheduled_arrival - f.scheduled_departure AS value_col -- ZAMENIT: продолжительность
FROM flights f
),
ranked AS (
SELECT *,
rank() OVER (
PARTITION BY group_col
ORDER BY value_col DESC
) AS rnk
FROM base
)
SELECT *
FROM ranked
WHERE rnk = 1;
/*
КОММЕНТАРИЙ К КОДУ 6
WITH base AS (...) готовит данные для ранжирования. Это CTE: сначала SQL получает промежуточный результат base, потом следующий CTE ranked читает его как обычную таблицу.
group_col - группа, внутри которой ищем первое место. В примере это f.aircraft_code, то есть для каждого самолета.
value_col - показатель, по которому выбираем максимум. В примере это длительность рейса.
WITH ranked AS (...) добавляет ранг к каждой строке из base.
rank() OVER (...) считает место строки внутри своей группы. PARTITION BY задает группы, ORDER BY задает порядок внутри группы, а rank выдает номер места.
PARTITION BY group_col означает, что ранжирование идет отдельно для каждой группы.
ORDER BY value_col DESC означает, что максимальное значение получает rnk = 1.
SELECT * FROM ranked WHERE rnk = 1 выводит все строки, занявшие первое место. Если максимум одинаковый у нескольких строк, rank оставит их все.
Почему фильтр по rnk делается снаружи: rnk создается в SELECT внутри CTE ranked. На этом же уровне WHERE еще не может нормально использовать этот псевдоним, поэтому делаем следующий SELECT из ranked.
SELECT * здесь допустим, потому что ranked уже содержит только поля, которые мы специально подготовили в base, плюс rnk.
*/
/* ============================================================
7. ОКОННАЯ ФУНКЦИЯ: НАКОПИТЕЛЬНАЯ СУММА
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "накопленная выручка"
- "накопленное количество"
- "с нарастающим итогом"
Значит:
- сначала считаем сумму по дню или группе
- потом используем sum(...) OVER (PARTITION BY ... ORDER BY ...)
ЧТО МЕНЯТЬ В КОДЕ:
1. amount - что накапливаем
2. PARTITION BY - внутри чего накапливаем
3. ORDER BY day - по чему идет порядок накопления
============================================================ */
WITH day_income AS (
SELECT
f.departure_airport,
f.arrival_airport,
f.scheduled_departure::date AS day,
sum(tf.amount) AS amount
FROM flights f
JOIN ticket_flights tf
ON tf.flight_id = f.flight_id
GROUP BY f.departure_airport, f.arrival_airport, f.scheduled_departure::date
)
SELECT *,
sum(amount) OVER (
PARTITION BY departure_airport, arrival_airport
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_amount
FROM day_income
ORDER BY departure_airport, arrival_airport, day;
/*
КОММЕНТАРИЙ К КОДУ 7
WITH day_income AS (...) сначала считает выручку по дням и направлениям. Это CTE, который превращает много строк ticket_flights в более короткий промежуточный результат: одна строка на день и направление.
f.departure_airport и f.arrival_airport задают направление.
f.scheduled_departure::date AS day превращает дату-время в обычную дату.
sum(tf.amount) AS amount считает выручку за день по направлению.
JOIN ticket_flights tf ON tf.flight_id = f.flight_id нужен, потому что стоимость хранится в ticket_flights.amount, а направление в flights.
GROUP BY нужен, потому что sum считается по каждому направлению и дню.
Механика GROUP BY: все строки с одинаковыми departure_airport, arrival_airport и днем вылета собираются в одну группу. sum(tf.amount) считает сумму только внутри этой группы.
После этого оконная sum(amount) OVER уже работает не по исходным билетам, а по готовым дневным суммам из CTE day_income.
Во внешнем SELECT используется оконная сумма.
sum(amount) OVER (...) считает накопительную сумму. В отличие от обычного sum с GROUP BY, оконный sum не объединяет строки, а показывает сумму рядом с каждой строкой.
PARTITION BY departure_airport, arrival_airport означает, что накопление идет отдельно для каждого направления.
ORDER BY day задает порядок накопления по дням.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW означает: брать все строки от начала группы до текущей строки.
ORDER BY в конце просто сортирует итоговый вывод, чтобы его было удобно читать.
*/
/* ============================================================
8. ОКОННАЯ ФУНКЦИЯ: ДОЛЯ И ОТКЛОНЕНИЕ
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "доля в общей выручке"
- "отклонение от среднего"
- "отклонение от максимального"
- "отклонение от минимального"
Значит:
- сначала считаем показатель
- потом сравниваем его с avg(...) OVER (), max(...) OVER (), min(...) OVER ()
ЧТО МЕНЯТЬ В КОДЕ:
1. income - показатель
2. GROUP BY - по чему считаем показатель
============================================================ */
WITH route_income AS (
SELECT
f.departure_airport,
f.arrival_airport,
sum(tf.amount) AS income
FROM flights f
JOIN ticket_flights tf
ON tf.flight_id = f.flight_id
GROUP BY f.departure_airport, f.arrival_airport
)
SELECT *,
income - avg(income) OVER () AS diff_from_avg,
income - max(income) OVER () AS diff_from_max,
income - min(income) OVER () AS diff_from_min,
income * 100.0 / sum(income) OVER () AS percent_from_total
FROM route_income
ORDER BY income DESC;
/*
КОММЕНТАРИЙ К КОДУ 8
WITH route_income AS (...) сначала считает выручку по направлениям. Это CTE: сначала получаем готовую таблицу направлений и выручки, потом уже считаем отклонения и проценты.
sum(tf.amount) AS income - сумма стоимости всех перелетов по направлению.
GROUP BY f.departure_airport, f.arrival_airport нужен, чтобы получить одну строку на одно направление.
Во внешнем SELECT считаются сравнения через оконные функции.
avg(income) OVER () считает среднюю выручку по всем направлениям. Пустые скобки OVER () означают окно по всему результату.
income - avg(income) OVER () показывает отклонение текущего направления от среднего.
income - max(income) OVER () показывает отклонение от максимума.
income - min(income) OVER () показывает отклонение от минимума.
income * 100.0 / sum(income) OVER () считает долю направления в общей выручке. 100.0 нужно, чтобы получить дробное число, а не целочисленное деление.
Механика OVER () без PARTITION BY: окно включает все строки результата route_income. Поэтому avg, max, min и sum считаются по всем направлениям сразу.
SELECT * здесь выводит поля из route_income: departure_airport, arrival_airport, income. Плюс добавляются вычисленные столбцы.
*/
/* ============================================================
9. ФУНКЦИЯ PL/PGSQL: ВЕРНУТЬ ТАБЛИЦУ ПО ПАРАМЕТРАМ
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "напишите функцию для получения ..."
- "параметр передается в функцию"
- "выводит таблицу ..."
Значит:
- нужна CREATE FUNCTION
- если выводится несколько строк и столбцов - RETURNS TABLE
- внутри пишем RETURN QUERY SELECT ...
ПРИМЕР:
получить бронирования пассажира за период.
ЧТО МЕНЯТЬ В КОДЕ:
1. параметры функции
2. список RETURNS TABLE
3. SELECT внутри RETURN QUERY
4. WHERE под условие задания
============================================================ */
CREATE OR REPLACE FUNCTION get_bookings_by_passenger_period(
p_passenger_id varchar,
p_date_from timestamptz,
p_date_to timestamptz
)
RETURNS TABLE(
book_ref bpchar,
book_date timestamptz,
total_amount numeric
) AS $$
BEGIN
RETURN QUERY
SELECT DISTINCT b.book_ref,
b.book_date,
b.total_amount
FROM bookings b
JOIN tickets t
ON t.book_ref = b.book_ref
WHERE t.passenger_id = p_passenger_id
AND b.book_date >= p_date_from
AND b.book_date < p_date_to;
END;
$$ LANGUAGE plpgsql;
SELECT *
FROM get_bookings_by_passenger_period(
'8149 604011',
'2017-08-01',
'2017-09-01'
);
/*
КОММЕНТАРИЙ К КОДУ 9
CREATE OR REPLACE FUNCTION создает функцию или заменяет ее, если она уже существует.
get_bookings_by_passenger_period - имя функции. Его можно менять под задачу.
p_passenger_id, p_date_from, p_date_to - параметры функции. Они передаются при вызове.
RETURNS TABLE(...) означает, что функция возвращает таблицу с указанными столбцами и типами. Это как обещание функции: какие колонки и каких типов она отдаст при вызове.
AS $$ ... $$ - тело функции.
BEGIN и END ограничивают блок кода PL/pgSQL.
RETURN QUERY означает: выполнить SELECT и отдать его строки наружу как результат функции.
Механика функции: когда вызывается get_bookings_by_passenger_period(...), PostgreSQL подставляет значения параметров p_passenger_id, p_date_from и p_date_to, выполняет SELECT внутри RETURN QUERY и возвращает его как таблицу.
SELECT DISTINCT нужен, потому что у одного бронирования может быть несколько билетов, а нам не нужны дубликаты бронирований.
FROM bookings b берем бронирования.
JOIN tickets t ON t.book_ref = b.book_ref связывает бронирования с билетами, чтобы найти пассажира.
WHERE t.passenger_id = p_passenger_id фильтрует по параметру пассажира.
AND b.book_date >= p_date_from AND b.book_date < p_date_to задает период. Лучше использовать такой диапазон, чем BETWEEN, чтобы аккуратно работать с датой и временем.
SELECT * FROM get_bookings_by_passenger_period(...) вызывает функцию и выводит все столбцы, которые функция сама объявила в RETURNS TABLE.
Почему здесь SELECT * нормален: мы не читаем сырую большую таблицу. Мы вызываем функцию, у которой заранее задан список возвращаемых столбцов в RETURNS TABLE.
*/
/* ============================================================
10. ПРОЦЕДУРА PL/PGSQL: ИЗМЕНИТЬ ДАННЫЕ ПО ПАРАМЕТРУ
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "процедура меняет статус"
- "процедура увеличивает дальность"
- "процедура умножает стоимость"
Значит:
- нужна CREATE PROCEDURE
- внутри обычно UPDATE
- вызывается через CALL
ЧТО МЕНЯТЬ В КОДЕ:
1. имя процедуры
2. параметр
3. таблицу в UPDATE
4. поле в SET
5. условие WHERE
============================================================ */
CREATE OR REPLACE PROCEDURE cancel_flights_by_aircraft(p_aircraft_code bpchar)
AS $$
BEGIN
UPDATE flights
SET status = 'Cancelled'
WHERE aircraft_code = p_aircraft_code;
END;
$$ LANGUAGE plpgsql;
CALL cancel_flights_by_aircraft('733');
/*
КОММЕНТАРИЙ К КОДУ 10
CREATE OR REPLACE PROCEDURE создает процедуру или заменяет старую.
cancel_flights_by_aircraft - имя процедуры.
p_aircraft_code bpchar - параметр: код самолета.
AS $$ ... $$ - тело процедуры.
BEGIN и END - блок команд.
UPDATE flights означает, что меняем строки в таблице flights.
SET status = 'Cancelled' устанавливает новый статус.
WHERE aircraft_code = p_aircraft_code ограничивает изменение только рейсами нужного самолета. Без WHERE обновились бы все рейсы, это опасно.
CALL cancel_flights_by_aircraft('733') вызывает процедуру.
Процедура подходит для задач, где нужно не просто получить строки, а изменить данные.
*/
/* ============================================================
11. ТРИГГЕР: ПРОВЕРИТЬ ЗНАЧЕНИЕ ПЕРЕД INSERT ИЛИ UPDATE
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "при добавлении проверяли"
- "если значение неверное, вывести ошибку"
- "дальность неотрицательная"
- "имя не пустое"
Значит:
- нужен BEFORE INSERT или BEFORE UPDATE
- внутри проверяем NEW
- если плохо - RAISE EXCEPTION
- если хорошо - RETURN NEW
ЧТО МЕНЯТЬ В КОДЕ:
1. таблицу в CREATE TRIGGER
2. поле NEW.range
3. условие ошибки
4. текст ошибки
============================================================ */
CREATE OR REPLACE FUNCTION check_aircraft_range()
RETURNS trigger AS $$
BEGIN
IF NEW.range < 0 THEN
RAISE EXCEPTION 'range must be positive';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_check_aircraft_range
BEFORE INSERT OR UPDATE ON aircrafts
FOR EACH ROW
EXECUTE FUNCTION check_aircraft_range();
/*
КОММЕНТАРИЙ К КОДУ 11
Триггер состоит из двух частей: функции и CREATE TRIGGER. Сначала пишется функция, что именно делать. Потом CREATE TRIGGER говорит PostgreSQL, когда автоматически запускать эту функцию.
CREATE OR REPLACE FUNCTION check_aircraft_range() создает триггерную функцию.
RETURNS trigger обязательно для триггерной функции. Это показывает PostgreSQL, что функция будет запускаться не вручную как обычная функция, а через триггер.
IF NEW.range < 0 THEN проверяет новое значение дальности. NEW - строка, которую пытаются вставить или изменить.
Механика BEFORE-триггера: PostgreSQL еще не записал строку в таблицу, но уже создал переменную NEW. Мы проверяем NEW.range и можем либо запретить операцию через RAISE EXCEPTION, либо вернуть NEW и разрешить запись.
RAISE EXCEPTION останавливает операцию и выводит ошибку.
RETURN NEW означает, что если проверка прошла, новая строка допускается дальше.
CREATE TRIGGER создает сам триггер.
BEFORE INSERT OR UPDATE означает, что триггер сработает до вставки или изменения. BEFORE нужен, потому что мы хотим проверить строку до сохранения.
ON aircrafts - таблица, на которую ставится триггер.
FOR EACH ROW означает, что функция вызывается для каждой изменяемой строки.
EXECUTE FUNCTION check_aircraft_range() указывает, какую функцию вызвать.
*/
/* ============================================================
12. ТРИГГЕР: ИЗМЕНИТЬ NEW ПЕРЕД ВСТАВКОЙ
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "при добавлении менять значение"
- "если класс неправильный, заменить на Economy"
- "обрезать код до трех символов"
Значит:
- нужен BEFORE INSERT
- меняем NEW.some_field
- возвращаем RETURN NEW
ЧТО МЕНЯТЬ В КОДЕ:
1. таблицу
2. поле NEW
3. условие IF
4. новое значение
============================================================ */
CREATE OR REPLACE FUNCTION fix_fare_conditions()
RETURNS trigger AS $$
BEGIN
IF NEW.fare_conditions NOT IN ('Economy', 'Comfort', 'Business') THEN
NEW.fare_conditions := 'Economy';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_fix_fare_conditions
BEFORE INSERT ON ticket_flights
FOR EACH ROW
EXECUTE FUNCTION fix_fare_conditions();
/*
КОММЕНТАРИЙ К КОДУ 12
Этот шаблон не запрещает вставку, а исправляет значение перед вставкой.
BEFORE INSERT нужен, потому что мы меняем NEW до того, как строка попадет в таблицу. Если бы был AFTER INSERT, строка уже была бы вставлена, и менять NEW было бы поздно.
NEW.fare_conditions - новое значение класса обслуживания.
IF NEW.fare_conditions NOT IN (...) проверяет, входит ли значение в список допустимых.
NEW.fare_conditions := 'Economy' заменяет неправильное значение на Economy.
Оператор := в plpgsql означает присваивание. То есть мы не сравниваем значения, а именно записываем новое значение в поле NEW.fare_conditions.
RETURN NEW возвращает измененную строку, и PostgreSQL вставляет уже исправленный вариант.
CREATE TRIGGER ... ON ticket_flights ставит триггер на таблицу перелетов в билетах.
FOR EACH ROW нужен, потому что проверять надо каждую вставляемую строку.
*/
/* ============================================================
13. ТРИГГЕР: ЖУРНАЛ УДАЛЕНИЯ
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "создать запись в журнале при удалении"
- "вести журнал удаленных аэропортов"
Значит:
- нужна таблица журнала
- нужен AFTER DELETE
- берем данные из OLD
- возвращаем OLD
ЧТО МЕНЯТЬ В КОДЕ:
1. таблицу журнала
2. поля журнала
3. исходную таблицу в CREATE TRIGGER
4. OLD.поля
============================================================ */
CREATE TABLE IF NOT EXISTS airports_delete_log (
airport_code bpchar,
airport_name text,
deleted_at timestamptz DEFAULT now()
);
CREATE OR REPLACE FUNCTION log_airport_delete()
RETURNS trigger AS $$
BEGIN
INSERT INTO airports_delete_log(airport_code, airport_name)
VALUES (OLD.airport_code, OLD.airport_name::text);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_log_airport_delete
AFTER DELETE ON airports
FOR EACH ROW
EXECUTE FUNCTION log_airport_delete();
/*
КОММЕНТАРИЙ К КОДУ 13
CREATE TABLE IF NOT EXISTS airports_delete_log создает таблицу журнала, если ее еще нет.
Журнал нужен, чтобы сохранить информацию об удаленной строке.
AFTER DELETE означает, что триггер срабатывает после удаления.
OLD содержит удаленную строку. При DELETE переменной NEW нет, потому что новой строки не создается.
Механика DELETE-триггера: строка удаляется, поэтому PostgreSQL дает доступ к ее старым значениям через OLD. Эти значения можно сохранить в таблицу-журнал.
INSERT INTO airports_delete_log(...) записывает данные из OLD в журнал.
OLD.airport_code и OLD.airport_name берутся из удаленного аэропорта.
RETURN OLD завершает триггер для DELETE.
CREATE TRIGGER ... AFTER DELETE ON airports ставит триггер на удаление из таблицы airports.
Если в базе таблица называется airports_data, нужно заменить ON airports на ON airports_data и поля проверить через information_schema.columns.
*/
/* ============================================================
14. ТРИГГЕР: ОБНОВИТЬ АГРЕГАТНУЮ ТАБЛИЦУ
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "вести таблицу с общей выручкой"
- "при добавлении билетов обновлять сумму"
- "обновлять количество пассажиров"
Значит:
- нужна отдельная таблица с итогами
- нужен AFTER INSERT
- используем NEW
- если строка уже есть, используем ON CONFLICT
ЧТО МЕНЯТЬ В КОДЕ:
1. таблицу итогов
2. ключ группировки
3. сумму или количество
4. исходную таблицу триггера
============================================================ */
CREATE TABLE IF NOT EXISTS fare_revenue (
fare_conditions varchar PRIMARY KEY,
total_amount numeric DEFAULT 0
);
CREATE OR REPLACE FUNCTION update_fare_revenue()
RETURNS trigger AS $$
BEGIN
INSERT INTO fare_revenue(fare_conditions, total_amount)
VALUES (NEW.fare_conditions, NEW.amount)
ON CONFLICT (fare_conditions)
DO UPDATE SET total_amount = fare_revenue.total_amount + EXCLUDED.total_amount;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_update_fare_revenue
AFTER INSERT ON ticket_flights
FOR EACH ROW
EXECUTE FUNCTION update_fare_revenue();
/*
КОММЕНТАРИЙ К КОДУ 14
fare_revenue - агрегатная таблица, где хранится итоговая выручка по классу обслуживания.
fare_conditions varchar PRIMARY KEY означает, что на каждый класс обслуживания будет одна строка.
AFTER INSERT ON ticket_flights означает, что триггер срабатывает после добавления нового перелета в билет.
NEW.fare_conditions - класс обслуживания новой строки.
NEW.amount - стоимость новой строки.
INSERT INTO fare_revenue(...) пытается добавить новую итоговую строку.
ON CONFLICT (fare_conditions) срабатывает, если строка для такого класса уже есть. Механика такая: PostgreSQL пытается сделать INSERT, видит конфликт по PRIMARY KEY, и вместо ошибки выполняет DO UPDATE.
EXCLUDED - специальное имя для строки, которую пытались вставить. Здесь EXCLUDED.total_amount означает новую сумму, которая пришла из INSERT.
DO UPDATE SET total_amount = fare_revenue.total_amount + EXCLUDED.total_amount увеличивает старую сумму на новую сумму.
EXCLUDED.total_amount - значение, которое пытались вставить.
RETURN NEW завершает триггер после INSERT.
*/
/* ============================================================
15. ТРИГГЕР: ЕСЛИ ПОСЛЕ DELETE НИЧЕГО НЕ ОСТАЛОСЬ, ИЗМЕНИТЬ СТАТУС
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "при удалении всех билетов на рейс обновить статус"
- "если записей больше нет, поставить Cancelled"
Значит:
- нужен AFTER DELETE
- берем OLD.flight_id
- проверяем NOT EXISTS
- обновляем flights
ЧТО МЕНЯТЬ В КОДЕ:
1. какую таблицу проверяем в NOT EXISTS
2. какое поле связывает удаленную строку с объектом
3. какой статус ставим
============================================================ */
CREATE OR REPLACE FUNCTION cancel_empty_flight()
RETURNS trigger AS $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM ticket_flights tf
WHERE tf.flight_id = OLD.flight_id
) THEN
UPDATE flights
SET status = 'Cancelled'
WHERE flight_id = OLD.flight_id;
END IF;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_cancel_empty_flight
AFTER DELETE ON ticket_flights
FOR EACH ROW
EXECUTE FUNCTION cancel_empty_flight();
/*
КОММЕНТАРИЙ К КОДУ 15
Этот триггер нужен для ситуации: удалили билет/перелет, и если на рейс больше нет билетов, рейс надо отменить.
AFTER DELETE ON ticket_flights означает, что проверка идет после удаления строки.
OLD.flight_id - рейс, из которого удалили строку.
IF NOT EXISTS (...) проверяет, остались ли еще строки ticket_flights для этого flight_id.
Механика NOT EXISTS: подзапрос пытается найти хотя бы одну строку по OLD.flight_id. Если не нашел, условие становится true, и выполняется UPDATE flights.
SELECT 1 внутри EXISTS используется потому, что нам не важны сами данные. Важно только, существует строка или нет.
Если строк больше нет, выполняется UPDATE flights.
SET status = 'Cancelled' меняет статус рейса.
WHERE flight_id = OLD.flight_id важно, чтобы изменить только нужный рейс.
RETURN OLD нужен, потому что это DELETE-триггер. При DELETE новой строки уже нет, поэтому PostgreSQL возвращает старую удаляемую строку OLD.
*/
/* ============================================================
16. ТРИГГЕР: ПРИ INSERT ПОСТАВИТЬ САМОЕ ПОПУЛЯРНОЕ ЗНАЧЕНИЕ
КАК ПОНЯТЬ ИЗ УСЛОВИЯ:
Если написано:
- "при добавлении рейса поставить наиболее популярный самолет"
- "наиболее популярный по числу рейсов"
Значит:
- нужен BEFORE INSERT ON flights
- считаем count(*) по aircraft_code
- записываем результат в NEW.aircraft_code
ЧТО МЕНЯТЬ В КОДЕ:
1. что считаем популярным
2. куда записываем в NEW
============================================================ */
CREATE OR REPLACE FUNCTION set_most_popular_aircraft()
RETURNS trigger AS $$
BEGIN
SELECT f.aircraft_code
INTO NEW.aircraft_code
FROM flights f
GROUP BY f.aircraft_code
ORDER BY count(*) DESC
LIMIT 1;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_set_most_popular_aircraft
BEFORE INSERT ON flights
FOR EACH ROW
EXECUTE FUNCTION set_most_popular_aircraft();
/*
КОММЕНТАРИЙ К КОДУ 16
Этот триггер меняет aircraft_code у нового рейса перед вставкой.
BEFORE INSERT ON flights нужен, потому что значение NEW.aircraft_code надо изменить до сохранения строки.
SELECT f.aircraft_code INTO NEW.aircraft_code выбирает код самолета и записывает его прямо в новую строку.
SELECT ... INTO в plpgsql не выводит результат на экран. Он записывает найденное значение в переменную или поле, в данном случае в NEW.aircraft_code.
FROM flights f смотрит уже существующие рейсы.
GROUP BY f.aircraft_code группирует рейсы по самолетам.
ORDER BY count(*) DESC ставит самый часто встречающийся самолет первым.
LIMIT 1 берет только один самый популярный самолет.
RETURN NEW возвращает новую строку обратно PostgreSQL. Для BEFORE INSERT или BEFORE UPDATE это важно: именно такая строка будет записана в таблицу.
Важно: если flights пустая, SELECT ничего не найдет. В реальной задаче можно добавить запасной вариант через COALESCE или отдельную проверку.
*/
/* ============================================================
17. CASE: СДЕЛАТЬ ТЕКСТОВУЮ КАТЕГОРИЮ ПО УСЛОВИЮ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- разделить билеты на дешевые, средние и дорогие
- вывести категорию рейса по статусу
- заменить значения на понятные подписи
ЧТО МЕНЯТЬ В КОДЕ:
1. поле, которое проверяется в CASE
2. условия WHEN
3. текст после THEN
4. таблицу в FROM
============================================================ */
SELECT
tf.ticket_no,
tf.flight_id,
tf.amount,
CASE
WHEN tf.amount < 5000 THEN 'cheap'
WHEN tf.amount BETWEEN 5000 AND 20000 THEN 'middle'
ELSE 'expensive'
END AS price_group
FROM ticket_flights tf;
/*
КОММЕНТАРИЙ К КОДУ 17
CASE нужен, когда в результате надо получить новое значение по условию.
Механика CASE: SQL проверяет WHEN сверху вниз. Как только условие подошло, берется соответствующий THEN, остальные WHEN уже не проверяются. Если ничего не подошло, используется ELSE.
tf.ticket_no, tf.flight_id и tf.amount выводятся как обычные поля, чтобы было видно, к какому билету относится категория.
WHEN tf.amount < 5000 THEN 'cheap' означает: если цена меньше 5000, написать cheap.
BETWEEN 5000 AND 20000 проверяет диапазон от 5000 до 20000 включительно.
ELSE 'expensive' срабатывает, если не подошло ни одно условие выше.
END AS price_group завершает CASE и дает название новому столбцу.
FROM ticket_flights tf означает, что данные берутся из таблицы перелетов по билетам.
*/
/* ============================================================
18. JSON-ПОЛЯ: ВЫТАЩИТЬ РУССКОЕ НАЗВАНИЕ ИЗ airport_name И city
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если в базе вместо airports используется airports_data.
В airports_data названия аэропорта и города могут храниться как JSON.
ЧТО МЕНЯТЬ В КОДЕ:
1. 'ru' можно заменить на 'en', если нужны английские названия
2. airports_data можно заменить на aircrafts_data, если работаешь с самолетами
============================================================ */
SELECT
a.airport_code,
a.airport_name ->> 'ru' AS airport_name_ru,
a.city ->> 'ru' AS city_ru
FROM airports_data a
WHERE a.city ->> 'ru' = 'Москва';
/*
КОММЕНТАРИЙ К КОДУ 18
В некоторых версиях базы есть таблица airports_data. В ней airport_name и city могут быть не обычным текстом, а JSON.
a.airport_name ->> 'ru' достает русское название аэропорта как текст.
Оператор ->> нужен именно для получения текста. Если написать ->, получится JSON-значение.
a.city ->> 'ru' AS city_ru достает русское название города и называет столбец city_ru.
WHERE a.city ->> 'ru' = 'Москва' оставляет только московские аэропорты.
Если в базе есть представление airports, то часто можно писать проще: airport_name и city без ->> 'ru'.
*/
/* ============================================================
19. DISTINCT ON: ВЗЯТЬ ОДНУ ЛУЧШУЮ СТРОКУ ДЛЯ КАЖДОЙ ГРУППЫ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- для каждого аэропорта найти самый поздний рейс
- для каждого самолета найти самый дорогой билет
- для каждой группы вывести только одну строку
ЧТО МЕНЯТЬ В КОДЕ:
1. поле в DISTINCT ON
2. порядок в ORDER BY
3. таблицу и условия
============================================================ */
SELECT DISTINCT ON (f.departure_airport)
f.departure_airport,
f.flight_id,
f.flight_no,
f.scheduled_departure
FROM flights f
ORDER BY f.departure_airport, f.scheduled_departure DESC;
/*
КОММЕНТАРИЙ К КОДУ 19
DISTINCT ON (f.departure_airport) оставляет только одну строку для каждого аэропорта отправления.
Механика DISTINCT ON: сначала строки сортируются по ORDER BY, потом PostgreSQL оставляет первую строку для каждого значения в скобках DISTINCT ON.
Какая именно строка останется, решает ORDER BY.
ORDER BY f.departure_airport, f.scheduled_departure DESC сначала группирует строки по аэропорту, а внутри аэропорта ставит самый поздний рейс первым.
Поэтому для каждого departure_airport будет выбран рейс с максимальным scheduled_departure.
Это удобный способ, когда надо получить не просто максимум, а всю строку с этим максимумом.
Важно: первое поле в ORDER BY должно совпадать с полем в DISTINCT ON.
*/
/* ============================================================
20. EXISTS: НАЙТИ СТРОКИ, ДЛЯ КОТОРЫХ ЕСТЬ СВЯЗАННЫЕ ЗАПИСИ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- найти рейсы, на которые есть посадочные талоны
- найти бронирования, где есть билеты
- найти аэропорты, из которых были рейсы
ЧТО МЕНЯТЬ В КОДЕ:
1. главную таблицу в FROM
2. таблицу внутри EXISTS
3. условие связи
============================================================ */
SELECT
f.flight_id,
f.flight_no,
f.scheduled_departure
FROM flights f
WHERE EXISTS (
SELECT 1
FROM boarding_passes bp
WHERE bp.flight_id = f.flight_id
);
/*
КОММЕНТАРИЙ К КОДУ 20
EXISTS проверяет, существует ли хотя бы одна связанная строка. Важны не данные внутри подзапроса, а сам факт: нашлась строка или нет.
Почему внутри пишут SELECT 1: PostgreSQL не нужен реальный столбец. Ему важно только, вернул подзапрос хотя бы одну строку или нет.
Главная таблица здесь flights. Мы перебираем рейсы.
Подзапрос внутри EXISTS смотрит таблицу boarding_passes.
SELECT 1 используется потому, что нам не нужны данные из boarding_passes. Нужно только проверить факт существования.
WHERE bp.flight_id = f.flight_id связывает посадочный талон с текущим рейсом.
Если хотя бы одна строка найдена, EXISTS возвращает true, и рейс попадает в результат.
EXISTS часто удобнее, чем JOIN, если надо только проверить наличие, а не выводить данные из второй таблицы.
*/
/* ============================================================
21. NOT EXISTS: НАЙТИ СТРОКИ БЕЗ СВЯЗАННЫХ ЗАПИСЕЙ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- найти рейсы без посадочных талонов
- найти бронирования без билетов
- найти самолеты, которые не используются
ЧТО МЕНЯТЬ В КОДЕ:
1. главную таблицу
2. таблицу внутри NOT EXISTS
3. условие связи
============================================================ */
SELECT
f.flight_id,
f.flight_no,
f.status
FROM flights f
WHERE NOT EXISTS (
SELECT 1
FROM boarding_passes bp
WHERE bp.flight_id = f.flight_id
);
/*
КОММЕНТАРИЙ К КОДУ 21
NOT EXISTS работает наоборот: строка попадает в результат, если связанных строк нет. SQL проверяет подзапрос для текущей строки и оставляет ее только если подзапрос ничего не нашел.
Это часто безопаснее, чем NOT IN, потому что NOT IN может вести себя неожиданно, если в списке есть NULL.
FROM flights f берет все рейсы.
Подзапрос ищет посадочные талоны для текущего рейса.
Если для рейса не найдено ни одной строки в boarding_passes, условие NOT EXISTS выполняется.
Такой запрос полезен для поиска отсутствующих связей.
Лучше использовать NOT EXISTS, а не NOT IN, если в подзапросе могут быть NULL.
*/
/* ============================================================
22. UPDATE С ПОДЗАПРОСОМ: ОБНОВИТЬ ТОЛЬКО НУЖНЫЕ СТРОКИ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- изменить статус рейсов без пассажиров
- увеличить цену билетов по условию
- обновить строки, найденные другим запросом
ВАЖНО:
На экзамене перед UPDATE лучше делать SELECT с таким же WHERE.
Так ты проверишь, какие строки изменятся.
============================================================ */
SELECT *
FROM flights f
WHERE NOT EXISTS (
SELECT 1
FROM ticket_flights tf
WHERE tf.flight_id = f.flight_id
);
UPDATE flights f
SET status = 'Cancelled'
WHERE NOT EXISTS (
SELECT 1
FROM ticket_flights tf
WHERE tf.flight_id = f.flight_id
);
/*
КОММЕНТАРИЙ К КОДУ 22
Первый SELECT нужен для проверки. Он показывает строки, которые потом будут обновлены.
SELECT * здесь допустим, потому что это проверочный запрос перед UPDATE. Нам удобно увидеть все поля строки.
UPDATE flights f означает, что изменяется таблица flights.
SET status = 'Cancelled' задает новое значение поля status.
WHERE ограничивает обновление. Без WHERE обновились бы все рейсы.
NOT EXISTS ищет рейсы, у которых нет строк в ticket_flights.
tf.flight_id = f.flight_id связывает ticket_flights с текущим рейсом.
Если преподаватель спрашивает, зачем SELECT перед UPDATE, ответ простой: чтобы случайно не изменить лишние строки.
*/
/* ============================================================
23. DELETE С ПОДЗАПРОСОМ И ПРОВЕРКОЙ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- удалить тестовые строки
- удалить записи без связей
- удалить строки по условию
ВАЖНО:
Сначала всегда делай SELECT с таким же WHERE.
Если надо не менять данные, запускай внутри транзакции и потом ROLLBACK.
============================================================ */
BEGIN;
SELECT *
FROM flights f
WHERE f.status = 'Cancelled'
AND NOT EXISTS (
SELECT 1
FROM ticket_flights tf
WHERE tf.flight_id = f.flight_id
);
DELETE FROM flights f
WHERE f.status = 'Cancelled'
AND NOT EXISTS (
SELECT 1
FROM ticket_flights tf
WHERE tf.flight_id = f.flight_id
);
ROLLBACK;
/*
КОММЕНТАРИЙ К КОДУ 23
BEGIN начинает транзакцию. Все изменения после BEGIN можно отменить.
Механика транзакции: PostgreSQL выполняет команды как временный набор изменений. Пока не сделан COMMIT, их можно отменить через ROLLBACK.
SELECT * показывает строки, которые подходят под условие удаления.
DELETE FROM flights f удаляет строки из flights.
WHERE f.status = 'Cancelled' оставляет только отмененные рейсы.
AND NOT EXISTS (...) добавляет вторую проверку: удалять только те рейсы, на которые нет билетов.
ROLLBACK отменяет DELETE. Это важно, если задание просит проанализировать удаление, но фактически не менять данные.
Если надо реально сохранить удаление, вместо ROLLBACK пишут COMMIT.
*/
/* ============================================================
24. ТРАНЗАКЦИЯ: ПРОВЕРИТЬ INSERT, UPDATE И DELETE БЕЗ ИЗМЕНЕНИЯ ДАННЫХ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- проанализировать план INSERT/UPDATE/DELETE
- выполнить изменение так, чтобы данные не поменялись
============================================================ */
BEGIN;
EXPLAIN ANALYZE
UPDATE flights
SET status = 'Cancelled'
WHERE flight_id = 1;
ROLLBACK;
/*
КОММЕНТАРИЙ К КОДУ 24
BEGIN открывает транзакцию.
EXPLAIN ANALYZE выполняет команду и показывает реальный план выполнения.
UPDATE flights меняет таблицу flights.
SET status = 'Cancelled' задает новое значение статуса.
WHERE flight_id = 1 ограничивает изменение одной строкой. Без WHERE изменились бы все строки.
ROLLBACK отменяет изменение, поэтому база остается как была.
Такой прием нужен, когда преподаватель просит посмотреть план изменения данных, но не испортить таблицу.
*/
/* ============================================================
25. WITH RECURSIVE: РЕКУРСИВНЫЙ ЗАПРОС ДЛЯ ПОСЛЕДОВАТЕЛЬНОСТИ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Редко, но могут спросить рекурсивный CTE.
В базе самолетов обычно нет дерева, поэтому проще показать генерацию чисел.
============================================================ */
WITH RECURSIVE nums(n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM nums
WHERE n < 10
)
SELECT *
FROM nums;
/*
КОММЕНТАРИЙ К КОДУ 25
WITH RECURSIVE создает рекурсивное CTE, которое само обращается к себе.
Механика рекурсии: сначала выполняется начальный SELECT 1. Потом рекурсивная часть SELECT n + 1 повторяется, пока WHERE n < 10 разрешает продолжать.
nums(n) - имя временного результата и имя его столбца.
SELECT 1 - начальная часть. С нее начинается рекурсия.
UNION ALL соединяет начальную часть и рекурсивную часть.
SELECT n + 1 FROM nums берет предыдущее число и прибавляет 1.
WHERE n < 10 останавливает рекурсию. Без ограничения запрос мог бы работать бесконечно.
SELECT * FROM nums выводит все полученные числа.
В реальных задачах WITH RECURSIVE часто используют для деревьев, цепочек и маршрутов.
*/
/* ============================================================
26. ДАТЫ: ПРАВИЛЬНЫЙ ФИЛЬТР ПО МЕСЯЦУ ИЛИ ДНЮ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- за месяц
- за день
- за период
- в 2017 году
ЛУЧШЕ ПИСАТЬ ДИАПАЗОНОМ:
book_date >= начало AND book_date < следующий период
============================================================ */
SELECT
b.book_ref,
b.book_date,
b.total_amount
FROM bookings b
WHERE b.book_date >= timestamp '2017-08-01'
AND b.book_date < timestamp '2017-09-01';
/*
КОММЕНТАРИЙ К КОДУ 26
WHERE задает фильтр по дате бронирования.
b.book_date >= timestamp '2017-08-01' берет все бронирования начиная с 1 августа.
b.book_date < timestamp '2017-09-01' берет только до начала сентября.
Такой вариант лучше, чем date_trunc в WHERE, потому что индекс по book_date может использоваться нормально.
Если нужен один день, пишем: >= '2017-08-01' и < '2017-08-02'.
Если результат пустой, это не всегда ошибка. Возможно, в этой версии базы нет данных за такую дату.
*/
/* ============================================================
27. NULL: ПРОВЕРКА ПУСТЫХ ЗНАЧЕНИЙ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- найти рейсы без фактического времени вылета
- найти строки, где значение не заполнено
- заменить NULL на текст
============================================================ */
SELECT
f.flight_id,
f.flight_no,
f.actual_departure,
coalesce(f.actual_departure::text, 'нет фактического вылета') AS actual_departure_text
FROM flights f
WHERE f.actual_departure IS NULL;
/*
КОММЕНТАРИЙ К КОДУ 27
NULL нельзя сравнивать через = NULL. Нужно писать IS NULL.
WHERE f.actual_departure IS NULL ищет рейсы, где фактическое время вылета не заполнено.
coalesce берет первое не NULL значение.
coalesce(f.actual_departure::text, 'нет фактического вылета') выводит дату текстом, а если даты нет, пишет текст.
::text приводит дату к тексту, потому что COALESCE должен возвращать значения одного типа.
Если нужно найти заполненные значения, пишут IS NOT NULL.
*/
/* ============================================================
28. UNION, INTERSECT, EXCEPT: ОБЪЕДИНЕНИЕ И СРАВНЕНИЕ РЕЗУЛЬТАТОВ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- объединить два списка
- найти общие значения
- найти значения из одного списка, которых нет в другом
============================================================ */
SELECT departure_airport AS airport_code
FROM flights
UNION
SELECT arrival_airport AS airport_code
FROM flights;
/*
КОММЕНТАРИЙ К КОДУ 28
Первый SELECT берет аэропорты отправления.
Второй SELECT берет аэропорты прибытия.
UNION объединяет два результата и убирает дубликаты.
Названия и типы столбцов в обоих SELECT должны совпадать по смыслу.
Если нужны все строки вместе с повторами, используют UNION ALL.
INTERSECT оставляет только общие строки.
EXCEPT оставляет строки из первого запроса, которых нет во втором.
*/
/* ============================================================
29. LATERAL: ПОДЗАПРОС ДЛЯ КАЖДОЙ СТРОКИ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Редко, но полезно, если нужно:
- для каждого рейса взять самый дорогой билет
- для каждого аэропорта взять один ближайший рейс
============================================================ */
SELECT
f.flight_id,
f.flight_no,
x.ticket_no,
x.amount
FROM flights f
LEFT JOIN LATERAL (
SELECT
tf.ticket_no,
tf.amount
FROM ticket_flights tf
WHERE tf.flight_id = f.flight_id
ORDER BY tf.amount DESC
LIMIT 1
) x ON true;
/*
КОММЕНТАРИЙ К КОДУ 29
LATERAL позволяет подзапросу использовать поля из строки внешнего запроса. Механика похожа на цикл: для каждой строки flights запускается свой маленький SELECT из ticket_flights.
Без LATERAL подзапрос в FROM обычно не может ссылаться на f.flight_id из внешней таблицы flights.
FROM flights f берет рейсы.
LEFT JOIN LATERAL (...) запускает подзапрос отдельно для каждого рейса.
WHERE tf.flight_id = f.flight_id внутри LATERAL использует flight_id текущего рейса.
ORDER BY tf.amount DESC ставит самый дорогой билет первым.
LIMIT 1 оставляет только один билет для каждого рейса.
ON true пишется потому, что условие связи уже находится внутри подзапроса.
LEFT JOIN нужен, чтобы рейс остался в результате даже если билетов для него нет.
*/
/* ============================================================
30. ARRAY_AGG И STRING_AGG: СОБРАТЬ ЗНАЧЕНИЯ В ОДНУ СТРОКУ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- вывести список билетов по бронированию
- вывести список пассажиров по рейсу
- собрать значения в один столбец
============================================================ */
SELECT
t.book_ref,
count(*) AS ticket_count,
string_agg(t.passenger_name, ', ' ORDER BY t.passenger_name) AS passengers
FROM tickets t
GROUP BY t.book_ref;
/*
КОММЕНТАРИЙ К КОДУ 30
GROUP BY t.book_ref группирует билеты по бронированию.
count(*) считает количество билетов в каждом бронировании.
string_agg(t.passenger_name, ', ' ORDER BY t.passenger_name) собирает имена пассажиров в одну строку через запятую.
ORDER BY внутри string_agg задает порядок имен внутри этой строки.
Если нужны не строки, а массив, можно использовать array_agg(t.passenger_name).
Такой запрос удобен, когда надо сделать один результат на одну группу, но показать внутри группы много значений.
*/
/* ============================================================
31. FILTER В АГРЕГАТАХ: НЕСКОЛЬКО COUNT И SUM В ОДНОМ ЗАПРОСЕ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- посчитать количество рейсов по статусам
- посчитать суммы по разным условиям
- сделать несколько агрегатов по одной таблице
============================================================ */
SELECT
count(*) AS all_flights,
count(*) FILTER (WHERE status = 'Scheduled') AS scheduled_flights,
count(*) FILTER (WHERE status = 'Cancelled') AS cancelled_flights,
count(*) FILTER (WHERE actual_departure IS NOT NULL) AS departed_flights
FROM flights;
/*
КОММЕНТАРИЙ К КОДУ 31
count(*) AS all_flights считает все рейсы.
FILTER (WHERE status = 'Scheduled') означает, что этот count считает только строки со статусом Scheduled.
FILTER удобен, когда нужно несколько разных подсчетов из одной таблицы.
FROM flights означает, что все подсчеты делаются по таблице рейсов.
Без FILTER пришлось бы писать несколько отдельных запросов или CASE внутри count.
Если условие не нашло строк, count вернет 0.
*/
/* ============================================================
32. HAVING: УСЛОВИЕ ПОСЛЕ GROUP BY
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- вывести только рейсы, где пассажиров больше 50
- вывести аэропорты, где больше 100 рейсов
- вывести бронирования с суммой больше среднего
============================================================ */
SELECT
f.flight_id,
f.flight_no,
count(bp.ticket_no) AS passenger_count
FROM flights f
LEFT JOIN boarding_passes bp
ON bp.flight_id = f.flight_id
GROUP BY f.flight_id, f.flight_no
HAVING count(bp.ticket_no) > 50;
/*
КОММЕНТАРИЙ К КОДУ 32
LEFT JOIN связывает рейсы с посадочными талонами.
count(bp.ticket_no) считает количество пассажиров по каждому рейсу.
GROUP BY f.flight_id, f.flight_no нужен, потому что count считается отдельно для каждого рейса.
HAVING count(bp.ticket_no) > 50 фильтрует уже готовые группы. WHERE не может напрямую фильтровать по count, потому что count появляется только после GROUP BY.
Если условие относится к обычной строке, используй WHERE. Если условие относится к результату count, sum, avg, max или min по группе, используй HAVING.
WHERE фильтрует строки до группировки, а HAVING фильтрует результат после GROUP BY.
Если в условии задачи есть слова \"где количество больше\", \"где сумма больше\", чаще всего нужен HAVING.
*/
/* ============================================================
33. CREATE VIEW: СОЗДАТЬ ПРЕДСТАВЛЕНИЕ ДЛЯ ЧАСТОГО ЗАПРОСА
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- создать представление
- сохранить запрос как виртуальную таблицу
- упростить сложный SELECT
============================================================ */
CREATE OR REPLACE VIEW flight_passengers_view AS
SELECT
f.flight_id,
f.flight_no,
f.scheduled_departure,
count(bp.ticket_no) AS passenger_count
FROM flights f
LEFT JOIN boarding_passes bp
ON bp.flight_id = f.flight_id
GROUP BY f.flight_id, f.flight_no, f.scheduled_departure;
SELECT *
FROM flight_passengers_view;
/*
КОММЕНТАРИЙ К КОДУ 33
CREATE OR REPLACE VIEW создает представление или заменяет его, если оно уже есть.
Представление - это сохраненный SELECT. Данные обычно не хранятся отдельно, запрос выполняется при обращении.
flight_passengers_view - имя представления.
SELECT внутри view считает количество пассажиров по рейсам.
После создания можно писать SELECT * FROM flight_passengers_view как будто это таблица.
SELECT * здесь удобен, потому что представление уже специально создано с нужными столбцами.
Если больше не нужно, можно удалить представление командой DROP VIEW flight_passengers_view;
*/
/* ============================================================
34. TEMP TABLE: ВРЕМЕННАЯ ТАБЛИЦА ДЛЯ ПРОМЕЖУТОЧНЫХ ДАННЫХ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- сохранить промежуточный результат
- несколько раз использовать один результат
- разбить сложную задачу на шаги
============================================================ */
CREATE TEMP TABLE temp_flight_counts AS
SELECT
f.flight_id,
f.flight_no,
count(bp.ticket_no) AS passenger_count
FROM flights f
LEFT JOIN boarding_passes bp
ON bp.flight_id = f.flight_id
GROUP BY f.flight_id, f.flight_no;
SELECT *
FROM temp_flight_counts
WHERE passenger_count > 50;
/*
КОММЕНТАРИЙ К КОДУ 34
CREATE TEMP TABLE создает временную таблицу. Она живет только в текущем подключении.
AS SELECT означает, что таблица создается по результату запроса.
В запросе считаются пассажиры по рейсам.
Потом SELECT * FROM temp_flight_counts читает уже готовый промежуточный результат.
WHERE passenger_count > 50 фильтрует временную таблицу.
Это удобно, если сложный результат нужен несколько раз.
Минус: временная таблица реально создается, в отличие от CTE.
*/
/* ============================================================
35. ИНДЕКС И EXPLAIN: СОЗДАТЬ ИНДЕКС И ПРОВЕРИТЬ ПЛАН
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- ускорить поиск
- проверить метод доступа
- сравнить план до и после индекса
============================================================ */
EXPLAIN ANALYZE
SELECT *
FROM ticket_flights
WHERE amount > 200000;
CREATE INDEX IF NOT EXISTS ticket_flights_amount_exam_idx
ON ticket_flights(amount);
EXPLAIN ANALYZE
SELECT *
FROM ticket_flights
WHERE amount > 200000;
/*
КОММЕНТАРИЙ К КОДУ 35
Первый EXPLAIN ANALYZE показывает план до создания индекса.
Механика EXPLAIN ANALYZE: PostgreSQL реально выполняет запрос и показывает, какие узлы плана использовал, сколько строк прошло через узлы и сколько времени заняло выполнение.
SELECT * здесь можно использовать, потому что задание обычно просит найти информацию о строках. Если нужны только конкретные поля, лучше перечислить их.
FROM ticket_flights означает, что ищем в таблице перелетов по билетам.
WHERE amount > 200000 оставляет только дорогие перелеты.
CREATE INDEX создает индекс по amount.
IF NOT EXISTS защищает от ошибки, если индекс уже создан.
Второй EXPLAIN ANALYZE показывает план после индекса.
Если строк мало, планировщик часто выберет Bitmap Index Scan или Index Scan.
Если строк много, Seq Scan может быть нормальным выбором.
*/
/* ============================================================
36. ВРЕМЕННО ЗАПРЕТИТЬ МЕТОД ДОСТУПА
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- запретить seq scan
- запретить index scan
- проверить, прав ли оптимизатор
============================================================ */
EXPLAIN ANALYZE
SELECT *
FROM ticket_flights
WHERE amount > 200000;
SET enable_bitmapscan = off;
EXPLAIN ANALYZE
SELECT *
FROM ticket_flights
WHERE amount > 200000;
RESET enable_bitmapscan;
/*
КОММЕНТАРИЙ К КОДУ 36
Первый EXPLAIN ANALYZE показывает обычный план, который выбрал планировщик.
SET enable_bitmapscan = off запрещает bitmap scan.
После запрета второй EXPLAIN ANALYZE показывает другой план.
RESET enable_bitmapscan возвращает настройку обратно.
Если после запрета запрос стал медленнее, значит оптимизатор был прав.
Запрещать нужно именно тот метод, который был выбран в первом плане. Если был Seq Scan, запрещают enable_seqscan. Если Bitmap Heap Scan, запрещают enable_bitmapscan.
*/
/* ============================================================
37. ОКОННАЯ ФУНКЦИЯ LAG: СРАВНИТЬ С ПРЕДЫДУЩЕЙ СТРОКОЙ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- сравнить текущий рейс с предыдущим
- найти разницу между соседними значениями
- показать предыдущее значение
============================================================ */
SELECT
f.flight_id,
f.flight_no,
f.scheduled_departure,
lag(f.scheduled_departure) OVER (
PARTITION BY f.flight_no
ORDER BY f.scheduled_departure
) AS prev_departure
FROM flights f;
/*
КОММЕНТАРИЙ К КОДУ 37
lag(...) показывает значение из предыдущей строки.
PARTITION BY f.flight_no делит данные по номеру рейса.
ORDER BY f.scheduled_departure задает порядок строк внутри каждого номера рейса.
prev_departure - предыдущая дата вылета для такого же flight_no.
Оконная функция не уменьшает количество строк, в отличие от GROUP BY.
Если предыдущей строки нет, lag вернет NULL.
*/
/* ============================================================
38. ОКОННАЯ ФУНКЦИЯ SUM OVER: НАКОПИТЕЛЬНЫЙ ИТОГ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- накопительную сумму
- сумму с начала периода
- бегущий итог
============================================================ */
SELECT
b.book_ref,
b.book_date,
b.total_amount,
sum(b.total_amount) OVER (
ORDER BY b.book_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM bookings b;
/*
КОММЕНТАРИЙ К КОДУ 38
sum(b.total_amount) OVER (...) считает сумму как оконную функцию.
ORDER BY b.book_date задает порядок, в котором накапливается сумма.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW означает: брать строки от начала до текущей строки.
running_total - накопительная сумма.
GROUP BY здесь не нужен, потому что мы не сворачиваем строки в группы.
Каждая строка остается в результате, но рядом появляется накопительный итог.
*/
/* ============================================================
39. ПАРАМЕТР В ФУНКЦИИ: ВЕРНУТЬ РЕЙСЫ ПО ГОРОДУ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- написать функцию с параметром
- функция должна возвращать таблицу
- найти данные по городу или аэропорту
============================================================ */
CREATE OR REPLACE FUNCTION get_flights_to_city(p_city text)
RETURNS TABLE (
flight_id integer,
flight_no char(6),
arrival_airport char(3),
city text
) AS $$
BEGIN
RETURN QUERY
SELECT
f.flight_id,
f.flight_no,
f.arrival_airport,
a.city
FROM flights f
JOIN airports a
ON a.airport_code = f.arrival_airport
WHERE a.city = p_city;
END;
$$ LANGUAGE plpgsql;
SELECT *
FROM get_flights_to_city('Москва');
/*
КОММЕНТАРИЙ К КОДУ 39
CREATE OR REPLACE FUNCTION создает функцию или заменяет старую. Функция - это сохраненный код в базе, который можно вызвать из SQL и получить результат. Механика такая: PostgreSQL сохраняет тело функции, а при вызове подставляет переданные параметры и выполняет код между BEGIN и END.
get_flights_to_city(p_city text) - функция принимает город как параметр.
RETURNS TABLE (...) означает, что функция вернет таблицу с указанными столбцами.
BEGIN и END ограничивают тело функции.
RETURN QUERY означает, что результат SELECT будет результатом функции.
JOIN airports a связывает рейсы с аэропортами прибытия.
WHERE a.city = p_city оставляет только рейсы в город, который передали в функцию.
SELECT * FROM get_flights_to_city('Москва') вызывает функцию.
Если в твоей базе нет airports, а есть airports_data, надо заменить airports и city на airports_data и city ->> 'ru'.
*/
/* ============================================================
40. ПРОЦЕДУРА С INOUT: ПАРАМЕТР МЕНЯЕТСЯ ВНУТРИ ПРОЦЕДУРЫ
КОГДА МОЖЕТ ПОПАСТЬСЯ:
Если просят:
- процедуру с входным и выходным параметром
- посчитать значение и вернуть через параметр
============================================================ */
CREATE OR REPLACE PROCEDURE count_tickets_by_booking(
IN p_book_ref char(6),
INOUT p_count integer
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT count(*)
INTO p_count
FROM tickets t
WHERE t.book_ref = p_book_ref;
END;
$$;
CALL count_tickets_by_booking('00000F', 0);
/*
КОММЕНТАРИЙ К КОДУ 40
CREATE OR REPLACE PROCEDURE создает процедуру. Процедура - это сохраненный код в базе. В отличие от функции, ее вызывают через CALL, и она обычно нужна для действий, а не для обычного SELECT.
IN p_book_ref char(6) - входной параметр, номер бронирования.
INOUT p_count integer - параметр, который передается в процедуру и потом возвращается измененным.
SELECT count(*) считает количество билетов.
INTO p_count записывает результат count в параметр p_count.
FROM tickets t берет данные из таблицы билетов.
WHERE t.book_ref = p_book_ref оставляет билеты только одного бронирования.
CALL вызывает процедуру.
Если бронирования '00000F' нет в базе, результат будет 0. Это нормально.
*/
