我们常见的股票会出现上下浮动,在浮动的过程中。会出现波峰和波谷。现在要求的是数据中的波峰和波谷。
数据样式:
(股票)id ds price
1 2023-01-01 10000
1 2023-01-02 10001
1 2023-01-03 10002
1 2023-01-04 10003
1 2023-01-05 10004
2 2023-01-01 10005
3 2023-01-01 10002
4 2023-01-01 10003
2 2023-01-02 10006
2 2023-01-03 10007
首先对数据开窗
select
id,
ds,
price,
lag(price,1) over(partition by id order by ds) as lag_price,
lead(price,1) over(partition by id order by ds) as lead_price
from
tableA
其中,lag函数,求改行所在的前n行的值,这里求得是price字段前一行的price。同理,lead函数,求该行所在的后一行。
求波峰波谷
select
id,
ds,
case
when price>lag_price and price>lead_price then '波峰'
when price<lag_price and price<lead_price then '波谷'
else '其他'
end as price_type
from
(
select
id,
ds,
price,
lag(price,1) over(partition by id order by ds) as lag_price,
lead(price,1) over(partition by id order by ds) as lead_price
from
tableA
)t
波峰的值比两边所在的值要大,波谷所在的值比两边都要小