Группа COUNT(*) по похожим меткам времени

avatar
Mohsen Sichani
8 апреля 2018 в 09:34
183
2
0

В моей таблице есть некоторые данные (tb1):

PK   datetime1               datetime2               grp
--------------------------------------------------------
 1   2016-01-01 00:30:10     2016-01-01 00:33:10      1
 2   2013-01-01 00:30:10     2013-01-01 00:34:10      2

Я пытаюсь найти количество событий на основе даты и времени1, даты и времени2 и местоположения.

Так вот мой запрос:

 select count(*), datetime1, datetime2, grp 
 from tb1
 group by datetime1, datetime2, grp

Здесь проблем нет.

В некоторой степени я хотел бы найти количество на основе:

когда разница между датой и временем1 между двумя последовательными строками (может быть достигнута опережающей функцией) меньше 1 секунды, а разница между датой и временем2 между двумя последовательными строками составляет менее 1 секунды, и grp.

Я могу использовать руководство для нахождения разницы между двумя последовательными строками, но не знаю, как здесь применить функцию подсчета для группировки по сходству во времени между двумя последовательными строками.

Чтобы сделать это проще, я ищу что-то вроде этого:

если 

 select 
     count(*), grp, ....
 from 
     tb1
 where 
     datediff(s, lead(datetime1, 1, 1)  over (partition by grp order by datetime1)) = 1 
     and datediff(s, lead(datetime2, 1, 1) = 1 over (partition by grp order by datetime2) = 1 
group by
    lead(datetime1, 1, 1) over (partition by grp order by datetime1), 
    lead(datetime2, 1, 1) over (partition by grp order by datetime2), 
    grp

Пожалуйста, дайте мне знать, если есть необходимость в дополнительных разъяснениях.

Источник
P.Salmon
8 апреля 2018 в 09:42
1

ЕСТЬ ли и отсутствует "когда разница между датой и временем 1 между двумя последовательными строками составляет менее 1 секунды И разница между датой и временем 2 между двумя последовательными строками составляет менее 1 секунды"

Mohsen Sichani
8 апреля 2018 в 09:45
0

исправлено, спасибо, а также неправильный порядок расположения и группировки :)

Zohar Peled
8 апреля 2018 в 09:49
0

Примечание: вы продолжаете писать фразу «две последовательные строки», но в реляционной базе данных строки таблицы по своей природе не отсортированы, поэтому нет такой вещи, как «последовательные строки».

Mohsen Sichani
8 апреля 2018 в 09:54
0

Я знаю об этом. Я достигаю этого, используя ведущую функцию, чтобы найти следующую строку, добавил это к вопросу, спасибо

P.Salmon
8 апреля 2018 в 10:12
1

Представьте, что строка 2 может быть сгруппирована с строкой 1 или строкой 3. Как избежать двойного счета?

Mohsen Sichani
8 апреля 2018 в 20:13
0

@P.Salmon см. sqlfiddle.com/#!18/b4a43/1/0

Mohsen Sichani
22 апреля 2018 в 19:19
0

@П.Сэлмон. Ты был на 100 прав. Мой подход был неправильным, и ваш комментарий имеет больше смысла, когда я углубляюсь в проблему. Эта ситуация не так проста, как я думал. Это требует больших усилий или изменения подхода, такого как кластеризация.

Ответы (2)

avatar
paparazzo
8 апреля 2018 в 16:27
2

Это основано на int, но такой же подход будет работать и с datetime

declare @T table (pk int identity primary key, val int);
insert into @T values ('1'), ('9'), ('9'), ('11'), ('2'), ('2'), ('3'), ('5'), ('7'), ('8');
select tt.pk, tt.val 
     , sum(ll) over (order by val, pk) as grp
from  ( select * 
             , case when lag(val,1) over (order by val, pk) is null 
                      or val - lag(val,1) over (order by val, pk) <= 1 then 0 
                    else 1 
               end as ll
          from @T t
      ) tt
order by val, pk;

pk          val         grp
----------- ----------- -----------
1           1           0
5           2           0
6           2           0
7           3           0
8           5           1
9           7           2
10          8           2
2           9           2
3           9           2
4           11          3
Mohsen Sichani
8 апреля 2018 в 20:01
0

Спасибо @paparazzo, линия val-lag натолкнула меня на мысль. Проголосовали за.. Я поместил свой код на основе вашего и Майкла в sqlfiddle.com/#!18/b4a43/1/0 Проголосовал за

avatar
Michał Turczyn
8 апреля 2018 в 10:46
2

Я сгенерировал некоторые образцы данных. Проверьте, это то, что вы ищете. Я добавляю необходимые комментарии в код. Запрос может быть более кратким, но я хотел объяснить как можно больше на каждом этапе:

declare @table table (PK int, datetime1 datetime, datetime2 datetime, grp int)
insert into @table values
(1, '2016-01-01 00:30:14.000', '2016-01-01 00:33:15.000', 1),
(2, '2016-01-01 00:30:10.232', '2016-01-01 00:33:10.000', 1),
(3, '2016-01-01 00:30:10.111', '2016-01-01 00:33:10.234', 1),
(4, '2016-01-01 00:30:12.000', '2016-01-01 00:33:15.000', 2),
(5, '2016-01-01 00:30:10.000', '2016-01-01 00:33:10.234', 2),
(6, '2016-01-01 00:30:10.222', '2016-01-01 00:33:10.000', 2)

select min(pk), min(datetime1), count(*) from (
    --in this query, based on differences, we will generate grouping column called IsClose
    select *, case when (diff1 <= 1000 and diff2 <= 1000) or (diff3 <= 1000 and diff4 <= 1000) then 1 else 0 end [IsClose] from (
        --this query gives to additionals columns with absolute differences between consecutive rows ordered by PK column
        select *,
               abs(datediff(ms, datetime1, lag(datetime1) over (order by pk))) [diff1],
               abs(datediff(ms, datetime2, lag(datetime2) over (order by pk))) [diff2],
               abs(datediff(ms, datetime1, lead(datetime1) over (order by pk))) [diff3],
               abs(datediff(ms, datetime2, lead(datetime2) over (order by pk))) [diff4]  
        from @table
    ) [a]
) [a] group by grp, IsClose
Mohsen Sichani
8 апреля 2018 в 20:00
0

Спасибо, @michał turczyn, очень хорошая идея, я применил ее и получил следующее: sqlfiddle.com/#!18/b4a43/1/0 Не могу понять, почему вы использовали и опережение, и отставание. Во-вторых, почему вы использовали ПК в самом внутреннем запросе? это должно быть разделение по порядку свинца по дате и времени. В вашем случае grp игнорируется и сортируется только на основе PK, что неверно, поскольку запрос может выбрать что-то неправильное для следующей строки. Я уверен, что это дает неправильный результат. Еще раз спасибо. Проголосовал за, так как это действительно дало мне идею

Michał Turczyn
9 апреля 2018 в 05:45
0

@mohsenhs Я рад, что смог помочь :)