Типові помилки при використанні віконних функцій

Тепер настав час поговорити про складнощі, які можуть виникнути при роботі з віконними функціями. Як завжди, в програмуванні (і в житті) краще вчитись на чужих помилках. Будемо розбирати типові помилки, якими грішать новачки і навіть досвідчені розробники, і вчитись їх уникати.

Помилка №1: Неправильне використання PARTITION BY

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

Припустимо, у нас є таблиця sales, яка містить дані про продажі:

Ти хочеш знайти накопичувальну суму (SUM()) продажів по місяцях для кожного регіону. Можна написати такий запит:

SELECT
    region,
    month,
    SUM(total) OVER (ORDER BY month) AS running_total
FROM 
    sales;

Результат:

На перший погляд здається, що все ок. Але результат явно не відповідає очікуванням, бо накопичувальна сума рахується не по регіонах, а для всіх рядків одразу. Проблема в тому, що ми забули додати PARTITION BY region.

Виправлений код:

SELECT 
    region,
    month,
    SUM(total) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM 
    sales;

Результат:

Тепер все працює правильно: дані групуються по регіонах, а накопичувальна сума рахується окремо для кожного регіону.

Помилка №2: Некоректний порядок в ORDER BY

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

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

SELECT
    month,
    total,
    SUM(total) OVER (ORDER BY month DESC) AS running_total
FROM 
    sales;

Результат:

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

Виправлення: перепиши запит, щоб використовувати правильний порядок ORDER BY:

SELECT
    month,
    total,
    SUM(total) OVER (ORDER BY month ASC) AS running_total
FROM 
    sales;

Помилка №3: Використання віконних функцій без індексів

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

Приклад: у нас є таблиця large_sales з мільйонами рядків, і ми хочемо обчислити ранги продажів:

SELECT
    id,
    total,
    RANK() OVER (ORDER BY total DESC) AS rank
FROM 
    large_sales;

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

Виправлення: додай індекс на стовпець, який використовується в ORDER BY:

CREATE INDEX idx_total ON large_sales(total DESC);

Тепер запит буде виконуватись значно швидше.

Помилка №4: Нерозуміння вікна, заданого ROWS або RANGE

При використанні ROWS і RANGE важливо розуміти, як вони обчислюють вікно рядків. Неправильне розуміння цих ключових слів може призвести до неочікуваних результатів.

Приклад: ти хочеш обчислити ковзаюче середнє продажів за поточний місяць і два попередніх:

SELECT
    month,
    AVG(total) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

Якщо замість ROWS ти вкажеш RANGE:

SELECT
    month,
    AVG(total) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

Результат буде іншим, бо RANGE працює з діапазонами значень, а не з конкретною кількістю рядків.

Помилка №5: Надмірне використання віконних функцій

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

Приклад:

SELECT 
    id,
    total,
    SUM(total) OVER (PARTITION BY region) AS region_total,
    SUM(total) OVER (PARTITION BY region) / COUNT(total) OVER (PARTITION BY region) AS region_avg
FROM 
    sales;

Тут SUM(total) і COUNT(total) обчислюються кілька разів для кожного рядка.

Виправлення: скороти запит з використанням підзапитів або CTE:

WITH cte_region_totals AS (
    SELECT 
        region,
        SUM(total) AS region_total,
        COUNT(total) AS region_count
    FROM 
        sales
    GROUP BY 
        region
)
SELECT 
    s.id,
    s.total,
    t.region_total,
    t.region_total / t.region_count AS region_avg
FROM 
    sales s
JOIN 
    cte_region_totals t ON s.region = t.region;

Поради щодо уникнення помилок

Перевіряй PARTITION BY і ORDER BY: завжди уточнюй, чи правильно задано вікно.

Індексуй дані: особливо якщо використовуєш сортування (ORDER BY) або фільтрацію.

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

Дивись на план виконання: використовуй EXPLAIN і EXPLAIN ANALYZE, щоб зрозуміти, як запит обробляється PostgreSQL.

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