Sql Issue - Calculate Max Days Sequence
Solution 1:
Another approach, the shortest, do a self-join:
with grouped_result as
(
select
sr.d,
sum((fr.d isnull)::int) over(orderby sr.d) as group_number
from tbl sr
leftjoin tbl fr on sr.d = fr.d +interval'1 day'
)
select d, group_number, count(d) over m as consecutive_days
from grouped_result
window m as (partitionby group_number)
Output:
d|group_number|consecutive_days---------------------+--------------+------------------2012-04-28 08:00:00|1|32012-04-29 08:00:00|1|32012-04-30 08:00:00|1|32012-05-03 08:00:00|2|22012-05-04 08:00:00|2|2(5rows)Live test: http://www.sqlfiddle.com/#!1/93789/1
sr = second row, fr = first row ( or perhaps previous row? ツ ). Basically we are doing a back tracking, it's a simulated lag on database that doesn't support LAG (Postgres supports LAG, but the solution is very long, as windowing doesn't support nested windowing). So in this query, we uses a hybrid approach, simulate LAG via join, then use SUM windowing against it, this produces group number
UPDATE
Forgot to put the final query, the query above illustrate the underpinnings of group numbering, need to morph that into this:
with grouped_result as
(select
sr.d,
sum((fr.d isnull)::int) over(order by sr.d) as group_number
from tbl sr
left join tbl fr on sr.d = fr.d + interval '1 day'
)
selectmin(d) as starting_date, max(d) as end_date, count(d) as consecutive_days
from grouped_result
groupby group_number
-- order by consecutive_days desc limit 1
STARTING_DATE END_DATE CONSECUTIVE_DAYS
April, 28 2012 08:00:00-0700 April, 30 2012 08:00:00-0700 3
May, 03 2012 08:00:00-0700 May, 04 2012 08:00:00-0700 2
UPDATE
I know why my other solution that uses window function became long, it became long on my attempt to illustrate the logic of group numbering and counting over the group. If I'd cut to the chase like in my MySql approach, that windowing function could be shorter. Having said that, here's my old windowing function approach, albeit better now:
with headers as
(
select
d,lag(d) over m isnullor d -lag(d) over m <>interval'1 day'as header
from tbl
window m as (orderby d)
)
,sequence_group as
(
select d, sum(header::int) over (orderby d) as group_number
from headers
)
selectmin(d) as starting_date,max(d) as ending_date,count(d) as consecutive_days
from sequence_group
groupby group_number
-- order by consecutive_days desc limit 1Live test: http://www.sqlfiddle.com/#!1/93789/21
Solution 2:
In MySQL you could do this:
SET@nextDate=CURRENT_DATE;
SET@RowNum=1;
SELECTMAX(RowNumber) AS ConecutiveVisits
FROM ( SELECT@RowNum := IF(@NextDate= Created_At, @RowNum+1, 1) AS RowNumber,
Created_At,
@NextDate := DATE_ADD(Created_At, INTERVAL1DAY) AS NextDate
FROM Visits
ORDERBY Created_At
) Visits
Example here:
http://sqlfiddle.com/#!2/6e035/8
However I am not 100% certain this is the best way to do it.
In Postgresql:
;WITHRECURSIVE VisitsCTE AS
( SELECT Created_At, 1AS ConsecutiveDays
FROM Visits
UNIONALLSELECT v.Created_At, ConsecutiveDays +1FROM Visits v
INNERJOIN VisitsCTE cte
ON1+ cte.Created_At = v.Created_At
)
SELECTMAX(ConsecutiveDays) AS ConsecutiveDays
FROM VisitsCTE
Example here:
Solution 3:
I know Postgresql has something similar to common table expressions as available in MSSQL. I'm not that familiar with Postgresql, but the code below works for MSSQL and does what you want.
createtable #tempdates (
mydate date
)
insertinto #tempdates(mydate) values('2012-04-28')
insertinto #tempdates(mydate) values('2012-04-29')
insertinto #tempdates(mydate) values('2012-04-30')
insertinto #tempdates(mydate) values('2012-05-03')
insertinto #tempdates(mydate) values('2012-05-04');
with maxdays (s, e, c)
as
(
select mydate, mydate, 1from #tempdates
unionallselect m.s, mydate, m.c +1from #tempdates t
innerjoin maxdays m on DATEADD(day, -1, t.mydate)=m.e
)
selectMIN(o.s),o.e,max(o.c)
from (
select m1.s,max(m1.e) e,max(m1.c) c
from maxdays m1
groupby m1.s
) o
groupby o.e
droptable #tempdates
And here's the SQL fiddle: http://sqlfiddle.com/#!3/42b38/2
Solution 4:
All are very good answers, but I think I should contribute by showing another approach utilizing an analytical capability specific to Vertica (after all it is part of what you paid for). And I promise the final query is short.
First, query using conditional_true_event(). From Vertica's documentation:
Assigns an event window number to each row, starting from 0, and increments the number by 1 when the result of the boolean argument expression evaluates true.
The example query looks like this:
select uid, created_at,
conditional_true_event( created_at -lag(created_at) >'1 day' )
over (partitionby uid orderby created_at) as seq_id
from visits;
And output:
uidcreated_atseq_id----------------------------1232012-04-28 00:00:00 01232012-04-29 00:00:00 01232012-04-30 00:00:00 01232012-05-03 00:00:00 11232012-05-04 00:00:00 11232012-06-04 00:00:00 21232012-06-04 00:00:00 2Now the final query becomes easy:
select uid, seq_id, count(1) num_days, min(created_at) s, max(created_at) f
from
(
select uid, created_at,
conditional_true_event( created_at -lag(created_at) >'1 day' )
over (partitionby uid orderby created_at) as seq_id
from visits
) as seq
groupby uid, seq_id;
Final Output:
uidseq_idnum_dayssf-------------------------------------------------------123032012-04-28 00:00:00 2012-04-30 00:00:00123122012-05-03 00:00:00 2012-05-04 00:00:00123222012-06-04 00:00:00 2012-06-04 00:00:00One final note:
num_days is actually number of rows of the inner query. If there are two '2012-04-28' visits in the original table (i.e. duplicates), you might want to work around that.
Solution 5:
The following should be Oracle friendly, and not require recursive logic.
;WITH
visit_dates (
visit_id,
date_id,
group_id
)
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY TRUNC(created_at)),
TRUNC(SYSDATE) - TRUNC(created_at),
TRUNC(SYSDATE) - TRUNC(created_at) - ROW_NUMBER() OVER (ORDER BY TRUNC(created_at))
FROM
visits
GROUP BY
TRUNC(created_at)
)
,
group_duration (
group_id,
duration
)
AS
(
SELECT
group_id,
MAX(date_id) - MIN(date_id) + 1 AS duration
FROM
visit_dates
GROUP BY
group_id
)
SELECT
MAX(duration) AS max_duration
FROM
group_duration
Post a Comment for "Sql Issue - Calculate Max Days Sequence"