七秒锺记忆
问题描述
我有一张大表，格式如下:
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
