Как найти самый длинный непрерывный ряд событий с помощью SQL

[VioletGiraffe]

919 дней посещения SO без прогулов, кто больше? :)

[vedenin1980]

Интересный запрос, ради интереса попробовал написать такой запрос сам, получился в лоб на чистом sql (первый запрос находит дату начала непрерывного интервала, второй конца, третий сводит дату начала и конца). Но ваш запрос будет работать, вероятно, быстрее.

WITH date AS (
    SELECT DISTINCT 
      CAST(CreationDate AS DATE) as date,
      CAST(CreationDate - 1 AS DATE) as min1, 
      CAST(CreationDate + 1 AS DATE) as plus1
    FROM Posts
    WHERE OwnerUserId = ##UserId##
  ),
firstdate AS (  
   SELECT * from date as d1 where NOT EXISTS(SELECT * FROM date d2 Where d1.min1 = d2.date)
),
lastdate AS (  
   SELECT * from date as d1 where NOT EXISTS(SELECT * FROM date d2 Where d1.plus1 = d2.date)
),
dates AS (
  SELECT firstdate.date as first, min(lastdate.date) as last FROM firstdate, lastdate WHERE firstdate.date <= lastdate.date
  GROUP BY firstdate.date 
)
SELECT first, last, DATEDIFF(dd,first, last)+1 as days FROM dates order by days desc

[encyclopedist]

Обратите внимание что это перевод.

[vedenin1980]

Ясно, не заметил. :) В любом случае практика лишней не будет.

[zzashpaupat]

В Oracle есть такая штука, как Pattern matching, с помощью неё тоже можно сделать. Но, это, конечно, будет работать только для Oracle, тогда как подход, описанный выше, работает везде, где есть CTE и оконные функции.

[dtestyk]

Затем на второй день. Затем на третий (возможно несколько раз, это не имеет значения). Не зашли на четвёртый? Начинаем считать заново.

т.е. по сути алгоритм работы debounce
Зашел пользователь — запустили таймер на 30 дней :)

[dtestyk]

подробности

зашел пользователь:
  если таймер "30 дней" не запущен
    запускаем таймер "30 дней"
  если запущен таймер "1 день без посещения"
    сбрасываем таймер "1 день без посещения"
  запускаем таймер "1 день без посещения"

сработал таймер "1 день без посещения":
  сбросываем таймер "30 дней"
  
сработал таймер "30 дней":
  alert('прошло ровно 30 дней с перерывами в посещении не больше 1 дня')

А вообще, задача актуальная: например, для подсчета прогулов.

[dtestyk]

Еще для решения этой задачи:

можно приспособить регулярные выражения

m — midnight event
v — visit event

«vmmmvvvm» — не подходит, есть несколько суток без посещения
«vvvmvmvvmvv» — пока что подходит

if(event_seq_str.match(/m(v+m){30,}/)) alert('30 days online')

Прошу прощения за монолог.

[biduin]

Можно сделать значительно проще.
Заджоините табличку саму с собой по on t1.mydate = dateadd(t2.mydate,-1) Ну и дальше по вычисляемому полю(t1.mydate-t2.mydate) будет всё понятно. К результируещей таблице безусловно нужно будет сделать запрос с агрегацией.

[dtestyk]

А если не использовать промежуточную таблицу, будет медленнее?
Оптимизируют ли sql-движки вложенные запросы,
или просто делают выборку из декартового произведения?

[biduin]

Многие оптимизируют.

[xtender]

еще легко такое делается с помощью разницы текущего значения и аналитических lead/lag этого поля и известного алгоритма «start_of_group». Причем это будет значительно легче для «гибких» диапазонов, т.е. например, если захотим считать перерывом 10 дней или 2 недели и тд…

[SemenovVV]

скрипт без group by, легко переделать в пропущенные интервалы T-sql

declare @Posts table ( data date  )
insert into @Posts (data) values('2010-11-26')
insert into @Posts (data) values('2010-11-27')
insert into @Posts (data) values('2010-11-29')
insert into @Posts (data) values('2010-11-30')
insert into @Posts (data) values('2010-12-01')
insert into @Posts (data) values('2010-12-02')
insert into @Posts (data) values('2010-12-03')
insert into @Posts (data) values('2010-12-05')
insert into @Posts (data) values('2010-12-06')
insert into @Posts (data) values('2010-12-07')
insert into @Posts (data) values('2010-12-08')
insert into @Posts (data) values('2010-12-09')
insert into @Posts (data) values('2010-12-13')
insert into @Posts (data) values('2010-12-14')
insert into @Posts (data) values('2010-12-15')  
insert into @Posts (data) values('2010-12-16')  
insert into @Posts (data) values('2010-12-19')  

;
with 
 grup0 as (  --- считаем пред и след дни
   select data  
    , dateadd( d, -1,   data ) as d_prev 
	, dateadd( d, +1,   data ) as d_next 
	 from @Posts  
  )
 ,  grup1 as
  ( select p.data
    ,  n.data   as next_exist 
    ,  s.data   as prev_exist
    from @Posts p 
	left join grup0 n   
	   on  n.d_prev  = p.data
	left join grup0 s
	   on s.d_next = p.data
   )
  ,  start as ( 
   select  data  , ROW_NUMBER()OVER(ORDER BY data) as npp 
      from grup1 p 
	  where prev_exist is null  )
  ,  finish as ( 
   select  data, ROW_NUMBER()OVER(ORDER BY data) as npp   
      from grup1 p where next_exist is null )
select   s.data as start 
  , f.data as finish   
  , datediff(d, s.data,f.data) +1  as kol_day 
  from start s, finish f
  where s.npp   = f.npp -- +1 получим интервалы пропуски 
order by start  --- kol_day desc

[m36]

Так еще можно:

declare @Posts table ( CreationDate date  )
insert into @Posts (CreationDate) values('2010-11-26')
insert into @Posts (CreationDate) values('2010-11-27')
insert into @Posts (CreationDate) values('2010-11-29')
insert into @Posts (CreationDate) values('2010-11-30')
insert into @Posts (CreationDate) values('2010-12-01')
insert into @Posts (CreationDate) values('2010-12-02')
insert into @Posts (CreationDate) values('2010-12-03')
insert into @Posts (CreationDate) values('2010-12-05')
insert into @Posts (CreationDate) values('2010-12-06')
insert into @Posts (CreationDate) values('2010-12-07')
insert into @Posts (CreationDate) values('2010-12-08')
insert into @Posts (CreationDate) values('2010-12-09')
insert into @Posts (CreationDate) values('2010-12-13')
insert into @Posts (CreationDate) values('2010-12-14')
insert into @Posts (CreationDate) values('2010-12-15')  
insert into @Posts (CreationDate) values('2010-12-16')  
insert into @Posts (CreationDate) values('2010-12-19')  

insert into @Posts (CreationDate) values('1900-01-01')  
insert into @Posts (CreationDate) values('3000-01-01')  


;with dates AS (
	select cast(p1.CreationDate as date) FinishDate, cast(MIN(p2.CreationDate) as date) StartDate 
	from @Posts p1, @Posts p2
	where p1.CreationDate < p2.CreationDate
	group by cast(p1.CreationDate as date)
	having MIN(datediff(day, cast(p1.CreationDate as date), cast(p2.CreationDate as date))) > 1
)
select d1.StartDate, MIN(d2.FinishDate) FinishDate from dates d1, dates d2
where d1.StartDate <= d2.FinishDate
group by d1.StartDate;

Стремился только к краткости, как его SQL-SERVER оптимизирует, не проверял. Суть: добавляются две граничные даты сверху и снизу. И ищутся дырки.

[sergix]

Из цте просто вью не сделать, думаю пострадает ли ваша идея в производительности если сделать вот так?

select
COUNT(*) AS consecutiveDates,
  MIN(date) AS minDate,
  MAX(date) AS maxDate
from
(
SELECT DISTINCT 
      CAST(CreationDate AS DATE) date,
      dateadd(day, 
        -DENSE_RANK() OVER (ORDER BY CAST(CreationDate AS DATE)), 
        CAST(CreationDate AS DATE)) AS grp
    FROM Posts
    WHERE OwnerUserId = ##UserId##
) x
group by grp