需求:提取数据
说明:原数据是一列多行,需要转化为一行多列
待查询表为:temp_05
hive> desc temp_05;
OK
data_dt string
zhanghao string
booking_time_final string
sign string
hname string
channel string
min_price_weight string
Time taken: 0.04 seconds, Fetched: 7 row(s)
待查询数据为:
hive>
> select
> booking_time_final,data_dt,min_price_weight
> from temp_05 where hname like'%兆龙%' and booking_time_final like '%2018-02%' and channel='5'
> ;
待查询数据如图:
需要提取的数据表头如下:
预定日期 | 昨日价格 | 前天价格 |
---|---|---|
2018-02-01 | ||
2018-02-02 | ||
2018-02-03 | ||
2018-02-04 |
可用提数 SQL
select
booking_time_final,
concat_ws('',collect_set(if(data_dt='20180129',min_price_weight,''))) a,
concat_ws('',collect_set(if(data_dt='20180130',min_price_weight,''))) a2
from temp_05
where hname like'%兆龙%' and booking_time_final like '%2018-02%' and channel='5'
group by booking_time_final ;
数据如图:
以下为尝试过程
hive>
> select
> if(data_dt='20180129' and booking_time_final='2018-02-01',min_price_weight,'') a,
> if(data_dt='20180130' and booking_time_final='2018-02-01',min_price_weight,'') a2
> from temp_05
> where hname like'%兆龙%' and channel='5'
> ;
OK
数据如图:
hive>
> select
> data_dt,booking_time_final,
> if(data_dt='20180129' and booking_time_final='2018-02-01',min_price_weight,'') a,
> if(data_dt='20180130' and booking_time_final='2018-02-01',min_price_weight,'') a2
> from temp_05
> where hname like'%兆龙%' and channel='5'
> ;
数据如图:
hive> select
> booking_time_final,
> if(data_dt='20180129',min_price_weight,'-') a,
> if(data_dt='20180130',min_price_weight,'-') a2
> from temp_05
> where hname like'%兆龙%' and booking_time_final like '%2018-02%' and channel='5'
> ;
数据如图:
hive>
> select
> booking_time_final,
> collect_set(if(data_dt='20180129',min_price_weight,'-')) a,
> collect_set(if(data_dt='20180130',min_price_weight,'-')) a2
> from temp_05
> where hname like'%兆龙%' and booking_time_final like '%2018-02%' and channel='5'
> group by booking_time_final
> ;
数据如图: