Оконные функции

Одна из интересных фич в sql. На джуниор ее редко спрашивают, и обычно ожидают понимания, что они существуют и как работает ROW_NUMBER().

Собственно как они работают на примере ROW_NUMBER(). Есть выражение в select:

`ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date)as number_of_hiring,

возвращает каким по счету сотрудник был нанят в этот отдел. Группирует по отделу, внутри группы сортирует по дате найма и внутри отсортированной группы возвращает номер строки.

А теперь по сложнее. Еще функции: RANK() - присваивает ранг с пропусками при одинаковых значениях DENSE_RANK() - присваивает ранг без пропусков LAG() - доступ к предыдущей строке LEAD() - доступ к следующей строке FIRST_VALUE() и LAST_VALUE()

Еще ключевые слова: ROWS - определяет окно по физическим строкам (абсолютное позиционирование) RANGE - определяет окно по логическим значениям (относительное позиционирование по значениям) UNBOUNDED PRECEDING - от начала раздела UNBOUNDED FOLLOWING - до конца раздела CURRENT ROW - текущая строка

И пример, по ссылке можно перейти и поиграться интерактивно https://www.db-fiddle.com/f/vmN2zRWvdr57MdocN2stoZ/0

`SELECT department_id, employee_id, salary, hire_date, -- каким по счету наняли этого сотрудника в отдел, на джуниор надо только это и то далеко не всегда ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date)as number_of_hiring,

-- Средняя зарплата в отделе AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,

-- Накопительная зарплата по дате приема (все предыдущие в отделе) SUM(salary) OVER ( PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_dept_salary,

-- Разница с предыдущей зарплатой любого сотрудника в отделе (по дате приема) salary - LAG(salary, 1) OVER ( PARTITION BY department_id ORDER BY hire_date ) AS diff_with_previous,

-- Средняя зарплата среди 3 ближайших по времени сотрудников (предыдущий, текущий, следующий) AVG(salary) OVER ( PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS moving_avg_salary FROM employees;``