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
