Window Sequence

[NorbertKrupa]

Came across this question. The closest I could get is with the following:

Code: Select all

WITH rank1 AS 
   (SELECT Category, 
           Date, 
           LEAD(Date, 1) 
             OVER ( 
               PARTITION BY Category 
               ORDER BY Date) AS NextDate
    FROM   public.test),
rank2 AS
   (SELECT *, 
           CASE WHEN DATEDIFF(day, Date, NextDate) > 2 THEN 1 ELSE 0 END AS Diff
    FROM   rank1)

SELECT *,
       RANK() 
         OVER ( 
           PARTITION BY Category, Diff
           ORDER BY Date) AS Rank 	 
FROM   rank2
ORDER  BY Category,
          Date;

Which produces:

Code: Select all

 Category |    Date    |  NextDate  | Diff | Rank
----------+------------+------------+------+------
     1211 | 2014-07-17 | 2014-08-06 |    1 |    1
     1211 | 2014-08-06 | 2014-08-08 |    0 |    1
     1211 | 2014-08-08 |            |    0 |    2
     1234 | 2014-07-15 | 2014-07-17 |    0 |    1
     1234 | 2014-07-17 | 2014-07-29 |    1 |    1
     1234 | 2014-07-29 | 2014-07-31 |    0 |    2
     1234 | 2014-07-31 | 2014-08-02 |    0 |    3
     1234 | 2014-08-02 | 2014-08-04 |    0 |    4
     1234 | 2014-08-04 | 2014-08-06 |    0 |    5
     1234 | 2014-08-06 |            |    0 |    6

Any suggestions?

[id10t]

Hi!

[DELETED]

[scutter]

Hey Norbert,

As sKwa pointed out, this is a great use for conditional_true_event — I combined it with rank().

The conditional_true_event() allows you to break out the groups of values where the dates are more than 2 days apart, and then you just need an incremental counter for those using rank().

This works:

Code: Select all

select cat, dt, rank() over (partition by cat, seq order by dt) from (
	select cat, dt, 
	conditional_true_event(DATEDIFF(day, prev_dt, dt) > 2) over (partition by cat order by dt) as seq 
	from (
		select *,
		lag(dt, 1) over (partition by cat order by dt) prev_dt
		from test
	) q
) q2
order by cat, dt;

Code: Select all

1211	2014-07-17	1
1211	2014-08-06	1
1211	2014-08-08	2
1234	2014-07-15	1
1234	2014-07-17	2
1234	2014-07-29	1
1234	2014-07-31	2
1234	2014-08-02	3
1234	2014-08-04	4
1234	2014-08-06	5

[NorbertKrupa]

Thank you both for the help (learned a new function today).