ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Группировка по временному интервалу (1 значение из интервала)
25 сообщений из 30, страница 1 из 2
  1  все
Группировка по временному интервалу (1 значение из интервала)
    #39841830
Dimais
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimais
Гость
Коллеги, добрый день.
Помогите советом.
Есть некие события у пользователей, нужно эти события сгруппировать по 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:06
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39841832
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Код: 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:12
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39841836
Dimais
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimais
Гость
Блин, неужели все так просто решалось, я тут начал всякие оконные функции сочинять итд ...

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

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

Код: 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, 14:58
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842016
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Код: 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, 16:49
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842121
3unknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
3unknown
Гость
Код: 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
26.07.2019, 23:21
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842138
3unknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
3unknown
Гость
Код: 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, 01:40
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842155
Фотография Ennor Tiegael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ennor Tiegael
Участник
Вы чего, народ, с ума все посходили, что ли? Понагородили, понимаешь. Проще надо быть:

Код: 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
27.07.2019, 07:48
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842434
Dimais
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimais
Гость
Спасибо, всем кто предлагал решения. Но, похоже, вариант Ennor Tiegael самый простой и красивый.

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

Код: 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:41
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842473
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
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:42
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842479
Dimais
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimais
Гость
2 invm - да вы правы
...
Рейтинг: 0 / 0
29.07.2019, 11:50
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842566
MaksK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaksK
Гость
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:24
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842578
Dimais
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimais
Гость
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:43
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842579
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
DimaisЯ так понял, что все способы без рекурсии не работают, так как начало нового диапазона, определяется всегда относительно предыдущего.Именно.
...
Рейтинг: 0 / 0
29.07.2019, 15:46
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842582
Dimais
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimais
Гость
2 invm - Спасибо Вам Большое :-)
...
Рейтинг: 0 / 0
29.07.2019, 15:51
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842590
MaksK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaksK
Гость
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, 15:59
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842618
Dimais
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimais
Гость
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
29.07.2019, 16:39
| Ответить | Цитировать | Написать  
Группировка по временному интервалу (1 значение из интервала)
    #39842891
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Справедливости ради - таки можно без рекурсии:
Код: 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
30.07.2019, 11:47
| Ответить | Цитировать | Написать  
25 сообщений из 30, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Группировка по временному интервалу (1 значение из интервала)
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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