求股票波峰波谷原创

# 求股票波峰波谷

# 1. 题目

有一个表dayly_sql.stock_price记录某只股票的价格，求一段时间内该股票价格的波峰及波谷，表结构及示例数据如下：

stock_id	price_date	price
001	2024-01-01	100
001	2024-01-02	200
001	2024-01-03	300
001	2024-01-04	400
001	2024-01-05	200
001	2024-01-06	100
001	2024-01-07	50
001	2024-01-08	200
001	2024-01-09	300
001	2024-01-10	500
001	2024-01-11	400
001	2024-01-12	450
001	2024-01-13	400
001	2024-01-14	600

建表语句：

create  table dayly_sql.stock_price
(
    stock_id string,
    price_date date,
    price decimal(22,2)
);

1
2
3
4
5
6

插数语句：

INSERT INTO dayly_sql.stock_price VALUES
 ('001',cast('2024-01-01' as date),100)
,('001',cast('2024-01-02' as date),200)
,('001',cast('2024-01-03' as date),300)
,('001',cast('2024-01-04' as date),400)
,('001',cast('2024-01-05' as date),200)
,('001',cast('2024-01-06' as date),100)
,('001',cast('2024-01-07' as date),50)
,('001',cast('2024-01-08' as date),200)
,('001',cast('2024-01-09' as date),300)
,('001',cast('2024-01-10' as date),500)
,('001',cast('2024-01-11' as date),400)
,('001',cast('2024-01-12' as date),450)
,('001',cast('2024-01-13' as date),400)
,('001',cast('2024-01-14' as date),600)
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 2. 解题思路

使用lead（返回在当前行之后指定偏移量的行的列值）、lag（返回在当前行之前指定偏移量的行的列值）开窗函数，按日期降序分别取出price前一天跟后一天的价格，按以下方法判断：

当今日价格 <昨日价格且今日价格<明天价格，则今天为波谷；
当今日价格 >昨日价格且今日价格>明天价格，则今天为波峰。

实现sql如下：

WITH tmp_w AS (
    SELECT   stock_id,price_date,price
            ,lag(price,1,price) over (PARTITION BY stock_id ORDER BY price_date) as lag_price
            ,lead(price,1,price) over (PARTITION BY stock_id ORDER BY price_date) as lead_price
    FROM    dayly_sql.stock_price
)
SELECT   stock_id,price_date,price
        ,CASE WHEN price > lag_price AND price > lead_price THEN '波峰'
              WHEN price < lag_price AND price < lead_price THEN '波谷'
         ELSE ''
         END AS res
FROM  tmp_w
WHERE
    (CASE WHEN price > lag_price AND price > lead_price THEN '波峰'
          WHEN price < lag_price AND price < lead_price THEN '波谷'
     ELSE ''
     END) <> ''
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

结果：

stock_id	price_date	price	res
001	2024-01-04	400.00	波峰
001	2024-01-07	50.00	波谷
001	2024-01-10	500.00	波峰
001	2024-01-11	400.00	波谷
001	2024-01-12	450.00	波峰
001	2024-01-13	400.00	波谷

#SQL #Hive

← 连续登录问题