Skip to main content

数据抽样

1.样例数据​

有数据表t_sample包含ID,type,val三个字段 1 ~ 10000行数据， type为随机分配的1 ~ 3 ,val为0 ~ 1的随机数；其中根据ID进行分10个桶；

建表语句和数据生成语句如下：

set hive.enforce.bucketing=true;
-- 建表语句
CREATE TABLE t_sample (
  id bigint,
  type bigint,
  val double
)
CLUSTERED BY (id) INTO 10 BUCKETS
STORED AS ORC;

--数据插入
insert into t_sample(id,type,val)
select ind+1 as id,floor(rand()*3)+1 as type,rand() as val from(select split(space(9999),'') as a) t lateral view posexplode(a) tt as ind,space_val;

样例数据查看

+-------+-------+----------------------+
|  id   | type  |         val          |
+-------+-------+----------------------+
| 1928  | 1     | 0.8149664325591012   |
| 4320  | 3     | 0.41168841833279834  |
| 5323  | 2     | 0.40307445813137033  |
| 9731  | 1     | 0.2036838175568444   |
| 3915  | 2     | 0.0226902346921094   |
| 659   | 3     | 7.92836807809949E-4  |
| 1299  | 3     | 0.3501926828217895   |
| 2976  | 1     | 0.7974942217644628   |
| 6173  | 3     | 0.2531007613722899   |
| 7462  | 1     | 0.14091100049939698  |
+-------+-------+----------------------+

2.随机抽样​

2.1随机抽取N行​

如何随机的抽取N行，假设N=10.我们使用rand()函数新生成一列，根据rand()值进行排序，正序倒序均可，然后使用limit限制10行即可。

实现SQL

select
id,
type,
val
from t_sample
order by rand() asc
limit 10

为了验证是随机的，我们执行多次，结果应该是不一样的，这里我们执行两次，下面是结果

01

02

2.2抽取一定比例数据（大致）​

假设我们不知道总行数，抽0.05%的数据。依旧使用rand()函数，我们知道rand()是随机生成的0~1的数据，所以我们认为如果rand()值小于0.0005 则是抽取的对应比例的数据。当数据量越大的时候数据，数据也约准确 。所以这里是一个大致结果。因为我们知道数据行数为10000，理论上应该是5条，但是由于我们的数据量比较小，应该会有比较大的偏差。我们还是执行2次，看下结果.

实现SQL

select
id,
type,
val
from t_sample
where rand()<=0.0005

查询结果

03

04

2.3抽取一定比例数据 （精确）​

如果我们想要精确的抽取对应比例的数据，该怎么办呢？首先需要统计出总的行数cnt，然后使用row_numer()开窗，根据一个rand()进行排序生成序号rn,然后rn/cnt<=0.0005即可。

实现SQL

select
id,
type,
val
from
(select
id,
type,
val,
row_number()over(order by rand()) as rn,
count(1)over() as cnt
from t_sample) t
where rn/cnt <=0.0005

执行结果

05

2.4分组随机抽样 抽取N行​

现在需求进一步增加，我们需要按照type进行分组，然后每组抽取5行数据。 这里我们要进行分组排序：使用开窗函数row_number()根据type进行分组，根据rand() 进行排序，然后得到序号rn,限制rn <= 5即可。

实现SQL

select
id,
type,
val
from
(select
id,
type,
val,
row_number()over(partition by type order by rand()) as rn
from t_sample)t
where rn <= 5

执行结果

06

3.分箱抽样​

我们假设按照ID进行分箱，共分成5箱，每箱随机抽取2个值。这里我们使用ntile来进行分箱n_box,然后根据n_box进行分组，每组抽取随机抽取2个值。为了方便查看我在sql最后加了按照id排序；

实现SQL, 

select
id,type,val
from
(select
id,type,val,n_box,
row_number()over(partition by n_box order by rand()) as rn
from
(select
id,type,val,ntile(5)over(order by id) as n_box
from t_sample
)t
)tt
where rn <=2
order by id asc

执行结果

07

4.数据块抽样​

4.1整体数据块抽样​

使用tablesample进行数据块抽样，指的是随机的抽取一块儿数据。这里不能保证数据的随机性，因为获取数据就是从某块数据开始随机的连续获取要的数据内容。我们可以看到多次执行同一个SQL，返回的结果是一致的。例如我抽取5行数据。

实现SQL

select
id,
type,
val
from t_sample
TABLESAMPLE (5 rows)

两次执行结果

08

09

#4.2分桶数据块抽样​

分桶数据块抽样，同样有数据块抽样的特点，但是指定了抽取那个桶的数据。 如果总的桶数量和默认表内桶数量一致，则直接从原有桶内抽取指定桶内数据，否则对表内数据重新分桶抽取。

查询SQL

select
id,
type,
val
from t_sample
TABLESAMPLE (BUCKET 1 OUT OF 10)
limit 10

10