sql - 如何在sql server中选择彼此相隔一小时的记录

Question

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

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

例如。

• 用户 1 2012-03-07 14:24:30.000
• 用户 1 2012-03-07 14:34:30.000
• 用户 1 2012-03-07 15:14:30.000
• 用户 1 2012-03-07 15:20:30.000
• 用户 1 2012-03-07 15:30:30.000
• 用户 1 2012-03-08 09:20:30.000
• 用户 1 2012-03-08 09:50:30.000
• 用户 1 2012-03-08 10:30:30.000
• 用户 2 2012-03-07 15:20:30.000

我只想看下面的记录

• 用户 1 2012-03-07 14:24:30.000
• 用户 1 2012-03-07 15:30:30.000
• 用户 1 2012-03-08 09:20:30.000
• 用户 1 2012-03-08 10:30:30.000
• 用户 2 2012-03-07 15:20:30.000

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

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

非常感谢任何帮助!!

Answer 1

在 Sql Server 2005 或更新版本中，此 CTE 将返回 LoginAt 日期时间表，删除与已选择的 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() 是唯一的方式 (IMO) 对 loginAt 日期时间进行排序并只保留第一个。

Sql Fiddle playground is this way .

更新:

行号分别适用于每一代。摘自 WITH common_table_expression (Transact-SQL) :

Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data pased to the recursive part of the CTE. For more information, see J. Using analytical functions in a recursive CTE.