目前，为了在配置单元中对列进行百分位排序，我使用了如下方法。我试图在一个列中按项目所属的百分比对项目进行排序，为每个项目分配一个0到1的值。下面的代码指定了一个从0到9的值，本质上是指 char_percentile_rank 0的值位于项目的底部10%，9的值位于项目的顶部10%。有没有更好的办法？

select item
    , characteristic
    , case when characteristic <= char_perc[0] then 0
        when characteristic <= char_perc[1] then 1
        when characteristic <= char_perc[2] then 2
        when characteristic <= char_perc[3] then 3
        when characteristic <= char_perc[4] then 4
        when characteristic <= char_perc[5] then 5
        when characteristic <= char_perc[6] then 6
        when characteristic <= char_perc[7] then 7
        when characteristic <= char_perc[8] then 8
        else 9
      end as char_percentile_rank
from (
    select split(item_id,'-')[0] as item
        , split(item_id,'-')[1] as characteristic
        , char_perc
    from (
        select collect_set(concat_ws('-',item,characteristic)) as item_set
            , PERCENTILE(BIGINT(characteristic),array(0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9)) as char_perc
        from(
            select item
                , sum(characteristic) as characteristic
            from table
            group by item
        ) t1
    ) t2
    lateral view explode(item_set) explodetable as item_id
) t3

注意：我必须做 collect_set 为了避免自连接，因为百分位函数隐式地执行 group by .
我发现percentile函数非常慢（至少在这种用法中是如此）。也许手动计算百分位数更好？