ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нумерация сета по дате
10 сообщений из 10, страница 1 из 1
Нумерация сета по дате
    #39615290
svanrus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
svanrus
Гость
Всем привет.

Появилась задача определения сета.

Сет может длится до 3 дней включительно после даты начала сета.

Ниже привожу пример:

дата N комментарий
2017-12-29 1 начинаем сет
2018-01-22 1 начинается новый сет т.к. после предыдущей даты прошло более 2 дней
2018-01-25 2 продолжается сет т.к. текущая дата входит в 3 последующих после начала сета
2018-01-31 1 начинаем новый
2018-02-02 2 продолжаем
2018-02-03 3 продолжаем

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

Спасибо!
...
Рейтинг: 0 / 0
15.03.2018, 12:13
| Ответить | Цитировать | Написать  
Нумерация сета по дате
    #39615301
так,
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
так,
Гость
svanrusВсем привет.

Появилась задача определения сета.

Сет может длится до 3 дней включительно после даты начала сета.

Ниже привожу пример:

дата N комментарий
2017-12-29 1 начинаем сет
2018-01-22 1 начинается новый сет т.к. после предыдущей даты прошло более 2 дней
2018-01-25 2 продолжается сет т.к. текущая дата входит в 3 последующих после начала сета
2018-01-31 1 начинаем новый
2018-02-02 2 продолжаем
2018-02-03 3 продолжаем

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

Спасибо!если первый день - 22, то 25 - это уже 4-й день.

покури-подумай, и сформулируй заново
...
Рейтинг: 0 / 0
15.03.2018, 12:31
| Ответить | Цитировать | Написать  
Нумерация сета по дате
    #39615302
Kopelly
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kopelly
Участник
svanrus,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
Declare @t table (d datetime)
Insert into @t values 
('2017-12-29'),
('2018-01-22'),
('2018-01-25'),
('2018-01-31'),
('2018-02-02'),
('2018-02-03');

With Starts as 
(Select *,case When lag(d) over (Order by d) between DATEADD(DD,-3,d) and d Then 0 else 1 end as IsStart From @t),
SetsID as 
(Select d,sum(IsStart) over (Order by d) as Set_ID
   From Starts)
Select *,ROW_NUMBER() over (Partition by Set_ID Order by d) as Set_Number From SetsID
...
Рейтинг: 0 / 0
15.03.2018, 12:32
| Ответить | Цитировать | Написать  
Нумерация сета по дате
    #39615340
так,
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
так,
Гость
Код: 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.
39.
40.
41.
declare @t table (dt datetime)
insert into @t values
('2017-12-29'),
('2018-01-12'),
('2018-01-23'),
('2018-01-24'),
('2018-01-25'),
('2018-01-26'),
('2018-01-27'),
('2018-01-28'),
('2018-01-31'),
('2018-02-02'),
('2018-02-03'),
('2018-02-04');

;with a as
	(select
		dt
		,rn	=row_number()over(order by dt)  
	from @t),
b as 
	(select 
		dt
		,rn
		,set_id			=1
		,num_in_set		=1
		,min_dt_in_set  =dt  
	from a
	where rn=1

	union all

	select
		a.dt
		,a.rn
		,set_id		=case when datediff(day,b.min_dt_in_set,a.dt)<=3 then b.set_id else b.set_id+1 end
		,num_in_set	=case when datediff(day,b.min_dt_in_set,a.dt)<=3 then b.num_in_set+1 else 1 end
		,min_dt_in_set	=case when datediff(day,b.min_dt_in_set,a.dt)<=3 then b.min_dt_in_set else a.dt end
	from a inner join b on a.rn=b.rn+1		
)
select dt, set_id, num_in_set from b


dtset_idnum_in_set2017-12-29 00:00:00.000112018-01-12 00:00:00.000212018-01-23 00:00:00.000312018-01-24 00:00:00.000322018-01-25 00:00:00.000332018-01-26 00:00:00.000342018-01-27 00:00:00.000412018-01-28 00:00:00.000422018-01-31 00:00:00.000512018-02-02 00:00:00.000522018-02-03 00:00:00.000532018-02-04 00:00:00.00061
...
Рейтинг: 0 / 0
15.03.2018, 13:12
| Ответить | Цитировать | Написать  
Нумерация сета по дате
    #39615420
svanrus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
svanrus
Гость
так,,

СПАСИБО, то что надо.

Kopelly, твой вариант по коду тоже должен быть рабочий, спасибо. Но моя нищая версия сервера не поддерживает lead lag & прочее. я уже начал мастерить костыли на твой вариант, но гость Так опередил мои успешные попытки.
...
Рейтинг: 0 / 0
15.03.2018, 14:44
| Ответить | Цитировать | Написать  
Нумерация сета по дате
    #39615572
svanrus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
svanrus
Гость
коллеги, добавил столбец, который негативно влияет на результаты выполнения.

Код: 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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
declare @t table (dt datetime, num varchar(20))
insert into @t values
('2017-12-29','aaa'),
('2018-01-12','aaa'),
('2018-01-23','aaa'),
('2018-01-24','aaa'),
('2018-01-25','aaa'),
('2018-01-26','aaa'),
('2018-01-27','aaa'),
('2018-01-28','aaa'),
('2018-01-31','aaa'),
('2018-02-02','aaa'),
('2018-02-03','aaa'),
('2018-02-04','aaa'),

('2017-03-29','bbb'),
('2018-04-12','bbb'),
('2018-05-23','bbb');





;

--select * from  regtest1

with a as
	(select
		dt
		,num
		,rn	=row_number()over(partition by num order by dt)  
	from @t),
b as 
	(select 
		dt
		,num
		,rn
		,set_id			=1
		,num_in_set		=1
		,min_dt_in_set  =dt  
	from a
	where rn=1

	union all

	select
		a.dt
		,a.num
		,a.rn
		,set_id		=case when datediff(day,b.min_dt_in_set,a.dt)<=3 then b.set_id else b.set_id+1 end
		,num_in_set	=case when datediff(day,b.min_dt_in_set,a.dt)<=3 then b.num_in_set+1 else 1 end
		,min_dt_in_set	=case when datediff(day,b.min_dt_in_set,a.dt)<=3 then b.min_dt_in_set else a.dt end
	from a inner join b on a.rn=b.rn+1		
)
select num,dt, set_id, num_in_set from b 
order by num


укажите, пожалуйста, на мою ошибку из-за которой на выходе 86 строк вместо 15...
Спасибо!
...
Рейтинг: 0 / 0
15.03.2018, 17:24
| Ответить | Цитировать | Написать  
Нумерация сета по дате
    #39615580
так,
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
так,
Гость
svanrusколлеги, добавил столбец, который негативно влияет на результаты выполнения.

Код: 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.
;with a as
	(select
		dt
		,num
		,rn	=row_number()over(partition by num order by dt)  
	from @t),
b as 
	(select 
		dt
		,num
		,rn
		,set_id			=1
		,num_in_set		=1
		,min_dt_in_set  =dt  
	from a
	where rn=1

	union all

	select
		a.dt
		,a.num
		,a.rn
		,set_id		=case when datediff(day,b.min_dt_in_set,a.dt)<=3 then b.set_id else b.set_id+1 end
		,num_in_set	=case when datediff(day,b.min_dt_in_set,a.dt)<=3 then b.num_in_set+1 else 1 end
		,min_dt_in_set	=case when datediff(day,b.min_dt_in_set,a.dt)<=3 then b.min_dt_in_set else a.dt end
	from a inner join b on a.rn=b.rn+1 and a.num=b.num			
)
select num,dt, set_id, num_in_set from b 
order by num


укажите, пожалуйста, на мою ошибку из-за которой на выходе 86 строк вместо 15...
Спасибо!
...
Рейтинг: 0 / 0
15.03.2018, 17:36
| Ответить | Цитировать | Написать  
Нумерация сета по дате
    #39615596
svanrus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
svanrus
Гость
так,,

спасибо ещё раз!


p.s.
На больших объёмах (более 150 тыс. строк) можете столкнуться с рекурсией, но осознанное OPTION (MAXRECURSION 0)
решает проблему.
...
Рейтинг: 0 / 0
15.03.2018, 17:55
| Ответить | Цитировать | Написать  
Нумерация сета по дате
    #39615708
Kopelly
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kopelly
Участник
svanrus,

Без рекурсий и lead&lag под расширенные данные:
Код: 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.
declare @t table (dt datetime, num varchar(20))
insert into @t values
('2017-12-29','aaa'),
('2018-01-12','aaa'),
('2018-01-23','aaa'),
('2018-01-24','aaa'),
('2018-01-25','aaa'),
('2018-01-26','aaa'),
('2018-01-27','aaa'),
('2018-01-28','aaa'),
('2018-01-31','aaa'),
('2018-02-02','aaa'),
('2018-02-03','aaa'),
('2018-02-04','aaa'),

('2017-03-29','bbb'),
('2018-04-12','bbb'),
('2018-05-23','bbb');

With Starts as 
(Select *,case When (Select top 1 b.dt From @t b Where a.dt>b.dt and a.num = b.num Order by b.dt desc) between DATEADD(DD,-3,dt) and dt Then 0 else 1 end as IsStart From @t a),
SetsID as 
(Select dt,num,sum(IsStart) over (Partition by num Order by dt) as Set_ID
   From Starts)
Select *,ROW_NUMBER() over (Partition by num,Set_ID Order by dt) as Set_Number From SetsID
...
Рейтинг: 0 / 0
16.03.2018, 04:30
| Ответить | Цитировать | Написать  
Нумерация сета по дате
    #39615864
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
svanrus, обычная такая задача на острова, и решается она тоже обычным таким способом...
 
Жми на плюс на свой страх и риск
Код: 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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
DECLARE @t TABLE ( [dt] DATE, [num] VARCHAR(20) )
INSERT INTO @t VALUES
  ( '2017-12-29', 'aaa' ),
  ( '2018-01-12', 'aaa' ),
  ( '2018-01-23', 'aaa' ),
  ( '2018-01-24', 'aaa' ),
  ( '2018-01-25', 'aaa' ),
  ( '2018-01-26', 'aaa' ),
  ( '2018-01-27', 'aaa' ),
  ( '2018-01-28', 'aaa' ),
  ( '2018-01-31', 'aaa' ),
  ( '2018-02-02', 'aaa' ),
  ( '2018-02-03', 'aaa' ),
  ( '2018-02-04', 'aaa' ),
                        
  ( '2017-03-29', 'bbb' ),
  ( '2018-04-12', 'bbb' ),
  ( '2018-05-23', 'bbb' )
;
WITH
t0 AS (
  SELECT
    [num],
    [dt],
    [set] = 1
  FROM
    @t
  UNION ALL
  SELECT
    [num],
    DATEADD( DAY, 3, [dt] ),
    [set] = 0
  FROM
    @t
),
t1 AS (
  SELECT
    [num],
    [dt],
    [set] = MAX( [set] )
  FROM
    t0
  GROUP BY
    [num],
    [dt]
),
t2 AS (
  SELECT
    [num],
    [dt],
    [set],
    [gr] = ROW_NUMBER() OVER ( PARTITION BY [num] ORDER BY [dt] )
         - ROW_NUMBER() OVER ( PARTITION BY [num], [set] ORDER BY [dt] )
  FROM
    t1
)
SELECT
  [num],
  [dt],
  [set],
  [rn] = ROW_NUMBER() OVER ( PARTITION BY [num], [gr] ORDER BY [dt] )
FROM
  t2
WHERE
  [set] = 1
ORDER BY
  [num],
  [dt]
;

...
Рейтинг: 0 / 0
16.03.2018, 11:48
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нумерация сета по дате
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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