WINDOW FUNCTIONs no PostgreSQL – Parte 1

Um recurso presente desde a versão 8.4 do PostgreSQL, mas que muitos ainda não conhecem ou não sabem como...

Dextra

View posts by Dextra
Somos especialistas em desenvolvimento de software sob medida para negócios digitais. Pioneiros na adoção de metodologias de gestão ágil, combinamos processos de design, UX, novas tecnologias e visão de negócio, desenvolvendo soluções que criam oportunidades para nossos clientes. A Dextra faz parte da Mutant, empresa B2B líder no mercado brasileiro e especialista em Customer Experience para plataformas digitais.
Data de publicação: 22/10/2012


Um recurso presente desde a versão 8.4 do PostgreSQL, mas que muitos ainda não conhecem ou não sabem como utilizar, são as chamadas WINDOW FUNCTIONS, ou, numa tradução literal, “FUNÇÕES DE JANELA”. Trata-se de um recurso muito interessante, que foi definido no padrão SQL:2003, a fim de potencializar as consultas em contextos OLAP.
Considerando um conjunto de linhas de uma consulta, uma window function é capaz de realizar cálculos em uma linha com base em dados presentes em linhas relacionadas à esta (de mesmo valor, vizinhas, etc.). Nesse contexto, um grupo de linhas correlacionadas é considerada uma janela (“window”). Na própria consulta definimos qual serão as janelas para que uma função de agregação trabalhe sobre a mesma.
Esta funcionalidade pode ser considerada complementar às clássicas funções de agregação (SUM, COUNT, MIN, MAX, etc.)
, que estão comumente associadas à clausula GROUP BY, entretanto estas podem ser usadas também como window function. A principal diferença é que o uso de uma window function não faz com que o resultado seja agrupado em apenas uma linha, podendo este ser distribuído pelas linhas presentes originalmente na consulta. Isso possibilita alcançar resultados antes possíveis somente com linguagens procedurais ou subconsultas.

Vamos à prática

Para facilitar o entendimento, consideramos como exemplo um banco de dados que armazena a quantidade de acessos às páginas de uma empresa. De forma simplificada, o esquema da tabela poderia ser o seguinte:

CREATE TABLE paginas (
  dominio text not null, -- [Sub]domínio
  url text not null,     -- URL, dentro do [sub]domínio
  acessos integer,       -- quantidade de acessos
  tags text[],           -- array de tags
  CONSTRAINT pk_paginas PRIMARY KEY (dominio, url)
);

Inserindo alguns dados:

INSERT INTO paginas(dominio,url,acessos,tags)
VALUES
('www.example.com', '/index.html',        448, '{index,exemplo}'),
('www.example.com', '/contato.html',      201, '{contato}'),
('www.example.com', '/exemplo.html',      272, '{exemplo,teste}'),
('blog.example.com', '/index.html',       513, '{blog}'),
('blog.example.com', '/postgresql.html',  896, '{blog,postgresql}'),
('blog.example.com', '/postgres-xc.html', 1036, '{postgresql,escalabilidade}'),
('evento.example.com', '/',                640, '{evento,postgresql}'),
('evento.example.com', '/inscricao.html',  289, '{evento,inscrição}');

Usando uma clássica função de agregação e o GROUP BY

Verificar a quantidade média de acesso por domínio:

SELECT dominio, AVG(acessos) AS media_acessos
FROM paginas
GROUP BY dominio;
      dominio       |     media_acessos
--------------------+----------------------
 www.example.com    | 307.0000000000000000
 blog.example.com   | 815.0000000000000000
 evento.example.com | 464.5000000000000000

Basicamente, o PostgreSQL irá agrupar toda linha que possua a mesma categoria.
Usando WINDOW FUNCTION

Definição de janela com o PARTITION BY

Tendo por base o exemplo anterior, como verificar, com uma única consulta, a comparação de acessos de cada URL com a média de acesso do domínio ao qual a mesma pertence?

SELECT dominio, url,
  AVG(acessos) OVER(PARTITION BY dominio) AS media_acessos
FROM paginas;
       dominio      |        url        |     media_acessos
--------------------+-------------------+----------------------
 www.example.com    | /contato.html     | 307.0000000000000000
 www.example.com    | /index.html       | 307.0000000000000000
 www.example.com    | /exemplo.html     | 307.0000000000000000
 blog.example.com   | /index.html       | 815.0000000000000000
 blog.example.com   | /postgres-xc.html | 815.0000000000000000
 blog.example.com   | /postgresql.html  | 815.0000000000000000
 evento.example.com | /inscricao.html   | 464.5000000000000000
 evento.example.com | /                 | 464.5000000000000000

A cláusula OVER referencia a janela que o PostgreSQL usará para realizar a agregação. A janela foi determinada usando o PARTITION BY, que, (de forma semelhante ao GROUP BY) agrupa as tuplas, fazendo com que todas do mesmo grupo, ou mesma partição, recebam o resultado da agregação. No nosso exemplo, consideramos o campo domínio como uma partição. A função AVG calcula a média para cada janela. Dessa forma, é possível manter as linhas originais apesar dos valores agregados.

Definição de janela com o ORDER BY

Ainda neste cenário, vamos substituir a coluna de média de acessos por ma coluna que mostre a soma parcial dos acessos. O resultado que buscamos é o seguinte:

      dominio       |        url        | acessos | parcial
--------------------+-------------------+---------+---------
 www.example.com    | /contato.html     |     201 |     201
 www.example.com    | /exemplo.html     |     272 |     473
 evento.example.com | /inscricao.html   |     289 |     762
 www.example.com    | /index.html       |     448 |    1210
 blog.example.com   | /index.html       |     513 |    1723
 evento.example.com | /                 |     640 |    2363
 blog.example.com   | /postgresql.html  |     896 |    3259
 blog.example.com   | /postgres-xc.html |    1036 |    4295

Para tanto, podemos definir nossa janela utilizando a cláusula ORDER BY:

SELECT dominio, url, acessos,
SUM(acessos) OVER(ORDER BY acessos, dominio, url) AS parcial
FROM paginas
ORDER BY acessos, dominio, url;

Quando definimos uma janela com o ORDER BY, o resultado é calculado primeiro ordenando as linhas, e, em seguida, calculando a agregação de forma incremental, linha a linha.
E se quisermos a soma parcial e a média, ambas agrupados por domínio? Da seguinte forma:

      dominio       |        url        | acessos | media_acessos | parcial
--------------------+-------------------+---------+---------------+---------
 blog.example.com   | /index.html       |     513 |         815.0 |     513
 blog.example.com   | /postgresql.html  |     896 |         815.0 |    1409
 blog.example.com   | /postgres-xc.html |    1036 |         815.0 |    2445
 evento.example.com | /inscricao.html   |     289 |         464.5 |     289
 evento.example.com | /                 |     640 |         464.5 |     929
 www.example.com    | /contato.html     |     201 |         307.0 |     201
 www.example.com    | /exemplo.html     |     272 |         307.0 |     473
 www.example.com    | /index.html       |     448 |         307.0 |     921

Além de manter a função AVG com a definição PARTITION BY, a função SUM é calculada sobre janelas definidas ao mesmo tempo por PARTITION BY e ORDER BY, como segue:

SELECT dominio, url, acessos,
  AVG(acessos) OVER(PARTITION BY dominio) AS media_acessos,
  SUM(acessos) OVER(PARTITION BY dominio ORDER BY acessos, url) AS parcial
FROM paginas
ORDER BY dominio, acessos, url;

Outras funções de agregação

Como mostrado nos exemplos acima, qualquer função de agregação já conhecida pode ser usado como uma window function, entretanto o PostgreSQL também oferece 11 funções específicas desta funcionalidade.
Por exemplo, usando a função row_number podemos adicionar um número sequencial em cada uma das linhas:

SELECT row_number() OVER(ORDER BY dominio, acessos, url),
  dominio, url, acessos,
  SUM(acessos) OVER(PARTITION BY dominio ORDER BY acessos, url) AS parcial
FROM paginas
ORDER BY dominio, acessos, url;
 row_number |      dominio       |        url        | acessos | parcial
------------+--------------------+-------------------+---------+---------
          1 | blog.example.com   | /index.html       |     513 |     513
          2 | blog.example.com   | /postgresql.html  |     896 |    1409
          3 | blog.example.com   | /postgres-xc.html |    1036 |    2445
          4 | evento.example.com | /inscricao.html   |     289 |     289
          5 | evento.example.com | /                 |     640 |     929
          6 | www.example.com    | /contato.html     |     201 |     201
          7 | www.example.com    | /exemplo.html     |     272 |     473
          8 | www.example.com    | /index.html       |     448 |     921

É possível fazer isto também por partições:

SELECT row_number() OVER(PARTITION BY dominio ORDER BY acessos, url),
dominio, url, acessos,
SUM(acessos) OVER(PARTITION BY dominio ORDER BY acessos, url) AS parcial
FROM paginas
ORDER BY dominio, acessos, url;
 row_number |      dominio       |        url        | acessos | parcial
------------+--------------------+-------------------+---------+---------
          1 | blog.example.com   | /index.html       |     513 |     513
          2 | blog.example.com   | /postgresql.html  |     896 |    1409
          3 | blog.example.com   | /postgres-xc.html |    1036 |    2445
          1 | evento.example.com | /inscricao.html   |     289 |     289
          2 | evento.example.com | /                 |     640 |     929
          1 | www.example.com    | /contato.html     |     201 |     201
          2 | www.example.com    | /exemplo.html     |     272 |     473
          3 | www.example.com    | /index.html       |     448 |     921

Conclusão

WINDOW FUNCTION é um recurso muito útil, principalmente para consultas a serem usadas em relatórios analíticos. Vale destacar que o PostgreSQL, até a escrita desse artigo, é o único banco de dados open-source a implementar esta funcionalidade, o que mostra, mais uma vez, que o elefante possui funcionalidades equiparáveis à bancos de dados comerciais.
Em breve, lançaremos aqui uma continuação desse artigo, mostrando exemplos com todas as 11 funções de janela presentes nativamente no PostgreSQL.
Aguarde.

Dextra

View posts by Dextra
Somos especialistas em desenvolvimento de software sob medida para negócios digitais. Pioneiros na adoção de metodologias de gestão ágil, combinamos processos de design, UX, novas tecnologias e visão de negócio, desenvolvendo soluções que criam oportunidades para nossos clientes. A Dextra faz parte da Mutant, empresa B2B líder no mercado brasileiro e especialista em Customer Experience para plataformas digitais.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

18 − dezessete =

Posts relacionados

  1. Sobre a Dextra

    Somos especialistas em desenvolvimento de software sob medida para negócios digitais. Pioneiros na adoção de metodologias de gestão ágil, combinamos processos de design, UX, novas tecnologias e visão de negócio, desenvolvendo soluções que criam oportunidades para nossos clientes.

  2. Categorias

Scroll to top