.Windows Functions – PostgreSQL

Juliana Jenny Kolb

Home > Simulados on-line  > Questões de Concursos > Tecnologia da Informação (TI) > PostgreSQL

Windows Functions – 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.

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

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

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

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.

Exemplo:

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;

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.

Exemplo:

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

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)

Exemplo:

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;

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.

Exemplo:

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;

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.

Exemplo:

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

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.

Exemplo:

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;

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.

Exemplo:

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

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.

Exemplo:

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

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.

Exemplo:

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;

Sites de Referência

http://www.postgresql.org/docs/9.4/static/tutorial-window.html

http://www.devmedia.com.br/