Основні віконні функції: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

У минулій лекції ми зрозуміли, навіщо потрібні віконні функції. Тепер подивимось на конкретні функції та їх результати. Деталі синтаксису розберемо у наступній лекції.

Функція ROW_NUMBER()

Функція ROW_NUMBER() повертає унікальний номер для кожного рядка в межах вікна. Це просто нумерація рядків у порядку, який визначено в ORDER BY.

Синтаксис:

ROW_NUMBER() OVER ([PARTITION BY column] ORDER BY column)

Де:

• PARTITION BY column (опціонально): ділить дані на групи. Якщо пропустити, нумерація буде глобальною по всьому набору.
• ORDER BY column: визначає порядок рядків для нумерації.

Приклад. Нумерація рядків у таблиці

Розглянемо таблицю students, яка містить інфу про студентів та їх оцінки.

SELECT * FROM students;

Тепер давай пронумеруємо рядки у порядку спадання їх оцінок (score):

SELECT
    name, 
    score, 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

Результат:

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

Це проста, але потужна операція — додати номер рядка у результат запиту. У класичному SELECT це зробити неможливо без віконних функцій.

Функція RANK()

Функція RANK() дуже схожа на ROW_NUMBER(), але вона враховує однакові значення. Якщо рядки мають однакове значення у сортуванні, вони отримають однаковий ранг, а наступний пропуститься.

Синтаксис:

RANK() OVER ([PARTITION BY column] ORDER BY column)

Приклад. Ранжування студентів за їх оцінками

Давай використаємо RANK() для тих самих даних:

SELECT
    name, 
    score, 
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

Результат:

Тут рядки з однаковими значеннями (95 і 87) отримали однаковий ранг, а наступні ранги пропущені.

Функція DENSE_RANK()

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

Синтаксис:

DENSE_RANK() OVER ([PARTITION BY column] ORDER BY column)

Приклад. Щільне ранжування

Використаємо DENSE_RANK() для тих самих даних:

SELECT
    name, 
    score, 
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

Результат:

Тут, на відміну від RANK(), ростуть тільки значення рангів без пропусків.

Функція NTILE()

Функція NTILE() ділить рядки на рівні групи (квантілі) і присвоює кожному рядку номер групи.

Синтаксис:

NTILE(n) OVER ([PARTITION BY column] ORDER BY column)

• n: кількість груп, на які треба поділити дані.

Приклад. Ділимо студентів на 3 групи

Давай розділимо студентів на 3 групи за спаданням оцінок:

SELECT
    name, 
    score, 
    NTILE(3) OVER (ORDER BY score DESC) AS group_num
FROM students;

Результат:

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

Коли яку функцію юзати?

• ROW_NUMBER(): для унікальної нумерації рядків у порядку сортування.
• RANK(): для ранжування з урахуванням однакових значень і пропуском наступного рангу.
• DENSE_RANK(): для ранжування з урахуванням однакових значень без пропуску рангів.
• NTILE(): для рівномірного поділу рядків на групи.

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