找出每个id在在⼀天之内所有的波峰与波⾕值

1. 原始数据

 sh66688,9:35,29.48
sh66688,9:40,28.72
sh66688,9:45,27.74
sh66688,9:50,26.75
sh66688,9:55,27.13
sh66688,10:00,26.30
sh66688,10:05,27.09
sh66688,10:10,26.46
sh66688,10:15,26.11
sh66688,10:20,26.88
sh66688,10:25,27.49
sh66688,10:30,26.70
sh66688,10:35,27.57
sh66688,10:40,28.26
sh66688,10:45,28.03
sh66688,10:50,27.36
sh66688,10:55,26.48
sh66688,11:00,27.41
sh66688,11:05,26.70
sh66688,11:10,27.35
sh66688,11:15,27.35
sh66688,11:20,26.63
sh66688,11:25,26.35
sh66688,11:30,26.81
sh66688,13:00,29.45
sh66688,13:05,29.41
sh66688,13:10,29.10
sh66688,13:15,28.24
sh66688,13:20,28.20
sh66688,13:25,28.59
sh66688,13:30,29.49
sh66688,13:35,30.45
sh66688,13:40,30.31
sh66688,13:45,30.17
sh66688,13:50,30.55
sh66688,13:55,30.75
sh66688,14:00,30.03
sh66688,14:05,29.61
sh66688,14:10,29.96
sh66688,14:15,30.79
sh66688,14:20,29.82
sh66688,14:25,30.09
sh66688,14:30,29.61
sh66688,14:35,29.88
sh66688,14:40,30.36
sh66688,14:45,30.88
sh66688,14:50,30.73
sh66688,14:55,30.76
sh88888,9:35,67.23
sh88888,9:40,66.56
sh88888,9:45,66.73
sh88888,9:50,67.43
sh88888,9:55,67.49
sh88888,10:00,68.34
sh88888,10:05,68.13
sh88888,10:10,67.35
sh88888,10:15,68.13
sh88888,10:20,69.05
sh88888,10:25,69.82
sh88888,10:30,70.62
sh88888,10:35,70.59
sh88888,10:40,70.40
sh88888,10:45,70.29
sh88888,10:50,70.53
sh88888,10:55,70.92
sh88888,11:00,71.13
sh88888,11:05,70.24
sh88888,11:10,70.37
sh88888,11:15,69.79
sh88888,11:20,69.73
sh88888,11:25,70.52
sh88888,11:30,71.23
sh88888,13:00,72.85
sh88888,13:05,73.76
sh88888,13:10,74.72
sh88888,13:15,75.48
sh88888,13:20,75.80
sh88888,13:25,76.74
sh88888,13:30,77.22
sh88888,13:35,77.12
sh88888,13:40,76.90
sh88888,13:45,77.80
sh88888,13:50,78.75
sh88888,13:55,78.30
sh88888,14:00,78.68
sh88888,14:05,78.99
sh88888,14:10,78.35
sh88888,14:15,78.37
sh88888,14:20,78.07
sh88888,14:25,78.80
sh88888,14:30,79.78
sh88888,14:35,79.72
sh88888,14:40,80.71
sh88888,14:45,79.92
sh88888,14:50,80.49
sh88888,14:55,80.44

2. 创建表
   create table t22( id string, time string, price double, maked string )row format delimited fields terminated by ',';

3.导入数据到数据库表；

load data local inpath "/opt/t2.dat" into table t22; 

3.SQL语句(没有按ID分组)

select * from 
		(select id,time,price, "Peak" as t 
			from (select id,price,time,t3.r1,t3.r2,t3.m t 
				from (select id,price,`time`,maked m,
				lag(price) over (partition by id order by `time`) lu,
				lead(price) over (partition by id order by `time`) ll,
			round(price-lag(price) over (partition by id order by `time`),2) r1,
			round( price-lead(price) over (partition by id order by `time`),2) r2 
			from t22) t3 where t3.r1>0 and t3.r2>0) t4 
union all 
		select id,time,price, "Botton" as t 
			from (select id,price,time,t3.r1,t3.r2,t3.m t 
				from (select id,price,`time`,maked m,
					lag(price) over (partition by id order by `time`) lu,
					lead(price) over (partition by id order by `time`) ll,
			round(price-lag(price) over (partition by id order by `time`),2) r1,
			round( price-lead(price) over (partition by id order by `time`),2) r2 
			from t22) t3 where t3.r1<0 and t3.r2<0) t5) t6 order by time;

4.结果如下

```powershell
t6.id	t6.time	t6.price	t6.t
sh66688	10:05	27.09	Peak
sh88888	10:10	67.35	Botton
sh66688	10:15	26.11	Botton
sh66688	10:25	27.49	Peak
sh88888	10:30	70.62	Peak
sh66688	10:30	26.7	Botton
sh66688	10:40	28.26	Peak
sh88888	10:45	70.29	Botton
sh66688	10:55	26.48	Botton
sh88888	11:00	71.13	Peak
sh66688	11:00	27.41	Peak
sh88888	11:05	70.24	Botton
sh66688	11:05	26.7	Botton
sh88888	11:10	70.37	Peak
sh88888	11:20	69.73	Botton
sh66688	11:25	26.35	Botton
sh66688	13:00	29.45	Peak
sh66688	13:20	28.2	Botton
sh88888	13:30	77.22	Peak
sh66688	13:35	30.45	Peak
sh88888	13:40	76.9	Botton
sh66688	13:45	30.17	Botton
sh88888	13:50	78.75	Peak
sh66688	13:55	30.75	Peak
sh88888	13:55	78.3	Botton
sh66688	14:05	29.61	Botton
sh88888	14:05	78.99	Peak
sh88888	14:10	78.35	Botton
sh88888	14:15	78.37	Peak
sh66688	14:15	30.79	Peak
sh88888	14:20	78.07	Botton
sh66688	14:20	29.82	Botton
sh66688	14:25	30.09	Peak
sh88888	14:30	79.78	Peak
sh66688	14:30	29.61	Botton
sh88888	14:35	79.72	Botton
sh88888	14:40	80.71	Peak
sh66688	14:45	30.88	Peak
sh88888	14:45	79.92	Botton
sh66688	14:50	30.73	Botton
sh88888	14:50	80.49	Peak
sh66688	14:55	30.76	Peak
sh88888	9:40	66.56	Botton
sh66688	9:50	26.75	Botton