005.hive分组求中位数--sort_array(collect_list(),insert overwrite table --- 实现字段update操作
select occur_period ,arr_1 ,case when size(arr_1)%2=1 then arr_1[cast((size(arr_1)-1)/2 as int )] else (arr_1[cast(size(arr_1)/2 as int) ]+arr_1[cast(( size(arr_1)/2-1 )as int)])/2 end as zws_1 ,case when size(arr_2)%2=1 then arr_2[cast((size(arr_2)-1)/2 as int )] else (arr_2[cast(size(arr_2)/2 as int) ]+arr_2[cast(( size(arr_2)/2-1 )as int)])/2 end as zws_2 from ( select occur_period , sort_array(collect_list(num_jdz_1)) as arr_1 , sort_array(collect_list(num_jdz_2)) as arr_2 from qhtest.num_zws_update_test group by occur_period )a
自带的中位数函数是去重求中位数
小数 percentile_approx
select percentile_approx( i_de_js, 0.5) from qhtest.test_table_zws;