Віконна функція (SQL)

У Вікіпедії є статті про інші значення цього терміна: Віконна функція.

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

Синтаксис

Синтаксис віконної функції виглядає так^[1]:

<віконна_функція>() OVER (
    [PARTITION BY вираз_1 [, вираз_2, ...]]
    [ORDER BY вираз_порядкування [ASC|DESC] [, ...]]
    [рамкова_умова]
)

Тут

• OVER(…) — обов'язкова частина, яка задає правила визначення вікна (набору рядків) для кожного рядка вхідної таблиці;^[2]
• PARTITION BY задає розбиття рядків на підгрупи (як GROUP BY, але без агрегації в один рядок);^[3]
• ORDER BY задає порядок всередині вікна;^[4]
• рамкова_умова — умова, яку має задовольняти рядок вхідної таблиці, щоб входити до вікна певного рядка.^[5]

Типи віконних функцій

Агрегатні функції як віконні

Кожну агрегатну функцію можна перетворити на віконну, бо для них вікна нічим не відрізняються від груп, утворених GROUP BY.^[6]

Функції впорядкування

Функція впорядкування (англ. ranking function^[7] або англ. ordinal function) повертає певний порядковий номер рядка у вікні. Щоб отримати змістовний результат обчислення такої функції, потрібно задати певний порядок у вікні через ORDER BY.^[8]

Перелік деякий функцій впорядкування:

• ROW_NUMBER() повертає унікальний порядковий номер рядка у вікні, починаючи з 1, відповідно до сортування, заданого в ORDER BY.^[8]
• RANK() повертає номер рядка за заданим порядком у вікні, при цьому рядки з однаковими значеннями по колонках, заданих в ORDER BY, отримують однаковий ранг, а наступний збільшується на кількість цих значень.^[8][9]
• DENSE_RANK() повертає номер рядка за заданим порядком у вікні, при цьому рядки з однаковими значеннями по колонках, заданих в ORDER BY, отримують однаковий ранг, а наступний збільшується на одиницю.^[10][11]
• PERCENT_RANK() повертає відносний ранг рядка у вікні у вигляді числа від 0 до 1. Якщо вікно містить n рядків при n > 1, а певний рядок має ранг r, то його відносний ранг дорівнює ${\displaystyle {\frac {r-1}{n-1}}.}$ Якщо вікно містить один рядок, то відносний ранг його єдиного рядка дорівнює NULL.^[12][13]
• CUME_DIST() (cumulative distribution — кумулятивний розподіл) показує, яка частка рядків у вікні менша або дорівнює певному рядку, відповідно до сортування, заданого в ORDER BY. Якщо вікно містить n рядків, а певний рядок за заданим порядком у вікні йде перед або дорівнює p рядкам, то для нього значення цієї віконної функції дорівнює ${\displaystyle {\frac {p}{n}}.}$^[12][13]
• NTILE(n) розбиває вікно на n груп, які містять приблизно однакову кількість рядків, присвоюючи кожній групі унікальний номер від 1 до n, і для кожного рядка повертає номер його групи.^[14]