TS SQL-按分钟分组

这将返回过去一小时内每分钟(有记录的地方)的结果计数

SELECT DATEPART(n, time_stamp) AS minute, COUNT(*) as results
FROM table_name 
WHERE time_stamp > DATEADD(hh, -1, GETDATE())
GROUP BY DATEPART(n, time_stamp)

WITH numbers ( num ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + num FROM numbers WHERE num < 60 )
SELECT num AS minute,
    (SELECT COUNT(*) AS results
    FROM table_name
    WHERE DATEPART(n, time_stamp) = num
    AND time_stamp > DATEADD(hh, -1, GETDATE())
FROM numbers

要查看结果,请将DATEADD(hh,-1,GETDATE())替换为DATEADD(mi,-15,GETDATE()),您将得到最后15分钟的结果,其他分钟的结果为0。

我发现这是一个很有用的替代方法,用于确定每分钟插入或更新多少记录。提前将日期格式作为变量的好处是,您可以轻松地将其更改为“每小时分析”。希望这有帮助!

DECLARE @dateFormat as varchar(max) = 'yyyy-MM-dd HH:mm'

SELECT format(timeColumn, @dateFormat) AS minute, COUNT(*) as results
FROM yourTable
WHERE timeColumn > DATEADD(hh, -1, GETDATE())
GROUP BY  format(timeColumn, @dateFormat)
ORDER BY 1

当你编辑问题时,我编辑我的答案。如果我理解正确,您只想查看过去的一个小时,也就是说,从发出请求前一个小时到当前时间的时间跨度。我会这样做:

SELECT
    COUNT(yourTimeStamp)
FROM yourTable
WHERE DATEADD('hh', -1, GetDate()) <= yourTimeStamp 
    AND yourTimeStamp < GetDate()
GROUP BY DATEPART('mm', yourTimeStamp)

MSSQL CURRENT_TIMESTAMP MINUTE 而不是 DATEPART 等等,但是你得到了解决方案的想法。

declare @times table
(
    someTime datetime
)

INSERT INTO @Times (sometime) values ('jan 12 2008 12:23')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:34')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:35')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:25')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:02')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:09')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:35')


select DATEPART(mi,sometime) AS Minute, count(*) AS NumOccurances
from @Times
WHERE SomeTime BETWEEN @Lower AND @Upper
GROUP BY DATEPART(mi, sometime)
order by NumOccurances DESC

Minute  NumOccurances
35  2
2   1
9   1
23  1
25  1
34  1

这是我对罗宾答案的修正版本。我让它以正确的顺序输出错误,并输出时间,而不仅仅是数字,如果你把它画出来的话,数字就没有多大用处了。

WITH numbers ( num ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + num FROM numbers WHERE num < 60 )
SELECT (SELECT DATEADD(n, -num, GETDATE())) AS TimeStamp,
    (SELECT COUNT(*) AS results
    FROM ErrorLogs
    WHERE DATEPART(n, TimeStamp) = DATEPART(n, DATEADD(n, -num, GETDATE()))
    AND TimeStamp > DATEADD(hh, -1, GETDATE())) as Count
FROM numbers

如果要按分钟对结果进行分组,则可以使用格式化字符串。这将分组 自1900年1月1日以来的分钟数 分秒必争 .

WITH formatted AS (
    SELECT FORMAT(<your_datetime_column>, 'yyyy-MM-dd HH:mm') AS minute_text
    FROM <your_table>
)
SELECT minute_text, COUNT(*) AS number_of_rows
FROM formatted
GROUP BY minute_text
ORDER BY 1 DESC

从TABLE.TS位于(开始时间、结束时间)之间的表中选择计数(TS)