HiveSQL备忘

行转列

-- 1     g1-- 1     g2          1     g1,g2,g3-- 1     g3    ====>-- 2     g1          2     g1,g2-- 2     g2select     group_by_columns ,     concat_ws("," , collect_set(concat_column))from resut_tablegroup by group_by_columns ; 

列转行

-- 使用 lateral view explode-- 数组形式的转换 lateral view explode (idArray) id_array_test as id -- map形式的转换  lateral view explode (dataMap) data_map_test as map_key, map_valueselect     map_key, map_valuefrom result_table    lateral view explode (dataMap) dataMap as map_key, map_valuewhere ......

百分比显示

-- a与b的百分比统计显示select    concat((round(a/b, 4)*100), '%') a_b_ratio from result_tablewhere ...