从相隔小于指定分钟数的行中选择非重复值

问题描述

我有一张大表，格式如下:

I have a huge table with the following format:

DATETIME                   NUMBER
--------------------------------------
2009-03-31 16:05:52.000    2453651622
2009-03-31 16:16:12.000    30206080
2009-03-31 16:16:16.000    16890039
2009-03-31 16:16:28.000    2452039696
2009-03-31 16:16:33.000    140851934
2009-03-31 16:16:51.000    2453120306
2009-03-31 16:16:57.000    2453120306
...
2009-04-01 21:15:24.000    2453651622

如果第二列中没有重复数字的行相隔不到 15 分钟，我该如何选择它们?

How can I select the rows that don't have duplicate numbers in the second column if they occur less than 15 minutes apart?

在前面的示例中，编号为 2453120306 的第二行是重复的，因为它与前一行的间隔小于 15 分钟，不应被选中.

In the previous example, the second row with number 2453120306 is a duplicate because it is less than 15 minutes apart from the previous one, and should not be selected.

最后一行与第一行的编号相同，但它不是重复的，因为它发生在超过 24 小时之后.

The last row has the same number as the first row, but it is not a duplicate because it occurs more that 24 hours later.

推荐答案

-- distinct required in case there are rows with 
-- exactly the same values for datetime and number
SELECT DISTINCT a.*
FROM your_table AS a
    LEFT JOIN your_table AS b
        ON a.[number] = b.[number]
            AND a.[datetime] > b.[datetime]
            AND a.[datetime] <= DATEADD(minute, 15, b.[datetime])
WHERE b.Number IS NULL