波峰波谷sql

最新推荐文章于 2024-09-15 14:16:55 发布

chimchim66

最新推荐文章于 2024-09-15 14:16:55 发布

阅读量511

点赞数 1

分类专栏： sql

本文链接：https://blog.csdn.net/chimchim66/article/details/125386724

版权

sql java 数据库

sql 专栏收录该内容

31 篇文章

订阅专栏

本文介绍了如何在数据库中定义波峰和波谷，并提供了两种计算方法。波峰是指当天价格高于前后一天的价格，而波谷则是价格低于前后一天。通过SQL查询，利用窗口函数row_number(), lag()和lead()来识别这些模式，从而对时间序列数据进行深入分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

波峰波谷定义

准备数据

计算方法

波峰波谷定义

波峰：当天的价格大于前一天和后一天

波谷：当天的价格小于前一天和后一天

准备数据

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