在sql中按小时使用查询

hope you can help me.

希望您能够帮助我。

I've a DB table, lets call it activities

我有一个数据库表，让我们称之为活动

[activities]
 [start_time - datetime]
 [end_time - datetime]
 [name - string]

i need a query which will return me, for each hour of a 24day how many activites were active at that time.

我需要一个将返回我的查询，对于24天的每个小时，当时有多少活动是活跃的。

for example:

例如：

[time]         [usage]
11:00 (11 am)    12

etc...

等等...

1 个解决方案

#1

You may want to use create another table (could be temporary) that holds the numbers 0-23 representing the hours of the day:

您可能想要使用创建另一个表（可能是临时的）来保存代表一天中小时的数字0-23：

CREATE TABLE hours_of_day (hour int);
INSERT INTO hours_of_day VALUES (1), (2), (3), (4), (5), (6), (7), (8),
                                (9), (10), (11), (12), (13), (14), (15), 
                                (16), (17), (18), (19), (20), (21), (22), (23);

Then you should be able to use a query such as the following (using MySQL):

然后你应该能够使用如下的查询（使用MySQL）：

SELECT      hd.hour, COUNT(a.name) `usage`
FROM        hours_of_day hd
LEFT JOIN   activities a ON 
            (hd.hour BETWEEN HOUR(a.start_time) AND HOUR(a.end_time))
GROUP BY    hd.hour;

Test case:

测试用例：

CREATE TABLE activities (start_time datetime, 
                         end_time   datetime, 
                         name       varchar(10));

INSERT INTO activities VALUES 
     ('2010-01-01 12:10:00', '2010-01-01 13:10:00', 'b'),
     ('2010-01-01 13:20:00', '2010-01-01 13:30:00', 'c'),
     ('2010-01-01 13:50:00', '2010-01-01 14:05:00', 'd'),
     ('2010-01-01 17:20:00', '2010-01-01 20:30:00', 'e');

Result:

结果：

+------+-------+
| hour | usage |
+------+-------+
|    1 |     0 |
|    2 |     0 |
|    3 |     0 |
|    4 |     0 |
|    5 |     0 |
|    6 |     0 |
|    7 |     0 |
|    8 |     0 |
|    9 |     0 |
|   10 |     0 |
|   11 |     0 |
|   12 |     1 |
|   13 |     3 |
|   14 |     1 |
|   15 |     0 |
|   16 |     0 |
|   17 |     1 |
|   18 |     1 |
|   19 |     1 |
|   20 |     1 |
|   21 |     0 |
|   22 |     0 |
|   23 |     0 |
+------+-------+
23 rows in set (0.00 sec)

If you don't want all those 0s, you can use INNER JOIN instead of LEFT JOIN. The results we be as follows:

如果您不想要所有这些0，则可以使用INNER JOIN而不是LEFT JOIN。结果我们如下：

+------+-------+
| hour | usage |
+------+-------+
|   12 |     1 |
|   13 |     3 |
|   14 |     1 |
|   17 |     1 |
|   18 |     1 |
|   19 |     1 |
|   20 |     1 |
+------+-------+
7 rows in set (0.00 sec)

#1