在HQL中,如何基于百分位数筛选表,然后再根据随机样本筛选表?

我认为Hive不支持使用标量子查询的方式(仅适用于 IN / EXISTS )因此,将逻辑移到 FROM 条款:

with sample_pop as (
      select *
      from mytable a cross join
           (select percentile_approx(transaction_amount, 0.99) as top1
            from mytable
           ) aa
      where a.transaction_amount > aa.top1
     )
select * 
from sample_pop distribute by rand(1) 
order by rand(1)
limit 200;

通过以下查询解决了我的问题-

with sample_pop as (select a.* from 
          (
          select *, cum_dist() over (order by transaction_amount asc) pct
          from mytable
          ) a
where pct >= 0.99
)
select * 
from sample_pop distribute by rand(1) 
order by rand(1)
limit 200;