MaxCompute：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 |
  +------------+---+---+