Оконные функции (Window Functions) в PostgreSQL — это продвинутый инструмент для выполнения вычислений над группами строк, связанных с текущей записью. В отличие от агрегатных функций (например, SUM(), AVG()), которые сворачивают множество строк в одну, оконные функции сохраняют исходную детализацию данных, добавляя к каждой строке результат вычислений. Это делает их незаменимыми для аналитических задач: ранжирования, расчета накопительных итогов, сравнения значений и многого другого.
Основы оконных функций
Ключевым элементом оконных функций является конструкция OVER(), которая определяет «окно» — набор строк, используемых для вычислений. Внутри OVER() можно задать:
1. PARTITION BY — разбивает данные на группы (аналогично GROUP BY, но без свертки).
2. ORDER BY — сортирует строки внутри окна.
3. Фрейм (ROWS/RANGE) — задает диапазон строк относительно текущей записи.
Пример структуры:
Типы оконных функций
1. Агрегатные функции как оконные
Любая агрегатная функция (например, SUM(), AVG()) может быть использована с OVER().
Здесь cumulative_sum покажет нарастающий итог выручки по дням.
2. Специализированные оконные функции
- ROW_NUMBER() — порядковый номер строки в окне.
- RANK() и DENSE_RANK() — ранжирование с пропусками и без.
- LEAD() и LAG() — доступ к данным следующей или предыдущей строки.
- FIRST_VALUE() и LAST_VALUE() — первое и последнее значение в окне.
Пример ранжирования:
Ранжирование товаров по убыванию цены.
Фреймы: контроль диапазона
Фрейм определяет, какие строки внутри окна участвуют в вычислениях. Используются ключевые слова:
- ROWS BETWEEN N PRECEDING AND M FOLLOWING — физические строки.
- RANGE BETWEEN ... — логические интервалы (например, по значениям).
Пример скользящего среднего:
Расчет среднего выручки за последние 3 дня (текущий + два предыдущих).
Практические кейсы
1. Сравнение с предыдущим периодом
2. Процент от итога группы
3. Удаление дубликатов
Использование ROW_NUMBER() для выборки уникальных записей:
Оптимизация производительности
- Индексы: Используйте индексы для столбцов в PARTITION BY и ORDER BY.
- Фреймы: Узкие фреймы (например, ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) работают быстрее.
- Избегайте избыточных сортировок: Если несколько окон используют одинаковый ORDER BY, вынесите его в отдельное выражение WINDOW.
Заключение
Оконные функции в PostgreSQL открывают возможности для сложной аналитики прямо на уровне СУБД, уменьшая необходимость обработки данных на стороне приложения. Они идеальны для задач, требующих сохранения контекста каждой строки: ранжирования, расчетов скользящих показателей, сравнений и трендов. Освоив этот инструмент, вы сможете писать более эффективные и лаконичные запросы, раскрывая полный потенциал ваших данных.
Для углубленного изучения обратитесь к документации PostgreSQL и экспериментируйте с примерами!
Подписывайтесь:
Телеграм https://t.me/lets_go_code
Канал "Просто о программировании" https://dzen.ru/lets_go_code