No post anterior vimos como retornar os valores antes e depois da linha atual na mesma linha facilitando a nossa visualização. Neste post quero apresentar um novo formato de visualização onde iremos retornar para um determinado item, seu primeiro e último valor registrado.
Também irei acrescentar neste post como podemos retornar o terceiro valor para um grupo através de uma solução de contorno. Vejam a seguir.
FIRST VALUE
Essa é a função utilizada para retornar o primeiro valor de um determinado objeto no banco. Por exemplo: Se realizamos uma consulta e nela queremos saber qual foi o primeiro valor registrado pelo cliente, podemos utilizar esta função para tal
Essas funções precisam de um ‘delimitador’ de exibição que poderíamos chamar de frame (irei explicar sobre nos próximos posts) para exibir o valor desejado.
E assim como Lag e Lead também é possível realizar operações com elas.
Veja o exemplo abaixo, da query retornando a data do primeiro pedido e reparem que a coluna orderdate tem seu valor alterado mas, a coluna firstorder não se altera.
Também é possível obter o mesmo resultado da anterior da seguinte forma:
LAST VALUE
Esta função segue a mesma lógica de exibição que a anterior, retornando apenas o último valor registrado por um determinado parâmetro.
Diferente da função de FIRST_VALUE, esta possui uma cláusula de exibição maior para funcionar corretamente, principalmente quando queremos realizar uma comparação com o conjunto de resultados.
A cláusula de exibição que devemos declarar é: ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. Este parâmetro passado ao final da query faz com que haja uma comparação entre os valores retornados e assim, retorna o último valor registrado.
Por exemplo, pegando a ideia da query anterior, vamos ver qual foi o último pedido registrado pelo cliente com o ID 02. Reparem que mesmo mudando a forma como o order by funciona, o resultado é o mesmo. Veja abaixo:
RETORNANDO APENAS VALORES DISTINTOS
Se porventura não quiser que os valores retornados da query venham repetidos, temos essa solução de contorno onde retornamos apenas o primeiro e último valor da compra de cada cliente.
Nesta query eu utilizei a função ROW_NUMBER com dois tipos de ordenamento: ascendente e descendente dentro de uma CTE, ordenando a query pela data do pedido.
Após, apliquei um select utilizando a cláusula CASE criando uma condição para as duas colunas onde a linha que será retornada, será a linha 1. Quando a cláusula case encontrar essa linha, me retorne o valor registrado chamando de coluna: FIRSTV & LASTV.
Ao final, utilizamos o GROUP BY para exibir apenas um cliente por linha.
Primeiro, veja como a query que forma a C.T.E retorna o resultado.
Veja que o maior valor vem em primeiro por ter sido o pedido mais recente, já que a data está do maior para o menor e isso influencia como o resultado é exibido para as duas colunas.
RNFV – ROW NUMBER FIRST VALUE
RNLV – ROW NUMBER LAST VALUE.
Agora, veja abaixo como fica quando tornamos o resultado distinto conforme explicação acima.
RETORNANDO UM VALOR DE UM PEDIDO ESPECÍFICO
Nesta query, vamos observar como podemos retornar um determinado valor de acordo com uma variável passada para a query em questão.
CALCULANDO DIFERENÇAS UTILIZANDO AS FUNÇÕES
Veja neste exemplo como podemos utilizar estas funções para calcular diferenças entre pedidos feitos por clientes que podemos passar por parâmetros declarando uma variável.
Nesta query calculamos o seguinte:
- Diferença de cada pedido com o primeiro pedido;
- Diferença de cada pedido com o último pedido;
- Retornar o primeiro pedido;
- Retornar o último pedido;
- E a coluna DIFF_PeLVAL foi calculado a diferença entre o último e primeiro pedido.
Este foi o ultimo exemplo a respeito deste grupo de função e desta postagem. Quis mostrar com eles o quão úteis elas podem ser e o quanto facilitam a vida na construção de query.
Para os próximos posts quero iniciar uma série sobre funções de análise e Window Function no SQL e já estou preparando material para as postagens!
Fiquem bem e saúde!
ManageBI 