mysql按照小时统计数据分秒为零,Mysql：按小时分组，如果没有数据，则为0

I have the following query:

SELECT count(*) as 'totalCalls', HOUR(`end`) as 'Hour'

FROM callsDataTable

WHERE company IN (

SELECT number

FROM products

WHERE products.id IN (@_PRODUCTS))

AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH

group by HOUR(`end`)

Above query returns only the hours in which there where calls made:

totalCalls Hour

2 0

1 2

4 7

98 8

325 9

629 10

824 13

665 15

678 16

665 17

606 18

89 22

5 23

The desired output should be all the hours, and where there are no calls it should be 0 calls for that hour, like below:

totalCalls Hour

0 0

0 1

1 2

0 3

0 4

0 5

0 6

4 7

98 8

325 9

629 10

0 11

0 12

824 13

0 14

665 15

678 16

665 17

606 18

0 19

0 20

0 21

89 22

5 23

解决方案

First, your query can be expressed in a simpler way as:

SELECT COUNT(*) AS totalCalls, HOUR(`end`) AS `Hour`

FROM callsDataTable c

INNER JOIN products p ON c.company = p.number

AND p.id IN (@_PRODUCTS)

AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH

GROUP BY HOUR(`end`) AS `Hour`

ORDER BY `Hour` ASC

Using the idea suggested by @NoDisplayName in their answer:

CREATE TABLE hours_table (hours INT);

INSERT INTO hours_table VALUES(0), (1), (2),

/* put the missing values here */ (23);

You can join the table that contains the hours to get the results you want:

SELECT COUNT(*) AS totalCalls, h.hours AS `Hour`

FROM callsDataTable c

INNER JOIN products p ON c.company = p.number

RIGHT JOIN hours_table h ON h.hours = HOUR(c.`end`)

AND p.id IN (@_PRODUCTS)

AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH

GROUP BY h.hours

ORDER BY h.hours ASC

If it runs too slow (and I'm sure it is very slow) you should investigate a way to use something like end BETWEEN '2015-01-01 00:00:00' AND '2015-01-31 23:59:59' instead of comparing YEAR(end) and MONTH(end).

It can be accomplished like this:

SET @start = STR_TO_DATE(CONCAT(@_YEAR, '-', @_MONTH, '-01 00:00:00'), '%Y-%m-%d %H:%i:%s');

SET @end = DATE_SUB(DATE_ADD(@start, INTERVAL 1 MONTH), INTERVAL 1 SECOND);

SELECT ...

...

AND `end` BETWEEN @start AND @end

...

But this change doesn't help by itself. It needs an index on field end to bring the desired speed improvement:

ALTER TABLE callsDataTable ADD INDEX(end);

Using HOUR(c.end) in the join condition is another reason to run slowly.

It can be improved by joining the table hours_table with the result set produced by the (simplified version of the) first query:

SELECT IFNULL(totalCalls, 0) AS totalCalls, h.hours AS `Hour`

FROM hours_table h

LEFT JOIN (

SELECT COUNT(*) AS totalCalls, HOUR(`end`) as `Hour`

FROM callsDataTable c

INNER JOIN products p ON c.company = p.number

AND p.id IN (@_PRODUCTS)

AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH

GROUP BY HOUR(`end`) AS `Hour`

) d ON h.hours = d.`Hour`

ORDER BY h.hours ASC