1.题目

有如下数据，记录每天每只股票的收盘价格，请查出每只股票的波峰和波谷的日期和价格；

波峰定义：股票价格高于前一天和后一天价格时为波峰

波谷定义：股票价格低于前一天和后一天价格是为波谷

2.数据准备

建表语句：

create table t_stock_test(ts_code string comment '股票代码',trade_date string comment '交易日期',close float comment '收盘价')

数据插入：

INSERT INTO `t_stock_test` VALUES ('000001.SZ','20220104',16.66),('000002.SZ','20220104',20.49),('000001.SZ','20220105',17.15),('000002.SZ','20220105',21.17),('000001.SZ','20220106',17.12),('000002.SZ','20220106',21.05),('000001.SZ','20220107',17.2),('000002.SZ','20220107',21.89),('000001.SZ','20220110',17.19),('000002.SZ','20220110',22.16),('000001.SZ','20220111',17.41),('000002.SZ','20220111',22.3),('000001.SZ','20220112',17),('000002.SZ','20220112',22.05),('000001.SZ','20220113',16.98),('000002.SZ','20220113',21.53),('000001.SZ','20220114',16.33),('000002.SZ','20220114',20.7),('000001.SZ','20220117',16.22),('000002.SZ','20220117',20.87);

3.考点分析

需要比较当天价格与前一天、后一天的价格进行比较，常规想法为进行关联，股票ID相等、日期为当天日期减1，为前一天价格，日期为当天价格加1，为后一天价格，然后进行计算；简化方法为使用lag和lead函数，可以避免进行表关联；

lag()函数

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行。参数1为列名，参数2为往上第n行（可选，默认为1），参数3为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

lead()函数

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行。参数1为列名，参数2为往下第n行（可选，默认为1），参数3为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

4.SQL

select   ts_code,  trade_date,  case when close> lastday_close and close>nextday_close then '波峰' else '波谷' end as typefrom  (select   ts_code,   trade_date,   close,   lag(close,1)over(partition by ts_code order by trade_date asc) as lastday_close,   lead(close,1)over(partition by ts_code order by trade_date asc) as nextday_close    from t_stock_test ) t where (close> lastday_close and close>nextday_close)  or (close< lastday_close and close<nextday_close);

执行结果

ITPub博客

SQL总结大厂真题-波峰波谷

最新文章