Tecnologia

WINDOW FUNCTIONs no PostgreSQL – Parte 1

Por: , outubro 22, 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.

  • Receba nosso conteúdo em primeira mão.