Exemplo de uso de Window Functions no PostgreSQL.

O objetivo deste post é demonstrar através de um exemplo simples, o uso de Window Functions no PostgreSQL.  Este recurso foi disponibilizado a partir da Versão 8.4 e representa, na minha opinião, um dos melhores recursos de query disponibilizados no PostgreSQL ultimamente.

As Window Functions executam cálculos em um conjunto de linhas da tabela que estão de alguma forma relacionados com a linha atual.   Isso é comparável com os tipos de cálculos que podem ser feitos com algumas funções agregadas.    Mas ao contrário de funções agregadas, o uso de uma Window Function não causa o agrupamento das linhas em uma única linha de saída, onde estas mantêm suas identidades separadas. As Window Function são capazes de acessar mais do que apenas a linha atual do resultado da consulta, o que permite que elas realizem e retornem toda a informação solicitada.

As Window Function disponíveis no PostgreSQL estao documentadas em http://www.postgresql.org/docs/8.4/static/functions-window.html porém, funcões comuns de agregação também podem ser utilizadas como Window Function.

Uma chamada de Window Function sempre contém uma cláusula OVER seguida do nome da Window e de seus argumentos.  Isto é o que a distingue sintaticamente uma função regular ou função agregada.   A cláusula OVER determina exatamente como as linhas da consulta serão divididas para o processamento da função.  A cláusula PARTITION BY dentro do OVER especifica as linhas de divisão em grupos, ou partições, que compartilham os mesmos valores da expressão.   Para cada linha, o retorno da Window Function é calculado através das linhas que caem na mesma logica definida para a linha atual.

Vamos a um pequeno exemplo de utilização:

Imaginem que tenham a seguinte tabela que contém a seguinte informação:

CREATE TABLE canais (
  id SERIAL,
  nome VARCHAR NOT NULL,
  id_aplicativo INTEGER NOT NULL,
  peso INTEGER DEFAULT 1 NOT NULL,
  CONSTRAINT canais_nome_key UNIQUE(nome),
  CONSTRAINT canais_pkey PRIMARY KEY(id)
) WITHOUT OIDS;

Este exemplo retrata uma tabela que armazena informações de canais de envio de noticias com os seus respectivos pesos de proporção de envio. Baseados nos valores de peso de cada canal, necessitamos calcular o percentual do peso dos canais em relação ao total de pesos de cada aplicativo. Na forma usual anterior, teriamos que fazer sum() separados para determinar isso, mas agora com as Window Functions ficou muito mais facil para o programador e para o SGBD.

Vamos inserir alguns registros na tabela para simular um SELECT como exemplo:

INSERT INTO canais (nome, id_aplicativo, peso) VALUES ('Futebol', 2, 5);
INSERT INTO canais (nome, id_aplicativo, peso) VALUES ('Formula 1', 2, 3);
INSERT INTO canais (nome, id_aplicativo, peso) VALUES ('Volei', 2, 1);
INSERT INTO canais (nome, id_aplicativo, peso) VALUES ('Cotação Dolar', 4, 5);
INSERT INTO canais (nome, id_aplicativo, peso) VALUES ('Cotação Euro', 4, 5);
INSERT INTO canais (nome, id_aplicativo, peso) VALUES ('Indice Bovespa', 4, 10);
INSERT INTO canais (nome, id_aplicativo, peso) VALUES ('Católicos', 5, 5);
INSERT INTO canais (nome, id_aplicativo, peso) VALUES ('Evangélicos', 5, 5);
INSERT INTO canais (nome, id_aplicativo, peso) VALUES ('Espiritualistas', 5, 5);

Para obter o resultado desejado, simulamos a execução da seguinte query:

SELECT  id,nome, id_aplicativo,peso,
    SUM(peso) OVER w AS tot_app,
    ((peso::NUMERIC / SUM(peso) OVER w) * 100)::NUMERIC(4,2) AS perc
    FROM public.canais window w AS (partition BY id_aplicativo)
    ORDER BY id_aplicativo,nome;

E esta query retorna:

Notem que na mesma query conseguimos totalizar o valor de peso, agrupado por id_aplicativo.  Isto é feito automaticamente pela função Sum(), trabalhando como Window Function.
Como podem notar abaixo, na foto do Explain da query, a resolução dos valores de retorno das Window Functions são realizados antes ainda do retorno das informações ao cliente do banco:

Agora ficou muito facil fazer estes tipos de calculos dentro da mesma query, aterando ao nosso gosto o tipo de agrupamento que desejamos para a Window.

Este é um pequeno exemplo do uso de Window Functions no PostgreSQL, sendo que a sua utilização é bem mais ampla e poderosa que a demostrada aqui.
Vale a pena dar uma olhada no manual do PostgreSQL (8.4 em diante) para ver as funções e usos disponibilizados…

E você, em quais situações costuma usar o recurso das Window Functions no PostgreSQL e em que elas otimizaram as suas consultas? Conte-nos um pouco da sua experiência nos campos de comentário abaixo e compartilhe seu conhecimento.

Obrigado e um abraço a todos!

Lucio Chiessi
Baú de Dev