Сибирский Dev | IT survival on Telegram: “#конспект #SQL Я решил пропустить раздел с соеди…”

#конспект #SQL

Я решил пропустить раздел с соединениями, ибо он очень объемный и это база, которую давали даже мне на онлайн курсе) Кому то она окажется тяжелой (например мне 😄) и он ее поймет спустя пару лет (например я😄), но тут лучше практикой, чем чтением. Так же, пропускаю маленькую тему про агрегатные функции типа max/min/avg.

Оконные функции (window functions)

Эти функции предоставляют возможность производить вычисления на множестве строк, логически связанных с текущей строкой, т. е. имеющих то или иное отношение к ней.
При работе с оконными функциями используются концепции раздела (partition) и оконного кадра (window frame).

Пример запроса с оконной функцией:

SELECT b.book_ref,
       b.book_date,
       extract( 'month' from b.book_date ) AS month,
       extract( 'day' from b.book_date ) AS day,
       count( * ) OVER (
         PARTITION BY date_trunc( 'month', b.book_date )
         ORDER BY b.book_date
       ) AS count
FROM ticket_flights tf
JOIN tickets t ON tf.ticket_no = t.ticket_no
JOIN bookings b ON t.book_ref = b.book_ref
WHERE tf.flight_id = 1
ORDER BY b.book_date;

Конструкция для вызова оконной функции:

count( * ) OVER (
  PARTITION BY date_trunc( 'month', b.book_date )
  ORDER BY b.book_date
) AS count

В этой конструкции обязательным является ключевое слово OVER. Функция count — это обычная агрегатная функция, но если вслед за ней идет это ключевое слово, то она становится оконной функцией. Предложение PARTITION BY задает правило разбиения строк выборки на разделы. Предложение ORDER BY предписывает порядок
сортировки строк в разделах.
Обобщая приведенные объяснения, можно сказать, что раздел включает в себя все строки выборки, имеющие в некотором смысле одинаковые свойства, например, одинаковые значения определенных выражений, задаваемых с помощью предложения PARTITION BY. Это могут быть выражения, построенные на основе одного или нескольких столбцов таблицы (или таблиц, участвующих в соединении).

Кстати, date_trunc — это функция, для усечения даты по выбранному критерию, в нашем случае b.book_date мы усекаем с начала и по конец месяца, где находится дата из переменной.
(Да, это я лично вставил длинное тире, а не ИИ 😎)

В этом запросе в предложении OVER ( PARTITION BY timezone ... ) указывается, что строки относятся к одному разделу на основе совпадения значений в столбце timezone. Обратите внимание, что хотя в предложении OVER задан порядок сортировки, действующий в пределах каждого окна, тем не менее, с помощью предложения ORDER BY указан также и порядок сортировки на уровне всего запроса.

SELECT airport_name,
       city,
       round( latitude::numeric, 2 ) AS ltd,
       timezone,
       rank() OVER (
         PARTITION BY timezone
         ORDER BY latitude DESC
       )
FROM airports
WHERE timezone IN ( 'Asia/Irkutsk', 'Asia/Krasnoyarsk' )
ORDER BY timezone, rank;

airport_name   | city          | ltd   | timezone         | rank
---------------+---------------+-------+------------------+------
Усть-Илимск    | Усть-Илимск   | 58.14 | Asia/Irkutsk     | 1
Усть-Кут       | Усть-Кут      | 56.85 | Asia/Irkutsk     | 2
Братск         | Братск        | 56.37 | Asia/Irkutsk     | 3
Иркутск        | Иркутск       | 52.27 | Asia/Irkutsk     | 4
...
Абакан         | Абакан        | 53.74 | Asia/Krasnoyarsk | 5
Барнаул        | Барнаул       | 53.36 | Asia/Krasnoyarsk | 6
Горно-Алтайск  | Горно-Алтайск | 51.97 | Asia/Krasnoyarsk | 7
Кызыл          | Кызыл         | 51.67 | Asia/Krasnoyarsk | 8
(13 строк)

Дайте реакций, как обычно 🤑