数据分析02-数据抽样
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
为了验证是随机的，我们执行多次，结果应该是不一样的，这里我们执行两次，下面是结果
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
查询结果
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
执行结果
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
执行结果
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
执行结果
4.数据块抽样
4.1整体数据块抽样
使用tablesample进行数据块抽样，指的是抽取一块数据。这里不能保证数据的随机性，因为获取数据就是头开始连续获取要的数据内容。我们可以看到多次执行同一个SQL，返回的结果是一致的。例如我抽取5行数据。
实现SQL
select
id,
type,
val
from t_sample
TABLESAMPLE (5 rows)
两次执行结果
4.2分桶数据块抽样
分桶数据块抽样，同样有数据块抽样的特点，但是指定了抽取那个桶的数据。如果总的桶数量和默认表内桶数量一致，则直接从原有桶内抽取指定桶内数据，否则对表内数据重新分桶抽取。
查询SQL
select
id,
type,
val
from t_sample
TABLESAMPLE (BUCKET 1 OUT OF 10)
limit 10
执行结果
