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进行转置操作,得到最终的结果。