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:
Gostou? Siga no Google + ou Facebook.
