hive 订单及订单类型行列互换

1. 6 订单及订单类型行列互换

order_id order_type order_time
111 N 10:00
111 A 10:05
111 B 10:10
是用hql获取结果如下：
order_id order_type_1 order_type_2 order_time_1 order_time_2
111 N A 10:00 10:05
111 A B 10:05 10:10

create table if not exists order_type(
order_id string,
order_type string,
order_time string
)
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile
;

load data local inpath '/root/hivedata/order_type.txt' into table order_type;

select
*
from
(select
order_id,
order_type type1,
lead(order_type) over(distribute by order_id sort by order_time) type2,
order_time time1,
lead(order_time) over(distribute by order_id sort by order_time) time2
from
order_type) tmp
where time2 is not null
;