阿里云云原生大数据计算服务 MaxComputeTABLESAMPLE采样-云淘科技

命令格式

• 分桶采样。

  TABLESAMPLE (BUCKET  OUT OF  [ON  | rand()])

  参数说明如下。

  • x，y：必填。将源表中的数据划分为y个桶，取其中的第x个桶，桶从1开始编号。

  • col_name：分桶列名即要进行采样的列名。当表不是聚簇表时，col_name与rand()函数必须二选一，当使用rand()函数时表示对输入的数据随机进行分桶。ON语句中最多支持指定10个列。

• 指定采样百分比采样。

  TABLESAMPLE ( PERCENT)

  其中n为采样百分比，取其中n%的数据，即采样返回的数据记录个数和源表中总记录个数之比大概是n%，非精确值。

• 随机返回指定记录数采样。

  TABLESAMPLE ( 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 |
  +------------+---+---+