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.