首页 > 解决方案 > SQL 条件增量和重置

问题描述

我需要获取价格连续上涨的次数,以及自系列开始以来的百分比上涨,每当遇到下跌时,这些值就会被重置。

我有这张桌子:

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

我需要把它变成以下格式:

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

标签: sqlsql-servertsql

解决方案


用于lag()确定下降发生的位置。然后对分组进行累积。最后排列你想要的数字:

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;

是一个 db<>fiddle。


推荐阅读