Урок 6: Віконні Функції — Дамо Твоїм Запитам Панорамний Вид!

Lesson 6

Урок 6: Віконні Функції — Дамо Твоїм Запитам Панорамний Вид!

Привіт знову, досліднику SQL! Сьогодні ми зануримося у світ віконних функцій — це як мати ексклюзивний VIP-пентхаус у світі SQL. Якщо ти коли-небудь мріяв додати трішки шику до свого аналізу даних, віконні функції покажуть тобі справжній вигляд. Уяви їх як телескопи: вони дозволяють розглядати кожен рядок, але при цьому бачити і всю картину загалом.

Що Таке Віконні Функції?

Віконні функції, також відомі як аналітичні функції, виконують обчислення для набору рядків таблиці, що якось пов’язані з поточним рядком. Це як порівнювати успіхи кожного лицаря, не втрачаючи при цьому загальний контекст усього королівства. Віконні функції допомагають тобі:

  • Визначати рейтинг лицарів на основі їхніх досягнень.
  • Обчислювати накопичувальні суми, середні значення або рухомі підсумки.
  • Знаходити відсотки та поточні підсумки для місій.

Синтаксис Віконної Функції

Віконні функції мають простий синтаксис, подібний до цього:

SELECT column_name,
       window_function(column_name) OVER (PARTITION BY column_name ORDER BY column_name)
FROM table_name;

Найважливіша частина віконної функції — це клаузула OVER. Вона говорить PostgreSQL, на яку “віконну” (або контекстну) частину таблиці слід звертати увагу під час обчислення.

RANK та DENSE_RANK: Бо Кожен Лицар Заслуговує на Титул

Почнемо з функцій рейтингу. Уяви, що в тебе є таблиця лицарів із кількістю виконаних місій, і ти хочеш побачити, хто справжній герой (або найбільш відважний приборкувач драконів):

SELECT imya, misyiyi_vykonano,
       RANK() OVER (ORDER BY misyiyi_vykonano DESC) AS rank
FROM lytsari;

Цей запит дасть рейтинг лицарям на основі кількості виконаних місій. Але будь уважний: RANK пропустить номери в разі рівності. Тобто, якщо два лицарі на першому місці, наступний буде третім, а не другим!

Хочеш, щоб кожен лицар відчув себе особливим? Використовуй DENSE_RANK:

SELECT imya, misyiyi_vykonano,
       DENSE_RANK() OVER (ORDER BY misyiyi_vykonano DESC) AS dense_rank
FROM lytsari;

Це трохи делікатніше — жодних пропущених рангів!

ROW_NUMBER: Бо Кожен Рядок Потребує Уваги

Якщо тобі потрібно лише просте нумерування для кожного рядка, ROW_NUMBER — твій найкращий друг:

SELECT imya, misyiyi_vykonano,
       ROW_NUMBER() OVER (ORDER BY misyiyi_vykonano DESC) AS nomer_ryadka
FROM lytsari;

Ідеально, коли ти хочеш, щоб у кожного лицаря був свій унікальний номер, незалежно від того, скільки місій вони виконали.

PARTITION BY: Організація Королівського Банкету

Тепер давай піднімемо рівень. PARTITION BY дозволяє групувати рядки у відокремлені вікна, як розсадити лицарів за королівствами на величезному банкеті. Скажімо, ти хочеш відсортувати лицарів у кожному королівстві окремо:

SELECT imya, korolivstvo, misyiyi_vykonano,
       RANK() OVER (PARTITION BY korolivstvo ORDER BY misyiyi_vykonano DESC) AS rank_v_korolivstvi
FROM lytsari;

Тепер кожен лицар має свій рейтинг у своєму королівстві, тож сер Галагет може почуватися номером один у Камелоті, навіть якщо сер Артур обігнав його загальною кількістю місій.

Накопичувальні Суми: Підраховуємо Золото у Скарбниці

Якщо ти хочеш бачити поточні підсумки, віконні функції впораються на ура. Припустимо, ми хочемо обчислити накопичувальну суму місій для лицарів, впорядковану за їхніми досягненнями:

SELECT imya, misyiyi_vykonano,
       SUM(misyiyi_vykonano) OVER (ORDER BY misyiyi_vykonano DESC) AS misyiyi_nakopychuvalno
FROM lytsari;

Це як складувати золоті монети у скарбницю: кожен внесок лицарів додається до купи, показуючи поточний підсумок.

Рухомі Середні: Круглий Стіл Обчислень

Рухомі середні — це зручно, коли ти хочеш згладити коливання. Знайдемо середню кількість місій для трьох лицарів:

SELECT imya, misyiyi_vykonano,
       AVG(misyiyi_vykonano) OVER (ORDER BY misyiyi_vykonano DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ruhoma_serednya
FROM lytsari;

Це обчислює середнє значення місій для кожного лицаря, враховуючи його місії та місії двох попередніх. Це як круглий стіл — кожен має своє слово!

LEAD та LAG: Заглянемо у Минуле й Майбутнє

Хочеш побачити, як лицар виконав свою попередню місію, або передбачити наступну? Використовуй LEAD і LAG, щоб подивитися на часову шкалу:

SELECT imya, misyiyi_vykonano,
       LAG(misyiyi_vykonano, 1) OVER (ORDER BY misyiyi_vykonano DESC) AS misyia_do,
       LEAD(misyiyi_vykonano, 1) OVER (ORDER BY misyiyi_vykonano DESC) AS misyia_pislya
FROM lytsari;

Це дозволяє порівнювати результати кожного лицаря з тим, хто перед ним, і тим, хто йде після нього — ідеально для виявлення тенденцій і аномалій.

Підведемо Підсумки: Велике Шоу Віконних Функцій!

Щоб підсумувати, ось запит, що використовує кілька віконних функцій для рейтингу, підсумку та аналізу:

WITH statystyka_lytsariv AS (
  SELECT imya, korolivstvo, misyiyi_vykonano,
         RANK() OVER (PARTITION BY korolivstvo ORDER BY misyiyi_vykonano DESC) AS rank_v_korolivstvi,
         SUM(misyiyi_vykonano) OVER (ORDER BY misyiyi_vykonano DESC) AS misyiyi_nakopychuvalno,
         LAG(misyiyi_vykonano, 1) OVER (ORDER BY misyiyi_vykonano DESC) AS misyia_do
  FROM lytsari
)
SELECT * FROM statystyka_lytsariv;

Ця витончена структура показує, як віконні функції можуть перетворити навіть найскладніші запити на просту задачу.

Що Ми Сьогодні Вивчили?

Сьогодні ми дослідили світ віконних функцій:

  • Функції Ранжування: RANK, DENSE_RANK і ROW_NUMBER — для того, щоб кожен лицар отримав своє заслужене місце.
  • Накопичувальні Функції: SUM, AVG і COUNT — для підрахунку, хто з лицарів найбільше накоїв (у хорошому сенсі).
  • LEAD і LAG: дозволяють зазирнути в минуле та передбачити майбутнє рядків — наче заглядати у кришталеву кулю.

Що Далі?

На наступному уроці ми поринемо у світ Повнотекстового Пошуку в PostgreSQL — саме час дати твоїм запитам голос і дозволити даним говорити за себе!

А тепер, уперед! Нехай твої віконні функції завжди пропонують найкращий вид на результати!