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