CODEWITHSUNDEEP
sqlsql-servert-sql
Piss Bender
Piss Bender

Reputation: 11

SQL Conditional Increment And Reset

I need to get the number of consecutive increases in price, as well as the percentage increase since the start of the series, with the values being reset whenever a decrease is encountered.

I have this table:

Date    Hour    Price   
2020-02-05  0   188.713 
2020-02-05  1   189.532 
2020-02-05  2   188.184 
2020-02-05  3   188.432 
2020-02-05  4   189.06  
2020-02-05  5   190.264 
2020-02-05  6   191.451 
2020-02-05  7   190.762 
2020-02-05  8   190.203

I need to get it into the following format:

Date    Hour    Price   ConsecutiveIncreases    PercentageIncrease
2020-02-05  0   188.713         0                       0
2020-02-05  1   189.532         1                    0.432117
2020-02-05  2   188.184         0                       0
2020-02-05  3   188.432         1                    0.1316124
2020-02-05  4   189.06          2                    0.4633449
2020-02-05  5   190.264         3                    1.0932178
2020-02-05  6   191.451         4                    1.7064418
2020-02-05  7   190.762         0                       0
2020-02-05  8   190.203         0                       0

Upvotes: 0

Views: 230

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Use lag() to determine where the decreases occur. Then a cumulative for the grouping. And finally arrange the numbers you want:

select t.*,
       (case when prev_price < price
             then row_number() over (partition by grp order by date, hour) - 1
             else 0
        end) as cumulative_increases,
       (case when prev_price < price
             then (max(price) over (partition by grp order by date, hour) * 1.0 /
                   min(price) over (partition by grp order by date, hour)
                  ) - 1
             else 0
        end) as cumulative_increases,
from (select t.*,
             sum(case when prev_price <= price then 0 else 1 end) over (order by date, hour) as grp
      from (select t.*,
                   lag(price) over (order by date, hour) as prev_price
            from t
           ) t
     ) t;

Here is a db<>fiddle.

Upvotes: 1

Related Questions