Добре дошли в сайта на PostgreSQL  България.

Тук може да намерите полезна информация относно любимата СУБД и разбира се да споделите своя опит.

Тема на месеца: Window Functions в PostgreSQL

Идеята за тази статия ми дойде, след като присъствах на PGConf тази година и един симпатичен италианец Gianni Ciolli говореше невероятно ентусиазирано на тази тема, обяснявайки ни колко интересни могат да бъдат за нас Window Functions  и какво могат да направят потребителите с тях в днешно време.

Но какво всъщност са тези Window Functions?

Window Functions извършват изчисление на набор от редове от таблица, които по някакъв начин са свързани с текущия ред. Може да се сравни с агрегатна функция. Но за разлика от обикновените агрегатни функции, използването на Window Function не води до това, редове да се групират в един изходен ред. Редовете запазват отделните си идентичности. Функцията е в състояние да получи повече от само текущия ред на резултата от заявката.

Преди да преминем към същината на самите Window Functions, нека си припомним какво са агрегатни функции в PostgreSQL.

Да разгледаме следния пример:

SELECT MIN (“Value”) FROM CARS;

Резултатът от изпълнението на заявката е едно число, което показва най-ниската цена за еднодневно ползване на кола. Идеята на функцията е да се намери минималната стойност на посочения атрибут в редовете, които отговарят на критерия за избор. Прилагат се атрибути или изрази, които съдържат числова стойност. Същото важи и при функциите MAX, AVG, COUNT, SUM.

В SQL съществува възможността да се използват функции, които правят изчисления, като се обобщават стойностите на указан атрибут от всички редове в таблица или от редовете, отговарящи на някакво условие, и получават като резултат една стойност . Това са т.н. агрегатни или групови функции.

След като вече си припомнихме какво са агрегатни функции, нека да разгледаме по – подробно Window Function.  Вече разбрахме, че те много си приличат.

Пример: Нека да си направим една таблица и да insert-нем записи в нея.

ID

product_name

price

group_ID

1

Баница със сирене

0,90 лв

1

2

Баница със спанак

0,80 лв

1

3

Баница с кашкавал

0,80 лв

1

4

Кроасан с масло

0,60 лв

2

5

Кроасан с шоколад

0,70 лв

2

6

Тиквеник

1,30 лв

3

7

Баница с мед и орехи

1,40 лв

1

 

ID

group_name

1

Баница

2

Кроасан

3

Тиквеник

 

Сега ще използваме агрегатната функция AVG, за да изчислим средната цена на всички продукти в таблицата products.

SELECT AVG (price) FROM products;

AVG ни връща 0,92857142…

Но ако искаме да приложим функцията върху подмножества от редове, трябва да използваме GROUP BY.

SELECT group_name, AVG (price) FROM products INNER JOIN product_groups USING (group_id) GROUP BY group_name;

Window Function (Oracle ги наричат oще аналитични функции) са част от стандарта SQL и тази публикация ще проучи как да ги използваме в PostgreSQL. Нека да видим как работят и какви проблеми могат да ни помогнат да решим.

Window Function са инструмент за извършване на разширено сортиране и ограничаване на подмножество от обединена таблица от данни (оттук и Window). Ще разгледаме една конкретна функция,  dense_rank(), но всички вградени (SUM например) и дефинирани от потребителя агрегирани функции могат да действат като Window Function чрез извикване на ключовата дума OVER. Някои други популярни функции включват row_number (), rank () и percent_rank ().  Повече за тях може да прочетете тук: https://www.postgresql.org/docs/9.3/functions-window.html.

Да разгледаме този пример:

Нека да си представим, че имаме блог платформа, в която имаме публикации, които имат много коментари. Искаме да получим три най-нови коментара на всеки пост. Можем да започнем, като напишем заявка, за да изтеглим всички публикации и коментари. След това можем да филтрираме този резултат на какъвто и да е език на приложението, който използваме (Ruby, PHP, Go, каквото и да е) в горната част на нашата база данни.

POST_ID

COMMENT_IDS

BODY

1

1

Foo old

1

2

Foo new

1

3

Foo newer

1

4

Foo newest

2

5

Bar old

2

6

Bar new

2

7

Bar newer

2

8

Bar newest

Това решение може да работи известно време, но какво се случва, когато имаме 10 хиляди публикации, в които всеки има 10 хиляди коментари (това е наистина популярен блог)? Получената таблица за присъединяване просто стана наистина голяма. Искаме да видим точно тези резултати, без да се нуждаем от допълнително филтриране:

POST_ID

COMMENT_IDS

BODY

1

4

Foo newest

1

3

Foo newer

1

2

Foo new

2

8

Bar newest

2

7

Bar newer

2

6

Bar new

, трябва да определим как да класираме или подредим коментарите за всяка публикация и след това да ограничим броя им. Тази задача в SQL можем да постигнем като въведем Window Functions.

Искаме трите най-скорошни коментара, така че да знаем, че ще трябва да направим някакво сортиране по create_at и след това да ограничим броя на резултатите.

 dense_rank() OVER (

  PARTITION BY post_id

  ORDER BY comments.created_at DESC

) AS comment_rank

OVER е ключовата дума, която задейства използването на Window Function. Вътре в заявката OVER ние казваме:

  • класирай коментарите от created_at (ORDER BY comments.create_at)
  • обхвани ранкинга на всяка публикация (PARTITION BY post_id)

Този резултат все още връща пълния набор, но сега имаме добавено поле comment_rank, което ни показва коментарите за всяка публикация в зависимост от възрастта. Като се има предвид тази класация, сега трябва да вземем само тези коментари, които имат comment_rank по-малко от 4 (тъй като искаме само трите най-скорошни коментара). Можем да постигнем тази цел, като използваме новата ни заявка за класиране като изявление за подбор като така:

SELECT comment_id, post_id, body FROM (

  SELECT posts.id AS post_id, comments.id AS comment_id, comments.body AS body,

    dense_rank() OVER (

      PARTITION BY post_id

      ORDER BY comments.created_at DESC

    ) AS comment_rank

  FROM posts LEFT OUTER JOIN comments ON posts.id = comments.post_id

) AS ranked_comments

WHERE comment_rank < 4;

И това е нашия отговор. Нашата заявка обаче стана доста трудна за четене. Window Function въвеждат допълнителен синтаксис, но са наистина мощен инструмент, когато се нуждаем от по-сложно сортиране или ограничаване на поведението. Синтаксисът е доста добре обяснен в документацията на PostgreSQL: https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS.

Ето и един доста интересен Guide: http://postgresguide.com/tips/window.html

 

П.П. Втората половина от тази статия е преведена от следния сайт: https://thoughtbot.com/blog/postgres-window-functions. Благодарим на Simon Taranto за добрите примери и обяснения.

Коментари
  1. udebosuudebosu на 15.01.2020 09:Ян#

    YmdwdWcub3Jn <a href='http://mewkid.net/buy-phicalis/#abmovuy-a'>abmovuy-a.anchor.com</a> [URL=http://mewkid.net/buy-phicalis/#abmovuy-u]abmovuy-u.anchor.com[/URL] http://mewkid.net/buy-phicalis/#abmovuy-t http://mewkid.net/buy-phicalis/#abmovuy-t http://mewkid.net/buy-phicalis/#abmovuy-t http://mewkid.net/buy-phicalis/#abmovuy-t http://mewkid.net/buy-phicalis/#abmovuy-t http://mewkid.net/buy-phicalis/#abmovuy-t http://mewkid.net/buy-phicalis/#abmovuy-t http://mewkid.net/buy-phicalis/#abmovuy-t olojopa

  2. ojevifojevif на 15.01.2020 09:Ян#

    YmdwdWcub3Jn <a href='http://mewkid.net/buy-phicalis/#onoamiwel-a'>onoamiwel-a.anchor.com</a> [URL=http://mewkid.net/buy-phicalis/#onoamiwel-u]onoamiwel-u.anchor.com[/URL] http://mewkid.net/buy-phicalis/#onoamiwel-t http://mewkid.net/buy-phicalis/#onoamiwel-t http://mewkid.net/buy-phicalis/#onoamiwel-t http://mewkid.net/buy-phicalis/#onoamiwel-t http://mewkid.net/buy-phicalis/#onoamiwel-t http://mewkid.net/buy-phicalis/#onoamiwel-t http://mewkid.net/buy-phicalis/#onoamiwel-t http://mewkid.net/buy-phicalis/#onoamiwel-t icotquza

  3. ayazacayayazacay на 16.01.2020 10:Ян#

    YmdwdWcub3Jn <a href='http://mewkid.net/buy-phicalis/#atuisodud-a'>atuisodud-a.anchor.com</a> [URL=http://mewkid.net/buy-phicalis/#atuisodud-u]atuisodud-u.anchor.com[/URL] http://mewkid.net/buy-phicalis/#atuisodud-t http://mewkid.net/buy-phicalis/#atuisodud-t http://mewkid.net/buy-phicalis/#atuisodud-t http://mewkid.net/buy-phicalis/#atuisodud-t http://mewkid.net/buy-phicalis/#atuisodud-t http://mewkid.net/buy-phicalis/#atuisodud-t http://mewkid.net/buy-phicalis/#atuisodud-t http://mewkid.net/buy-phicalis/#atuisodud-t iuciqaq

  4. dojupidojupi на 16.01.2020 10:Ян#

    YmdwdWcub3Jn <a href='http://mewkid.net/buy-phicalis/#anirizic-a'>anirizic-a.anchor.com</a> [URL=http://mewkid.net/buy-phicalis/#anirizic-u]anirizic-u.anchor.com[/URL] http://mewkid.net/buy-phicalis/#anirizic-t http://mewkid.net/buy-phicalis/#anirizic-t http://mewkid.net/buy-phicalis/#anirizic-t http://mewkid.net/buy-phicalis/#anirizic-t http://mewkid.net/buy-phicalis/#anirizic-t http://mewkid.net/buy-phicalis/#anirizic-t http://mewkid.net/buy-phicalis/#anirizic-t http://mewkid.net/buy-phicalis/#anirizic-t odudacixu

  5. ujijriujijri на 16.01.2020 10:Ян#

    YmdwdWcub3Jn <a href='http://mewkid.net/buy-phicalis/#wivajeh-a'>wivajeh-a.anchor.com</a> [URL=http://mewkid.net/buy-phicalis/#wivajeh-u]wivajeh-u.anchor.com[/URL] http://mewkid.net/buy-phicalis/#wivajeh-t http://mewkid.net/buy-phicalis/#wivajeh-t http://mewkid.net/buy-phicalis/#wivajeh-t http://mewkid.net/buy-phicalis/#wivajeh-t http://mewkid.net/buy-phicalis/#wivajeh-t http://mewkid.net/buy-phicalis/#wivajeh-t http://mewkid.net/buy-phicalis/#wivajeh-t http://mewkid.net/buy-phicalis/#wivajeh-t egocopu

  6. olevelololevelol на 16.01.2020 10:Ян#

    YmdwdWcub3Jn <a href='http://mewkid.net/buy-phicalis/#inuqefu-a'>inuqefu-a.anchor.com</a> [URL=http://mewkid.net/buy-phicalis/#inuqefu-u]inuqefu-u.anchor.com[/URL] http://mewkid.net/buy-phicalis/#inuqefu-t http://mewkid.net/buy-phicalis/#inuqefu-t http://mewkid.net/buy-phicalis/#inuqefu-t http://mewkid.net/buy-phicalis/#inuqefu-t http://mewkid.net/buy-phicalis/#inuqefu-t http://mewkid.net/buy-phicalis/#inuqefu-t http://mewkid.net/buy-phicalis/#inuqefu-t http://mewkid.net/buy-phicalis/#inuqefu-t efqfaih

Публикувай коментар