Cantada Nerd

Window Function - PostgreSQL

Window function é um recurso muito útil porque nos permite realizar cálculos que antes só poderiam ser feitos com functions ou subquerys, assim podemos escrever menos código e evitar joins. Uma window function consegue particionar as linhas de uma consulta e fazer cálculos dentro destas partições. É como fazemos com funções agregadas, porém sem agrupar e mantendo as linhas separadas. Pra começar, disponibilizo as sentenças para criar e popular uma tabela com dados que vão servir de exemplo:

CREATE TABLE formula1
(
  posicao integer NOT NULL,
  piloto character varying(100),
  equipe character varying(100),
  pontos integer,
  CONSTRAINT pk_formula1 PRIMARY KEY (posicao)
);

INSERT INTO formula1 VALUES (1, 'FERNANDO ALONSO', 'FERRARI', 164);
INSERT INTO formula1 VALUES (2, 'MARK WEBBER', 'RED BULL', 124);
INSERT INTO formula1 VALUES (3, 'SEBASTIAN VETTEL', 'RED BULL', 122);
INSERT INTO formula1 VALUES (4, 'LEWIS HAMILTON', 'MCLAREN', 117);
INSERT INTO formula1 VALUES (5, 'KIMI RAIKKONEN', 'LOTUS', 116);
INSERT INTO formula1 VALUES (6, 'NICO ROSBERG', 'MERCEDES BENZ', 77);
INSERT INTO formula1 VALUES (7, 'JENSON BUTTON', 'MCLAREN', 76);
INSERT INTO formula1 VALUES (8, 'ROMAIN GROSJEAN', 'LOTUS', 76);
INSERT INTO formula1 VALUES (9, 'SERGIO PEREZ', 'SAUBER', 47);
INSERT INTO formula1 VALUES (10, 'KAMUI KOBAYASHI', 'SAUBER', 33);

Fazendo um simples select temos o seguinte resultado:

 posicao |      piloto      |    equipe     | pontos 
---------+------------------+---------------+--------
       1 | FERNANDO ALONSO  | FERRARI       |    164
       2 | MARK WEBBER      | RED BULL      |    124
       3 | SEBASTIAN VETTEL | RED BULL      |    122
       4 | LEWIS HAMILTON   | MCLAREN       |    117
       5 | KIMI RAIKKONEN   | LOTUS         |    116
       6 | NICO ROSBERG     | MERCEDES BENZ |     77
       7 | JENSON BUTTON    | MCLAREN       |     76
       8 | ROMAIN GROSJEAN  | LOTUS         |     76
       9 | SERGIO PEREZ     | SAUBER        |     47
      10 | KAMUI KOBAYASHI  | SAUBER        |     33

Analisando os dados, imagine uma forma de inserir mais uma coluna que mostrará a soma de pontos por equipes. Poderia usar uma subquery:

SELECT *, 
 (SELECT SUM(pontos) FROM formula1 aux
 WHERE formula1.equipe = aux.equipe
 GROUP BY equipe) AS pontos_equipe
FROM formula1
ORDER BY posicao

Poderia usar uma function também, mas fica melhor com uma window function:

SELECT *, SUM(pontos) OVER (PARTITION BY equipe) AS pontos_equipe 
FROM formula1
ORDER BY posicao

 posicao |      piloto      |    equipe     | pontos | pontos_equipe 
---------+------------------+---------------+--------+---------------
       1 | FERNANDO ALONSO  | FERRARI       |    164 |           164
       2 | MARK WEBBER      | RED BULL      |    124 |           246
       3 | SEBASTIAN VETTEL | RED BULL      |    122 |           246
       4 | LEWIS HAMILTON   | MCLAREN       |    117 |           193
       5 | KIMI RAIKKONEN   | LOTUS         |    116 |           192
       6 | NICO ROSBERG     | MERCEDES BENZ |     77 |            77
       7 | JENSON BUTTON    | MCLAREN       |     76 |           193
       8 | ROMAIN GROSJEAN  | LOTUS         |     76 |           192
       9 | SERGIO PEREZ     | SAUBER        |     47 |            80
      10 | KAMUI KOBAYASHI  | SAUBER        |     33 |            80

A chamada da função deve vir acompanhada da cláusula OVER. É isto que determina quais linhas estarão disponíveis para o processamento da função. A outra instrução, PARTITION BY, divide as linhas em grupos para que a função seja aplicada sobre elas. Também podemos definir a ordem que as linhas serão processadas pela função usando ORDER BY. A window function processa as linhas independente da ordem em que estão sendo exibidas, portanto deve-se ter cuidado para que os dados sejam processados na ordem desejada. Suponhamos que queremos saber quem é o piloto número 1 e 2 de cada equipe, sendo o número 1 com a maior quantidade de pontos: 

SELECT *, row_number() OVER (PARTITION BY equipe ORDER BY posicao) AS numero_piloto
FROM formula1
ORDER BY posicao

 posicao |      piloto      |    equipe     | pontos | numero_piloto 
---------+------------------+---------------+--------+---------------
       1 | FERNANDO ALONSO  | FERRARI       |    164 |             1
       2 | MARK WEBBER      | RED BULL      |    124 |             1
       3 | SEBASTIAN VETTEL | RED BULL      |    122 |             2
       4 | LEWIS HAMILTON   | MCLAREN       |    117 |             1
       5 | KIMI RAIKKONEN   | LOTUS         |    116 |             1
       6 | NICO ROSBERG     | MERCEDES BENZ |     77 |             1
       7 | JENSON BUTTON    | MCLAREN       |     76 |             2
       8 | ROMAIN GROSJEAN  | LOTUS         |     76 |             2
       9 | SERGIO PEREZ     | SAUBER        |     47 |             1
      10 | KAMUI KOBAYASHI  | SAUBER        |     33 |             2

A ordenação foi necessária neste caso porque apesar da consulta ser ordenada pela posição, a window function processa os dados de acordo com sua própria ordenação. 

Nesta última query temos o uso da função row_number, que é uma window function embutida. Funções deste tipo devem ser chamadas somente usando a sintaxe da window function (onde a cláusula OVER é obrigatória). 

Tanto as funções embutidas quanto as de agregação podem ser usadas como window functions, mas as de agregação somente se comportam como window functions quando chamadas com a cláusula OVER, caso contrário serão apenas funções de agregação regulares. Outras funções embutidas podem ser encontradas aqui, mas vou apresentar algumas: 

• lag(coluna, deslocamento) - Usada para retornar valores de linhas anteriores onde o deslocamento determina quantas linhas antes. 
• lead(coluna, deslocamento) - Parecida com a função anterior, mas para linhas posteriores. 
• first_value(coluna) - Retorna o valor da primeira linha. 
• last_value(coluna) - Retorna o valor da última linha. 

E pra finalizar, caso uma query tenha mais de uma window function com o mesmo comportamento, é possível utilizar uma cláusula WINDOW que será referenciada em OVER: 

SELECT *, row_number() OVER w AS numero_piloto
FROM formula1
WINDOW w AS (PARTITION BY equipe ORDER BY posicao)
ORDER BY posicao

Referência: http://www.postgresql.org/docs/9.1/static/tutorial-window.html

Exemplo de função no PostgreSQL

Esta semana precisei criar uma função no banco de dados PostgreSQL que me retornasse o menor valor dentre outros cotados. Nesta função eu passo dois parâmetros: codigo do material e codigo da cotação (ambos do tipo inteiro).

A função me retorna um tipo NUMERIC(20, 2), que é o tipo no qual venho trabalhando com valores monetários pois segundo a própria documentação do PostgreSQL:

It is especially recommended for storing monetary amounts and other quantities where exactness is required.

Outro detalhe importante que podemos observar é que na query, os valores contidos nos parâmetros são lidos utilizando $1 e $2 respectivamente. Vejamos:

CREATE FUNCTION func_menor_valor(codigo_material INT, codigo_cotacao INT)
RETURNS NUMERIC(20, 2) AS $$ 

 SELECT MIN(valor_total) AS menor_valor FROM view_cotacoes
 WHERE codigo_material = $1 AND codigo_cotacao = $2
 GROUP BY codigo_material;

$$ LANGUAGE SQL;

Enfim, isso é apenas um exemplo básico de função. Mais informações podem ser encontradas na documentação. Também é importante lembrar que a versão do PostgreSQL utilizada foi a 9.1.