TSQL 分组 N 秒 [英] TSQL Group by N Seconds

问题描述

我可以按秒或分钟进行分组，如下所示:

I can group by seconds or minutes with something like the following:

SELECT datepart(minute, Time)
      ,count(*) as hits
FROM Log
GROUP BY datepart(minute, Time)

有没有办法做同样的事情，但有指定的秒数，例如按每 10 秒"分组?

Is there a way I can do the same thing, but with a specified amount of seconds, so group by "every 10 seconds" for example?

更多信息:
这也结合了 Where Time between :

More info:
This is combined with a Where Time between as well:

SELECT datepart(minute, Time)
      ,count(*) as hits
FROM Log with (nolock)
WHERE Time between dateadd(minute, -2, getdate()) and getdate()
GROUP BY datepart(minute, Time)

推荐答案

试试这个:

DATEDIFF (ss ,'19700101' ,Time )) 将给出自 1970 年 1 月 1 日以来的秒数(您可以选择另一个小于您所有日期的日期).将其除以 10，每 10 秒有一个 GROUP BY:

DATEDIFF (ss ,'19700101' ,Time )) will give the amount of seconds since 01/01/1970 (you can choose another date that is less than all your dates). Divide it by ten to have a GROUP BY by each 10 seconds:

SELECT DATEDIFF (ss ,'19700101' ,Time )/10, count(*) as hits
  FROM Log
 GROUP BY DATEDIFF (ss ,'19700101' ,Time )/10

(随着时间):

SELECT convert(varchar, min(Time), 108) time,
       DATEDIFF (ss ,'19700101' ,Time )/10 sec_interval,
       count(*) as hits
  FROM Log
 GROUP BY DATEDIFF (ss ,'19700101' ,Time )/10

这篇关于TSQL 分组 N 秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！