Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Нумерация сета по дате  [new]
svanrus
Member

Откуда:
Сообщений: 53
Всем привет.

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

Сет может длится до 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 продолжаем

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

Спасибо!
15 мар 18, 12:13    [21258106]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация сета по дате  [new]
так,
Guest
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-й день.

покури-подумай, и сформулируй заново
15 мар 18, 12:31    [21258182]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация сета по дате  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
svanrus,

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
15 мар 18, 12:32    [21258185]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация сета по дате  [new]
так,
Guest
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_set
2017-12-29 00:00:00.00011
2018-01-12 00:00:00.00021
2018-01-23 00:00:00.00031
2018-01-24 00:00:00.00032
2018-01-25 00:00:00.00033
2018-01-26 00:00:00.00034
2018-01-27 00:00:00.00041
2018-01-28 00:00:00.00042
2018-01-31 00:00:00.00051
2018-02-02 00:00:00.00052
2018-02-03 00:00:00.00053
2018-02-04 00:00:00.00061
15 мар 18, 13:12    [21258395]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация сета по дате  [new]
svanrus
Member

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

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

Kopelly, твой вариант по коду тоже должен быть рабочий, спасибо. Но моя нищая версия сервера не поддерживает lead lag & прочее. я уже начал мастерить костыли на твой вариант, но гость Так опередил мои успешные попытки.
15 мар 18, 14:44    [21258901]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация сета по дате  [new]
svanrus
Member

Откуда:
Сообщений: 53
коллеги, добавил столбец, который негативно влияет на результаты выполнения.

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...
Спасибо!

Сообщение было отредактировано: 15 мар 18, 17:31
15 мар 18, 17:24    [21259631]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация сета по дате  [new]
так,
Guest
svanrus
коллеги, добавил столбец, который негативно влияет на результаты выполнения.

;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...
Спасибо!
15 мар 18, 17:36    [21259676]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация сета по дате  [new]
svanrus
Member

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

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


p.s.
На больших объёмах (более 150 тыс. строк) можете столкнуться с рекурсией, но осознанное OPTION (MAXRECURSION 0)
решает проблему.
15 мар 18, 17:55    [21259728]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация сета по дате  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
svanrus,

Без рекурсий и lead&lag под расширенные данные:
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
16 мар 18, 04:30    [21260235]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация сета по дате  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
svanrus, обычная такая задача на острова, и решается она тоже обычным таким способом...
+ Жми на плюс на свой страх и риск
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]
;
16 мар 18, 11:48    [21260959]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить