我有一组带时间戳的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分区的方式。

任何帮助深表感谢!!

最佳答案

在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日期时间并仅保留第一个的唯一方法(IMO)。

Sql Fiddle playground is this way

更新:

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


CTE的递归部分中的分析函数和集合函数为
应用于当前递归级别的集合,而不应用于集合
CTE。 ROW_NUMBER之类的函数仅在以下子集上起作用
当前递归级别而不是整个递归级别传递给他们的数据
粘贴到CTE递归部分的一组数据。欲了解更多
有关信息,请参阅J.在递归CTE中使用分析函数。

关于sql - 如何在SQL Server中选择彼此相距一个小时的记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/10037126/

10-12 00:23
查看更多