Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Группировка по временному интервалу (1 значение из интервала) / 25 сообщений из 30, страница 1 из 2
  1  все
26.07.2019, 12:06
    #39841830
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Коллеги, добрый день.
Помогите советом.
Есть некие события у пользователей, нужно эти события сгруппировать по 20-минутным интервалам и взять 1 значение.
Другими словами, если возникло событие, то все последующие по времени в пределах 20 минут по тому же пользователю (userid) нужно отбросить, 1 значение > 20 мин - считать новым интервалом, итд.

Пример:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
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



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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 12:12
    #39841832
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select
 userid, min(dt)
from
 @t
group by
 userid, datediff(mi, '1900', dt) / 20 
order by
 userid, min(dt);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 12:19
    #39841836
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Блин, неужели все так просто решалось, я тут начал всякие оконные функции сочинять итд ...

Спасибо !
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 14:38
    #39841935
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Нет, все-таки я поторопился с выводами по вашему решению, вот для этих данных ответ не верный ...
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 14:43
    #39841941
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Dimais,

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

для понимания как это группирует
Код: sql
1.
2.
3.
4.
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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 14:53
    #39841946
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
TaPaK,

Если у него время (условно минуты) 1, 25, 43 - то первая группа включает только 1, а вторая 25 и 43.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 14:54
    #39841947
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Dimais , задача по сути итерационная. Решайте её с использованием курсора - тогда дело обойдётся однократным сканированием таблицы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 14:55
    #39841949
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
2 Akina - именно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 14:57
    #39841951
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Ну с курсором это понятно, другое дело что курсор на больших объемах не самое лучшее решение
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 14:58
    #39841952
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Хотя, наверное, можно и так (схематично):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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
            )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 16:49
    #39842016
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2019, 23:21
    #39842121
3unknown
3unknown
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2019, 01:40
    #39842138
3unknown
3unknown
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2019, 07:48
    #39842155
Ennor Tiegael
Ennor Tiegael
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Вы чего, народ, с ума все посходили, что ли? Понагородили, понимаешь. Проще надо быть:

Код: sql
1.
2.
3.
4.
5.
6.
7.
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;


По уму, в таблице должен быть нормальный первичный ключ, тогда условие
Код: sql
1.
and xt.dt < t.dt

можно будет переписать на нормальное неравенство PK. Иначе строки будут сами себя исключать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2019, 10:45
    #39842434
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Спасибо, всем кто предлагал решения. Но, похоже, вариант Ennor Tiegael самый простой и красивый.

ЗЫ Хотя, возможно, я опять поторопился с выводами, сейчас потестирую ... :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2019, 11:41
    #39842468
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Вот, я так и знал ...

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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;



Не верный результат, похоже действительно без рекурсии никак ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2019, 11:42
    #39842473
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
DimaisНо, похоже, вариант Ennor Tiegael самый простой и красивый.И неправильный, если исходить изDimaisДругими словами, если возникло событие, то все последующие по времени в пределах 20 минут по тому же пользователю (userid) нужно отбросить, 1 значение > 20 мин - считать новым интервалом, итд.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2019, 11:50
    #39842479
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
2 invm - да вы правы
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2019, 15:24
    #39842566
MaksK
MaksK
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2019, 15:43
    #39842578
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
2 MaksK

не, не верно

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
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



Должны быть те, значения, которые указаны "комментарием".
Я так понял, что все способы без рекурсии не работают, так как начало нового диапазона, определяется всегда относительно предыдущего.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2019, 15:46
    #39842579
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
DimaisЯ так понял, что все способы без рекурсии не работают, так как начало нового диапазона, определяется всегда относительно предыдущего.Именно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2019, 15:51
    #39842582
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
2 invm - Спасибо Вам Большое :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2019, 15:59
    #39842590
MaksK
MaksK
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2019, 16:39
    #39842618
Dimais
Dimais
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
2 MaksK нет, вот что должно получится

Код: sql
1.
2.
3.
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' --<
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.07.2019, 11:47
    #39842891
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка по временному интервалу (1 значение из интервала)
Справедливости ради - таки можно без рекурсии:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Группировка по временному интервалу (1 значение из интервала) / 25 сообщений из 30, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]