# Problema

# 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.


# 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:


# 1. Criando uma tabela "horas" que irá armazenar as 24 horas.

CREATE TABLE horas (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
horas VARCHAR(3) NOT NULL
);

# Populando a tabela com as 24 horas:

insert into horas (horas) values('00');
insert into horas (horas) values('01');
insert into horas (horas) values('02');
insert into horas (horas) values('03');
insert into horas (horas) values('04');
insert into horas (horas) values('05');
insert into horas (horas) values('06');
insert into horas (horas) values('07');
insert into horas (horas) values('08');
insert into horas (horas) values('09');
insert into horas (horas) values('10');
insert into horas (horas) values('11');
insert into horas (horas) values('12');
insert into horas (horas) values('13');
insert into horas (horas) values('14');
insert into horas (horas) values('15');
insert into horas (horas) values('16');
insert into horas (horas) values('17');
insert into horas (horas) values('18');
insert into horas (horas) values('19');
insert into horas (horas) values('20');
insert into horas (horas) values('21');
insert into horas (horas) values('22');
insert into horas (horas) values('23');

2. Criando uma tabela "datas" quer irá armazenar os próximos 100 anos de datas a partir de uma data predeterminada.


CREATE TABLE datas (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    data DATE
);

# Procedure que irá popular a tabela "datas"

DROP PROCEDURE IF EXISTS load_datas;

create procedure load_datas()
begin
    declare x int unsigned default 1;
    WHILE x <= 36500 DO
        insert into datas (data) SELECT DATE_ADD(DATE("2017-01-01"), INTERVAL x DAY);
        SET x = x + 1;
    END WHILE;
end

CALL load_datas();

3. 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.


select
    datas.data as data,
    coalesce(pg.valor,0) as valor
from
    (
        select
            date_format(concat(datas.data, ' ', horas.horas), '%Y-%m-%d %H') as data
        from
            datas
        cross JOIN
            horas
        where
            datas.data between STR_TO_DATE('15/08/2019', '%d/%m/%Y') AND STR_TO_DATE('15/08/2019', '%d/%m/%Y')
    ) as datas
left
    join
    (
        select
            date_format(created_date, '%Y-%m-%d %H') as datahoras,
            sum(valor) as valor
        from
            pagamento
        where
            evento_id = 6
        group by
            date_format(created_date, '%Y-%m-%d %H')
        order by
                date_format(created_date, '%Y-%m-%d %H') asc
    ) as pg
    on
        pg.datahoras = datas.data