代码之家 › 专栏 › 技术社区 › Parth Shiras

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

hue hiveql hql hive sql

1

Parth Shiras · 技术社区 · 8 年前

我试图从一个表中随机抽取200行,但首先我想对其进行筛选,以便从变量中仅选取前1%的值。

我发现以下错误-

编译语句时出错:失败:ParseException行3:31 无法识别“select”“percentile\u approx”(“中的”)附近的输入表达式规范

下面是我的疑问-

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

2 回复 | 直到 8 年前

1

0

Gordon Linoff 8 年前

我认为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;

2

0

Parth Shiras 8 年前

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

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;