按时间分组记录 [英] Group records by time
问题描述
我有一个包含日期时间列和其他一些其他列的表。日期时间列表示正在发生的事件。它可以包含一个时间(当时发生的事件)或NULL(事件没有发生)
我现在要计算在特定时间间隔内发生的记录数(15分钟),但不知道该怎么做。
例子:
id |时间| foreign_key
1 | 2012-01-01 00:00:01 | 2
2 | 2012-01-01 00:02:01 | 4
3 | 2012-01-01 00:16:00 | 1
4 | 2012-01-01 00:17:00 | 9
5 | 2012-01-01 00:31:00 | 6
我现在想要创建一个查询来创建类似于以下内容的结果集:
interval | COUNT(id)
2012-01-01 00:00:00 | 2
2012-01-01 00:15:00 | 2
2012-01-01 00:30:00 | 1
这是SQL中的可能吗?或者任何人都可以建议我可以使用哪些其他工具? (例如,将数据导出到电子表格程序不会成为问题)
试试这个:
select datetime((strftime('%s',time)/ 900)* 900,'unixepoch')interval,
count *)cnt
from t
按时间间隔
按时间间隔排序
检查小提琴此处。
I have a table containing a datetime column and some misc other columns. The datetime column represents an event happening. It can either contains a time (event happened at that time) or NULL (event didn't happen)
I now want to count the number of records happening in specific intervals (15 minutes), but do not know how to do that.
example:
id | time | foreign_key
1 | 2012-01-01 00:00:01 | 2
2 | 2012-01-01 00:02:01 | 4
3 | 2012-01-01 00:16:00 | 1
4 | 2012-01-01 00:17:00 | 9
5 | 2012-01-01 00:31:00 | 6
I now want to create a query that creates a result set similar to:
interval | COUNT(id)
2012-01-01 00:00:00 | 2
2012-01-01 00:15:00 | 2
2012-01-01 00:30:00 | 1
Is this possible in SQL or can anyone advise what other tools I could use? (e.g. exporting the data to a spreadsheet program would not be a problem)
Give this a try:
select datetime((strftime('%s', time) / 900) * 900, 'unixepoch') interval,
count(*) cnt
from t
group by interval
order by interval
Check the fiddle here.
这篇关于按时间分组记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!