Оконные функции (window functions) — это фича, позволяющая производить манипуляции между строками, возвращаемыми одним SQL-запросом. Похоже на GROUP BY , но, в отличие от него, строки не объединяются в одну. Есть задачи, в которых оконные функции исключительно удобны. Например, когда нужно показать некие значения (выручку, посещаемость) за месяц, и рядом с ними — насколько это больше или меньше по сравнению с прошлым месяцем.
Для начала, выведем числа от 1 до 3:
x
===
1
2
3
Теперь перепишем запрос вот таким странным образом:
FROM generate_series ( 1 , 3 ) AS x;
x | array_agg
==============
1 | { 1 , 2 , 3 }
2 | { 1 , 2 , 3 }
3 | { 1 , 2 , 3 }
Каждый раз, когда вы видите синтаксис OVER ( ... ) , можете быть уверены — запрос использует оконные функции. В данном примере используется простой OVER ( ) без указания чего-либо в скобочках. Поэтому функция array_agg ( ) видит все строки, возвращаемые запросом. Эти строки называются фреймом , а array_agg ( ) выступает в роли оконной функции .
Запрос имеет доступ только к тому, что было извлечено по WHERE условию:
FROM generate_series ( 1 , 3 ) AS x
WHERE x > 1 ;
x | array_agg
===============
2 | { 2 , 3 }
3 | { 2 , 3 }
Это правило действует всегда. Если нужно обратиться к чему-то, что не удовлетворяет WHERE-условию, необходимо использовать подзапросы.
Помимо array_agg ( ) можно использовать sum ( ) , count ( ) , и другие агрегаты:
FROM generate_series ( 1 , 3 ) AS x;
x | count | sum
=================
1 | 3 | 6
2 | 3 | 6
3 | 3 | 6
Кроме обычных агрегатов еще есть специализированные оконные функции . Некоторые из них будут рассмотрены далее.
OVER ( ) с пустыми скобочками на самом деле эквивалентен:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) FROM generate_series ( 1 , 3 ) AS x;
x | array_agg
===============
1 | { 1 , 2 , 3 }
2 | { 1 , 2 , 3 }
3 | { 1 , 2 , 3 }
Чтобы несколько раз не писать OVER ( ... ) , можно воспользоваться таким синтаксисом:
FROM generate_series ( 1 , 3 ) AS x
WINDOW w AS ( RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) ;
x | count | sum
=================
1 | 3 | 6
2 | 3 | 6
3 | 3 | 6
В рассмотренных примерах фрейм всегда содержал все возвращаемые строки. Это можно исправить:
FROM generate_series ( 1 , 3 ) AS x
WINDOW w AS (
ORDER BY x
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) ;
x | array_agg
===============
1 | { 1 }
2 | { 1 , 2 }
3 | { 1 , 2 , 3 }
= # SELECT x , array_agg ( x ) OVER w
FROM generate_series ( 1 , 3 ) AS x
WINDOW w AS (
ORDER BY x
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) ;
x | array_agg
===============
1 | { 1 , 2 , 3 }
2 | { 2 , 3 }
3 | { 3 }
Часть про ORDER BY x не является обязательной. Ведь нам известно, что generate_series ( ) возвращает числа в порядке возрастания. Но в общем случае, при SELECT ‘е из таблицы, таких гарантий нет.
Внимательные читатели могли заметить, что вместо синтаксиса RANGE здесь мы перешли на ROWS . Это сделано не случайно. Дело в том, что вызов оконных функций может происходить в разных режимах. Они так и называются, RANGE mode и ROWS mode. Существует также GROUPS mode. RANGE mode немного запутанный. В частности, он переопределяет смысл CURRENT ROW в зависимости от того, какая часть остального синтаксиса используется в запросе. Сделано так, по всей видимости, чтобы используемый по умолчанию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW работал по принципу наименьшего удивления, и эту часть запроса можно было опускать. Читателям, желающим основательно во всем этом разобраться, стоит обратиться к официальной документации . Здесь же мы тактично обойдем стороной этот вопрос.
Рассмотрим еще примеры:
FROM generate_series ( 1 , 3 ) AS x
WINDOW w AS (
ORDER BY x
ROWS BETWEEN CURRENT ROW AND CURRENT ROW
) ;
x | array_agg
===============
1 | { 1 }
2 | { 2 }
3 | { 3 }
= # SELECT x , array_agg ( x ) OVER w
FROM generate_series ( 1 , 3 ) AS x
WINDOW w AS (
ORDER BY x
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) ;
x | array_agg
===============
1 | { 1 }
2 | { 1 , 2 }
3 | { 2 , 3 }
= # SELECT x , array_agg ( x ) OVER w
FROM generate_series ( 1 , 3 ) AS x
WINDOW w AS (
ORDER BY x
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
) ;
x | array_agg
===============
1 | { 1 , 2 }
2 | { 2 , 3 }
3 | { 3 }
Текущую строку не обязательно включать во фрейм:
FROM generate_series ( 1 , 3 ) AS x
WINDOW w AS (
ORDER BY x
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
) ;
x | sum
=========
1 |
2 | 1
3 | 2
Смотрите, как удобно. Можно просто получить соседнее значение из выборки. Далее мы рассмотрим еще более простой способ это сделать.
Еще есть партиции . Рассмотрим их на более интересных данных:
«department» TEXT ,
«name» TEXT ,
«salary» INT ) ;
= # INSERT INTO employees
SELECT ‘dep_’ || chr ( d ) , ’emp_’ || chr ( d ) || e , d * 10 + e * 1
FROM generate_series ( ascii ( ‘a’ ) , ascii ( ‘c’ ) ) AS d ,
generate_series ( 1 , 3 ) AS e;
= # SELECT * FROM employees;
department | name | salary
==============================
dep_a | emp_a1 | 971
dep_a | emp_a2 | 972
dep_a | emp_a3 | 973
dep_b | emp_b1 | 981
dep_b | emp_b2 | 982
dep_b | emp_b3 | 983
dep_c | emp_c1 | 991
dep_c | emp_c2 | 992
dep_c | emp_c3 | 993
= # SELECT name , salary , array_agg ( salary ) OVER w
FROM employees
WINDOW w AS ( PARTITION BY department ) ;
name | salary | array_agg
=================================
emp_a1 | 971 | { 971 , 972 , 973 }
emp_a2 | 972 | { 971 , 972 , 973 }
emp_a3 | 973 | { 971 , 972 , 973 }
emp_b1 | 981 | { 981 , 982 , 983 }
emp_b2 | 982 | { 981 , 982 , 983 }
emp_b3 | 983 | { 981 , 982 , 983 }
emp_c1 | 991 | { 991 , 992 , 993 }
emp_c2 | 992 | { 991 , 992 , 993 }
emp_c3 | 993 | { 991 , 992 , 993 }
О партиции можно думать, как о такой специальной штуке, ограничивающей фрейм. Здесь мы партицируем данные по отделам. Ни один из фреймов не выходит за границы своей партиции. В остальном все работает так же, как раньше:
FROM employees
WINDOW w AS (
PARTITION BY department
ORDER BY salary
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
) ;
name | salary | sum
=======================
emp_a1 | 971 |
emp_a2 | 972 | 971
emp_a3 | 973 | 972
emp_b1 | 981 |
emp_b2 | 982 | 981
emp_b3 | 983 | 982
emp_c1 | 991 |
emp_c2 | 992 | 991
emp_c3 | 993 | 992
Партицировать можно и по условию:
FROM generate_series ( 1 , 6 ) AS x
WINDOW w AS (
PARTITION BY x > 3
ORDER BY x
— попробуйте убрать следующую строчку; объясните результат
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) ;
INT4 | x | array_agg
======================
0 | 1 | { 1 , 2 , 3 }
0 | 2 | { 1 , 2 , 3 }
0 | 3 | { 1 , 2 , 3 }
1 | 4 | { 4 , 5 , 6 }
1 | 5 | { 4 , 5 , 6 }
1 | 6 | { 4 , 5 , 6 }
Выше говорилось о существовании специализированных оконных функций . Самая простая из них — это row_number ( ) :
FROM employees
WINDOW w AS (
PARTITION BY department
ORDER BY salary
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
) ;
row_number | name | salary | sum
====================================
1 | emp_a1 | 971 |
2 | emp_a2 | 972 | 971
3 | emp_a3 | 973 | 972
1 | emp_b1 | 981 |
2 | emp_b2 | 982 | 981
3 | emp_b3 | 983 | 982
1 | emp_c1 | 991 |
2 | emp_c2 | 992 | 991
3 | emp_c3 | 993 | 992
Функция возвращает номер строки в партиции. Нумерация начиная с единицы.
Еще две полезные функции — это lag ( ) и lead ( ) :
FROM employees
WINDOW w AS (
PARTITION BY department
ORDER BY salary
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
) ;
name | salary | lag | lead
==============================
emp_a1 | 971 | | 972
emp_a2 | 972 | 971 | 973
emp_a3 | 973 | 972 |
emp_b1 | 981 | | 982
emp_b2 | 982 | 981 | 983
emp_b3 | 983 | 982 |
emp_c1 | 991 | | 992
emp_c2 | 992 | 991 | 993
emp_c3 | 993 | 992 |
Функции позволяют подсмотреть вперед или назад на заданное число строк в рамках партиции.
Также есть first_value ( ) и last_value ( ) :
salary ,
first_value ( salary ) OVER w ,
last_value ( salary ) OVER w
FROM employees
WINDOW w AS (
PARTITION BY department
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) ;
name | salary | first_value | last_value
============================================
emp_a1 | 971 | 971 | 973
emp_a2 | 972 | 971 | 973
emp_a3 | 973 | 971 | 973
emp_b1 | 981 | 981 | 983
emp_b2 | 982 | 981 | 983
emp_b3 | 983 | 981 | 983
emp_c1 | 991 | 991 | 993
emp_c2 | 992 | 991 | 993
emp_c3 | 993 | 991 | 993
Заметьте, что условие BETWEEN ... было переписано. Дело в том, что lag ( ) и lead ( ) работают на уровне партиции , и им не важно, какое условие было указано в BETWEEN ... . Но функции first_value ( ) и last_value ( ) работают с фреймом и учитывают эти условия.
Еще существует nth_value ( ) . Функция также работает с фреймом:
FROM employees
WINDOW w AS (
PARTITION BY department
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) ;
name | salary | nth_value
=============================
emp_a1 | 971 | 972
emp_a2 | 972 | 972
emp_a3 | 973 | 972
emp_b1 | 981 | 982
emp_b2 | 982 | 982
emp_b3 | 983 | 982
emp_c1 | 991 | 992
emp_c2 | 992 | 992
emp_c3 | 993 | 992
Довольно часто используется функция rank ( ) :
= # SELECT name , salary , row_number ( ) OVER w , rank ( ) OVER w
FROM employees
WINDOW w AS ( PARTITION BY department ORDER BY salary DESC ) ;
name | salary | row_number | rank
=====================================
emp_a1 | 970 | 1 | 1
emp_a3 | 970 | 2 | 1
emp_a2 | 970 | 3 | 1
emp_b3 | 983 | 1 | 1
emp_b2 | 982 | 2 | 2
emp_b1 | 981 | 3 | 3
emp_c3 | 993 | 1 | 1
emp_c2 | 992 | 2 | 2
emp_c1 | 991 | 3 | 3
Функция возвращает номер строки в соответствии с указанным порядком сортировки. Часть синтаксиса про ROWS BETWEEN ... здесь была опущена, поскольку она не влияла на результат.
Наконец, никто не говорил, что нельзя указать несколько OVER ( ... ) :
salary ,
rank ( ) OVER w1 AS company_rank ,
rank ( ) OVER w2 AS department_rank
FROM employees
WINDOW w1 AS ( ORDER BY salary DESC ) ,
w2 AS ( PARTITION BY department ORDER BY salary DESC ) ;
name | salary | company_rank | department_rank
==================================================
emp_c3 | 993 | 1 | 1
emp_c2 | 992 | 2 | 2
emp_c1 | 991 | 3 | 3
emp_b3 | 983 | 4 | 1
emp_b2 | 982 | 5 | 2
emp_b1 | 981 | 6 | 3
emp_a1 | 970 | 7 | 1
emp_a2 | 970 | 7 | 1
emp_a3 | 970 | 7 | 1
Это далеко не все возможности оконных функций. Однако их должно хватать для очень многих практических задач.