SQL Query Optimization

Navigation
1

I've set up a series of SQL but I'm having difficulty generating something that stresses less the server, I ask for help from you.

Follow SQL: 

SELECT '2015-06-10 00:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 00 AND 01;
SELECT '2015-06-10 01:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 01 AND 02;
SELECT '2015-06-10 02:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 02 AND 03;
SELECT '2015-06-10 03:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 03 AND 04;
SELECT '2015-06-10 04:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 04 AND 05;
SELECT '2015-06-10 05:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 05 AND 06;
SELECT '2015-06-10 06:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 06 AND 07;
SELECT '2015-06-10 07:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 07 AND 08;
SELECT '2015-06-10 08:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 08 AND 09;
SELECT '2015-06-10 09:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 09 AND 10;
SELECT '2015-06-10 10:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 10 AND 11;
SELECT '2015-06-10 11:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 11 AND 12;
SELECT '2015-06-10 12:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 12 AND 13;
SELECT '2015-06-10 13:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 13 AND 14;
SELECT '2015-06-10 14:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 14 AND 15;
SELECT '2015-06-10 15:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 15 AND 16;
SELECT '2015-06-10 16:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 16 AND 17;
SELECT '2015-06-10 17:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 17 AND 18;
SELECT '2015-06-10 18:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 18 AND 19;
SELECT '2015-06-10 19:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 19 AND 20;
SELECT '2015-06-10 20:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 20 AND 21;
SELECT '2015-06-10 21:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 21 AND 22;
SELECT '2015-06-10 22:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 22 AND 23;
SELECT '2015-06-10 23:00:00' AS 'Hora', COUNT(id_contato) AS 'Total' FROM contato WHERE data_hora LIKE '2015-06-10%' AND HOUR(data_hora) BETWEEN 23 AND 00;
    
asked by anonymous 10.06.2015 / 22:05

1 answer

0

You can try 

SELECT  SUBSTRING(data_hora, 1, 13) + '00:00' AS 'Hora', 
        COUNT(id_contato) AS 'Total' 
FROM contato 
WHERE data_hora LIKE '2015-06-10%' 
GROUP BY SUBSTRING(CONVERT(VARCHAR, data_hora), 1, 13)

If you have the following data in your table 

DATA_HORA                  ID
'2015-06-10 21:01:01:000'  1
'2015-06-10 22:05:01:000'  2
'2015-06-10 22:12:01:000'  3

This will be the output 

Hora                 Total
2015-06-30 21:00:00  1
2015-06-30 22:00:00  2

If you want all the hours to be displayed in the result, even those for which there is no precise record of a sort of "Calendar" table. Here is an example that produces, what I think, is the result you want. It's just a proof of concept, to show that it's possible to perform the database-side operation, and honestly, it's too far-fetched, so it would not advise you to use it. 

SELECT  CONCAT('2015-06-10 ', Horas.Hora, ':00:00') as Hora, COALESCE(COUNT(id_contacto), 0) Total
FROM (
  SELECT '00' Hora  UNION SELECT '01' UNION SELECT '02' UNION SELECT '03' UNION SELECT '04' UNION
  SELECT '05' UNION SELECT '06' UNION SELECT '07' UNION SELECT '08' UNION SELECT '09' UNION
  SELECT '10' UNION SELECT '11' UNION SELECT '12' UNION SELECT '13' UNION SELECT '14' UNION
  SELECT '15' UNION SELECT '16' UNION SELECT '17' UNION SELECT '18' UNION SELECT '19' UNION
  SELECT '20' UNION SELECT '21' UNION SELECT '22' UNION SELECT '23'
) Horas
LEFT JOIN contatos C
  ON SUBSTRING(C.data_hora, 12, 2) = Horas.Hora
 AND C.data_hora LIKE '2015-06-10%' 
GROUP BY CONCAT('2015-06-10 ', Horas.Hora, ':00:00')
ORDER BY 1

With this query and for the same data as the previous example, the output will be: 

Hora                Total
2015-06-10 00:00:00 0 
2015-06-10 01:00:00 0 
2015-06-10 02:00:00 0 
2015-06-10 03:00:00 0 
2015-06-10 04:00:00 0 
2015-06-10 05:00:00 0 
2015-06-10 06:00:00 0 
2015-06-10 07:00:00 0 
2015-06-10 08:00:00 0 
2015-06-10 09:00:00 0 
2015-06-10 10:00:00 0 
2015-06-10 11:00:00 0 
2015-06-10 12:00:00 0 
2015-06-10 13:00:00 0 
2015-06-10 14:00:00 0 
2015-06-10 15:00:00 0 
2015-06-10 16:00:00 0 
2015-06-10 17:00:00 0 
2015-06-10 18:00:00 0 
2015-06-10 19:00:00 0 
2015-06-10 20:00:00 0 
2015-06-10 21:00:00 1 
2015-06-10 22:00:00 2 
2015-06-10 23:00:00 0
    
10.06.2015 / 22:17
Avoid persisting data when refreshing My Crystal Report always appears blank