Віконні функції

Що таке віконні функції і чим вони відрізняються від GROUP BY?

Віконна функція рахує значення по групі пов'язаних рядків («вікну»), але, на відміну від GROUP BY, не схлопує рядки: кожен рядок лишається в результаті й отримує додатковий стовпець.

Синтаксис: функція() OVER (PARTITION BY … ORDER BY …).

• PARTITION BY — на які групи ділити (як GROUP BY, але без схлопування).
• ORDER BY — порядок усередині групи (для рангів і накопичувальних сум).

-- кожна транзакція + загальна сума по її рахунку поруч
SELECT id, account_id, amount,
       SUM(amount) OVER (PARTITION BY account_id) AS account_total
FROM transactions;

Чим відрізняються ROW_NUMBER, RANK і DENSE_RANK?

Усі три нумерують рядки в порядку ORDER BY. Різниця — в однакових значеннях:

• ROW_NUMBER — завжди унікальні номери; серед однакових порядок довільний.
• RANK — однакові отримують однаковий ранг, далі пропуск.
• DENSE_RANK — однаковий ранг без пропусків.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

Як знайти топ-N записів у кожній групі?

Пронумерувати рядки всередині кожної групи віконною функцією, а потім відфільтрувати за номером. Фільтрувати віконну функцію напряму у WHERE не можна — вона обчислюється пізніше, тому потрібен CTE або підзапит.

-- 3 найбільші транзакції кожного рахунку
WITH ranked AS (
  SELECT id, account_id, amount,
         ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY amount DESC) AS rn
  FROM transactions
  WHERE status = 'completed'
)
SELECT account_id, id, amount
FROM ranked
WHERE rn <= 3
ORDER BY account_id, rn;

Що роблять LAG і LEAD?

LAG(col) повертає значення з попереднього рядка у вікні, LEAD(col) — з наступного. Корисно для порівняння з попереднім станом: різниця між подіями, інтервали, зміни статусу.

-- інтервал між послідовними транзакціями рахунку
SELECT account_id, id, created_at,
       created_at - LAG(created_at) OVER (PARTITION BY account_id ORDER BY created_at) AS gap
FROM transactions;

-- підозріло: кілька однакових транзакцій менш ніж за 5 секунд (подвійна відправка?)
WITH g AS (
  SELECT id, account_id, amount, created_at,
         LAG(amount)     OVER w AS prev_amount,
         LAG(created_at) OVER w AS prev_at
  FROM transactions
  WINDOW w AS (PARTITION BY account_id ORDER BY created_at)
)
SELECT * FROM g
WHERE amount = prev_amount AND created_at - prev_at < INTERVAL '5 seconds';

Як порахувати накопичувальну суму (running total)?

SUM() OVER (… ORDER BY …) додає значення від першого рядка вікна до поточного. Так з історії транзакцій відтворюють баланс на кожен момент часу.

SELECT account_id, id, created_at, type, amount,
       SUM(CASE WHEN type = 'deposit' THEN amount ELSE -amount END)
         OVER (PARTITION BY account_id ORDER BY created_at, id) AS running_balance
FROM transactions
WHERE status = 'completed'
ORDER BY account_id, created_at, id;