波峰波谷sql

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