Optimization of SQL Query

Asked

Viewed 92 times

1

I set up a series of SQL but I’m having difficulty generating something that stress less the server, I ask help from you.

Follows the 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;
php mysql sql select select-sql

  • The field data_hora is sweep?

  • The reason for this '2015-06-10 23:00:00' AS 'Hora'? Wouldn’t it be better to take the date stored in the bank?

  • is varchar, but is with the information in timestamp format Ex. "2015-06-09 08:15:02"

  • @Guilhermenascimento I did this because I need to create a display of quantity per hour, I mounted a for to generate the loop of each time.

  • 1

    Yes Moises, but this could be done in the PHP layer, you could also use only one SELECT and OR and at the PHP layer organize the data. Surely this would optimize.

  • @Guilhermenascimento I really tried to do but could not. I did a loop run to list. If you can help me be very grateful.

  • At the moment I can not for an example because I am on the mobile, as soon as I can.

  • @Guilhermenascimento Thank you, will help a lot.

Show 3 more comments

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 the result to show all hours, even those for which there are no precise records of a kind of "Calendar" table. Follow an example that produces what I think is the result you want. It is only a "Proof of Concept", to show that it is possible to carry out the operation on the side of the database and frankly, it is too convoluted, in such a way that it would not advise its use. 

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 of the previous example, the output sera:

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

  • We answered the same thing at the same time. Your answer got more top.

  • @Thanks for the reply, but I need it to be displayed also if the result is 0. Would you know how to tell me ? Thanks.

  • You mean, even if there are no records for a given time, you want the time to be represented in the results with Total = 0?

  • Yes exactly. Can be done in direct SQL, or deal in PHP?

  • Yes, it can be done in SQL. Give me a few minutes I update the answer.

  • Thank you very much, I’ll be waiting

  • Imagine that in your table there is no date '2015-06-10', should be presented some result? Or only results will be presented for dates where there is at least one result (at least one contact)?

  • Which would be ideal for it. In this example Time: 2015-06-09 01:00:00 - Total:0, Time: 2015-06-09 02:00:00 - Total:0, Time: 2015-06-09 03:00:00 - Total:1, Time: 2015-06-09 04:00:00 - Total:0, Time: 2015-06-09 05:00:00 - Total:4, Time: 2015-06-09 06:00:00 - Total:0, See if you can understand, thank you.

  • 1

    @Thanks for the help, that’s exactly what I needed.

Show 4 more comments

Browser other questions tagged php mysql sql select select-sql

You are not signed in. Login or sign up in order to post.