WINDOW FUNCTIONs no PostgreSQL – Parte 2

Se você perdeu, essa é a continuação do artigo WINDOW FUNCTIONs no PostgreSQL. No primeiro, apresentamos um introdução prática...

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: 04/11/2013

Se você perdeu, essa é a continuação do artigo WINDOW FUNCTIONs no PostgreSQL. No primeiro, apresentamos um introdução prática sobre o que são as famigeradas “Funções de Janela”, com exemplos de uso com funções de agregação simples. Como prometido, vamos mostrar cada uma das 11 funções específicas para WINDOW FUNCTIONS do PostgreSQL, mas faremos isso em duas partes. Nessa primeira parte serão vistas 6 funções que possuem propósitos similares: ranquear as linhas baseando-se em ordenações (podendo também dividir em partições).

Todas as funções de ranqueamento (ou ranking) que apresentaremos aqui trabalham primeiramente realizando a ordenação dos registros, definido pela janela ORDER BY, e, em seguida, enumerando cada linha segundo a posição da mesma após a ordenação. Esta posição pode ser relativa a todos os registros ou então à uma determinada partição, caso utilizamos a janela PARTITION BY.

Vamos à prática?

Este será, mais uma vez, um artigo bem prático, e usaremos o mesmo modelo e dados da primeira parte do artigo:

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)
);
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}');

Mas vamos adicionar mais um registro para potencializar nossos exemplos:

INSERT INTO paginas(dominio,url,acessos,tags)
VALUES
('www.example.com', '/empresa.html', 513, '{empresa,exemplo}');

Função row_number()

Esta função, como o nome sugere, simplesmente retorna o número da linha atual. Podemos pensar que o PostgreSQL primeiro irá ordenar os registros, pelos campos definidos na cláusula ORDER BY e, em seguida, enumerar de 1 até a quantidade total de registros (incrementando sempre de 1 em 1):

SELECT row_number() OVER(ORDER BY acessos DESC), dominio, url, acessos
FROM paginas
ORDER BY acessos DESC;
 row_number |      dominio       |        url        | acessos
------------+--------------------+-------------------+---------
          1 | blog.example.com   | /postgres-xc.html |    1036
          2 | blog.example.com   | /postgresql.html  |     896
          3 | evento.example.com | /                 |     640
          4 | www.example.com    | /empresa.html     |     513
          5 | blog.example.com   | /index.html       |     513
          6 | www.example.com    | /index.html       |     448
          7 | evento.example.com | /inscricao.html   |     289
          8 | www.example.com    | /exemplo.html     |     272
          9 | www.example.com    | /contato.html     |     201
(9 rows)

Claro que podemos também podemos utilizar a cláusula PARTITION BY para enumerar por partições:

SELECT row_number() OVER(PARTITION BY dominio ORDER BY acessos DESC), dominio, url, acessos
FROM paginas
ORDER BY dominio, acessos DESC;
 row_number |      dominio       |        url        | acessos
------------+--------------------+-------------------+---------
          1 | blog.example.com   | /postgres-xc.html |    1036
          2 | blog.example.com   | /postgresql.html  |     896
          3 | blog.example.com   | /index.html       |     513
          1 | evento.example.com | /                 |     640
          2 | evento.example.com | /inscricao.html   |     289
          1 | www.example.com    | /empresa.html     |     513
          2 | www.example.com    | /index.html       |     448
          3 | www.example.com    | /exemplo.html     |     272
          4 | www.example.com    | /contato.html     |     201
(9 rows)

Ou seja, se adicionarmos a cláusula PARTITION BY, a função row_number() irá reiniciar seu contador interno a cada vez que entrar numa nova partição.

Função rank()

A função rank() possui um resultado similar à row_number(), porém, no caso de empates (mesmo valor nos campos do ORDER BY) ela irá repetir o resultado. Por exemplo, as URLs “www.example.com/empresa.html” e “blog.example.com/index.html” possuem a mesma quantidade de acessos, logo elas devem possuir o mesmo ranking (por acesso):

SELECT rank() OVER(ORDER BY acessos DESC), dominio, url, acessos
FROM paginas
ORDER BY acessos DESC;
 rank |      dominio       |        url        | acessos
------+--------------------+-------------------+---------
    1 | blog.example.com   | /postgres-xc.html |    1036
    2 | blog.example.com   | /postgresql.html  |     896
    3 | evento.example.com | /                 |     640
    4 | www.example.com    | /empresa.html     |     513
    4 | blog.example.com   | /index.html       |     513
    6 | www.example.com    | /index.html       |     448
    7 | evento.example.com | /inscricao.html   |     289
    8 | www.example.com    | /exemplo.html     |     272
    9 | www.example.com    | /contato.html     |     201
(9 rows)

Repare que os valores retornados não são contínuos, como no caso do “www.example.com/index.html”, que retornou 6 ao invés de 5. Isso porque houve dois registros com rank=4, ou seja, o contador interno é incrementado mesmo em caso de empates, mas é exibido o mesmo resultado para aquelas que empataram.

Função dense_rank()

Esta é similar à função rank() aplicando também o mesmo rank para empates, entretanto a numeração geral é contínua (não há saltos). Repare no exemplo e compare com o mesmo resultado da função rank():

SELECT dense_rank() OVER(ORDER BY acessos DESC), dominio, url, acessos
FROM paginas
ORDER BY acessos DESC;
 dense_rank |      dominio       |        url        | acessos
------------+--------------------+-------------------+---------
          1 | blog.example.com   | /postgres-xc.html |    1036
          2 | blog.example.com   | /postgresql.html  |     896
          3 | evento.example.com | /                 |     640
          4 | www.example.com    | /empresa.html     |     513
          4 | blog.example.com   | /index.html       |     513
          5 | www.example.com    | /index.html       |     448
          6 | evento.example.com | /inscricao.html   |     289
          7 | www.example.com    | /exemplo.html     |     272
          8 | www.example.com    | /contato.html     |     201
(9 rows)

Função percent_rank()

A percent_rank() traz a mesma informação da função rank(), mas com valor relativo (decimal) entre 0 (primeiro registro) e 1 (último registro):

SELECT percent_rank() OVER(ORDER BY acessos DESC), dominio, url, acessos
FROM paginas
ORDER BY acessos DESC;
 percent_rank |      dominio       |        url        | acessos
--------------+--------------------+-------------------+---------
            0 | blog.example.com   | /postgres-xc.html |    1036
        0.125 | blog.example.com   | /postgresql.html  |     896
         0.25 | evento.example.com | /                 |     640
        0.375 | www.example.com    | /empresa.html     |     513
        0.375 | blog.example.com   | /index.html       |     513
        0.625 | www.example.com    | /index.html       |     448
         0.75 | evento.example.com | /inscricao.html   |     289
        0.875 | www.example.com    | /exemplo.html     |     272
            1 | www.example.com    | /contato.html     |     201
(9 rows)

O resultado da mesma é exatamente o resultado de (rank - 1) / (total_linhas - 1), como no exemplo:

SELECT percent_rank() OVER(ORDER BY acessos DESC),
    ((rank() OVER(ORDER BY acessos DESC)) - 1)/((count(*) OVER()) -1)::double precision AS formula, dominio, url, acessos
FROM paginas
ORDER BY acessos DESC;
 percent_rank | formula |      dominio       |        url        | acessos
--------------+---------+--------------------+-------------------+---------
            0 |       0 | blog.example.com   | /postgres-xc.html |    1036
        0.125 |   0.125 | blog.example.com   | /postgresql.html  |     896
         0.25 |    0.25 | evento.example.com | /                 |     640
        0.375 |   0.375 | www.example.com    | /empresa.html     |     513
        0.375 |   0.375 | blog.example.com   | /index.html       |     513
        0.625 |   0.625 | www.example.com    | /index.html       |     448
         0.75 |    0.75 | evento.example.com | /inscricao.html   |     289
        0.875 |   0.875 | www.example.com    | /exemplo.html     |     272
            1 |       1 | www.example.com    | /contato.html     |     201
(9 rows)

Ela possui uma utilidade muito interessante, dado um registro é possível determinar rapidamente a posição do mesmo em relação aos demais registros, por exemplo, a url “www.example.com/index.html” possui o percent_rank de 0,625, ou seja esta está um pouco à frente da metade (50%) dos registros, mais precisamente em 62,5%.

Função cume_dist()

Assim como a percent_rank(), esta traz a posição relativa do registro corrente, mas considerando o número de registros que precedem ou são iguais ao registro corrente dividido pelo total de registros:

SELECT cume_dist() OVER(ORDER BY acessos DESC), dominio, url, acessos
FROM paginas
ORDER BY acessos DESC;
     cume_dist     |      dominio       |        url        | acessos
-------------------+--------------------+-------------------+---------
 0.111111111111111 | blog.example.com   | /postgres-xc.html |    1036
 0.222222222222222 | blog.example.com   | /postgresql.html  |     896
 0.333333333333333 | evento.example.com | /                 |     640
 0.555555555555556 | blog.example.com   | /index.html       |     513
 0.555555555555556 | www.example.com    | /empresa.html     |     513
 0.666666666666667 | www.example.com    | /index.html       |     448
 0.777777777777778 | evento.example.com | /inscricao.html   |     289
 0.888888888888889 | www.example.com    | /exemplo.html     |     272
                 1 | www.example.com    | /contato.html     |     201
(9 rows)

Função ntile(n)

A função ntile(n) tem a função de dividir os registros (podendo ser nas partições) em grupos o mais equalitários possível (considerando o número de elementos do grupo). Cada grupo é numerado de 1 até o parâmetro informado n.

Por exemplo, se quiséssemos dividir nossas URLs em 4 grupos, considerando os primeiro grupos com as mais acessadas e os últimos com a menos acessadas, teríamos a seguinte consulta:

SELECT ntile(4) OVER(ORDER BY acessos DESC), dominio, url, acessos
FROM paginas
ORDER BY acessos DESC;
 ntile |      dominio       |        url        | acessos
-------+--------------------+-------------------+---------
     1 | blog.example.com   | /postgres-xc.html |    1036
     1 | blog.example.com   | /postgresql.html  |     896
     1 | evento.example.com | /                 |     640
     2 | blog.example.com   | /index.html       |     513
     2 | www.example.com    | /empresa.html     |     513
     3 | www.example.com    | /index.html       |     448
     3 | evento.example.com | /inscricao.html   |     289
     4 | www.example.com    | /exemplo.html     |     272
     4 | www.example.com    | /contato.html     |     201
(9 rows)

No exemplo, apenas o grupo 1 possui 3 elementos, os demais possuem exatamente 2 elementos, ou seja, a melhor divisão inteira possível para 9 registros em 4 grupos.

Conclusão

Mais uma vez as WINDOW FUNCTIONs se mostraram muito eficazes, nesse caso para dividir, enumerar ou agrupar registros baseados numa dada ordenação, podendo também dividir em partições.

Na terceira (e provavelmente última) parte da série, vamos explorar as 5 funções que faltam: lag, lead, first_value, last_value e nth_value. Veremos como estas podem ser usadas para recuperar valores de registros relativos ao atual. 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.

Comentários

  1. Angelo10 de dezembro de 2014

    Muito bom, Matheus.
    Bastante SUCINTO e esclarecedor.
    Alguma previsão para o lançamento da parte 3?
    Estou ANSIOSO!

    Responder
  2. Alberto jose paulo27 de Maio de 2015

    gostei do que e publicado… quero mais….

    Responder

Deixe uma resposta

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

2 × 4 =

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