大数据开发面试题

1、一个股票的表里有如下字段id,code,time,price价格，请编写一个SQL统计改股票的所有波峰和波谷。（面试官口述的）

当时面试的时候我没有思路，在回来的路上突然想到一个思路也不知道是否正确。做了一个实验如下

hive> CREATE TABLE IF NOT EXISTS stock(
id int ,
code bigint ,
time string ,
price double )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS textfile

;

1	1	2017-12-14 12:03:06	12
2	2	1	2017-12-14 12:03:07	11
3	3	1	2017-12-14 12:03:08	13
4	4	1	2017-12-14 12:03:09	10
5	5	1	2017-12-14 12:03:10	15
6	6	2	2017-12-14 12:03:11	18
7	7	2	2017-12-14 12:03:12	16
8	8	2	2017-12-14 12:03:13	19

select a.id,a.price,
case when b.price is null then "未知"
when c.price is null then "未知"
when a.price>b.price and a.price>c.price then "波峰"
when a.price<b.price and a.price<c.price then "波谷"
else "未知" end as mark
from default.stock a
left join
default.stock b on a.code=b.code and b.id=a.id-1
left join

default.stock c on a.code=c.code and c.id=a.id+1

或者

select code,
time,price,
case when g_price is null or d_price is null
then 'unknown'
when price<g_price and price<d_price
then 'lower'
when price>g_price and price>d_price
then 'hight'
ELSE NULL END as p
from (select code,time,price,lag(price,1)over(order by time asc) as g_price,lead(price,1)over(order by time asc) as d_price
from stock

) a

大数据开发面试题

2、表数据如何变成

大数据开发面试题

实现如下结果

code p1

1 12,11 ,13,10 ,15
2 18,16,19

select code,concat_ws(',',collect_set(price)) p1 from stock group by code

3.如何把上面的结果

code p1

1 12,11 ,13,10 ,15

2 18,16,19 变回如下结果

大数据开发面试题

select code,add_new from (select code,concat_ws(',',collect_set(price)) p1 from stock group by code) a lateral view explode(split(a.p1,',')) ad as add_new

大数据运维群584912368，欢迎运维同学加入

大数据开发面试题

相关推荐