1. 呈现连续上涨超过 5 天的股票及上涨天数
解释:
code - 股票代码
ddate - 日期
price - 股票价格
with tmp as(
select
code,
ddate,
price,
lag(price, 1, price) over(partition by code order by ddate) as lag_price, --求股票上一天的价格
case when price >= lag(price, 1, price) over(partition by code order by ddate) then 0 else 1 end as changeSign --价格上涨为0, 价格下降为1
from stock_record
)
,tmp2 as(
select
code,
ddate,
price,
lag_price,
changeSign,
sum(changeSign) over(partition by code order by ddate) as unRiseDays --求价格下降的天数
from tmp
)
,tmp3 as (
--
select
code,
unRiseDays,
count(1) as risenum --上涨天数:实际需要 -1,可理解为上涨记录数
from tmp2
group by code,unRiseDays
)
select
code,
max(risenum)-1 as maxRiseDays --最大上涨天数
from tmp3
group by code
having max(risenum) > 5
;