Как посчитать всё на свете одним SQL-запросом. Оконные функции PostgreSQL


    Я с удивлением обнаружил, что многие разработчики, даже давно использующие postgresql, не понимают оконные функции, считая их какой-то особой магией для избранных. Ну или в лучшем случае «копипастят» со StackOverflow выражения типа «row_number() OVER ()», не вдаваясь в детали. А ведь оконные функции — полезнейший функционал PostgreSQL.
    Попробую по-простому объяснить, как можно их использовать.



    Для начала хочу сразу пояснить, что оконные функции не изменяют выборку, а только добавляют некоторую дополнительную информацию о ней. Т.е. для простоты понимания можно считать, что postgres сначала выполняет весь запрос (кроме сортировки и limit), а потом только просчитывает оконные выражения.


    Синтаксис примерно такой:



    функция OVER окно
    

    Окно — это некоторое выражение, описывающее набор строк, которые будет обрабатывать функция и порядок этой обработки.
    Причем окно может быть просто задано пустыми скобками (), т.е. окном являются все строки результата запроса.



    Например, в этом селекте к обычным полям id, header и score просто добавится нумерация строк.



    SELECT
        id,
        section,
        header,
        score,
        row_number() OVER ()  AS num
    FROM news;
    
     id | section |  header   | score | num 
    ----+---------+-----------+-------+-----
      1 |       2 | Заголовок |    23 |   1
      2 |       1 | Заголовок |     6 |   2
      3 |       4 | Заголовок |    79 |   3
      4 |       3 | Заголовок |    36 |   4
      5 |       2 | Заголовок |    34 |   5
      6 |       2 | Заголовок |    95 |   6
      7 |       4 | Заголовок |    26 |   7
      8 |       3 | Заголовок |    36 |   8
    
    


    В оконное выражение можно добавить ORDER BY, тогда можно изменить порядок обработки.



    SELECT
        id,
        section,
        header,
        score,
        row_number() OVER (ORDER BY score DESC)  AS rating
    FROM news
    ORDER BY id;
    
     id | section |  header   | score | rating 
    ----+---------+-----------+-------+--------
      1 |       2 | Заголовок |    23 |      7
      2 |       1 | Заголовок |     6 |      8
      3 |       4 | Заголовок |    79 |      2
      4 |       3 | Заголовок |    36 |      4
      5 |       2 | Заголовок |    34 |      5
      6 |       2 | Заголовок |    95 |      1
      7 |       4 | Заголовок |    26 |      6
      8 |       3 | Заголовок |    36 |      3
    
    

    Обратите внимание, что я добавил еще и в конце всего запоса ORDER BY id, при этом рейтинг посчитан все равно верно. Т.е. посгрес просто отсортировал результат вместе с результатом работы оконной функции, один order ничуть не мешает другому.



    Дальше — больше. В оконное выражение можно добавить слово PARTITION BY [expression],
    например row_number() OVER (PARTITION BY section), тогда подсчет будет идти в каждой группе отдельно:



    SELECT
        id,
        section,
        header,
        score,
        row_number() OVER (PARTITION BY section ORDER BY score DESC)  AS rating_in_section
    FROM news
    ORDER BY section, rating_in_section;
    
     id | section |  header   | score | rating_in_section 
    ----+---------+-----------+-------+-------------------
      2 |       1 | Заголовок |     6 |                 1
      6 |       2 | Заголовок |    95 |                 1
      5 |       2 | Заголовок |    34 |                 2
      1 |       2 | Заголовок |    23 |                 3
      4 |       3 | Заголовок |    36 |                 1
      8 |       3 | Заголовок |    36 |                 2
      3 |       4 | Заголовок |    79 |                 1
      7 |       4 | Заголовок |    26 |                 2
    
    

    Если не указывать партицию, то партицией является весь запрос.



    Тут сразу надо немного сказать о функциях, которые можно использовать, так как есть очень важный нюанс.
    В качестве функции можно использовать, так сказать, истинные оконные функции из мануала — это row_number(), rank(), lead() и т.д., а можно использовать функции-агрегаты, такие как: sum(), count() и т.д. Так вот, это важно, агрегатные функции работают слегка по-другому: если не задан ORDER BY в окне, идет подсчет по всей партиции один раз, и результат пишется во все строки (одинаков для всех строк партиции). Если же ORDER BY задан, то подсчет в каждой строке идет от начала партиции до этой строки.

    Давайте посмотрим это на примере. Например, у нас есть некая (сферическая в вакууме) таблица пополнений балансов.



    SELECT
        transaction_id,
        change
    FROM balance_change 
    ORDER BY transaction_id;
    
     transaction_id | change 
    ----------------+--------
                  1 |   1.00
                  2 |  -2.00
                  3 |  10.00
                  4 |  -4.00
                  5 |   5.50
    
    

    и мы хотим узнать заодно, как менялся остаток на балансе при этом:



    SELECT
        transaction_id,
        change,
        sum(change) OVER (ORDER BY transaction_id) as balance
    FROM balance_change 
    ORDER BY transaction_id;
    
     transaction_id | change | balance 
    ----------------+--------+---------
                  1 |   1.00 |    1.00
                  2 |  -2.00 |   -1.00
                  3 |  10.00 |    9.00
                  4 |  -4.00 |    5.00
                  5 |   5.50 |   10.50
    
    

    Т.е. для каждой строки идет подсчет в отдельном фрейме. В данном случае фрейм — это набор строк от начала до текущей строки (если было бы PARTITION BY, то от начала партиции).



    Если же мы для агрегатной фунции sum не будем использовать ORDER BY в окне, тогда мы просто посчитаем общую сумму и покажем её во всех строках. Т.е. фреймом для каждой из строк будет весь набор строк
    от начала до конца партиции.



    SELECT
        transaction_id,
        change,
        sum(change) OVER () as result_balance
    FROM balance_change
    ORDER BY transaction_id;
    
     transaction_id | change | result_balance 
    ----------------+--------+----------------
                  1 |   1.00 |          10.50
                  2 |  -2.00 |          10.50
                  3 |  10.00 |          10.50
                  4 |  -4.00 |          10.50
                  5 |   5.50 |          10.50
    
    

    Вот такая особенность агрегатных функций, если их использовать как оконные. На мой взгляд, это довольно-таки странный, интуитивно неочевидный момент SQL-стандарта.



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



    SELECT
        transaction_id,
        change,
        sum(change) OVER (ORDER BY transaction_id) as balance,
        sum(change) OVER () as result_balance,
        round(
            100.0 * sum(change) OVER (ORDER BY transaction_id)  /  sum(change) OVER (),
            2
        ) AS percent_of_result,
        count(*) OVER () as transactions_count
    FROM balance_change
    ORDER BY transaction_id;
    
     transaction_id | change | balance | result_balance | percent_of_result | transactions_count 
    ----------------+--------+---------+----------------+-------------------+--------------------
                  1 |   1.00 |    1.00 |          10.50 |              9.52 |                  5
                  2 |  -2.00 |   -1.00 |          10.50 |             -9.52 |                  5
                  3 |  10.00 |    9.00 |          10.50 |             85.71 |                  5
                  4 |  -4.00 |    5.00 |          10.50 |             47.62 |                  5
                  5 |   5.50 |   10.50 |          10.50 |            100.00 |                  5
    
    

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



    SELECT
        sum(salary) OVER w,
        avg(salary) OVER w
    FROM empsalary
    WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
    

    Здесь w после слова OVER идет без уже скобок.



    Результат работы оконной функции невозможно отфильтровать в запросе с помощью WHERE, потому что оконные фунции выполняются после всей фильтрации и группировки, т.е. с тем, что получилось. Поэтому чтобы выбрать, например, топ 5 новостей в каждой группе, надо использовать подзапрос:



    SELECT *
    FROM (
        SELECT
            id,
            section,
            header,
            score,
            row_number() OVER (PARTITION BY section ORDER BY score DESC)  AS rating_in_section
        FROM news
        ORDER BY section, rating_in_section
    ) counted_news
    WHERE rating_in_section <= 5;
    


    Еще пример для закрепления. Помимо row_number() есть несколько других функций. Например lag, которая ищет строку перед последней строкой фрейма. К примеру мы можем найти насколько очков новость отстает от предыдущей в рейтинге:

    SELECT
        id,
        section,
        header,
        score,
        row_number() OVER w        AS rating,
        lag(score) OVER w - score  AS score_lag
    FROM news
    WINDOW w AS (ORDER BY score DESC)
    ORDER BY score desc;
    
     id | section |  header   | score | rating | score_lag 
    ----+---------+-----------+-------+--------+-----------
      6 |       2 | Заголовок |    95 |      1 |          
      3 |       4 | Заголовок |    79 |      2 |        16
      8 |       3 | Заголовок |    36 |      3 |        43
      4 |       3 | Заголовок |    36 |      4 |         0
      5 |       2 | Заголовок |    34 |      5 |         2
      7 |       4 | Заголовок |    26 |      6 |         8
      1 |       2 | Заголовок |    23 |      7 |         3
      2 |       1 | Заголовок |     6 |      8 |        17
    
    


    Прошу в коментариях накидать примеров, где особенно удобно применять оконные фунции. А также, какие с ними могут возникнуть проблемы, если таковые имеются.

    Подписывайтесь на подкаст о разработке "Цинковый прод", где мы обсуждаем базы данных, языки программирования и всё на свете!
    Support the author
    Share post

    Similar posts

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 51

      +5
      «There was SQL before window functions and SQL after window functions».

      Оконные функции удобно применять для всякой аналитики, отчетов, и так далее.

      А еще однажды была задача добавить unique constraint на таблицу, где уже было порядка 500к записей и из них много не соответствовало условию. Запрос на удаление лишних записей был удобно сформирован с помощью оконной фукнции. Боюсь представить, сколько времени я бы удалял это вручную.
        +1
        Да-да, помнится, делал удаление дублей до того как познакомился с окнами, а потом после. С окнами это один запрос, а без — страшно подумать, добавление столбца, создание последовательности, потом выборка по хевингу, и все это в табле с 2+млн записей, ужас! ( '/ /_ / /)
        –6
        Ну, самый распространенный способ использования — для пагинации раз уж у нас номера строк под рукой
          –1
          Вы бы еще OFFSET предложили.
            –1
            ну, затупил. Я єтим в mssql пользуюсь а там только TOP (если в последних версиях конечно не добавили что то типа offset).
              +1
              Может я чего не понимаю, но в MySQL есть «LIMIT from,count», которым я всегда пользовался…
                0
                MSSQL
                у мускула как раз нет оконных функций
                  +1
                  Основная проблема тут в том, что при «LIMIT 100000, 10» MySQL сначала посмотрит 100000 записей, а только потом вернёт 10.
                  +1
                  если в последних версиях конечно не добавили что то типа offset

                  Добавили technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx
                  SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
                  
              0
              А можно ли использовать результат оконной функции в HAVING? Как раз сегодня решал задачу на MySQL, нужно было сделать пагинацию по дням, имея колонку с таймстемпом записей. Очень горевал, что он не умеет оконные функции, пришлось извращаться с переменными в селектах и подзапросами, но зато потом эту переменную можно в HAVING напихать и всё отфлильтруется.
                0
                > А можно ли использовать результат оконной функции в HAVING?
                нет, нельзя. Оконные функции просчитываются уже после фильтрации по having
                  0
                  Но ведь можно запихнуть запрос с оконной функцией в with а в результирующем запросе сделать и группировку и having нет?
                    0
                    если запрос засунуть в with, то в результирующем запросе можно и группировку, и having, и всё, что угодно
                      0
                      Я собственно про это же.
                –1
                Вот такая особенность агрегатных функций, если их использовать как оконные. На мой взгляд, это довольно-таки странный, интуитивно неочевидный момент SQL-стандарта.

                Не понял, чего тут не очевидного? Не указываем order by — границами окна являются первая и последняя запись в выборке, указываем — первая и текущая запись в выборке.

                Кстати, ваше описание оконных функций неполно — как минимум не указаны полезнейшие предложения range / rows, позволяющие явно указать границы окна.
                  +1
                  > Не понял, чего тут не очевидного? Не указываем order by — границами окна являются первая и последняя запись в выборке, указываем — первая и текущая запись в выборке.

                  Если мы берем функцию row_number(), то она как-то все равно учитывает текущую строку, даже если не делать order by. А sum — никак не учитывает. Вот это и не очевидно, на мой взгляд.
                    0
                    Об этом прямо написано в документации. sum — это агрегатная функция изначально, а row_number — оконная.

                    «When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame. An aggregate used with ORDER BY and the default window frame definition produces a „running sum“ type of behavior, which may or may not be what's wanted. To obtain aggregation over the whole partition, omit ORDER BY or use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other frame specifications can be used to obtain other effects.»
                      +2
                      угу
                      +1
                      А в чём проблема? FIRST_VALUE вы куда отнесёте?
                      –2
                      я довольно часто использовал оконную функцию, чтобы найти крайнее значение из набора.
                      Представим, что есть таблица со списком тарифов, для каждого тарифа есть цена и дата начала действия. Текущую цену можно будет узнать таким запросом.
                      +----+------------+--------+------------+
                      | id | service_id | tariff |  put_into  |
                      +----+------------+--------+------------+
                      |  1 |          1 |  100.0 | 2013-10-01 |
                      |  2 |          1 |  200.0 | 2014-12-01 |
                      |  3 |          1 |  150.0 | 2015-02-01 |
                      |  4 |          1 |  170.0 | 2016-02-15 |
                      |  5 |          2 |  300.0 | 2013-10-01 |
                      |  7 |          2 |  330.0 | 2015-02-01 |
                      |  8 |          2 |  315.0 | 2016-02-15 |
                      +----+------------+--------+------------+
                      
                      select distinct first_value (t1.tariff) over (order by t1.put_into desc) as price
                      from tariff_price t1 where t1.service_id = 1 and t1.put_into <= CURRENT_DATE;
                      
                      +-------+
                      | price |
                      +-------+
                      | 150.0 |
                      +-------+
                      
                        +4
                        Эмм. А зачем тут оконная функция?
                        select t1.tariff from from tariff_price t1 where t1.service_id = 1 and t1.put_into <= CURRENT_DATE order by t1.put_into desc limit 1

                        И очевиднее и план запроса куда проще.
                          0
                          Ваш запрос подойдет для предложенной задачи только при условии «t1.service_id = 1».
                          Если же нужно найти последние цены по всем видам услуг — то не подойдет :)
                            0
                            Если бы не было этого условия, и было бы PARTITION BY, тогда да. А так — странноватый запрос.
                              +1
                              Чудак ты автор, сам же просил:
                              Прошу в коментариях накидать примеров, где особенно удобно применять оконные фунции. А также, какие с ними могут возникнуть проблемы, если таковые имеются.

                              Ну я и дал пример сферического запроса в вакуме, реальный запрос намного сложнее и масштабнее, но я его здесь приводить не буду так как не интересно.
                              0
                              Та запросто:
                              select distinct on (t1.service_id) t1.service_id, t1.tariff from from tariff_price t1 where t1.put_into <= CURRENT_DATE order by t1.service_id, t1.put_into desc
                          –3
                          Нужна помощь зала… как красиво вытащить последний ряд партишена?

                          http://sqlfiddle.com/#!15/6bba4/1
                            0
                            Зря вы так, попробуйте лучше сюда.
                              +1
                              последний ряд партишена, если я правильно понял задачу, можно вытащить примерно так: пронумеровать row_number() over (order by… desc) as num, потом весь запрос сделать подзапросом, и отфильтровать where num = 1
                                0
                                * я забыл собственно partition by вставить )
                              0
                              В очередной раз жалею, что выбрал MySQL для своего проекта с несколькими десятками таблиц…
                                +2
                                Несколько десятков – это не тысячи, можно и смигрировать :-)
                                  0
                                  Можно. Осталось быдлокод подготовить. Пока медленно выходит. Плюс надо преодолеть страх отказаться от phpMyAdmin. За столько лет привык уже. А полноценной замены ему нет. Кстати, для многих это и есть причина оставаться на MySQL. Потому что «так удобнее»,
                                    0
                                    а как же phppgadmin?
                                      0
                                      По удобству это совсем не то. Хотя я пользовался.
                                      0
                                      phpMyAdmin можно легко заменить на Adminer, он и mysql и postgresql поддерживает
                                    +1
                                    Держите. pgloader.io
                                      0
                                      Из плюсов — он единственный из списка wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL работает с гео полями
                                        0
                                        Ну у меня есть в базе координаты объектов. Только я особо не заморачивался. Главное отображаются поверх Openstreetmap.
                                        0
                                        Что-то вот так просто никак не соображу как его собрать под Gentoo.
                                        0
                                        Прежде учтите, что в индексах пг хранится ссылка на данные по внутреннему идентификатору
                                        Как следствие — при обновлении строки обновляется ссылка на данные в индексе
                                        0
                                        Оконные функции я часто использую для миграции данных. Очень удобно. Это либо удаление дубликатов, либо штуки типа перенумерования записей, например так: envek.name/ru/blog/2015/04/28/sql-window-functions
                                          0
                                          Отличная статья, спасибо, очень радует, что материалы по этой действительно великолепной СУБД стали появляться всё чаще. Ещё очень бы хотелось материалов по масштабированию — советы, практики использования таких вещей, как pgPool-II, PostgresXL и т.д.
                                            +1
                                            Оконные функции типа first/last_value очень удобны при работе с версионированными или хронологическими данными. Особенно функции, «заглядывающие» вперёд по запросу. В том же мускуле с помощью переменных худо-бедно можно выводить результаты в текущей строке, базирующиеся на результатах предыдущих, но вот если нужны последующие, то без подзапросов с обратной сортировкой не обойтись.
                                              +2
                                              Неплохо бы ещё пару слов сказать про волшебное «UNBOUNDED FOLLOWING», и как оно влияет на выражения. Оптимизатору несложно выполнять агрегации над теми строками, которые он уже «просканировал», вероятно, поэтому по умолчанию используются границы «RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW», но если вам нужно «заглядывать вперёд» и учитывать значения будущих строк окна, то приходится использовать то самое «FOLLOWING», которое может кардинальным образом менять план и скорость выполнения запроса.

                                              Вот простой пример расчёта среднего по умолчанию

                                              SELECT 
                                                 NAME,
                                                 SALARY, 
                                                 AVG(SALARY) OVER (ORDER BY SAL) AVG
                                              FROM
                                                 EMP;
                                              
                                              NAME           SALARY        AVG
                                              ---------- ---------- ----------
                                              SMITH             800        800
                                              JAMES             950        875
                                              ADAMS            1100        950
                                              WARD             1250       1025
                                              


                                              Подробнее, например, на www.dba-oracle.com/t_advanced_sql_windowing_clause.htm
                                                0
                                                Не упомянута вот такая возможность:
                                                select id,
                                                sum(viewed) FILTER (where viewed > 5) OVER (ORDER BY expectedenddate DESC) AS rating
                                                from table1
                                                Если добавлено предложение FILTER, агрегатной функции подаются только те входные строки, для которых условие_фильтра вычисляется как истинное; другие строки отбрасываются
                                                Ну и доп. ссылки:PS: спецом на postgrespro.ru ссылки поставил, уж больно Олег Бартунов его хвалил в Уфе
                                                  0
                                                  Ключевое слово filter — это фильтрация вообще для любой аггрегатной функции, хоть оконной хоть нет. Появилось в 9.4. Вещь жутко удобная, это точно.
                                                  0
                                                  один order ничуть не мешает другому
                                                  Автор, я попробовал на тестовых данных, объясни, плиз, как сделать этот запрос лучше, ибо ордеры, таки мешают друг другу
                                                    +1
                                                    Я имел в виду, логически не мешают. По производительности надо посмотреть. А у вас есть дамп с данными, чтобы погонять можно было?
                                                    +1
                                                    подскажите, я правильно понял, что окна не бывают «скользящими»? Иными словами, нельзя вычислить скользящее среднее, типа (тут окно в 2 значения):
                                                    val avg
                                                    3 | 4.5
                                                    6 | 4.5
                                                    3 | 4
                                                    5 | 4.5
                                                    4 |
                                                      +1

                                                      Бывают. Для этого как раз предназначены фразы range / rows оконной функции. На примере Oracle:


                                                      with data as (
                                                          select 3 val from dual union all
                                                          select 6 val from dual union all
                                                          select 3 val from dual union all
                                                          select 5 val from dual union all
                                                          select 4 val from dual
                                                      )
                                                      select n, val, avg(val) over(order by n rows between current row and 1 following) avg
                                                      from (select rownum n, val from data) t;

                                                      Результат:


                                                      | N | VAL | AVG |
                                                      |---|-----|-----|
                                                      | 1 |   3 | 4.5 |
                                                      | 2 |   6 | 4.5 |
                                                      | 3 |   3 |   4 |
                                                      | 4 |   5 | 4.5 |
                                                      | 5 |   4 |   4 |

                                                      Здесь колонка n искусственно введена для того, чтобы правильно упорядочить данные во фразе order by окна. Если у вас уже есть колонка сортировки, эта искусственная колонка не нужна. Фраза rows between current row and 1 following определяет границы окна: между текущей строкой и плюс одной строкой от текущей (включительно) — получаем ваш интервал в 2 строки; rows означает, что оперируем физическими строками. Значения за последней строкой считаются как бы равными null. Ещё можно использовать range — в этом случае диапазон будет рассчитываться не от порядковых номеров строк, а от значений в колонке n (той, которая в order by). В нашем случае порядковый номер и значение совпадают, так что результат был бы одним и тем же, но если бы вместо n стоял, например, реальный идентификатор строки, разница была бы существенной!


                                                      Документация по аналитическим функциям.

                                                  Only users with full accounts can post comments. Log in, please.