Hive习题汇总（持续汇总中）_hive期末机试

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
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35