Основні віконні функції для аналітики

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

Віконні функції — це функції SQL, які працюють з підмножиною рядків (назвемо її "вікном") з набору даних. На відміну від агрегатних функцій, які об'єднують рядки в одну (наприклад, SUM() чи AVG()), віконні функції залишають рядки як є, додаючи до них обчислені значення.

Відмінність від агрегатних функцій

Агрегатні функції "стискають" дані, групуючи рядки:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Результат: всього кілька рядків, за кількістю відділів.

Порівняй з віконною функцією — тут рядки залишаються на місці, але додається нове поле, що містить, наприклад, ROW_NUMBER():

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

Тут ти отримаєш всі ті ж рядки, але з додатковою колонкою rank_within_department, де кожному співробітнику присвоєно номер у межах свого відділу.

Основні віконні функції

Синтаксис OVER()

Найважливіша частина кожної віконної функції — це магічне слово OVER(). Воно визначає, з яким саме "вікном" даних буде працювати ця функція. Всередині OVER() можна задавати розділення на групи (PARTITION BY) і/або порядок сортування (ORDER BY).

Загальний синтаксис:

<віконна_функція>() OVER (
   [PARTITION BY <група>] 
   [ORDER BY <порядок>]
)

Компоненти:

• PARTITION BY: Ділимо рядки на групи. Наприклад, "розділи дані по відділах".
• ORDER BY: Вказуємо порядок обробки рядків. Наприклад, "сортуй співробітників за зарплатою від більшої до меншої".

Функція ROW_NUMBER()

Функція ROW_NUMBER() нумерує рядки, починаючи з 1, всередині вказаного "вікна". Іноді це корисно для простого створення номера рядка у тимчасовій таблиці або для визначення порядкового місця запису.

Приклад. Таблиця sales (продажі):

Запит:

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Результат:

Як це працює:

1. Дані діляться на групи по product_category.
2. Кожна група сортується по revenue (у порядку спадання).
3. Рядки всередині кожної групи отримують порядковий номер.

Функція RANK()

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

Приклад:

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Результат:

Функція DENSE_RANK()

DENSE_RANK() схожа на RANK(), за винятком одного моменту: вона не пропускає номери рангів, якщо є співпадаючі значення.

Приклад. Додаємо продаж з таким же доходом:

Запит:

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Результат:

Приклади використання: нумерація рядків

Завдання: нумерувати всі замовлення у таблиці orders, відсортовані за датою.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Результат: отримуємо список замовлень з нумерацією у порядку їх виконання.

Приклади використання: топ-3 продавців у кожній категорії

Завдання: визначити трьох кращих продавців у кожній категорії товарів.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

Приклади використання: виявлення однакових показників

Завдання: визначити, чи є продавці з однаковим доходом у кожній категорії.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Тепер можна побачити ранги, де "залипають" однакові значення.