Dentro destas funções de janela e na utilização do frame, podemos especificar uma certa quantidade de linhas ao qual a função irá agir.

Dependendo do tipo de frame que utilizarmos definimos quantas linhas anteriores a atual e quantas linhas posteriores a atual uma determinada ação será executada.

FIRST AND LAST VALUE

São funções classificadas como funções de frame que retornam o primeiro e o último valor de uma coluna passada como parâmetro para a função dentro de uma query.

Como dito, por ser uma função do tipo frame, logicamente conseguimos especificar o tipo de frame que queremos usar em conjunto. Claro que aqui não iremos realizar nenhuma operação aritmética.

Por falar em operação aritmética, é possível criar bons relatórios utilizando este recurso em conjunto com algumas operações, veja este exemplo.

Cada linha de compra do cliente irá diminuir pelo valor da primeira compra, já que esta query está ordenada por cliente e data. Lembrando que quando temos este tipo de frame “rows between current row and unbounded following”, nossa operação começa de ‘baixo para cima’.

Uma situação interessante que ocorre quando omitimos o uso de frame com as funções de first e last value é a seguinte:

Percebam que a nossa query está particionada pela coluna customerid, logo os valores são “comparados” pelo cliente e as funções de first e last_value deveriam retornar a primeira e última compra realizada por tal independente do dia, mês ou até mesmo o ano, porém, observando o resultado devolvido pela coluna LVALUE não é o que acontece.

Isso ocorre pela falta do framing: “rows between current row and unbounded following” pois se nós repararmos, na coluna fvalue, o resultado está correto, considerando a partição cliente retornando para todas as linhas a primeira compra realizada. Perceba que até a data está ordenada de forma ascendente.

Para solucionar este pequeno problema, temos de explicitar o framing para a coluna LVALUE.

Um dos motivos de não ocorrer este ‘erro’ com o First_value é que implicitamente, toda query executada com window function por padrão já vem declarado ‘rows unbounded preceding‘ o que torna parte do resultado assertivo, como aconteceu no exemplo acima. Veja abaixo um exemplo com o framing devidamente aplicado para as duas colunas.

LAG E LEAD

Finalizando este assunto de offset e framing dentro das window function veremos as duas últimas funções que são LAG e LEAD.

Diferente do que vimos com first e last_value, aqui, além de passarmos uma coluna ao qual aplicaremos o ‘offset’ temos de indicar também um valor numérico que irá indicar quantas linhas ou um determinado período iremos avançar ou retroceder.

Esta função é muito útil pela facilidade em criar queries de comparação entre períodos como por exemplo, semestrais ou ate anuais.

Veja o primeiro exemplo:

Como as duas primeiras linhas não possuem valores anteriores que possam ser retornados, se tornam null. No exemplo, destaquei o valor do meio por ser fácil a visualização do resultado tanto para lag quanto para lead.

Na função de lag e lead não é obrigatório destacar um valor para a quantidade de linhas que se deseja visualizar, mas caso não o faça, assume 1 como padrão:

Um ponto interessante desta função é que ela pode substituir os nulls por um valor desejado, dispensando o uso de coalesce!!

Para isso, basta indicar no terceiro parâmetro um valor qualquer, logo após indicar quantas linhas deverá retirar do resultado final.

Tanto lag quanto lead não aceitam as funções de frame na query.

Com estas duas funções encerro o post sobre framing e exibição de resultados.

Não se esqueça de colocar a delimitação “rows between current row and unbounded following” quando for utilizar last_value para evitar inconsistência no resultado gerado na query.

Lag e lead podem ser ótimas quando for utilizar para comparar resultados por x períodos ou valores, é uma boa ferramenta para se dominar!

Espero que gostem! Saúde!

Publicidade