Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Выделение временных кластеров / 5 сообщений из 5, страница 1 из 1
18.04.2021, 12:34
    #40063553
VictorChuff
VictorChuff
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выделение временных кластеров
Добрый день!

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

ID. Date
1 2020-01-10
2. 2020-01-15
3 2020-01-26
4 2020-01-30
5 2020-01-31
6 2020-02-16
7 2020-02-18
8 2020-02-22
9 2020-02-24
10 2020-03-12

запрос должен выделить два кластера

ID. Date. Cluster
2. 2020-01-15. 1
3 2020-01-26. 1
4 2020-01-30. 1
6 2020-02-16. 2
7 2020-02-18. 2
8 2020-02-22. 2
9 2020-02-24. 2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2021, 14:12
    #40063576
Критик
Критик
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выделение временных кластеров
VictorChuff,

И куда у вас данные пропали в результате? Например, с id=1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2021, 14:23
    #40063581
godsql
godsql
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выделение временных кластеров
Как-то так, на скорую руку
Код: 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.
with #t as (
	select id, dat from ( values (1, cast ('20200110' as datetime )),
(2, '20200115'),
(3,'20200126'),
(4,'20200130'),
(5,'20200131'),
(6,'20200216'),
(7,'20200218'),
(8,'20200222'),
(9,'20200224'),
(10,'20200312')) as t(id, dat)
)
,
#t1 as (
select id, dat,(select max(a.dat) from #t a where (datediff(dd,b.dat,a.dat) <=15) and (a.id>b.id)

) as dat2 from  #t b
)
,
#t3 as (
select z.id, z.dat, z.dat2
from #t1 z
where (select count(m.dat) from #t1 m where m.dat between z.dat and z.dat2) >=3 
)
select id, dat , dat2
, ROW_NUMBER () over (order by (select null)) as grp from #t3
where not exists (select 1 from #t3 t1 where #t3.dat between t1.dat and t1.dat2  and (#t3.id > t1.id))



Это периоды
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2021, 20:20
    #40064005
godsql
godsql
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выделение временных кластеров
доделал и причесал
 

Код: 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.
with #t as (
	select id, dat from ( values (1, cast ('20200110' as datetime )),
(2, '20200115'),
(3,'20200126'),
(4,'20200130'),
(5,'20200131'),
(6,'20200216'),
(7,'20200218'),
(8,'20200222'),
(9,'20200224'),
(10,'20200312')) as t(id, dat)
)
,#t1 as (
select b.id, b.dat, max(z.dat) as dat2
from  #t b
 cross apply ( select a.dat from #t a where ( datediff(dd,b.dat,a.dat) <=15 ) and (a.id>=b.id) )  z
 group by b.id, b.dat
 having  count(z.dat)>=3
)
,#t2 as (
select t.id, t.dat, t.dat2, ROW_NUMBER () over (order by (select null)) as grp  
from #t1 t
where not exists (select 1 from #t1 t1 where t.dat between t1.dat and t1.dat2  and (t.id > t1.id)) 
)
select a.id, a.dat, b.grp from #t a
join #t2 b on a.dat between b.dat and b.dat2

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2021, 21:54
    #40064026
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выделение временных кластеров
Код: 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.
37.
38.
declare @t table (id int primary key, d date);
insert into @t
values
 (1, '2020-01-10'),
 (2, '2020-01-15'),
 (3,'2020-01-26'),
 (4,'2020-01-30'),
 (5,'2020-01-31'),
 (6,'2020-02-16'),
 (7,'2020-02-18'),
 (8,'2020-02-22'),
 (9,'2020-02-24'),
 (10,'2020-03-12');

with t as
(
 select
  a.d,
  lag(max(b.d)) over (order by a.d) as d_max__prev,
  string_agg(b.id, ',') as ids_list
 from
  @t a join
  @t b on b.d between a.d and dateadd(day, 15, a.d)
 group by
  a.d
 having
  count(*) > 2
)
select
 b.id, b.d, dense_rank() over (order by t.d)
from
 t cross apply
 string_split(t.ids_list, ',') a join
 @t b on b.id = a.value
where
 t.d > t.d_max__prev or t.d_max__prev is null
order by
 b.d;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Выделение временных кластеров / 5 сообщений из 5, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 208ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1684805]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 69ms
get topic data:
 14ms
get forum data:
 2ms
get page messages:
 77ms
get tp. blocked users:
 2ms
others: 14ms
total:  208ms
x
x
Закрыть


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