前言
在日常工作中,我们会对表中记录随机抽样然后探查,如何进行抽样就是本章要讲的重点。数仓中的抽样方法有很多,这里主要介绍一下:随机抽样、块抽样、桶抽样、分组抽样这四种,话不多说,直接上干货。
随机抽样
随机抽样就是给每行数据赋值一个随机数 ,排序之后进行抽样,主要分为:
- order by,直接全局排序。
- distribute by + sort by,先分组再排序。
orderBy
select *
from userinfo
where pt = '${yyyymmdd}'
order by `column` -- 全局排序
limit 100;
sortBy
select *
from userinfo
where pt = '${yyyymmdd}'
distribute by `column1` -- 按照 某字段 分组
sort by `column2` -- 组内按照某字段 排序
limit 100;
桶采样
- TABLESAMPLE (BUCKET
x
OUT OFy
[ON <col_name> | rand()])- x,y:必填。将源表中的数据划分为y个桶,取其中的第x个桶,桶从
1
开始编号。 - col_name:分桶列名即要进行采样的列名。当表不是聚簇表时,col_name与rand()函数必须二选一,当使用rand()函数时表示对输入的数据随机进行分桶。ON语句中最多支持指定10个列。
- x,y:必填。将源表中的数据划分为y个桶,取其中的第x个桶,桶从
现在有一张分区用户表 usr_info
,总用户量 1700W 左右,想从中抽取 10% 的用户进行分析,具体代码如下:
select count(1)
from userinfo
tablesample(bucket 1 out of 10 on rand())
where pt = '${yyyymmdd}'
;
块采样
指定记录数抽样
- TABLESAMPLE (
m
ROWS) ,其中 m 为指定随机返回的记录数。
切记,此时要考虑 HDFS中block
个数,因为 TABLESAMPLE 本身是基于 block 抽样的,如果此时有 10个 block ,那么如下代码最终返回结果等于:m
x block 个数
。
select count(1)
from (select uid
from userinfo
where pt = '${yyyymmdd}'
) t
tablesample(1000 rows)
;
指定百分比采样
- TABLESAMPLE ( PERCENT),其中
n
为采样百分比,取其中n%
的数据,即采样返回的数据记录个数和源表中总记录个数之比大概是n%
,非精确值。
select count(1)
from (select uid
from userinfo
where pt = '${yyyymmdd}'
) t
tablesample(20 percent)
;
按照数据大小抽样
- TABLESAMPLE(
M
MB/GB/GB)
select count(1)
from (select uid
from userinfo
where pt = '${yyyymmdd}'
) t
tablesample(100M) -- 抽取 100MB 大小的数据,抽取的是数据块,是压缩的数据。
;
分组抽样
ClusterSample
此函数并非 Hive 原生函数,而是 MaxCompute 独有。
用户随机抽样。返回 True
表示该行数据被抽中。命令如下:
- cluster_sample(bigint ) OVER ([partition_clause]) ,表示随机抽取N条数据。
- cluster_sample(bigint , bigint ) OVER ([partition_clause]),表示按比例(M/N)随机抽取。即抽取partition_row_count×M / N条数据。
partition_row_count
指分区中的数据行数。
案例: 按照不同意向职位(软开,硬件,机械,产运,金融,市场营销)、是否新老用户(1;0)分组,各抽取 10 个用户入样,代码如下:
with usr as (
select *
,CLUSTER_SAMPLE(10) over(partition by careerjob,isnew) as flag
from userinfo
where pt = '${yyyymmdd}'
and careerjob in ('软开','硬件','机械','产运','金融','市场营销')
)
select count(uid)
from usr
where flag = true
;
结果如下所示:
上述案例中,careerjob
有 6 个枚举,isnew
有 2 个枚举,组合方案有 12 组,每组抽取10人,一共抽取 120 人。
ntile
将数据顺序切分成N等份,返回数据所在等份的编号(从1到N)。
- ntile(bigint N) over ([partition_clause] [orderby_clause])
select id
from (select
id
,ntile(9) over (order by rand()) as seed
from values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) as t(id)
)
where seed=1;
标签:采样,数仓,抽样,pt,TABLESAMPLE,count,yyyymmdd,where,select
From: https://blog.csdn.net/weixin_43145550/article/details/141567415