🔒Открыть пост
и посмотреть медиа
и посмотреть медиа
Ранжирующие функции в SQL
Памятка по тому, какие бывают ранжирующие функции:
▶️Row_number()
Присваивает уникальный номер каждой строке в результирующем наборе (начиная с 1). Если значения совпадают, номера всё равно будут разными. По сути обычная нумерация строк по порядку (в зависимости от заданных условий):
SELECT user_id, name, age, email,
row_number() OVER (ORDER BY user_id) AS r
FROM users
▶️Rank()
Присваивает ранг с пропусками: если значения одинаковые, они получают один ранг, а следующий ранг пропускается.
Например, если у нас будут идти user_id = [1, 1, 2, 2, 2, 3, 4], то rank присвоит значения [1, 1, 3, 3, 3, 6, 7]
SELECT user_id, name, age, email,
rank() OVER (ORDER BY user_id) AS r
FROM users
▶️Dense_rank()
Присваивает ранг без пропусков: если значения одинаковые, они получают один ранг, но следующий ранг идёт без разрыва.
То есть при том же наборе user_id = [1, 1, 2, 2, 2, 3, 4] dense_rank = [1, 1, 2, 2, 2, 3, 4]
SELECT user_id, name, age, email,
dense_rank() OVER (ORDER BY user_id) AS r
FROM users
▶️Ntile(n)
Разделяет данные на n примерно равных групп. Каждая группа получает свой повторяющийся ранг.
SELECT user_id, name, age, email,
ntile(3) OVER (ORDER BY user_id) AS n
FROM users
▶️FIRST_VALUE() и LAST_VALUE()
Возвращают первое и последнее значение в выборке. Учитывает и применяемую сортировку. И если с first_value всё понятно, то для last_value желательно использовать условие внутри OVER: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Это условие позволит использовать last_value по аналогии с first_value, а именно сначала получить выборку и уже из полученной выборки выбрать первое/последнее значение.
SELECT user_id, name, age, email,
first_value(user_id) OVER (ORDER BY user_id) AS fv,
last_value(user_id) OVER (ORDER BY user_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM users
▶️LAG() и LEAD()
LAG() возвращает значение из предыдущей строки. LEAD() возвращает значение из следующей строки. Второй аргумент функции - цифра - задается как величина шага: сколько строк нужно пройти вперед/назад, чтобы вернуть значение.
SELECT user_id, name, age, email,
lag(user_id, 1) OVER (ORDER BY user_id) AS prev,
lead(user_id, 1) OVER (ORDER BY user_id) AS next
FROM users