MySQL获得记录计数的小时，甚至没有记录的小时 [英] MySQL get record count by hour, even the hours with no records

问题描述

我要查询过去24小时的表格中每小时的列值的平均值。我在显示结果时遇到麻烦，即使是没有记录的时间（在这些情况下，我应该打印平均值为0）。

这里是我到目前为止： / p>

  SELECT HOUR（time），AVG（score）FROM place_rvw WHERE time> = NOW（） -  INTERVAL 1 DAY AND place_id = 1 ORDER BY HOUR（时间）; 
  

现在（12 AM），例如，我唯一的记录是9PM和10PM。因此，结果中只有两行。我想要它有24行（代表过去24小时的每一行），如果一个小时没有记录，只显示平均值0。

任何形式的帮助赞赏。谢谢！

编辑
我尝试了下面的查询，就像Stephan建议的一样，但它仍然显示相同的结果。理想的结果应该是所有24行代表每小时。任何建议？

 以小时为单位选择za_hours.za_hour，以average_score为单位计算AVG（分数，0） （
 SELECT 0 as za_hour 
 UNION 
 SELECT 1 as za_hour 
 UNION 
 SELECT 2 as za_hour 
 UNION 
 SELECT 3 as za_hour 
 UNION 
 SELECT 4 as za_hour 
 UNION 
 SELECT 5 as za_hour 
 UNION 
 SELECT 6 as za_hour 
 UNION 
 SELECT 7 as za_hour 
 UNION 
 SELECT 8 as za_hour 
 UNION 
 SELECT 9 as za_hour 
 UNION 
 SELECT 10 as za_hour 
 UNION 
 SELECT 11 as za_hour 
 UNION 
 SELECT 12 as za_hour 
 UNION 
 SELECT 13 as za_hour 
 UNION 
 SELECT 14 as za_hour 
 UNION 
 SELECT 15 as za_hour 
 UNION 
 SELECT 16 as za_hour 
 UNION 
 SELECT 17 as za_hour 
 UNION 
 SELECT 18 as za_hour 
 UNION 
 SELECT 19 as za_hour 
 UNION 
 SELECT 20 as za_hour 
 UNION 
 SELECT 21 as za_hour 
 UNION 
 SELECT 22 as za_hour 
 UNION 
 SELECT 23 as za_hour 
）za_hours 
 LEFT JOIN place_rvw 
 ON za_hours.za_hour = HOUR（time）
 AND 
 time> = NOW -  INTERVAL 1 DAY 
 AND place_id = 1 
 GROUP BY 
 za_hours.za_hour 
 ORDER BY za_hours.za_hour 
  

解决方案

1）创建一个24行的表，每个表一个小时。

$ b $

解决方案WORKS如下所示：
SQL小提示

由于过滤，您仍未收到结果

I want to query for the average of the column values in a table by hour for the past 24 hours. I'm having trouble displaying results even for the hours with no records (in these cases, I should print an average of 0)

Here's what I have so far:

SELECT HOUR(time), AVG(score) FROM place_rvw WHERE time >= NOW() - INTERVAL 1 DAY AND place_id = 1 ORDER BY HOUR(time);

Right now (12 AM), for example, the only records I have is for 9PM and 10PM. Thus, there are only two rows in the result. I want it to have 24 rows (representing each of the past 24 hours) and simply display an average of 0 if an hour has no records.

Any form of help appreciated. Thanks!

Edit I tried the query below like what Stephan suggested, but it still displays the same result. The ideal result should have all 24 rows representing each hour. Any suggestions?

  SELECT za_hours.za_hour as hour, AVG(IFNULL(score,0)) as average_score
  FROM (
  SELECT 0 as za_hour
  UNION
  SELECT 1 as za_hour
  UNION
  SELECT 2 as za_hour
  UNION
  SELECT 3 as za_hour
  UNION
  SELECT 4 as za_hour
  UNION
  SELECT 5 as za_hour
  UNION
  SELECT 6 as za_hour
  UNION
  SELECT 7 as za_hour
  UNION
  SELECT 8 as za_hour
  UNION
  SELECT 9 as za_hour
  UNION
  SELECT 10 as za_hour
  UNION
  SELECT 11 as za_hour
  UNION
  SELECT 12 as za_hour
  UNION
  SELECT 13 as za_hour
  UNION
  SELECT 14 as za_hour
  UNION
  SELECT 15 as za_hour
  UNION
  SELECT 16 as za_hour
  UNION
  SELECT 17 as za_hour
  UNION
  SELECT 18 as za_hour
  UNION
  SELECT 19 as za_hour
  UNION
  SELECT 20 as za_hour
  UNION
  SELECT 21 as za_hour
  UNION
  SELECT 22 as za_hour
  UNION
  SELECT 23 as za_hour
) za_hours
LEFT JOIN place_rvw 
  ON za_hours.za_hour = HOUR(time)
  AND
  time >= NOW() - INTERVAL 1 DAY 
  AND place_id = 1 
GROUP BY
  za_hours.za_hour
ORDER BY za_hours.za_hour

解决方案

1) Create a table with 24 rows, one for each for a hour.

2) Make a left outter join with your table.

The solution WORKS as show in this: SQL fiddle

You are still not getting results due to the filtering

这篇关于MySQL获得记录计数的小时，甚至没有记录的小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！