用于获取每小时计数的 MySQL 查询

Question

我需要统计每小时发生的操作数。

我的数据库按操作的时间戳保存日志。

我知道我可以做一个

SELECT table.time COUNT(table.time) from table t group by t.time

但是，有些时间段没有任何操作发生。例如，如果我在上午 8:00 有 10 个 Action ，在上午 9:00 没有 Action ，在上午 10:00 有 4 个 Action ，

该查询将返回:

8:00    10
10:00   4

跳过上午 9:00，因为它没有条目。

如何进行查询以考虑 0 计数条目。

我还需要按星期几对条目进行相同的查询，但我假设通过回答第一个问题我可以轻松处理其他问题。

提前致谢!

Answer 1

您可以通过创建一个表来解决这个问题，该表将包含 24 个小时值(00:00、01:00 等)并与其执行左(或右)连接并且您的表允许空值，因此您将拥有所有 24 个值行，即使您的表根本包含 0 行，分组依据也应该可以正常工作。

当您执行连接时，不要忘记截断表中除小时以外的所有内容，这样您调用并执行 join on 的函数的结果可以等于此帮助表的值。

在用 24 个 test_time 值填充 testtime 表后，您可以使用以下查询来完成这项工作

select test_time,sum(sign(coalesce(idFromYourTable,0))) as count from testtime 
left join yourTable on test_time=hour(yourTableTime) 
group by test_time

如果没有与测试表中的行匹配的值，这将提供 0 作为计数，而即使您的表为空，count(*) 也会提供 24 行 1 而不是 0，如果只有 1 行您的表无法区分 0 行之间的差异，因为以下 2 行不同的结果看起来相同

23 NULL
23 1

cause 都将提供相同的结果行计数等于 1 ，而 sum 技术以不同方式处理这些行