HIVE实战处理（二十二）股票连续上涨最长的天数

场景一：求股票最长连续上涨天数

with t(code  ,  dt  ,  price ) as (
select 110330  , date'2009-01-01'  ,   1.87 from dual union all
select 110330  , date'2009-01-02'  ,   1.98 from dual union all
select 110330  , date'2009-01-05'  ,   2.03 from dual union all
select 110330  , date'2009-01-06'  ,   1.85 from dual union all
select 110330  , date'2009-01-07'  ,   2.0 from dual union all
select 110330  , date'2009-01-08'  ,   1.98 from dual union all
select 110330  , date'2009-01-09'  ,   1.95 from dual union all
select 110330  , date'2009-01-12'  ,   1.8 from dual union all
select 110330  , date'2009-01-13'  ,   1.87 from dual union all
select 110330  , date'2009-01-14'  ,   1.95 from dual union all
select 110330  , date'2009-01-15'  ,   2.09 from dual union all
select 110330  , date'2009-01-19'  ,   2.11 from dual union all
select 110330  , date'2009-01-16'  ,   2.22 from dual union all
select 110330  , date'2009-01-21'  ,   1.97 from dual union all
select 110330  , date'2009-01-20'  ,   2.05 from dual union all
select 110330  , date'2009-01-22'  ,   1.93 from dual union all
select 110330  , date'2009-01-23'  ,   1.96 from dual union all
select 110330  , date'2009-01-26'  ,   2.1 from dual union all
select 110330  , date'2009-01-27'  ,   2.05 from dual union all
select 110330  , date'2009-01-28'  ,   2.12 from dual union all
select 110330  , date'2009-01-30'  ,   1.98 from dual union all
select 110330  , date'2009-01-29'  ,   2.12 from dual
)

select 
t3.*,
sum(1) over(partition by code,ss order by dt)-1  连涨天数  
from (
select t2.*,sum(d) over(partition by code order by dt) ss
 from
 (select t.*,
case when nvl(lag(price) over (partition by code order by dt),0) >price then 1 else 0 end d
 from t
) t2 
)t3 
--最长上涨时间的话对上面结果求max

二、场景二：连续涨幅超过5%天数最多的股票ID和连续天数。

现有每个交易日每只股票的开盘价及收盘价，在表stock_price表中，表中字段如下：

另有交易日期记录表stock_date表，字段如下：

现要得到，连续涨幅超过5%天数最多的股票ID和连续天数。（连续天数计算连续的交易工作日）

解题思路

1.计算每一只股票每天的涨跌幅，筛选出涨幅超过5%的数据
2.通过stock_date表中的ID排除周末为非交易日带来的干扰（ID为连续自增，如果直接按照交易日期判断会因为周末为非交易日，导致无法计算满足题目要求的最大连续工作日）
3.按每一只股票计算连续涨幅超过5%的天数
4.找出最长的时间和对应的股票ID

 

--1.计算每一只股票每天的涨跌幅，筛选出涨幅超过5%的数据 
with t1 as (
select stock_id,date
from stock_price
where (s_price-k_price)/k_price > 0.05);

--2.通过stock_date表中的ID排除周末为非交易日带来的干扰 
with t2 as (
select stock_id,id
from t1
left join 
stock_date tt2
on t1.date = tt2.date);
 
--3.按股票ID对日期进行排序，若是连续天数，id-ranks的值应该是相同的
with t3 as (
select stock_id,id,
    row_number() over(partition by stock_id order by id) as ranks
from t2    );
 
--4.计算每只股票在所有交易日内，有出现连续涨幅5%以上的天数
with t4 as (
select stock_id,judge,count(1) as count_day--连续天数计算
from
(
    select stock_id,id-ranks as judge
    from t3
)
group by stock_id,judge  );


--5.在4的基础上取最大连续天数即可
select stock_id,max(count_day) as max_day
from t4;