用于获取每小时计数的 MySQL 查询 [英] MySQL Query for obtaining count per hour

问题描述

我需要计算每小时发生的操作数.

I need to obtain a count of how many actions occur on an hourly basis.

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

My database keeps a log by timestamp of the actions.

我知道我可以做一个

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

但是，有些时间段没有执行任何操作.例如，如果我在上午 8:00 有 10 个操作，在上午 9:00 没有操作，在上午 10:00 有 4 个操作，

However, there are periods of time where no actions take place. For example if I have 10 actions during 8:00AM, no actions during 9:00AM and 4 actions during 10:00AM,

该查询将返回:

8:00    10
10:00   4

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

Skipping 9:00AM because it has no entries.

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

How can I make a query that will take into account 0-count entries.

我还需要按星期几对条目进行相同的查询，但我认为通过回答第一个问题，我可以轻松处理另一个问题.

I also need to make the same query for entries by days of the week, but I assume that by answering the first question I can easily handle the other.

提前致谢！

推荐答案

您可以通过创建一个包含 24 个小时值(00:00、01:00 等)并执行左(或右)的表来解决这个问题加入它，并且您的表允许空值，因此即使您的表包含 0 行，您也将拥有所有 24 行，然后 group by 应该可以正常工作.

you can solve this by creating a table that will contain 24 values for hours (00:00, 01:00 etc) and perform a left (or right) join with it and your table allowing nulls so you will have all 24 rows even if your table contains 0 rows at all, then group by should work fine.

不要忘记在执行 join 时从表中截断除小时以外的所有内容，以便调用 & func 的结果.perform join on 可以等于这个帮助表的值.

Dont forget to truncate everything but hour from your table when you perform join so result of func you call & perform join on can be equal to value of this help table.

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

you can use following query to do the job after populating testtime table with 24 test_time values

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 行 1s 而不是 0s，即使您的表是空的，如果只有 1 行您的表格无法区分 0 行之间的差异，因为以下 2 行的结果看起来相同

This will provide 0 as count if there are no values matching row from test table, while having count(*) will provide 24 rows with 1s instead of 0s even if your table is empty, also if there is just 1 row in your table it is impossible to distinguish the difference between 0 rows cause results will look the same for following 2 different rows

23 NULL
23 1

23 NULL
23 1

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

cause will both provide same result row count equal to 1 , while sum technique treats this rows differently

这篇关于用于获取每小时计数的 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！