Функції вікон SQLExplained

27 of 27 rows

Функція / речення↕	Що повертає↕	Приклад↕
Ранжування
⤢ROW_NUMBER()	Унікальне послідовне ціле число для кожного рядка в межах вікна.	ROW_NUMBER() OVER (ORDER BY total DESC)
⤢RANK()	Ранг із пропусками; рівні ділять один ранг, а наступні номери пропускаються.	RANK() OVER (ORDER BY total DESC)
⤢DENSE_RANK()	Ранг без пропусків; рівні ділять один ранг, і нічого не пропускається.	DENSE_RANK() OVER (ORDER BY total DESC)
⤢NTILE(n)	Розбиває впорядковане вікно на n приблизно рівних кошиків.	NTILE(4) OVER (ORDER BY total DESC)
Погляд на сусідів
⤢LAG(expr, n, default)	Значення на n рядків назад; якщо такого рядка немає, підставляється значення за замовчуванням.	LAG(total, 1, 0) OVER (ORDER BY created_at)
⤢LEAD(expr, n, default)	Значення на n рядків уперед; якщо такого рядка немає, підставляється значення за замовчуванням.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Агрегація по вікну
⤢SUM() OVER	Нарощувана або за розділами сума без згортання рядків.	SUM(total) OVER (ORDER BY created_at)
⤢AVG() OVER	Нарощуване або за розділами середнє.	AVG(total) OVER (PARTITION BY user_id)
⤢COUNT() OVER	Нарощувана або за розділами кількість рядків.	COUNT(*) OVER (PARTITION BY user_id)
⤢MIN() OVER	Найменше значення по вікну або розділу.	MIN(total) OVER (PARTITION BY user_id)
⤢MAX() OVER	Найбільше значення по вікну або розділу.	MAX(total) OVER (PARTITION BY user_id)
⤢Running total	SUM з ORDER BY і кадром, відкритим від початку; кожен рядок сумує все до самого себе.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
⤢Moving average	AVG по кадру з n рядків до і після поточного рядка.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Перше, останнє та процентилі
⤢FIRST_VALUE()	Перше значення в порядку сортування вікна.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
⤢LAST_VALUE()	Останнє значення в кадрі; без UNBOUNDED FOLLOWING кадр зупиняється на поточному рядку.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
⤢NTH_VALUE(expr, n)	Значення в n-му рядку порядку сортування вікна.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
⤢PERCENT_RANK()	Відносний ранг рядка, від 0 до 1.	PERCENT_RANK() OVER (ORDER BY total)
⤢CUME_DIST()	Частка рядків до поточного рядка включно, від 1/n до 1.	CUME_DIST() OVER (ORDER BY total)
Речення OVER
⤢OVER (...)	Визначає вікно, яке читає кожна функція.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
⤢PARTITION BY	Перезапускає вікно для кожної групи рядків.	PARTITION BY user_id
⤢ORDER BY	Упорядковує рядки всередині кожного розділу.	ORDER BY created_at
⤢ROWS BETWEEN	Обмежує ковзний кадр, напр. від попередніх рядків до наступних.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
⤢RANGE BETWEEN	Обмежує кадр значеннями, а не позиціями рядків; CURRENT ROW охоплює рівних сусідів, і це типовий кадр за наявності ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
⤢GROUPS BETWEEN	Обмежує кадр групами рівних рядків із ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
⤢EXCLUDE CURRENT ROW	Видаляє поточний рядок із кадру; натомість EXCLUDE TIES видаляє його рівних сусідів.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
⤢WINDOW	Дає вікну ім'я один раз на запит, щоб кілька речень OVER могли його повторно використати.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
⤢FILTER (WHERE ...)	Звужує рядки, які читає агрегатна функція вікна, до застосування вікна.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)