Налаштування віконного фрейму за допомогою ROWS і RANGE

Коли ти юзаєш window functions, виникає питання: "Скільки рядків всередині вікна бере участь у розрахунку значення для поточного рядка?" Відповідь на це залежить від віконного фрейму.

Віконний фрейм — це діапазон рядків, який використовується для обчислення результату window function. Цей діапазон будується на основі поточного рядка, а також додаткових умов, заданих через ROWS або RANGE.

Простий приклад: рахуючи накопичувальну суму, ти можеш вказати:

• Враховувати тільки поточний рядок.
• Враховувати поточний рядок і всі рядки вище.
• Враховувати поточний рядок і фіксовану кількість рядків вище/нижче.

Саме ROWS і RANGE керують тим, які рядки потраплять у віконний фрейм.

Використання ROWS

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

Синтаксис

window_function OVER (
    ORDER BY стовпець
    ROWS BETWEEN початок AND кінець
)

Ключові вирази:

• CURRENT ROW — поточний рядок.
• число PRECEDING — певна кількість рядків вище поточного.
• число FOLLOWING — певна кількість рядків нижче поточного.
• UNBOUNDED PRECEDING — від початку вікна.
• UNBOUNDED FOLLOWING — до кінця вікна.

Приклад: накопичувальна сума для поточного рядка і 2 попередніх

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

Пояснення:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW означає: візьми поточний рядок і два рядки вище нього.
• Накопичувальна сума буде рахуватись тільки для цих трьох рядків.

Результат:

Приклад: аналіз "ковзаючого вікна" з фіксованою кількістю рядків

Задача: порахувати середню зарплату для поточного рядка і двох наступних.

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

Результат:

Використання RANGE

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

Синтаксис

window_function OVER (
    ORDER BY стовпець
    RANGE BETWEEN початок AND кінець
)

Приклад: накопичувальна сума по діапазону значень

Задача: порахувати накопичувальну суму для рядків, де зарплата відрізняється від поточної не більше ніж на 2000.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

Пояснення:

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING означає: візьми рядки, де значення salary знаходиться у діапазоні ±2000 від поточного рядка.

Результат:

Порівняння ROWS і RANGE

• ROWS працює з реальними рядками і їх кількістю. Він не залежить від значень.
• RANGE працює з логічним діапазоном значень, заданим для стовпця з ORDER BY.

Для порівняння наведемо приклад. Припустимо, у нас є таблиця sales з даними:

Порівняємо запити:

ROWS:

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

Результат:

Тут кожен рядок система додає до суми по мірі їх реальної появи.

RANGE:

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

Результат:

Тут рядки 1 і 2 об'єдналися, бо їх amount = 100. RANGE враховує повторювані значення у стовпці amount.

Приклади реальних задач

1. Розрахунок приросту доходу

Задача: порахувати зміну доходу порівняно з попереднім рядком.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. Порівняння поточного рядка із середнім у групі

Задача: для кожного відділу порахувати різницю зарплати співробітника із середньою зарплатою по відділу.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

Помилки при використанні ROWS і RANGE

Неправильно вказаний порядок рядків (ORDER BY): Якщо не вказати порядок сортування, PostgreSQL видасть помилку, бо не зможе визначити поточний рядок.

Змішування підходів ROWS і RANGE в одній задачі: Обирай підхід залежно від твоїх даних. ROWS підходить для задач із фіксованою кількістю рядків, а RANGE — для діапазонів значень.

Пропуск повторюваних значень у RANGE: Пам'ятай, що RANGE враховує всі повторювані значення, що може суттєво змінити результат.