Комментарии 16
919 дней посещения SO без прогулов, кто больше? :)
0
Интересный запрос, ради интереса попробовал написать такой запрос сам, получился в лоб на чистом 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
+2
В Oracle есть такая штука, как Pattern matching, с помощью неё тоже можно сделать. Но, это, конечно, будет работать только для Oracle, тогда как подход, описанный выше, работает везде, где есть CTE и оконные функции.
+3
подробности
А вообще, задача актуальная: например, для подсчета прогулов.зашел пользователь: если таймер "30 дней" не запущен запускаем таймер "30 дней" если запущен таймер "1 день без посещения" сбрасываем таймер "1 день без посещения" запускаем таймер "1 день без посещения" сработал таймер "1 день без посещения": сбросываем таймер "30 дней" сработал таймер "30 дней": alert('прошло ровно 30 дней с перерывами в посещении не больше 1 дня')
0
Можно сделать значительно проще.
Заджоините табличку саму с собой по on t1.mydate = dateadd(t2.mydate,-1) Ну и дальше по вычисляемому полю(t1.mydate-t2.mydate) будет всё понятно. К результируещей таблице безусловно нужно будет сделать запрос с агрегацией.
Заджоините табличку саму с собой по on t1.mydate = dateadd(t2.mydate,-1) Ну и дальше по вычисляемому полю(t1.mydate-t2.mydate) будет всё понятно. К результируещей таблице безусловно нужно будет сделать запрос с агрегацией.
0
еще легко такое делается с помощью разницы текущего значения и аналитических lead/lag этого поля и известного алгоритма «start_of_group». Причем это будет значительно легче для «гибких» диапазонов, т.е. например, если захотим считать перерывом 10 дней или 2 недели и тд…
+3
скрипт без 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
0
Так еще можно:
Стремился только к краткости, как его SQL-SERVER оптимизирует, не проверял. Суть: добавляются две граничные даты сверху и снизу. И ищутся дырки.
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 оптимизирует, не проверял. Суть: добавляются две граничные даты сверху и снизу. И ищутся дырки.
0
Из цте просто вью не сделать, думаю пострадает ли ваша идея в производительности если сделать вот так?
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
0
Зарегистрируйтесь на Хабре , чтобы оставить комментарий
Как найти самый длинный непрерывный ряд событий с помощью SQL