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

Откуда: Москва
Сообщений: 190
Доброго всем времени суток!

Забавная возникла задачка - сгруппировать записи по "периодам активности" клиента.
Период активности - это когда разница между датами в двух соседних записях не более некоторой величины.
Для каждого периода нужно определить начало, конец, количество записей, некоторые агрегаты по разным полям

Например, если в таблице
+

create table #t (d date, s int)
insert into #t
values ('20180301', 100)
	,('20180322', 100)
	,('20180303', 100)
	,('20180402', 100)
	,('20180304', 100)
	,('20180327', 100)
	,('20180315', 100)
	,('20180410', 100)
	,('20180320', 100)
go

select *
from #t
order by d
go


считать под некоторой величиной неделю (7 дней), то ожидается вот такой результат
(для простоты здесь один агрегат - сумма)
dStartdEndiCountiSum
2018-03-012018-03-043300
2018-03-152018-04-026600
2018-04-102018-04-101100

количество дней между соседними датами подсчитать просто
+
select t.d, t.s
	,lag(t.d, 1, t.d) over (order by t.d) prev
	,datediff(day, lag(t.d, 1, t.d) over (order by t.d), t.d) dif
from #t t
go

dsprevdif
2018-03-011002018-03-010
2018-03-031002018-03-012
2018-03-041002018-03-031
2018-03-151002018-03-0411
2018-03-201002018-03-155
2018-03-221002018-03-202
2018-03-271002018-03-225
2018-04-021002018-03-276
2018-04-101002018-04-028

Но вот как дальше сгруппировать, что-то не соображу...
Почему-то курсор городить неохота, а другие варианты пока на ум не идут...



Я всегда лгу.
8 апр 18, 00:48    [21321172]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по "периодам активности"  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
на вскидку:
declare @t table (d date, s int)
insert @t
values
  ('20180301', 100)
  ,('20180322', 100)
  ,('20180303', 100)
  ,('20180402', 100)
  ,('20180304', 100)
  ,('20180327', 100)
  ,('20180315', 100)
  ,('20180410', 100)
  ,('20180320', 100)
  ,('20180411', 100)
  ,('20180413', 100)
  ,('20180421', 100)
  ,('20180425', 100)
  ,('20180429', 100)

declare @N tinyint = 7 -- << ваша величина
;
with
  t1 as
  (
  select
    d, s,
    sign(datediff(dd, isnull(lag(d)over(order by d), d), d) / (@N + 1)) as dd
  from
    @t
  ),
  t2 as
  (
  select
    d, s,
    sum(dd)over(order by d rows unbounded preceding) as gr
    from
    t1
  )

select
   d, s, gr
from
  t2
order by
  d
8 апр 18, 02:36    [21321194]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по "периодам активности"  [new]
Contrast
Member

Откуда: Москва
Сообщений: 190
Дедушка!

Спасибо!
Получается
8 апр 18, 10:00    [21321323]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по "периодам активности"  [new]
uaggster
Member

Откуда:
Сообщений: 826
Гм, Дедушка, блин, я думал, что это задача на поиск островов, и решается рекурсивным запросом.
Спасибо, попытаюсь вкурить!
9 апр 18, 08:29    [21322289]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить