目录
波峰:当天的价格大于前一天和后一天
波谷:当天的价格小于前一天和后一天
CREATE TABLE syc_ads.t1
(
UID string,
DT string,
Price double
);
Insert into syc_ads.t1
VALUES
('00001','2014-06-01',-20),
('00001','2014-06-02',50),
('00001','2014-06-04',15),
('00001','2014-06-05',13),
('00001','2014-06-06',100),
('00002','2014-06-02',33),
('00002','2014-06-05',66),
('00002','2014-06-06',101),
('00002','2014-06-07',26),
('00002','2014-06-08',30),
('00002','2014-06-10',43)
方法1:
with t as
(select *,row_number() over(partition by UID order by DT) as rn
from syc_ads.t1)
select
a.UID
,a.DT
,a.Price
,case when (b.rn is null and c.Price>a.Price)
or (c.rn is null and b.Price>a.Price)
or (b.Price>a.Price and c.Price>a.Price)
then '波谷'
when (b.rn is null and c.Price<a.Price)
or (c.rn is null and b.Price<a.Price)
or (b.Price<a.Price and c.Price<a.Price)
then '波峰'
else ''
end as Wave
from t a
left join t b on a.UID=b.UID and a.rn=b.rn+1
left join t c on a.UID=c.UID and a.rn=c.rn-1
order by a.UID,a.rn
方法2:
select
UID,DT,Price
,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
UID
,DT
,Price
,lag(Price,1) over(partition by UID order by DT) as lag_price
,lead(Price,1) over(partition by UID order by DT) as lead_price
from syc_ads.t1
) t