HIVE SQL分位数percentile使用方法案例

percentile：percentile(col, p) col是要计算的列（值必须为int类型），p的取值为0-1，若为0.2，那么就是2分位数，依次类推。

percentile_approx：percentile_approx(col, p)。列为数值类型都可以。

percentile_approx还有一种形式percentile_approx(col, p，B)，参数B控制内存消耗的近似精度，B越大，结果的精度越高。默认值为10000。当col字段中的distinct值的个数小于B时，结果就为准确的百分位数。

有一组年龄数据，其中有几位老人和小孩子，剔除这些异常的数据，然后算一个有影响力的平均值。

select 
    avg(age) as avg_age,
    avg(
        case
            when age < high_age
            and age > low_age then age
        end
    ) as handle_avg_age
from
    (
        select name,
            age,
            percentile_approx(age, 0.9) over(partition by 1) as high_age,
            percentile_approx(age, 0.2) over(partition by 1) as low_age
        from
            (
                select 'a' as name,68 as age
                union all
                select 'b' as name,73 as age
                union all
                select 'c' as name,20 as age
                union all
                select 'd' as name,21 as age
                union all
                select 'e' as name,23 as age
                union all
                select 'f' as name,24 as age
                union all
                select 'g' as name,21 as age
                union all
                select 'h' as name,20 as age
                union all
                select 'i' as name,8 as age
                union all
                select 'g' as name,9 as age
            ) a
    ) a

结果

avg_age	handle_avg_age 
28.7	21.5