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;