SQL Server 2000 - 将查询分成 15 分钟的块 [英] SQL Server 2000 - Breaking a query up into 15 minute blocks

问题描述

我有一个连续的时间数据集，我想使用 sql 将其分解为 15 分钟的块.

I have a continous time dataset and I want to break it up into 15 minute blocks using sql.

如果我能帮上忙，我不想必须创建一个新表才能做到这一点.

即
时间，计数
09:15, 1
09:30, 3
09:45, 0
10:00, 2
10:15, 3
.....

i.e.
Time, Count
09:15, 1
09:30, 3
09:45, 0
10:00, 2
10:15, 3
.....

有没有人知道我如何做到这一点.我认为这是使用类似于以下内容的选择:

Does anyone have any idea of how I can do this. I presume that is use a select similar to the following:

SELECT [对MyDate"的某种数据操作]
, COUNT(ID)
来自MyTable
GROUP BY [对MyDate"的某种数据操作]

SELECT [Some kind of data manipulation on "MyDate"]
, COUNT(ID)
FROM MyTable
GROUP BY [Some kind of data manipulation on "MyDate"]

推荐答案

小心使用 dateadd 和 datediff，这是可以实现的.我的解决方案缩短了时间.

With careful use of dateadd and datediff, this can be accomplished. My solution rounds the times down.

第一部分计算行日期和纪元 (0) 之间的分钟数，并对其进行 mod 15，给出行日期和最接近的 15 分钟间隔之间的差异:

The first piece calculates the number of minutes between the row's date and the epoch (0) and does a mod 15 on it, giving the difference between the row's date and the closest 15 minute interval:

select -1 * datediff(minute, 0, mydate) % 15
from mytable

接下来，我们只需要处理分钟，因此我们使用了我从 2007 年 2 月 SQL Server 杂志(Itzik Ben-Gan 的日期时间计算)中学到的日期部分剥离技术:

Next, we need to deal with just the minutes, so we use this date-part stripping technique I learned from SQL Server Magazine February 2007 (Datetime Calculations by Itzik Ben-Gan):

select dateadd(minute, datediff(minute, 0, mydate), 0)
from mytable

然后，我们将差异添加到行的日期列和组中，然后计数，瞧！

Then, we add the difference to the row's date column and group and count and voila!

select dateadd(minute, -1 * datediff(minute, 0, mydate) % 15, dateadd(minute, datediff(minute, 0, mydate), 0)), count(ID)
from mytable
group by dateadd(minute, -1 * datediff(minute, 0, mydate) % 15, dateadd(minute, datediff(minute, 0, mydate), 0))

这篇关于SQL Server 2000 - 将查询分成 15 分钟的块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！