Віконністандарт SQL

NTH_VALUE

Бере значення з N-го рядка вікна: другого, третього…

Синтаксис

NTH_VALUE(<вираз>, <номер>) OVER (
  ORDER BY <критерій>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

NTH_VALUE(<вираз>, <номер>) OVER (
  PARTITION BY <група> ORDER BY <критерій>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Як FIRST_VALUE, але для будь-якого номера: NTH_VALUE(salary, 2) — друга зарплата у вікні. Рядків не меншає, кожен отримує значення зі свого вікна. Якщо у вікні ще нема N-го рядка, результат — NULL.

Дані для прикладів

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

Ці таблиці вже створено в пісочниці — приклади нижче працюють із ними. Хочеш повторити у своїй базі — скопіюй SQL.

Друга зарплата поруч з кожною

SELECT name, salary,

NTH_VALUE(salary, 2) OVER (

ORDER BY salary DESC

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) AS second_salary

FROM employees;

name	salary	second_salary
Olivia	1350	1200
Emma	1200	1200
Sophia	1200	1200
Liam	1000	1200
Noah	950	1200

Без рамки — NULL на початку

SELECT name, salary,

NTH_VALUE(name, 2) OVER (ORDER BY salary DESC) AS second_so_far

FROM employees;

name	salary	second_so_far
Olivia	1350	NULL
Emma	1200	Emma
Sophia	1200	Emma
Liam	1000	Emma
Noah	950	Emma

Без рамки вікно закінчується на поточному рядку (разом із рівними йому). У Olivia у вікні лише вона сама, тож другого рядка ще нема.

Друге ім'я в кожному відділіСкладніше

SELECT name, department, salary,

NTH_VALUE(name, 2) OVER (

PARTITION BY department ORDER BY salary DESC

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) AS second_in_department

FROM employees;

name	department	salary	second_in_department
Emma	backend	1200	Liam
Liam	backend	1000	Liam
Sophia	design	1200	NULL
Olivia	frontend	1350	Noah
Noah	frontend	950	Noah

У design одна людина, тож другого нема — NULL.

Зверни увагу

Без рамки ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING перші рядки отримують NULL: вікно закінчується на поточному рядку, і N-го в ньому ще нема.

Друга за величиною зарплата — не завжди NTH_VALUE(salary, 2): якщо дві найбільші рівні, другий рядок дасть те саме число, що й перший. Друге різне значення знаходить DENSE_RANK.

Використати результат у WHERE того самого запиту не можна — спершу порахуй його в підзапиті, а фільтруй уже зовні.

Схожі функції

FIRST_VALUE DENSE_RANK LEAD