Sql Server: Есть ли способ группировать записи по датам на основе дат других записей?

У меня есть таблица, в которой есть события, и мне нужно найти повторяющиеся события. Проблема в том, что события, происходящие в течение 1 секунды друг от друга, считаются дубликатами. Итак, если моя таблица имеет эти значения 



id | var1 | var2 | var3 | date

1  | 1    | 2    | 3    | 2001-01-01 01:01:01.456

2  | 1    | 2    | 3    | 2001-01-01 01:01:02.234

3  | 1    | 2    | 3    | 2001-01-01 01:01:04.789


Записи 1 и 2 считаются дубликатами, потому что они находятся в пределах одной секунды, но 3-не потому, что это больше, чем одна секунда после 2.



Есть ли способ написать запрос, который выбирает только первую запись в серии дубликатов?



Правка: там также могут быть строки, которые не дублируются,которые нужно будет захватить. Id является первичным ключом таблицы и не используется в критериях соответствия; он просто существует для уточнения.

529   3  

3 ответов:

Вот способ, который, кажется, должен работать для вас.

Некоторые предположения:

  1. я предполагаю, что дубликат-это фактический дубликат строки, исключающий ID, основанный на 1-м предложении, которое вы предоставили. Если это не так... удалите раздел по части функции окна row_number(), и это изменит поведение
  2. это удаляет рекурсивные дубликаты. То есть, если 3,4 или даже 15 строк находятся в пределах секунды друг от друга, он сохраняет 1.
  3. это должно работать независимо от того, если первая или последняя строка является дубликатом

Вот код. Раскомментируйте две строки в таблице, чтобы увидеть изменения

declare @table table(id int, var1 int, var2 int, var3 int, date datetime2)
insert into @table
values
--(0,1,2,3,'2001-01-01 00:01:01.456'), 

(1,1,2,3,'2001-01-01 01:01:01.456'), --dupe of 1/2/3
(2,1,2,3,'2001-01-01 01:01:02.214'), --dupe of 1/2/3
(3,1,2,3,'2001-01-01 01:01:02.234'), --dupe of 1/2/3
(4,1,2,3,'2001-01-01 01:01:02.244'), --dupe of 1/2/3

(5,1,2,3,'2001-01-01 01:01:04.789'), --dupe of 4/5
(6,1,2,3,'2001-01-01 01:01:04.989'), --dupe of 4/5

--(7,1,2,3,'2001-01-01 01:01:06.789'), --dupe of 6/7
(8,1,2,3,'2001-01-01 01:01:06.799') --dupe of 6/7

--apply the sequence
;with cte as(
select 
    *,
    ROW_NUMBER() over (partition by var1, var2, var3 order by date) as RN  --just in case... change this to just order by id, date if need be and remove the partition
from 
    @table),

--get first / most of the batch to remove
cte2 as(
select
    c1.*
    ,c2.RN as RowsToRemove
from cte c1
left join
    cte c2 on c1.RN < c2.rn  and 
    datediff(second,c1.date,c2.date) < 1),


--remove the rows identified in the above cte
cte3 as(
select distinct
    ID, 
    var1,
    var2,
    var3,
    date,
    RN
from cte2
where 
    RN not in (select distinct isnull(RowsToRemove,0) from cte2)),

--add another sequence. This is necessary for first/last row check for duplicate
cte4 as(
select
    f.*,
    row_number() over (partition by var1, var2, var3 order by date) RN2
from 
    cte3 f)

--return the results
select 
    f.ID, 
    f.var1,
    f.var2,
    f.var3,
    f.date
from 
    cte4 f
left join
    cte4 d on d.RN = f.RN - 1
where isnull(datediff(second,d.date,f.date),500) > 1

Возвращает 

+----+------+------+------+-----------------------------+
| ID | var1 | var2 | var3 |            date             |
+----+------+------+------+-----------------------------+
|  1 |    1 |    2 |    3 | 2001-01-01 01:01:01.4560000 |
|  5 |    1 |    2 |    3 | 2001-01-01 01:01:04.7890000 |
|  8 |    1 |    2 |    3 | 2001-01-01 01:01:06.7990000 |
+----+------+------+------+-----------------------------+
2  
2017-07-14 23:13:33

ЛАГ является одним из возможных решений, что-то вроде этого: 

select * from (
select *, lag(date,1) over(order by date) previoustime from yourtable
) x
where datediff(second,previoustime,date)<1
2  
2017-07-14 22:28:05 
    select T1.date,... from MyTable T1
left outer join MyTable T2 on cast(T1.date as date) = cast(T2.date as date) and 
datediff(second,T1.date,T2.date)<=1
group by cast(T1.date as date)
0  
2017-07-15 15:05:46

Comments

    Ничего не найдено.
Click here to cancel reply.