如何在SQL Server中选择彼此相距一个小时的记录

Question

我有一组带时间戳的user_id登录数据。

一个用户可以多次登录，但从最小记录开始，我们需要至少相隔一个小时返回记录。重复数据删除必须在用户级别进行（可以有多个用户）

例如

• user1 2012-03-07 14：24：30.000
• user1 2012-03-07 14：34：30.000
• user1 2012-03-07 15：14：30.000
• user1 2012-03-07 15：20：30.000
• user1 2012-03-07 15：30：30.000
• user1 2012-03-08 09：20：30.000
• user1 2012-03-08 09：50：30.000
• user1 2012-03-08 10：30：30.000
• user2 2012-03-07 15：20：30.000

我只想查看以下记录

• user1 2012-03-07 14：24：30.000
• user1 2012-03-07 15：30：30.000
• user1 2012-03-08 09：20：30.000
• user1 2012-03-08 10：30：30.000
• user2 2012-03-07 15：20：30.000

================================================== ======================

有什么办法可以做到这一点吗？我们可以递归地执行此操作，但我希望可能有一种使用row_number分区的方式。

任何帮助深表感谢！！

Answer 1

在Sql Server 2005或更高版本中，此CTE将返回LoginAt datetimes表，将其与已选择的LoginAts相隔不到一小时的表删除。

;with SkipHour(UserID, LoginAT, rn) as (
  select UserID, min(LoginAt), cast (1 as bigint)
    from LogTable
   group by UserID
  union all
  select SkipHour.UserID, LogTable.LoginAt,
         row_number() over (partition by SkipHour.UserID 
                            order by Logtable.LoginAt) rn
  from SkipHour
     inner join LogTable
        on LogTable.UserID = SkipHour.UserID
     where datediff(minute, SkipHour.LoginAt, LogTable.LoginAt) >= 60
     -- Only first rows from previous generation qualify to have children
        and rn = 1
)
select *
from SkipHour
where rn = 1
order by UserID, LoginAT

关键部分是row_number（）。由于Sql Server既不允许聚合函数也不允许顶级谓词，因此row_number（）是订购loginAt
datetimes并仅保留第一个的唯一方法（IMO）。

Sql Fiddle游乐场就是这种方式。

更新 ：

行号分别适用于每一代。从WITH common_table_expression（Transact-
SQL）中
提取：

CTE递归部分中的html" target="_blank">分析函数和集合函数将应用于当前递归级别的集合，而不应用于CTE的集合。像ROW_NUMBER之类的函数仅对当前递归级别传递给它们的数据子集起作用，而不对粘贴到CTE递归部分的整个数据集起作用。有关更多信息，请参见J.在递归CTE中使用分析函数。