Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
Коллеги, добрый день.
Помогите советом.
Есть некие события у пользователей, нужно эти события сгруппировать по 20-минутным интервалам и взять 1 значение.
Другими словами, если возникло событие, то все последующие по времени в пределах 20 минут по тому же пользователю (userid) нужно отбросить, 1 значение > 20 мин - считать новым интервалом, итд.

Пример:

declare @t as table (userid int, dt datetime2(0))
insert into @t select 1, '2019-07-01 07:03:39'
union select 1, '2019-07-01 07:05:12'
union select 1, '2019-07-01 07:35:52'
union select 2, '2019-07-01 01:01:21'
union select 2, '2019-07-01 01:19:37'
union select 2, '2019-07-01 04:12:37'
union select 2, '2019-07-01 04:15:37'
union select 2, '2019-08-12 15:42:31'

select * from @t order by 1,2


Т.е. Должно получится следующее:

1, '2019-07-01 07:03:39'
1, '2019-07-01 07:35:52'
2, '2019-07-01 01:01:21'
2, '2019-07-01 04:12:37'
2, '2019-08-12 15:42:31'


select @@VERSION

Microsoft SQL Server 2014 12.0.6293.0
26 июл 19, 12:06    [21935231]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
invm
Member

Откуда: Москва
Сообщений: 8845
select
 userid, min(dt)
from
 @t
group by
 userid, datediff(mi, '1900', dt) / 20 
order by
 userid, min(dt);
26 июл 19, 12:12    [21935236]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
Блин, неужели все так просто решалось, я тут начал всякие оконные функции сочинять итд ...

Спасибо !
26 июл 19, 12:19    [21935241]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
Нет, все-таки я поторопился с выводами по вашему решению, вот для этих данных ответ не верный ...
set dateformat ymd

declare @t as table (userid int, dt datetime2(0))

insert into @t select 1, '2019-07-01 07:01:01'
union select 1, '2019-07-01 07:35:00'
union select 1, '2019-07-01 07:44:59'


select userid, min(dt)
from @t
group by userid, datediff(mi, '1900', dt) / 20
order by userid, min(dt)
26 июл 19, 14:38    [21935425]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6693
Dimais,

аругемнируйте

для понимания как это группирует
select userid, min(dt), DATEADD(mi, 20 * (datediff(mi, '1900', dt) / 20),0)
from @t
group by userid, datediff(mi, '1900', dt) / 20
order by userid, min(dt)
26 июл 19, 14:43    [21935435]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19591
TaPaK,

Если у него время (условно минуты) 1, 25, 43 - то первая группа включает только 1, а вторая 25 и 43.
26 июл 19, 14:53    [21935445]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19591
Dimais, задача по сути итерационная. Решайте её с использованием курсора - тогда дело обойдётся однократным сканированием таблицы.
26 июл 19, 14:54    [21935447]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
2 Akina - именно
26 июл 19, 14:55    [21935449]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
Ну с курсором это понятно, другое дело что курсор на больших объемах не самое лучшее решение
26 июл 19, 14:57    [21935451]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19591
Хотя, наверное, можно и так (схематично):

WITH cte AS ( SELECT TOP 1 *
              FROM table
              WHERE (conditions)
              ORDER BY datetime ASC
            UNION ALL
              SELECT TOP 1 *
              FROM table
              WHERE (conditions)
                AND datetime > (SELECT MAX(datetime) + 20 minutes
                                FROM cte)
              ORDER BY datetime ASC
            )
26 июл 19, 14:58    [21935454]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
invm
Member

Откуда: Москва
Сообщений: 8845
with t as
(
 select
  userid, dt
 from
  (select userid, dt, row_number() over (partition by userid order by dt) from @t) a(userid, dt, rn)
 where
  rn = 1

 union all

 select
  t.userid, a.dt
 from
  t cross apply
  (select dt, row_number() over (partition by userid order by dt) from @t where userid = t.userid and dt > dateadd(mi, 20, t.dt)) a(dt, rn)
 where
  a.rn = 1
)
select * from t order by userid, dt;

ЗЫ: Нужен будет индекс по (userid, dt)
26 июл 19, 16:49    [21935599]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
3unknown
Member

Откуда: New York
Сообщений: 137
declare @t as table (userid int, dt datetime2(0))
insert into @t select 1, '2019-07-01 07:03:39'
union select 1, '2019-07-01 07:05:12'
union select 1, '2019-07-01 07:35:52'
union select 2, '2019-07-01 01:01:21'
union select 2, '2019-07-01 01:19:37'
union select 2, '2019-07-01 04:12:37'
union select 2, '2019-07-01 04:15:37'
union select 2, '2019-08-12 15:42:31'


;with a as(
select userid,dt
,ROW_NUMBER() over(PARTITION by userid order by dt) num
from @t
)

select userid,st as dt from(
select a.userid,a.dt as st,a1.dt fin
,case when DATEDIFF(MINUTE,a.dt,a1.dt)>=20 then 1 else 0 end d
from a
 join a a1 on a.userid = a1.userid
 and a1.num-a.num = 1
 ) g
 where d = 0
 union
 select userid,fin from(
select a.userid,a.dt as st,a1.dt fin
,case when DATEDIFF(MINUTE,a.dt,a1.dt)>=20 then 1 else 0 end d
from a
 join a a1 on a.userid = a1.userid
 and a1.num-a.num = 1
 ) g
 where d = 1


Сообщение было отредактировано: 27 июл 19, 00:32
26 июл 19, 23:21    [21935824]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
3unknown
Member

Откуда: New York
Сообщений: 137
declare @t as table (userid int, dt datetime2(0))
insert into @t select 1, '2019-07-01 07:03:39'
union select 1, '2019-07-01 07:05:12'
union select 1, '2019-07-01 07:35:52'
union select 2, '2019-07-01 01:01:21'
union select 2, '2019-07-01 01:19:37'
union select 2, '2019-07-01 04:12:37'
union select 2, '2019-07-01 04:15:37'
union select 2, '2019-08-12 15:42:31'



select
distinct userid, case f when 0 then dt_lag else dt end as date
from(
select userid
,LAG(dt,1,dt)  over(PARTITION by userid order by dt) dt_lag
,dt
,case when datediff(mi,LAG(dt,1,dt)  over(PARTITION by userid order by dt),dt) >=20 then 1 else 0 end f
from @t
) a
27 июл 19, 01:40    [21935855]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3077
Вы чего, народ, с ума все посходили, что ли? Понагородили, понимаешь. Проще надо быть:

select t.*
from @t t
where not exists (
  select 0 from @t xt where xt.userid = t.userid and datediff(minute, xt.dt, t.dt) <= 20
    and xt.dt < t.dt
)
order by t.userid, t.dt;

По уму, в таблице должен быть нормальный первичный ключ, тогда условие
and xt.dt < t.dt
можно будет переписать на нормальное неравенство PK. Иначе строки будут сами себя исключать.
27 июл 19, 07:48    [21935882]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
Спасибо, всем кто предлагал решения. Но, похоже, вариант Ennor Tiegael самый простой и красивый.

ЗЫ Хотя, возможно, я опять поторопился с выводами, сейчас потестирую ... :-)
29 июл 19, 10:45    [21936644]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
Вот, я так и знал ...

set dateformat ymd

declare @t as table (userid int, dt datetime2(0))

insert into @t select 1,         '2019-07-01 07:00:00'
union select 1,			 '2019-07-01 07:35:00'
union select 1,			 '2019-07-01 07:44:59'
union select 1,			 '2019-07-01 07:45:58'
union select 1,			 '2019-07-01 08:05:58'
union select 1,			 '2019-07-01 08:25:58'
union select 1,			 '2019-07-01 09:06:15'


select t.*
from @t t
where not exists (
  select 0 from @t x where x.userid = t.userid and x.dt < t.dt and datediff(SECOND, x.dt, t.dt) <= 1200
)
order by t.userid, t.dt;


Не верный результат, похоже действительно без рекурсии никак ...
29 июл 19, 11:41    [21936691]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
invm
Member

Откуда: Москва
Сообщений: 8845
Dimais
Но, похоже, вариант Ennor Tiegael самый простой и красивый.
И неправильный, если исходить из
Dimais
Другими словами, если возникло событие, то все последующие по времени в пределах 20 минут по тому же пользователю (userid) нужно отбросить, 1 значение > 20 мин - считать новым интервалом, итд.
declare @t as table (userid int, dt datetime2(0), index IX_t (userid, dt));

insert into @t select 1, '2019-07-01 07:00'
union select 1, '2019-07-01 07:19'
union select 1, '2019-07-01 07:21'
union select 1, '2019-07-01 07:39'
union select 1, '2019-07-01 07:45';

with t as
(
 select
  userid, dt
 from
  (select userid, dt, row_number() over (partition by userid order by dt) from @t) a(userid, dt, rn)
 where
  rn = 1

 union all

 select
  t.userid, a.dt
 from
  t cross apply
  (select dt, row_number() over (partition by userid order by dt) from @t where userid = t.userid and dt > dateadd(mi, 20, t.dt)) a(dt, rn)
 where
  a.rn = 1
)
select * from t order by userid, dt;

select t.*
from @t t
where not exists (
  select 0 from @t xt where xt.userid = t.userid and datediff(minute, xt.dt, t.dt) <= 20
    and xt.dt < t.dt
)
order by t.userid, t.dt;
29 июл 19, 11:42    [21936696]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
2 invm - да вы правы
29 июл 19, 11:50    [21936709]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
MaksK
Member

Откуда:
Сообщений: 6
Dimais, попробуйте такой вариант:

declare @t as table (userid int, dt datetime2(0))
insert into @t select 1, '2019-07-01 07:03:39'
union select 1, '2019-07-01 07:05:12'
union select 1, '2019-07-01 07:35:52'
union select 2, '2019-07-01 01:01:21'
union select 2, '2019-07-01 01:19:37'
union select 2, '2019-07-01 04:12:37'
union select 2, '2019-07-01 04:15:37'
union select 2, '2019-08-12 15:42:31'

;with sel as (
select
userid
,dt
,n= ROW_NUMBER() over (partition by userid order by userid)
from @t)


select userid
,dt
from
(
select
userid
,dt
,n
,IIF(
min(n) over (partition by userid) = n
,20
,
DATEDIFF(MINUTE, (select dt from sel s where s.userid= sel.userid and s.n = sel.n-1), dt)
) aa

from sel
) as d
where
aa>= 20
order by 1,2
29 июл 19, 15:24    [21936925]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
2 MaksK

не, не верно

declare @t as table (userid int, dt datetime2(0))
insert into @t select 1,         '2019-07-01 07:00:00' --<
union select 1,			 '2019-07-01 07:19:15'
union select 1,			 '2019-07-01 07:20:01' --<
union select 1,			 '2019-07-01 07:40:01'
union select 1,			 '2019-07-01 07:45:15' --<

;with sel as (
select
userid
,dt
,n= ROW_NUMBER() over (partition by userid order by userid)
from @t)

select userid
,dt
from
(
select
userid
,dt
,n
,IIF(min(n) over (partition by userid) = n
,1200
,
DATEDIFF(SECOND, (select dt from sel s where s.userid = sel.userid and s.n = sel.n - 1), dt)
) aa

from sel
) as d
where
aa >= 1200
order by 1,2


Должны быть те, значения, которые указаны "комментарием".
Я так понял, что все способы без рекурсии не работают, так как начало нового диапазона, определяется всегда относительно предыдущего.
29 июл 19, 15:43    [21936943]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
invm
Member

Откуда: Москва
Сообщений: 8845
Dimais
Я так понял, что все способы без рекурсии не работают, так как начало нового диапазона, определяется всегда относительно предыдущего.
Именно.
29 июл 19, 15:46    [21936947]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
2 invm - Спасибо Вам Большое :-)
29 июл 19, 15:51    [21936953]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
MaksK
Member

Откуда:
Сообщений: 6
Dimais, а если так:

declare @t as table (userid int, dt datetime2(0))
insert into @t select 1, '2019-07-01 07:00:00' --<
union select 1, '2019-07-01 07:19:15'
union select 1, '2019-07-01 07:20:01' --<
union select 1, '2019-07-01 07:40:01'
union select 1, '2019-07-01 07:45:15' --<

;with sel as (
select
userid
,dt
,n= ROW_NUMBER() over (partition by userid order by userid)
from @t)
,sel_ as
(
select *, IIF(min(n) over (partition by userid) = n
,1200
,DATEDIFF(SECOND, (select dt from sel s where s.userid = ss.userid and s.n = ss.n - 1), dt)) as sec
from sel ss
)

select userid, dt, aa
from
(
select
userid
,dt
,n
,IIF(min(n) over (partition by userid) = n
,1200
,DATEDIFF(SECOND, (select max(dt) dt from sel_ s where s.userid = sel.userid and s.n < sel.n and s.sec>= 1200 ), dt)) aa
from sel_ sel
) as d
where aa >= 1200
order by 1,2
29 июл 19, 15:59    [21936964]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
Dimais
Member

Откуда:
Сообщений: 227
2 MaksK нет, вот что должно получится

insert into @t select 1,         '2019-07-01 07:00:00' --<
union select 1,			 '2019-07-01 07:20:01' --<
union select 1,			 '2019-07-01 07:45:15' --<
29 июл 19, 16:39    [21937011]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по временному интервалу (1 значение из интервала)  [new]
invm
Member

Откуда: Москва
Сообщений: 8845
Справедливости ради - таки можно без рекурсии:
with a as
(
 select
  a.*,
  b.x - lag(b.x, 1, b.x) over (partition by a.userid order by a.dt) as d
 from
  @t a cross apply
  (select datediff(mi, '1900', a.dt)) b(x)
),
b as (select *, sum(d) over (partition by a.userid order by a.dt) % 20 as drt from a),
c as (select userid, dt, case when drt % 20 < lag(drt % 20, 1, drt + 1) over (partition by userid order by dt) then 1 else 0 end as f from b)
select userid, dt from c where f = 1;
30 июл 19, 11:47    [21937697]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить