Trabalhando com Windows Functions no PostgreSQL

As Windows Functions são trabalhadas com um conjunto de linhas definidas por uma cláusula OVER, que permite trabalhar com totais, agrupamentos, ordenações, cálculos complexos dentre outros. Assim, conseguimos melhorar a performance com ordenações avançadas, além de limitarmos o número de linhas que serão retornadas em um subconjunto de dados associados a uma determinada tabela. As funções de agregação que são definidas pelo usuário também podem atuar como Windows Functions quando estas possuem uma chamada com a palavra-chave OVER.

A seguir veremos com mais detalhes as Windows Functions definidas no PostgreSQL, que hoje são um total de 11: cume_dist(), row_number(), rank(), dense_rank(), present_rank(), first_value(), last_value(), nth_value(), ntile(), lag() e a lead().

Estudando as Windows Functions

Função Cume_dist()

Esta é utilizada com o intuito de obtermos a classificação da linha atual. Para que este resultado seja obtido é realizado um cálculo no qual ocorre a divisão do número de linhas anteriores a linha atual pelo total de linhas encontradas. Essa razão é apresentada na fórmula a seguir, onde o tipo de retorno é o double precision:

Linha atual = (Número de linhas anteriores a linha atual)/(número total de linhas)

Na Listagem 1 temos um exemplo simples da criação de uma nova tabela, a qual chamaremos de funcionarios_windows_function. O nome do banco de dados fica a critério, mas no nosso caso utilizamos TesteDevmedia.

Listagem 1. Criação da tabela funcionarios_windows_function.

CREATE TABLE funcionarios_windows_function
      (
        codigo_func integer NOT NULL,
        nome_func character varying(100) NOT NULL,
        profissao character varying(100) NOT NULL,
        nome_departamento character varying(100) NOT NULL,
        departamento_cod integer NOT NULL,
        salario real,
        CONSTRAINT funcionarios_windows_function_pkey PRIMARY KEY (codigo_func)
      )
      WITH (
        OIDS=FALSE
      );
      ALTER TABLE funcionarios
        OWNER TO postgres;

Com a nossa tabela de testes criada, inserimos alguns dados de teste, como vemos na Listagem 2.

Listagem 2. Inserindo dados na tabela funcionarios_windows_function.

INSERT INTO FUNCIONARIOS_WINDOWS_FUNCTION (codigo_func, nome_func, profissao, nome_departamento, departamento_cod, SALARIO) VALUES (1, 'Edson Dionisio', 'Desenvolvedor Web', 'Desenvolvimento web', 10, 2000.00);
        INSERT INTO FUNCIONARIOS_WINDOWS_FUNCTION (codigo_func, nome_func, profissao, nome_departamento, departamento_cod, SALARIO) VALUES (2, 'Marília Késsia', 'Scrum Master', 'Desenvolvimento', 10, 6000.00);
        INSERT INTO FUNCIONARIOS_WINDOWS_FUNCTION (codigo_func, nome_func, profissao, nome_departamento, departamento_cod, SALARIO) VALUES (3, 'Caroline França', 'Desenvolvedor Android', 'Mobile', 30, 2500.00);
        INSERT INTO FUNCIONARIOS_WINDOWS_FUNCTION (codigo_func, nome_func, profissao, nome_departamento, departamento_cod, SALARIO) VALUES (4, 'Gustavo França', 'Desenvolvedor IOS', 'Mobile', 30, 2800.00);
        INSERT INTO FUNCIONARIOS_WINDOWS_FUNCTION (codigo_func, nome_func, profissao, nome_departamento, departamento_cod, SALARIO) VALUES (5, 'Renato silva', 'Desenvolvedor de Sistemas', 'Desenvolvimento de Sistemas', 10, 2000.00);
        INSERT INTO FUNCIONARIOS_WINDOWS_FUNCTION (codigo_func, nome_func, profissao, nome_departamento, departamento_cod, SALARIO) VALUES (6, 'João dos testes', 'Analista de Testes', 'Testes', 16, 2000.00);
        INSERT INTO FUNCIONARIOS_WINDOWS_FUNCTION (codigo_func, nome_func, profissao, nome_departamento, departamento_cod, SALARIO) VALUES (7, 'Maria das dores', 'Análista de Software', 'Engenharia de software', 12, 3000.00);
        INSERT INTO FUNCIONARIOS_WINDOWS_FUNCTION (codigo_func, nome_func, profissao, nome_departamento, departamento_cod, SALARIO) VALUES (8, 'Rodrigo Sampaio', 'Desenvolvedor Windows Phone', 'Mobile', 30, 2600.00);

Com a inserção dos registros na tabela de testes utilizaremos o comando SELECT para ver se as informações foram inseridas corretamente:

SELECT * FROM funcionarios_windows_function

Após isso, utilizaremos a mesma consulta, só que dessa vez adicionando a função cume_dist(), para ver o resultado da operação, conforme a seguinte instrução:

select *, cume_dist() OVER (ORDER BY departamento_cod) from FUNCIONARIOS_WINDOWS_FUNCTION;

Ao utilizarmos a cláusula OVER para o código do departamento, temos que a função cume_dist() irá atribuir o mesmo valor para os departamentos que tenham o mesmo código, conforme vemos na Figura 1.

Figura 1. Resultado da utilização da função cume_dist.

Repare que temos a representação visual de um ranking sendo apresentado em ordem crescente, com base no código do departamento.

Função row_number()

Essa função é utilizada para obter o número da linha atual dentro de sua partição, sendo este iniciado com o valor um. Na Listagem 4 temos um exemplo do seu uso.

Listagem 4. Utilizando a função row_number().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      row_number() 
      OVER (PARTITION BY departamento_cod)
       FROM funcionarios_windows_function;
    

Como resultado da consulta temos que cada um dos registros apresenta o número de fileiras com base no código dos departamentos, mostrando dessa forma a partição entre eles. Podemos ver o resultado da consulta na Figura 2.

Figura 2. Consulta com a função row_number().

A quantidade de partições é apresentada com base na quantidade de registros com o mesmo código do departamento, de forma que os departamentos Mobile e de desenvolvimento possuem três registros sendo apresentados na partição, enquanto que os demais apresentam apenas uma partição.

Função Rank()

A função rank() é utilizada basicamente para obtermos a classificação da linha atual onde, em caso de haver empate, o resultado será repetido entre as linhas com mesmo código, como mostra a Listagem 5.

Listagem 5. Utilizando a função rank().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      rank() OVER (
      PARTITION BY departamento_cod ORDER BY nome_departamento)
      FROM funcionarios_windows_function;
    

Como o resultado da consulta anterior temos os departamentos classificados de acordo com o nome, estando estes separados em partições, como podemos ver na Figura 3.

Figura 3. Utilizando a função rank().

Agora vamos considerar o seguinte caso: ao invés de utilizarmos a função para classificarmos os registros com base no código do departamento, vamos fazer a consulta baseada no nome do departamento, como mostra a Listagem 6.

Listagem 6. Consulta com base no nome do departamento.

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      rank() OVER (ORDER BY nome_departamento)
        FROM funcionarios_windows_function;
    

A nossa consulta muda o resultado, assim a classificação será com base no nome do departamento, atribuindo o mesmo valor classificatório para os itens repetidos, como podemos ver na Figura 4. Percebam também que obstruímos a pesquisa com o PARTITION BY.

Figura 4. Utilizando a função rank() ordenando pelo nome do departamento.

Função dense_rank()

A função dense_rank() também é utilizada para a obtenção da classificação atual dos registros, onde as linhas que apresentam valores iguais para os critérios de classificação recebem o mesmo valor, apresentando uma numeração contínua. Esta função difere da função rank() em apenas um aspecto: em caso de empate entre duas ou mais linhas, não havendo nenhuma lacuna presente na sequência dos valores classificados. Para demonstrarmos esse exemplo, acompanhe a Listagem 7.

Listagem 7. Utilização da função dense_rank().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      dense_rank() 
      OVER (ORDER BY departamento_cod)
      FROM funcionarios_windows_function;

Veja que temos apenas quatro departamentos sendo apresentados, então teremos a classificação máxima com o valor 4, como podemos ver na distribuição apresentada na Figura 5.

Figura 5. Distribuição classificatória com o dense_rank().

Em caso de utilizarmos a coluna “nome_departamento” na cláusula OVER, como fizemos anteriormente na Listagem 6, teremos um resultado diferente por conta das diferentes profissões cadastradas, como podemos ver na Listagem 8.

Listagem 8. Utilização da coluna “nome_departamento”.

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      dense_rank() 
      OVER (ORDER BY nome_departamento)
      FROM funcionarios_windows_function;

Como resultado desta nova consulta obtivemos o rank gerado até a sexta posição, pois temos seis departamentos distintos sendo apresentados, como mostra a Figura 6.

Figura 6. Classificação com base no nome_departamento.

Função percent_rank()

Quando precisamos obter uma classificação relativa das classificações, podemos utilizar a função percent_rank(), que é utilizada para obtermos a classificação relativa da linha atual. Para que tenhamos a posição relativa da linha atual, realizamos o cálculo com base na seguinte fórmula:

Posição relativa da linha atual = (rank - 1) / (número total de linhas - 1)

Vejamos agora com base na Listagem 9, um exemplo simples de sua utilização para uma melhor compreensão.

Listagem 9. Utilizando a função percent_rank().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      percent_rank() 
      OVER (PARTITION BY departamento_cod ORDER BY profissao)
      FROM funcionarios_windows_function;

Aqui continuamos utilizando a cláusula PARTITION BY, com relação ao código do departamento, dessa forma teremos que a função percent_rank() será com base no mesmo código, o que podemos ver na Figura 7.

Figura 7. Utilização da função percent_rank().

Percebam que neste caso, para os departamentos com o mesmo código, obtivemos os valores de 0, 0.5 e 1. Enquanto que os demais registros obtiveram o valor 0.

Função first_value()

Esta função é utilizada para a obtenção do valor presente na primeira linha da tabela. Para isso passamos o nome da coluna requerida como argumento de entrada, como mostra o exemplo da Listagem 10.

Listagem 10. Utilizando a função first_value().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      first_value(departamento_cod) OVER (ORDER BY departamento_cod)
      FROM funcionarios_windows_function WHERE departamento_cod > 12;

Com base na Listagem 10, temos a consulta na tabela, onde buscamos os registros que tenham o código de departamento maior que 12, após isso aplicamos a cláusula Order By pelo código do departamento. Dessa forma, como retorno, teremos uma lista contendo os departamentos com código acima de 12. Como utilizamos a função first_value, obtivemos o primeiro código da tabela e este será apresentado como resultado para todas as linhas presentes, como podemos ver na Figura 8.

Figura 8. Windows Function first_value().

Num segundo exemplo, poderíamos utilizar a cláusula order by para ordenarmos os registros por nome do departamento, onde com isso, obteremos um valor diferente para o valor da função first_value(), como podemos ver presente na Listagem 11.

Listagem 11. Ordenando por nome do departamento.

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      first_value(departamento_cod) OVER (ORDER BY nome_departamento)
      FROM funcionarios_windows_function WHERE departamento_cod > 12;

Função last_value()

Ao contrário da função first_value(), a função last_value() é utilizada para a obtenção do valor presente na última linha de registro presente na tabela, onde utilizamos o nome da coluna como argumento, de igual forma a função anterior. Podemos ver de acordo com a Listagem 12 como proceder com essa função.

Listagem 12. Utilizando a função last_value().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      last_value(departamento_cod) OVER (ORDER BY nome_departamento)
      FROM funcionarios_windows_function;

Após a consulta, obtivemos o resultado presente na Figura 9, onde o nosso último registro, com base na ordenação por nome de departamento, é o departamento de testes, que corresponde ao código do departamento 16.

Figura 9. Utilizando a função last_value().

Função nth_value()

A próxima função a ser vista é a nth_value(), a qual nos possibilita receber um valor diferente do inicial e do final, obtendo assim um valor presente na enésima linha da tabela. Para utilizar esta função passamos o nome da coluna desejada e o enésimo número como argumentos de entrada. Caso o valor informado não seja encontrado na tabela, o valor apresentado pela função será nulo, como mostra a Listagem 13 como proceder.

Listagem 13. Obtendo resultados com a função nth_value().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      nth_value(nome_departamento, 2) 
      OVER (PARTITION BY departamento_cod ORDER BY nome_departamento)
      FROM funcionarios_windows_function;

Veja que estamos utilizando a cláusula PARTITION BY para dividirmos os registros com base no código do departamento, onde cada partição criada terá um número de saída, que será o valor que utilizaremos para a função nth_value. Observe o resultado apresentado na Figura 10.

Figura 10. Utilizando a Windows Function nth_value().

Função ntile()

Esta função nos permite atribuir valores para grupos de resultados, ou seja, um número inteiro a eles. Para melhor entendermos a sua utilização, vejamos o exemplo presente na Listagem 14.

Listagem 14. Utilizando a Window Function ntile().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      ntile(2) 
      OVER (ORDER BY departamento_cod)
      FROM funcionarios_windows_function;

A tabela está dividida em duas partições pela função ntile(), como mostra o resultado presente na Figura 11.

Figura 11. Resultado da utilização do ntile().

Neste próximo exemplo da Listagem 15, vejamos como a função irá se comportar ao utilizarmos o valor 3 como argumento e, com isso, ver o impacto sobre os resultados.

Listagem 15. Utilizando um novo argumento para o ntile().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      ntile(3) 
      OVER (ORDER BY departamento_cod)
      FROM funcionarios_windows_function;
    

Com a mudança do argumento, apenas aumentamos a quantidade de partições que podem ser utilizadas pelo ntile().

Função Lag()

A função lag() é utilizada para acessarmos mais de uma linha presente na tabela ao mesmo tempo, sem a necessidade de utilizarmos o SELF JOIN. Para entendermos essa funcionalidade vejamos o código da Listagem 16 como proceder.

Listagem 16. Utilizando a função lag().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      lag(departamento_cod, 3) 
      OVER (ORDER BY departamento_cod)
      FROM funcionarios_windows_function;
    

Veja que passamos para a função o código do departamento e como segundo argumento o valor 3. Nessa hora estamos passando um valor para o deslocamento, que no nosso caso é o 3, e isto significa que o cursor vai começar a partir do quarto registro da tabela. Dessa forma, faremos uma SELF JOIN com base no código do departamento e o restante dos registros, como mostra a Figura 12.

Figura 12. Utilizando a função Lag().

Função lead()

A última função a ser apresentada é a lead(), a qual é utilizada para obtermos os valores retornados para linhas de registro com base no deslocamento abaixo da linha atual da partição. Se o argumento de deslocamento não é informado no momento de chamarmos a função, ela será definida como um, por padrão. Vejamos como isso será executado com base no exemplo apresentado pela Listagem 17. Neste nós temos a tabela com base no código do departamento e, em seguida, chamamos a função lead().

Listagem 17. Utilizando a Windows Function lead().

SELECT codigo_func, nome_func, profissao, nome_departamento, departamento_cod, salario,
      lead(nome_departamento, 1) 
      OVER (PARTITION BY departamento_cod ORDER BY departamento_cod)
      FROM funcionarios_windows_function;

Podemos ver o resultado da consulta na Figura 13.

Figura 13. Resultado da utilização da função lead().

Com isso finalizamos o nosso artigo, onde vimos funções que nos auxiliam com relação a melhoria de performance e redução de código, no caso de obtermos resultados sem a necessidade realizarmos JOINS entre tabelas. Esperamos que tenham gostado. Até a próxima! =)

Link

Documentação
http://www.postgresql.org/docs/9.4/static/tutorial-window.html