TABLESAMPLE采样的语法和使用示例_云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心

MaxCompute支持通过TABLESAMPLE功能来对表数据进行采样，包含三种采样方式：分桶采样、指定采样百分比采样和随机返回指定记录数采样。本文为您介绍使用TABLESAMPLE采样的命令语法和使用示例。

命令格式

分桶采样。
```
TABLESAMPLE (BUCKET <x> OUT OF <y> [ON <col_name> | rand()])
```
参数说明如下。
- x，y：必填。将源表中的数据划分为y个桶，取其中的第x个桶，桶从1开始编号。
- col_name：分桶列名即要进行采样的列名。当表不是聚簇表时，col_name与rand()函数必须二选一，当使用rand()函数时表示对输入的数据随机进行分桶。ON语句中最多支持指定10个列。
指定采样百分比采样。
```
TABLESAMPLE (<n> PERCENT)
```
其中n为采样百分比，取其中n%的数据，即采样返回的数据记录个数和源表中总记录个数之比大概是n%，非精确值。
随机返回指定记录数采样。
```
TABLESAMPLE (<m> ROWS)
```
其中m为指定随机返回的记录数。如果源表中的总记录个数小于m，则返回源表中的全部记录。m最大值为10000。

示例数据

本文使用示例需要用到如下两个表。

BIGDATA_PUBLIC_DATASET.life_service.phoneno_basic_info_2020。
BIGDATA_PUBLIC_DATASET.life_service.phoneno_basic_info_2020表是MaxCompute公开数据集中的表，详情请参见公开数据集概述。

tblsample_test。

tblsample_test表为聚簇表，建表DDL及数据插入命令如下。

--创建cluster表
CREATE TABLE tblsample_test(a bigint, b string, c string) 
  CLUSTERED BY (a, c) SORTED by (a, c) INTO 32 BUCKETS;
--插入数据
insert overwrite table tblsample_test values(1,"b1","c1"),
    (2,"b2","c2"),
    (3,"b3","c3"),
    (4,"b4","c4");
--查询表数据
select * from tblsample_test;
--返回结果
+------------+---+-----+
| a          | b | c   |
+------------+---+-----+
| 2          | b2 | c2 |
| 4          | b4 | c4 |
| 3          | b3 | c3 |
| 1          | b1 | c1 |
+------------+---+---+

使用示例

示例1：根据列的值分桶进行采样。

--对BIGDATA_PUBLIC_DATASET.life_service.phoneno_basic_info_2020进行采样
SELECT isp_code,
			 phoneno,
			 province
  FROM BIGDATA_PUBLIC_DATASET.life_service.phoneno_basic_info_2020 
TABLESAMPLE (BUCKET 1 OUT of 1000000 ON isp_code, 
                      									phoneno, 
                      									province) s;
                                        
--返回结果
+----------+---------+----------+
| isp_code | phoneno | province |
+----------+---------+----------+
| 185      | 1853500 | 山西   |
| 187      | 1878332 | 四川   |
+----------+---------+----------+

示例2：使用RAND()函数，对输入的数据随机分桶进行采样。

--对BIGDATA_PUBLIC_DATASET.life_service.phoneno_basic_info_2020进行采样
SELECT isp_code,
			 phoneno,
       province
  FROM BIGDATA_PUBLIC_DATASET.life_service.phoneno_basic_info_2020
TABLESAMPLE (BUCKET 3 OUT OF 500000 ON RAND()) s;

--返回结果
+----------+---------+----------+
| isp_code | phoneno | province |
+----------+---------+----------+
| 131      | 1312224 | 上海   |
| 135      | 1353936 | 广东   |
| 158      | 1586377 | 山东   |
+----------+---------+----------+

示例3：对聚簇表tblsample_test可省略ON条件进行采样。
```
select a, b from tblsample_test TABLESAMPLE (BUCKET 1 OUT OF 2) as ts;

--返回结果
+------------+---+
| a          | b |
+------------+---+
| 2          | b2|
+------------+---+
```
说明
因为聚簇表中的数据在存储的时候已经分桶存储，因此在采样的时候会直接从某个桶里取数据，这样做可以提高采样的性能。tblsample_test表在创建的时候是分了32个桶，采样的时候，只要总桶数y的取值为32的倍数（32/64/128...）或者是能整除32（16/8/4/...），都可以取得性能优化的效果。

示例4：对tblsample_test表进行采样，取其中n%的数据，即采样返回的数据记录个数和源表中总记录个数之比大概是n%（不能保证绝对精确）。

--取tblsample_test 50%的数据
SELECT * FROM tblsample_test TABLESAMPLE (50 PERCENT) s;

--返回结果
+------------+---+---+
| a          | b | c |
+------------+---+---+
| 2          | b2 | c2 |
| 3          | b3 | c3 |
+------------+---+---+

示例5：对tblsample_test表进行采样，随机返回n条记录。

select * FROM tblsample_test TABLESAMPLE (2 ROWS);

--返回结果
+------------+---+---+
| a          | b | c |
+------------+---+---+
| 2          | b2 | c2 |
| 3          | b3 | c3 |
+------------+---+---+