Добре дошли в сайта на 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-нем записи в нея.

Сега ще използваме агрегатната функция 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, каквото и да е) в горната част на нашата база данни.

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

, трябва да определим как да класираме или подредим коментарите за всяка публикация и след това да ограничим броя им. Тази задача в 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 за добрите примери и обяснения.