关于对key-value的数据行转化为列的sql操作
insert into table ${db_name}.uv_month_20190529 select k_v['${date}'] as date_id, k_v['0'] as 0h, k_v['1'] as 1h , k_v['2'] as 2h, k_v['3'] as 3h, k_v['4'] as 4h, k_v['5'] as 5h, k_v['6'] as 6h, k_v['7'] as 7h, k_v['8'] as 8h, k_v['9'] as 9h, k_v['10'] as 10h, k_v['11'] as 11h, k_v['12'] as 12h, k_v['13'] as 13h, k_v['14'] as 14h, k_v['15'] as 15h, k_v['16'] as 16h, k_v['17'] as 17h, k_v['18'] as 18h, k_v['19'] as 19h, k_v['20'] as 20h, k_v['21'] as 21h, k_v['22'] as 22h, k_v['23'] as 23h from ( select str_to_map(concat_ws(',',collect_list(concat(hour,':',uv)))) k_v from ${db_temp}.tmp_ol_xianxia_store_table1_${date} ) t;
这段sql是将统计出来的每个小时的uv进行转置操作,得到最终的结果。