create table test_d as
SELECT price, to_date(startdate,'YYYY-MM-DD') startdate
FROM (
SELECT 632 as price,'2019-05-01' as startdate FROM DUAL UNION ALL
SELECT 649,'2019-05-02' FROM DUAL UNION ALL
SELECT 632,'2019-05-03' FROM DUAL UNION ALL
SELECT 607,'2019-05-04' FROM DUAL UNION ALL
SELECT 598,'2019-05-05' FROM DUAL UNION ALL
SELECT 624,'2019-05-06' FROM DUAL UNION ALL
SELECT 641,'2019-05-07' FROM DUAL UNION ALL
SELECT 598,'2019-05-08' FROM DUAL UNION ALL
SELECT 556,'2019-05-09' FROM DUAL UNION ALL
SELECT 480,'2019-05-10' FROM DUAL UNION ALL
SELECT 510,'2019-05-11' FROM DUAL UNION ALL
SELECT 541,'2019-05-12' FROM DUAL UNION ALL
SELECT 634,'2019-05-13' FROM DUAL UNION ALL
SELECT 634,'2019-05-14' FROM DUAL );
There is a table. i want to find longest consecutive date sequence starting from date with lowest price. so in this case
1 480 2019-05-10 1 2019-05-09 0
2 510 2019-05-11 2 2019-05-10 0
3 541 2019-05-12 3 2019-05-11 0
What i've already done:
SELECT t1.*,
case
when startdate = next_d + 1 then 1
else 0
end seq
FROM (SELECT t.*,
NVL(LAG(startdate) over(order by rnk asc),startdate-1) next_d
FROM (select price,
startdate,
DENSE_RANK() OVER(ORDER BY price ASC) AS RNK
from test_d) t) t1
which gives me:
1 480 2019-05-11 1 2019-05-10 1
2 510 2019-05-10 2 2019-05-11 0
3 541 2019-05-12 3 2019-05-10 0
4 556 2019-05-09 4 2019-05-12 0
5 598 2019-05-05 5 2019-05-09 0
6 598 2019-05-08 6 2019-05-05 0
7 607 2019-05-04 7 2019-05-08 0
8 624 2019-05-06 8 2019-05-04 0
9 632 2019-05-01 9 2019-05-06 0
10 632 2019-05-03 10 2019-05-01 0
11 634 2019-05-13 11 2019-05-03 0
12 634 2019-05-14 12 2019-05-13 1
13 641 2019-05-07 13 2019-05-14 0
14 649 2019-05-02 14 2019-05-07 0
How can i group sequence in SEQ column? Like the first row gets number 1, rows 2-11 gets number 2, 12 - 3, 13-14 - 0 etc.? Or mayby anyone has idea for another approach? Thanks