Основы использования оконных функций в PostgreSQL

3 октября 2022

Оконные функции (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

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

Метки: , .


Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.

Копирование материалов данного сайта не возбраняется при условии
указания ссылки на первоисточник. © 2009–2024 Записки программиста

Статистика LiveInternet