sql - 按时间跨度的行数进行 SQL 查询

Question

我在 SQL Server 中有一个名为的数据库表，Cars其中包含一个time列，该列是该行的插入时间戳：

TABLE [Cars] 
(
    [ID] [BIGINT] NOT NULL,
    [Time] [DATETIME] NOT NULL,
    ...
)

我需要一个查询来获取在 1 分钟内插入超过 3 行的所有行组

我达到的唯一解决方案是

select a.ID, b.ID, c.ID, a.Time, b.Time, c.Time, c.Time-a.Time as duration
FROM Cars a
INNER JOIN Cars b on b.Time-a.Time < '1900-01-01 00:01'
INNER JOIN Cars c on c.Time-a.Time < '1900-01-01 00:01'
WHERE b.time > a.time and c.time > a.time

但是如果我在一分钟内尝试超过 3 行，它似乎太重了。

欢迎任何其他建议

标签： sqlsql-server

Answer 1

尝试以下内容

SELECT
    COUNT(C.[ID]),
    c.[Time]
FROM
    [Cars] AS C
GROUP BY
    DATEPART(YEAR, C.[Time]),
    DATEPART(MONTH, C.[Time]),
    DATEPART(DAY, C.[Time]),
    DATEPART(HOUR, C.[Time]),
    DATEPART(MINUTE, C.[Time])
HAVING
    COUNT(C.[ID]) > 3