本文参考:
https://www.cnblogs.com/blogyuhan/p/9274784.html
https://blog.csdn.net/sunnyyoona/article/details/62894761
感谢上述2位原创作者
hive 中的“行”与“列”的转换:
--- 行转列
desc temp_loan_pzh_out_month_test4;
OK
id string
concat_month string
eg:
- id concat_month
- 00178df4cdd5e8534222ab06f2414bd 201807&201810&201806
- 0017908f2ec080e3eb0ff7761969fed 201811
- 00181415f665600560f569bad51baf7 201812
- 001875bc87e61574bef41be84e56157 201805&201810&201811&201812&201806&201804&201808
--- --------------------- 分割线 --- 代码---------------------
- select id,adid from temp_loan_pzh_out_month_test4
- LATERAL VIEW explode(split(concat_month,'&')) num AS adid limit 100;
--- --------------------- 分割线 --- 结果---------------------
id adid
00103f4c3840bc20d9b137240bebccf 201803
00103f4c3840bc20d9b137240bebccf 201711
00103f4c3840bc20d9b137240bebccf 201804
0011db989909aab33467fd808bed1fe 201810
001295d574cda16528b747f628ebe34 201808
001295d574cda16528b747f628ebe34 201811
001295d574cda16528b747f628ebe34 201807
001295d574cda16528b747f628ebe34 201812
--- 列传行
select mobile,concat_ws('&',collect_set(substr(dt,1,6))) from temp_loan_pzh_out_month_test4 group by mobile
concat_ws:字符间用'&'拼接
collect_set:字符去重
collect_list:字符不去重