Какие оконные функции известны
Оконные функции предоставляют мощные возможности для выполнения сложных расчетов над подмножеством строк, которые определены в рамках "окна", относящегося к текущей строке в результате запроса. Вот некоторые из основных и часто используемых оконных функций:
1. Функции ранжирования
Эти функции присваивают ранг каждой строке в рамках определенного раздела данных.
- ROW_NUMBER(): Возвращает уникальный номер каждой строки, начиная с 1, в порядке, указанном в `ORDER BY` внутри оконной спецификации.
- RANK(): Присваивает ранг каждой строке, одинаковые значения получают одинаковый ранг, при этом следующий за теми же значениями ранг будет увеличен на количество повторений.
- DENSE_RANK(): Похоже на `RANK`, но ранги увеличиваются на 1 между группами и не пропускаются значения (например, 1, 2, 2, 3).
2. Функции смещения
Эти функции предоставляют доступ к данным в строках, находящихся на определенном расстоянии от текущей строки.
- LAG(value, offset, default): Возвращает значение столбца из строки, находящейся на `offset` позиций перед текущей строкой. Если такой строки нет, возвращается `default`.
- LEAD(value, offset, default): Возвращает значение столбца из строки, находящейся на `offset` позиций после текущей строки. Если такой строки нет, возвращается `default`.
3. Статистические функции
Эти функции используются для вычисления статистического ранга относительно других строк в окне.
- PERCENT_RANK(): Возвращает процентное положение текущей строки в группе, где самая первая строка имеет значение 0.
- CUME_DIST(): Возвращает кумулятивный дистрибутив текущей строки в группе.
4. Агрегатные функции
Могут быть применены в контексте оконных функций для выполнения вычислений в пределах определенного окна.
- SUM(value): Возвращает сумму значений столбца в окне.
- AVG(value): Возвращает среднее значение столбца в окне.
- MIN(value) и MAX(value): Возвращают минимальное и максимальное значение столбца в окне.
- COUNT(value): Возвращает количество значений в окне.
```sql
SELECT
employee_id,
department_id,
salary,
AVG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS avg_salary
FROM
employees;
```Этот запрос вычисляет среднюю зарплату в пределах каждого отдела, используя `AVG` как оконную функцию.
Оконные функции позволяют проводить сложные вычисления над группой строк (окном), которые связаны с текущей строкой. Это очень полезно для аналитики, где нужно сохранить оригинальные данные и при этом получить агрегированную информацию, такую как средняя зарплата по отделу, ранжирование данных или смещение данных для временных рядов.
May 22, 2024, easyoffer
