postgresql-window-functions/

Оконные функции (window functions) — это фича, позволяющая производить манипуляции между строками, возвращаемыми одним SQL-запросом. Похоже на GROUP BY , но, в отличие от него, строки не объединяются в одну. Есть задачи, в которых оконные функции исключительно удобны. Например, когда нужно показать некие значения (выручку, посещаемость) за месяц, и рядом с ними — насколько это больше или меньше по сравнению с прошлым месяцем.

Для начала, выведем числа от 1 до 3:

SELECT x FROM generate_series ( 1 , 3 ) AS x;

x
===
1
2
3

Теперь перепишем запрос вот таким странным образом:

= # SELECT x , array_agg ( x ) OVER ( )
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 условию:

= # SELECT x , array_agg ( x ) OVER ( )
FROM generate_series ( 1 , 3 ) AS x
WHERE x > 1 ;

x | array_agg
===============
2 | { 2 , 3 }
3 | { 2 , 3 }

Это правило действует всегда. Если нужно обратиться к чему-то, что не удовлетворяет WHERE-условию, необходимо использовать подзапросы.

Помимо array_agg ( ) можно использовать sum ( ) , count ( ) , и другие агрегаты:

= # SELECT x , count ( x ) OVER ( ) , sum ( x ) OVER ( )
FROM generate_series ( 1 , 3 ) AS x;

x | count | sum
=================
1 | 3 | 6
2 | 3 | 6
3 | 3 | 6

Кроме обычных агрегатов еще есть специализированные оконные функции . Некоторые из них будут рассмотрены далее.

OVER ( ) с пустыми скобочками на самом деле эквивалентен:

= # SELECT x , array_agg ( x ) 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 ( ... ) , можно воспользоваться таким синтаксисом:

= # SELECT x , count ( x ) OVER w , sum ( x ) OVER w
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

В рассмотренных примерах фрейм всегда содержал все возвращаемые строки. Это можно исправить:

= # SELECT x , array_agg ( x ) OVER w
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 работал по принципу наименьшего удивления, и эту часть запроса можно было опускать. Читателям, желающим основательно во всем этом разобраться, стоит обратиться к официальной документации . Здесь же мы тактично обойдем стороной этот вопрос.

Рассмотрим еще примеры:

= # 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 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 }

Текущую строку не обязательно включать во фрейм:

= # SELECT x , sum ( x ) OVER w
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

Смотрите, как удобно. Можно просто получить соседнее значение из выборки. Далее мы рассмотрим еще более простой способ это сделать.

Еще есть партиции . Рассмотрим их на более интересных данных:

= # CREATE TABLE employees (
«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 }

О партиции можно думать, как о такой специальной штуке, ограничивающей фрейм. Здесь мы партицируем данные по отделам. Ни один из фреймов не выходит за границы своей партиции. В остальном все работает так же, как раньше:

= # SELECT name , salary , sum ( salary ) OVER w
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

Партицировать можно и по условию:

= # SELECT INT4 ( x > 3 ) , x , array_agg ( x ) OVER w
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 ( ) :

= # SELECT row_number ( ) OVER w , name , salary , sum ( salary ) OVER w
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 ( ) :

= # SELECT name , salary , lag ( salary , 1 ) OVER w , lead ( salary , 1 ) OVER w
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 ( ) :

= # SELECT name ,
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 ( ) . Функция также работает с фреймом:

= # SELECT name , salary , nth_value ( salary , 2 ) OVER w
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 ( ) :

= # UPDATE employees SET salary = 970 WHERE salary < 980 ;

= # 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 ( ... ) :

= # SELECT name ,
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

Это далеко не все возможности оконных функций. Однако их должно хватать для очень многих практических задач.

EnglishRussianUkrainian