时间戳

首页 > 解决方案 > 相对于当前日期时间值按 +- 30 分钟分组

sql - 相对于当前日期时间值按 +- 30 分钟分组

问题描述

我在表中有重复项，必须删除它们。

为什么？用户可以单击“保存”或“保存并关闭”按钮，由于错误，当他多次单击“保存”按钮时会出现记录克隆。典型的定位。

用户可以在表中添加重复项，但不经常每小时添加一个，会话时间约为 30 分钟。

换句话说 - 我们应该删除在 +- 30 分钟时间段内创建的记录。

我需要帮助，我可以在没有循环（光标）的情况下解决我的任务吗？

我的尝试和示例数据：

declare @testData table(id int, createdOn datetime, val varchar(20))

insert into @testData(id, createdOn, val)
select 1, '2018-06-01 14:00:00' as CreatedOn, 'value1' as value1
union select 2, '2018-06-01 14:02:00', 'value1'  -- duplicate
union select 3, '2018-06-01 14:04:00', 'value1'  -- duplicate
union select 4, '2018-06-01 15:00:00', 'value2'
union select 5, '2018-06-01 15:02:00', 'value2'  -- duplicate
union select 6, '2018-06-01 15:03:00', 'valueUniq1'
union select 7, '2018-06-01 15:04:00', 'valueUniq2'
union select 8, '2018-06-01 15:40:00', 'value2'
union select 9, '2018-06-01 15:41:00', 'valueUniq3'
union select 10, '2018-06-01 15:59:00', 'value1'  -- NOT DUPLICATE!!!
union select 11, '2018-06-01 16:05:00', 'value1'  -- duplicate

-- Option 1

;
with duplicates(IdDup, CreatedOnDup, valueDup)
as (
    select a.Id, a.CreatedOn, a.val
    from @testData a, @testData b
    where a.id <> b.id
        and a.val = b.val
        and a.CreatedOn between dateadd(minute, -30, b.CreatedOn) and dateadd(minute, 30, b.CreatedOn)
)
select * from @testData
where Id in (
    select IdDup
    from duplicates)
and Id not in (
    select min(IdDup)
    from duplicates
    group by valueDup)

-- Option 2

;
with duplicates(CounterDup, IdDup)
as (
    select ROW_NUMBER() OVER(
        Partition By 
            a.val
            , cast(a.CreatedOn as date)  -- Incorrect, must be +- 30 minutes, not the whole day
        Order By a.Id ASC) As counterDup
        , a.Id as idDup
    from @testData a, @testData b
    where a.id <> b.id
        and a.val = b.val
        and a.CreatedOn between dateadd(minute, -30, b.CreatedOn) and dateadd(minute, 30, b.CreatedOn)
    )
select * from @testData
where Id in (
    select IdDup
    from duplicates
    where CounterDup > 1)
and Id not in (
    select IdDup
    from duplicates
    where CounterDup = 1)

两种方法都返回相同的结果，要删除的行（重复）：

2 2018-06-01 14:02:00.000   value1
3 2018-06-01 14:04:00.000   value1
5 2018-06-01 15:02:00.000   value2
10 2018-06-01 15:59:00.000  value1
11 2018-06-01 16:05:00.000  value1

倒数第二行不能在结果集中。

10  2018-06-01 15:59:00.000 value1

这不是重复的，它是一个新会话，因为在前一个“value1”之后 > 30 分钟。

标签： sqlsql-servergroup-by

解决方案

如果您想尝试没有延迟，您可以将此查询用于 SQL 的先前版本 

 Select * into  #tmp from 
(Select ROW_NUMBER() OVER(partition by val order by createdOn) valorder ,* 
 from @testData 
) t


Select * from #tmp a
inner join #tmp b on a.id = (b.id + 1) and a.val = b.val
where DATEDIFF(mi, b.CreatedOn, a.CreatedOn) <=30

drop table #tmp;

推荐阅读