Tecnologia da Informação
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:
Solução
A tabela tb_venda possui as seguintes colunas:
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:
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:
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:
Solução
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:
Deixe o seu comentário ou sugestão.
Gostou? Siga no Google + ou Facebook.
- Postgresql - Adicionar Um Intervalo A Uma Data / Hora
Vamos mostrar neste artigo, como acrescentar um período a uma data. Descrevemos 5 exemplos: Caso tenha interesse, faça o download do exemplo, ou veja no GitHub. 1º exemplo: mostra como acrescentar um período em dias a uma data. São descritas duas...
- Postgresql - Função Count
O artigo apresenta 8 exemplos de como utilizar a função de agrupamento "count", das seguintes formas: COUNT(*);COUNT(nome_da_coluna);COUNT COM GROUP BY;COUNT(DISTINCT(nome_da_coluna)); Caso tenha interesse faça o download dos scripts ou veja no GitHub. ...
- Mysql - Diferença Entre Datas
Neste post vamos apresentar 4 exemplos de como calcular a diferença entre datas. Para calcular a diferença entre duas datas, em dias, utilizamos a função "DATEDIFF". Esta função é utilizada no 1º e 2º exemplos. DATEDIFF (data_final, data_inicial)...
- Postgresql - Diferença Entre Datas
Neste post vamos apresentar 4 exemplos de como calcular a diferença entre datas. Caso tenha interesse, faça o download ou veja o script no Github. Para calcular a diferença entre duas datas, em dias, no postgresql utilizamos o operador aritmético...
- Postgresql Sum - Soma
A função SUM retorna a soma de valores de uma coluna. SINTAXE SELECT SUM(nome_da_coluna) FROM nome_da_tabela; Para os 6 exemplos abaixo, utilizaremos a tabela "tb_fornecedor". Veja a imagem abaixo: 1º Exemplo Cenário: um funcionário quer...
Tecnologia da Informação
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 |
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;
- 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;
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 |
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;
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 |
- 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
Gostou? Siga no Google + ou Facebook.
loading...
- Postgresql - Adicionar Um Intervalo A Uma Data / Hora
Vamos mostrar neste artigo, como acrescentar um período a uma data. Descrevemos 5 exemplos: Caso tenha interesse, faça o download do exemplo, ou veja no GitHub. 1º exemplo: mostra como acrescentar um período em dias a uma data. São descritas duas...
- Postgresql - Função Count
O artigo apresenta 8 exemplos de como utilizar a função de agrupamento "count", das seguintes formas: COUNT(*);COUNT(nome_da_coluna);COUNT COM GROUP BY;COUNT(DISTINCT(nome_da_coluna)); Caso tenha interesse faça o download dos scripts ou veja no GitHub. ...
- Mysql - Diferença Entre Datas
Neste post vamos apresentar 4 exemplos de como calcular a diferença entre datas. Para calcular a diferença entre duas datas, em dias, utilizamos a função "DATEDIFF". Esta função é utilizada no 1º e 2º exemplos. DATEDIFF (data_final, data_inicial)...
- Postgresql - Diferença Entre Datas
Neste post vamos apresentar 4 exemplos de como calcular a diferença entre datas. Caso tenha interesse, faça o download ou veja o script no Github. Para calcular a diferença entre duas datas, em dias, no postgresql utilizamos o operador aritmético...
- Postgresql Sum - Soma
A função SUM retorna a soma de valores de uma coluna. SINTAXE SELECT SUM(nome_da_coluna) FROM nome_da_tabela; Para os 6 exemplos abaixo, utilizaremos a tabela "tb_fornecedor". Veja a imagem abaixo: 1º Exemplo Cenário: um funcionário quer...