PostgreSql - Função LAG - Diferença de tempo para uma mesma coluna

Vamos apresentar 4 exemplos de como calcular a diferença de período para uma mesma coluna. Para isto vamos utilizar a função LAG do PostgreSql. Está função pertence ao grupo das Window Functions e funciona a partir do PostgreSQL 8.4.22

Caso tenha interesse, faça o download ou veja os scripts no github.


1º Exemplo

Um mercado que funciona 24 horas por dia quer calcular qual o intervalo que cada cliente aguarda em uma fila única. Neste exemplo, vamos utilizar a tabela "tb_venda", que podemos visualizar a seguir:

tb_venda

Solução

A tabela tb_venda possui as seguintes colunas:

• cliente_id: a identificação de cada cliente;
• data_hora: a data e a hora de inicio do atendimento para cada cliente;

Logo vamos ter que calcular a diferença de horário entre:

• o 2º e 1º cliente;
• o 3º e 2º cliente;
• o 4º e 3º cliente;
• e assim por diante;

Para exibir o intervalo que cada cliente aguarda executaremos a sentença abaixo:

SELECT
cliente_id,
data_hora, 
data_hora - LAG(data_hora) OVER (ORDER BY data_hora) AS tempo_espera
FROM tb_venda;

Após a execução da sentença teremos o seguinte resultado:

2º Exemplo

Uma loja de departamento que funciona das (08:00h às 23:00h) também quer calcular qual o intervalo que cada cliente aguarda em uma fila única. Será que poderíamos utilizar a mesma consulta do 1º exemplo em que o estabelecimento não fechava? Vamos tentar? Neste exemplo, vamos utilizar a tabela "tb_venda_depart", que podemos visualizar a seguir:

Para exibir o intervalo que cada cliente aguarda executaremos a sentença abaixo:

SELECT
cliente_id,
data_hora,
data_hora - LAG(data_hora) OVER (ORDER BY data_hora) AS tempo_espera
FROM tb_venda_depart;

Após a execução da sentença teremos o seguinte resultado:

Podemos verificar que a consulta está incorreta, pois o cliente 546, não ficou mais de 9h na fila, na verdade ele foi o primeiro cliente da fila do dia 27 de novembro e não teve que esperar na fila. Para corrigir a consulta vamos calcular a diferença para cada dia.

Para solucionar este problema vamos:

• incluir na consulta o "PARTITION BY" para quebrar a consulta por data;
• já que não temos uma coluna só com a data vamos truncar a coluna "data_hora" e utilizar somente a data, utilizando a função "DATE_TRUNC" do PostgreSql;   

SELECT
cliente_id,
data_hora,
data_hora - LAG(data_hora)
OVER (PARTITION BY(DATE_TRUNC('DAY', data_hora)) ORDER BY data_hora) AS tempo_espera
FROM tb_venda_depart;

Veja que o comando que foi adicionado está destacado em azul
Após a execução da sentença acima temos o resultado correto:

O cliente 546 foi o primeiro cliente do dia 27 de novembro, logo não precisou aguardar na fila.


3º Exemplo

Calcular a média de tempo de espera na fila para a loja de departamento. Para calcular a média também vamos utilizar a tabela "tb_venda_depart", conforme podemos visualizar abaixo:

tb_venda_depart

Solução

Vamos colocar a sentença anterior dentro de um comando "WITH" e vamos da o nome de "cliente_periodo" para este "WITH".
Quando fazemos isso é como se fosse criada uma tabela chamada cliente_periodo que contém três colunas:
cliente_id, data_hora, tempo_espera.

Depois vamos calcular a média para a coluna tempo_espera utilizando a função AVG.

Veja a sentença abaixo:


WITH cliente_periodo AS
(
SELECT
cliente_id,
data_hora,
data_hora - LAG(data_hora)
OVER (PARTITION BY(DATE_TRUNC('DAY', data_hora)) ORDER BY data_hora) AS tempo_espera
FROM tb_venda_depart
)
SELECT  AVG(tempo_espera) AS media_tempo_espera
FROM cliente_periodo;

Após a execução da sentença temos o seguinte resultado:

Logo, cada cliente espera em média, 4 minutos e 20 segundos e 5 milésimos.

4º Exemplo

Calcular a média de tempo de espera por dia (data). Para calcular a média também vamos utilizar a tabela "tb_venda_depart", conforme podemos visualizar abaixo:

tb_venda_depart

Solução

• Vamos utilizar a sentença idêntica anterior, porém vamos adicionar o comando "GROUP BY" para que as médias sejam agrupadas por data.  
• Como precisamos agrupar os dados por data e não temos uma coluna só com a data, vamos truncar a coluna data_hora e pegar só a data para o agrupamento, para isso vamos utilizar a função DATE_TRUNC do postgresql;
• Vamos calcular a média para a coluna tempo_espera utilizando a função AVG.

Veja a sentença abaixo:

WITH cliente_periodo AS
(
   SELECT
   cliente_id,
   data_hora,
   data_hora - LAG(data_hora)
   OVER (PARTITION BY(DATE_TRUNC('DAY', data_hora)) ORDER BY data_hora) AS         tempo_espera
   FROM tb_venda_depart
)
SELECT DATE_TRUNC('DAY', data_hora), AVG(tempo_espera)
FROM cliente_periodo
GROUP BY DATE_TRUNC('DAY', data_hora);

Após a execução da sentença temos o seguinte resultado:

Veja que já temos a média de tempo de espera para cada dia, porém a data não está no formato dd/mm/yyyy.
Para que a data seja exibida neste formato, vamos utilizar a função TO_CHAR.

Veja a sentença abaixo utilizando TO_CHAR.


WITH cliente_periodo AS
(
SELECT
cliente_id,
data_hora,
data_hora - LAG(data_hora)
OVER (PARTITION BY(DATE_TRUNC('DAY', data_hora)) ORDER BY data_hora) AS tempo_espera
FROM tb_venda_depart
)
SELECT TO_CHAR(data_hora, 'DD/MM/YYYY'), AVG(tempo_espera)
FROM cliente_periodo
GROUP BY TO_CHAR(data_hora, 'DD/MM/YYYY');


Após a execução da sentença teremos o seguinte resultado:

Também podemos truncar a média de tempo de espera em hh:mm:ss, para não exibir os microssegundos depois do ponto vamos utilizar a função DATE_TRUNC para exibir a média até a unidade de segundos.

Veja a sentença abaixo:

WITH cliente_periodo AS
(
SELECT
cliente_id,
data_hora,
data_hora - LAG(data_hora)
OVER (PARTITION BY(DATE_TRUNC('DAY', data_hora)) ORDER BY data_hora) AS tempo_espera
FROM tb_venda_depart
)
SELECT TO_CHAR(data_hora, 'DD/MM/YYYY') AS DATA,
DATE_TRUNC('SECOND',AVG(tempo_espera)) AS media_tempo_espera
FROM cliente_periodo
GROUP BY TO_CHAR(data_hora, 'DD/MM/YYYY');

Após a execução da sentença teremos o seguinte resultado:

Artigos relacionados:

Postgresql - diferença entre datas

Deixe o seu comentário ou sugestão.
Gostou?  Siga no Google +  ou Facebook.

Postgresql - Função dense_rank - Exibir a posição dos valores

Neste artigo, vamos apresentar 3 exemplos de como exibir as posições de acordo com valores de uma coluna, ou explicando de uma forma mais simples, como ranquear os valores de uma coluna.
Veja o script com os exemplos no github.

1º) Exemplo:

Queremos exibir a classificação dos atletas em uma corrida de acordo com o tempo gasto para completar a prova.
Para exibir a classificação vamos utilizar a tabela "tb_corrida" exibida abaixo:

Solução:

Para exibir a posição dos corredores utilizamos a sentença abaixo:

SELECT
DENSE_RANK() OVER(ORDER BY tempo_gasto ASC) AS posicao,
inscricao,
tempo_gasto,
sexo
FROM tb_corrida 

Após a execução da sentença acima,  teremos o seguinte resultado:

Observações

• Utilizamos DENSE_RANK sobre a coluna que queremos obter a classificação, neste caso "tempo_gasto". 
• Observe que DENSE_RANK esta acompanhado de "OVER", e dentro do "OVER" vamos colocar a coluna que vamos utilizar para obter as posições e de que maneira queremos a classificação "ASC" - crescente (do valor mais baixo até o valor mais alto) ou "DESC" - decrescente (do valor mais alto até o valor mais baixo).

Repare que dois corredores ficaram empatados na 3ª posição. Através desse exemplo podemos perceber que a função dense_rank, leva em consideração os empates .

2º) Exemplo:

Queremos a classificação dos corredores separadas pelo sexo (masculino e feminino). Vamos utilizar a mesma tabela do primeiro exemplo,  "tb_corrida":

Solução:
Para exibir a posição dos corredores separadas por sexo utilizamos a sentença abaixo:

SELECT
DENSE_RANK() OVER(PARTITION BY sexo ORDER BY tempo_gasto ASC) AS posicao,
inscricao,
tempo_gasto,
sexo
FROM tb_corrida


Após a execução da sentença acima,  teremos o seguinte resultado:

Repare que a única alteração que fizemos foi incluir "PARTITION BY" dentro do OVER. 
Após o PARTITION BY incluímos a coluna sexo, pois queremos que a classificação seja dividida por sexo.

3º) Exemplo:

Queremos a classificação dos vendedores em uma loja de acordo com o total das vendas.
Para exibir a classificação vamos utilizar a tabela "tb_vendas" exibida abaixo:

Solução:

Para exibir a classificação dos vendedores de acordo com o total das vendas executamos a sentença a seguir:

SELECT
codigo_vendedor,
SUM(valor),DENSE_RANK() OVER(ORDER BY SUM(valor) DESC) AS posicao
FROM tb_vendas
GROUP BY codigo_vendedor

Após a execução da sentença acima,  teremos o seguinte resultado:

Repare que utilizamos a função da agrupamento SUM dentro do OVER para que fossem classificadas as somas do valores (e não os valores individualmente) e utilizamos o "GROUP BY" para agrupar as somas por código do vendedor. Além disso utizamos o "ORDER BY" com DESC para agrupar da soma com maior valor até a soma com menor valor.

A função  dense_rank pertence ao grupo de funções chamadas "window functions". Existem outras funções desse tipo, em breve publicaremos outras funções.

Deixe suas sugestões ou críticas.