2NmCG4jN

1# Problema
2
3# Existe uma tabela no banco de dados MYSQL chamada "pagamentos", o problema consistia em criar uma query que retorne a soma total de vendas por hora dado um intervalo de datas, inclusive as horas que não houveram vendas com o valor zerado.
4
5
6# Existem algumas formas de solucionar esse problema, como por exemplo: a nível de aplicação ou usando tabelas temporárias, após analisar os pós e contras de cada abordagem resolvi resolver o problema da seguinte maneira:
7
8
9# 1. Criando uma tabela "horas" que irá armazenar as 24 horas.
10
11CREATE TABLE horas (
12id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
13horas VARCHAR(3) NOT NULL
14);
15
16# Populando a tabela com as 24 horas:
17
18insert into horas (horas) values('00');
19insert into horas (horas) values('01');
20insert into horas (horas) values('02');
21insert into horas (horas) values('03');
22insert into horas (horas) values('04');
23insert into horas (horas) values('05');
24insert into horas (horas) values('06');
25insert into horas (horas) values('07');
26insert into horas (horas) values('08');
27insert into horas (horas) values('09');
28insert into horas (horas) values('10');
29insert into horas (horas) values('11');
30insert into horas (horas) values('12');
31insert into horas (horas) values('13');
32insert into horas (horas) values('14');
33insert into horas (horas) values('15');
34insert into horas (horas) values('16');
35insert into horas (horas) values('17');
36insert into horas (horas) values('18');
37insert into horas (horas) values('19');
38insert into horas (horas) values('20');
39insert into horas (horas) values('21');
40insert into horas (horas) values('22');
41insert into horas (horas) values('23'); 
42
432. Criando uma tabela "datas" quer irá armazenar os próximos 100 anos de datas a partir de uma data predeterminada.
44
45
46CREATE TABLE datas (
47	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
48	data DATE
49);
50
51# Procedure que irá popular a tabela "datas"
52
53DROP PROCEDURE IF EXISTS load_datas;
54
55create procedure load_datas()
56begin
57	declare x int unsigned default 1;
58	WHILE x <= 36500 DO
59		insert into datas (data) SELECT DATE_ADD(DATE("2017-01-01"), INTERVAL x DAY);
60		SET x = x + 1;
61	END WHILE;
62end
63
64CALL load_datas();
65
663. Criando query que irá realizar um CROSS JOIN entre as tabelas "horas" e "datas" filtrando pelo range necessário, como resultado terei as 24 horas de cada dia do período informado. Conm o resultado do CROSS JOIN será feito um LEFT JOIN com os dados de pagamentos.
67
68
69select
70	datas.data as data,
71	coalesce(pg.valor,0) as valor
72from
73	(
74		select
75			date_format(concat(datas.data, ' ', horas.horas), '%Y-%m-%d %H') as data
76		from
77			datas
78		cross JOIN
79			horas
80		where
81			datas.data between STR_TO_DATE('15/08/2019', '%d/%m/%Y') AND STR_TO_DATE('15/08/2019', '%d/%m/%Y')
82	) as datas
83left
84	join
85	(
86		select
87			date_format(created_date, '%Y-%m-%d %H') as datahoras,
88			sum(valor) as valor
89		from
90			pagamento
91		where
92			evento_id = 6
93		group by
94			date_format(created_date, '%Y-%m-%d %H')
95		order by
96				date_format(created_date, '%Y-%m-%d %H') asc
97	) as pg
98	on
99		pg.datahoras = datas.data