全部產品
Search
文件中心

MaxCompute:其他函數

更新時間:Jun 08, 2024

MaxCompute SQL提供了開發過程中常見的其他函數,您可以根據實際需要選擇合適的函數。本文為您提供MaxCompute SQL支援的CAST、DECODE、LEAST等函數的命令格式、參數說明及樣本。

函數

功能

BASE64

將二進位表示值轉換為BASE64編碼格式字串。

BETWEEN AND運算式

篩選滿足區間條件的資料。

CASE WHEN運算式

根據運算式的計算結果,靈活地返回不同的值。

CAST

將運算式的結果轉換為目標資料類型。

COALESCE

返回參數列表中第一個非NULL的值。

COMPRESS

對STRING或BINARY類型輸入參數按照GZIP演算法進行壓縮。

CRC32

計算字串或位元據的迴圈冗餘校正值。

DECODE

實現if-then-else分支選擇的功能。

DECOMPRESS

對BINARY類型輸入參數按照GZIP演算法進行解壓。

GET_IDCARD_AGE

根據社會安全號碼碼返回當前的年齡。

GET_IDCARD_BIRTHDAY

根據社會安全號碼碼返回出生日期。

GET_IDCARD_SEX

根據社會安全號碼碼返回性別。

GET_USER_ID

擷取當前帳號的帳號ID。

GREATEST

返回輸入參數中的最大值。

HASH

根據輸入參數計算Hash值。

IF

判斷指定的條件是否為真。

LEAST

返回輸入參數中最小的值。

MAX_PT

返回分區表的一級分區的最大值。

NULLIF

比較兩個入參是否相等。

NVL

指定值為NULL的參數的返回結果。

ORDINAL

將輸入變數按從小到大排序後,返回指定位置的值。

PARTITION_EXISTS

查詢指定的分區是否存在。

SAMPLE

對所有讀入的列值,採樣並過濾掉不滿足採樣條件的行。

SHA

計算字串或位元據的SHA-1雜湊值。

SHA1

計算字串或位元據的SHA-1雜湊值。

SHA2

計算字串或位元據的SHA-2雜湊值。

STACK

將指定的參數組分割為指定的行數。

STR_TO_MAP

將字串按照指定的分隔字元分割得到Key和Value。

TABLE_EXISTS

查詢指定的表是否存在。

TRANS_ARRAY

將一行資料轉為多行的UDTF,將列中儲存的以固定分隔字元格式分隔的數組轉為多行。

TRANS_COLS

將一行資料轉換為多行資料的UDTF,將不同的列拆分為不同的行。

UNBASE64

將BASE64編碼格式字串轉換為二進位表示值。

UNIQUE_ID

返回一個隨機ID,運行效率高於UUID函數。

UUID

返回一個隨機ID。

BASE64

  • 命令格式

    string base64(binary <value>)
  • 命令說明

    value從二進位轉換為BASE64編碼格式字串。

  • 參數說明

    value:必填。BINARY類型。待轉換參數值。

  • 傳回值說明

    返回STRING類型。輸入參數為NULL時,返回結果為NULL。

  • 樣本

    • 樣本1:將cast ('alibaba' as binary)二進位結果轉換為BASE64編碼格式字串。命令樣本如下:

      --返回YWxpYmFiYQ==。
      select base64(cast ('alibaba' as binary));
    • 樣本2:輸入參數為NULL。命令樣本如下:

      --返回NULL。
      select base64(null);

BETWEEN AND運算式

  • 命令格式

    <a> [NOT] between <b> and <c>
  • 命令說明

    篩選滿足a的值位於bc之間或不在bc之間的資料。

  • 參數說明

    • a:必填。待篩選的欄位。

    • bc:必填。指定的區間條件。資料類型必須與a的資料類型保持一致。

  • 傳回值說明

    返回滿足條件的資料。

    如果abc為空白,返回結果為空白。

  • 樣本

    例如表emp中的資料如下。

    | empno | ename | job | mgr | hiredate| sal| comm | deptno |
    7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
    7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
    7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
    7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
    7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
    7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
    7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
    7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
    7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
    7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
    7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
    7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
    7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
    7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
    7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
    7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
    7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

    查詢sal大於等於1000小於等於1500之間的資料,命令樣本如下。

    select * from emp where sal between 1000 and 1500;

    返回結果如下。

    +-------+-------+-----+------------+------------+------------+------------+------------+
    | empno | ename | job | mgr        | hiredate   | sal        | comm       | deptno     |
    +-------+-------+-----+------------+------------+------------+------------+------------+
    | 7521  | WARD  | SALESMAN | 7698  | 1981-02-22 00:00:00 | 1250.0     | 500.0      | 30  |
    | 7654  | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.0     | 1400.0     | 30 |
    | 7844  | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.0     | 0.0        | 30 |
    | 7876  | ADAMS | CLERK | 7788  | 1987-05-23 00:00:00 | 1100.0     | NULL     | 20   |
    | 7934  | MILLER | CLERK | 7782  | 1982-01-23 00:00:00 | 1300.0     | NULL      | 10  |
    | 7956  | TEBAGE | CLERK | 7748  | 1982-12-30 00:00:00 | 1300.0     | NULL      | 10  |
    +-------+-------+-----+------------+------------+------------+------------+------------+

CASE WHEN運算式

  • 命令格式

    MaxCompute提供以下兩種case when格式:

    • case <value>
      when <value1> then <result1>
      when <value2> then <result2>
      ...
      else <resultn>
      end
    • case
      when (<_condition1>) then <result1>
      when (<_condition2>) then <result2>
      when (<_condition3>) then <result3>
      ...
      else <resultn>
      end
  • 命令說明

    根據value_condition的計算結果,靈活地返回不同的result值。

  • 參數說明

    • value:必填。比較的值。

    • _condition:必填。指定判斷條件。

    • result:必填。傳回值。

  • 傳回值說明

    • 如果result類型只有BIGINT、DOUBLE,統一轉為DOUBLE後,再返回結果。

    • 如果result類型中有STRING類型,則統一轉為STRING後,再返回結果。如果無法進行類型轉換,例如BOOLEAN類型無法轉換為STRING類型,則會返回報錯。

    • 不允許其他類型之間的轉換。

  • 樣本

    例如表sale_detail的欄位為shop_name string, customer_id string, total_price double,包含資料如下。

    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2013       | china      |
    | s2         | c2          | 100.2       | 2013       | china      |
    | s3         | c3          | 100.3       | 2013       | china      |
    | null       | c5          | NULL        | 2014       | shanghai   |
    | s6         | c6          | 100.4       | 2014       | shanghai   |
    | s7         | c7          | 100.5       | 2014       | shanghai   |
    +------------+-------------+-------------+------------+------------+

    命令樣本如下。

    select 
    case  
    when region='china' then 'default_region'
    when region like 'shang%' then 'sh_region'
    end as region 
    from sale_detail;

    返回結果如下。

    +------------+
    | region     |
    +------------+
    | default_region |
    | default_region |
    | default_region |
    | sh_region  |
    | sh_region  |
    | sh_region  |
    +------------+

CAST

  • 命令格式

    cast(<expr> as <type>)
  • 命令說明

    expr的結果轉換成目標資料類型type

  • 參數說明

    • expr:必填。待轉換資料來源。

    • type:必填。目標資料類型。用法如下:

      • cast(double as bigint):將DOUBLE資料類型值轉換成BIGINT資料類型。

      • cast(string as bigint):在將字串轉為BIGINT資料類型時,如果字串中是以整型表達的數字,則會直接將其轉為BIGINT類型。如果字串中是以浮點數或指數形式表達的數字,則會先轉為DOUBLE資料類型,再轉為BIGINT資料類型。

      • cast(string as datetime)cast(datetime as string):會採用預設的日期格式yyyy-mm-dd hh:mi:ss

  • 傳回值說明

    • 傳回值為轉換後的目標資料類型。

    • 如果設定了setproject odps.function.strictmode=false,則會返回字母前的數字。

    • 如果設定了setproject odps.function.strictmode=true,則會返回錯誤。

    • 當轉化成Decimal類型時,如果設定了odps.sql.decimal.tostring.trimzero=true:去掉小數點後末尾的零;如果設定了odps.sql.decimal.tostring.trimzero=false:保留小數點後末尾的零。

      重要

      目前odps.sql.decimal.tostring.trimzero參數只對錶裡取數生效,對靜態值不生效。

  • 樣本

    • 樣本1:常見用法。命令樣本如下。

      --返回1。
      select cast('1' as bigint);
    • 樣本2:將STRING資料類型值轉換成BOOLEAN資料類型,當STRING為空白字串時返回false,否則返回true。命令樣本如下。

      • STRING為空白字串。

        select cast("" as boolean);
        --返回
        +------+
        | _c0  |
        +------+
        | false |
        +------+
      • STRING為非Null 字元串。

        select cast("false" as boolean);
        --返回true
        +------+
        | _c0  |
        +------+
        | true |
        +------+
    • 樣本3:將字串轉換成日期。

      --將字串轉換成日期
      select cast("2022-12-20" as date);
      --返回
      +------------+
      | _c0        |
      +------------+
      | 2022-12-20 |
      +------------+
      
      --將帶時分秒的日期文字轉成日期
      select cast("2022-12-20 00:01:01" as date);
      --返回
      +------------+
      | _c0        |
      +------------+
      | NULL       |
      +------------+
      --如果需要正常顯示,需要設定以下參數:
      set odps.sql.executionengine.enable.string.to.date.full.format= true;
      select cast("2022-12-20 00:01:01" as date);
      --返回
      +------------+
      | _c0        |
      +------------+
      | 2022-12-20 |
      +------------+
      說明

      預設參數odps.sql.executionengine.enable.string.to.date.full.format的值為false,如果需要轉換帶時分秒的日期文字,需要把該參數值設定成true

    • 樣本4(錯誤命令樣本):異常用法,如果轉換不成功或遇到不支援的類型轉換,會引發異常。錯誤命令樣本如下。

      select cast('abc' as bigint);
    • 樣本5:設定了setproject odps.function.strictmode=false的情境樣本。

      setprojectodps.function.strictmode=false;
      select cast('123abc'as bigint);
      --返回
      +------------+
      |_c0|
      +------------+
      |123|
      +------------+
    • 樣本6:設定了setproject odps.function.strictmode=true的情境樣本。

      setprojectodps.function.strictmode=true;
      select cast('123abc' as bigint);
      --返回
      FAILED:ODPS-0130071:[0,0]Semanticanalysisexception-physicalplangenerationfailed:java.lang.NumberFormatException:ODPS-0123091:Illegaltypecast-Infunctioncast,value'123abc'cannotbecastedfromStringtoBigint.
    • 樣本7:設定了odps.sql.decimal.tostring.trimzero的情境樣本。

      --建立表
      create table mf_dot (dcm1 decimal(38,18),
                           dcm2 decimal(38,18));
      --insert資料
      insert into table mf_dot values (12.45500BD,12.3400BD);
      
      --Flag為true或者不設定時
      set odps.sql.decimal.tostring.trimzero=true;
      --去掉小數點後末尾的零
      select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot;
      --傳回值
      +------------+------------+
      | _c0        | _c1        |
      +------------+------------+
      | 12.455     | 12.34      |
      +------------+------------+
      
      --Flag為false時
      set odps.sql.decimal.tostring.trimzero=false;
      --保留小數點後末尾的零
      select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot;
      --傳回值
      +------------+------------+
      | _c0        | _c1        |
      +------------+------------+
      | 12.455     | 12.340     |
      +------------+------------+
      
      --對靜態值不生效
      set odps.sql.decimal.tostring.trimzero=false;
      select cast(round(12345.120BD,3) as string);
      --返回:
      +------------+
      | _c0        |
      +------------+
      | 12345.12   |
      +------------+

COALESCE

  • 命令格式

    coalesce(<expr1>, <expr2>, ...)
  • 命令說明

    返回<expr1>, <expr2>, ...中第一個非NULL的值。

  • 參數說明

    expr:必填。待驗證的值。

  • 傳回值說明

    傳回值類型和參數資料類型相同。

  • 樣本

    • 樣本1:常見用法。命令樣本如下。

      --返回1。
      select coalesce(null,null,1,null,3,5,7);
    • 樣本2:參數實值型別未定義,返回報錯。

      • 錯誤命令樣本

        --參數abc未定義類型,系統引擎無法識別,導致返回報錯。
        select coalesce(null,null,1,null,abc,5,7);
      • 正確命令樣本

        select coalesce(null,null,1,null,'abc',5,7);
    • 樣本3:在非表讀取的情況下,如果參數值全部為空白,則返回報錯。錯誤命令樣本如下。

      --返回報錯,會提示至少有一個參數值非NULL。
      select coalesce(null,null,null,null);
    • 樣本4:在表讀取的情況下,如果參數值全部為空白,則返回NULL。

      未經處理資料表:

      +-----------+-------------+------------+
      | shop_name | customer_id | toal_price |
      +-----------+-------------+------------+
      | ad        | 10001       | 100.0      |
      | jk        | 10002       | 300.0      |
      | ad        | 10003       | 500.0      |
      | tt        | NULL        | NULL       |
      +-----------+-------------+------------+

      由未經處理資料表可以看出,tt的值全部為空白。執行以下語句後傳回值為NULL。

      select coalesce(customer_id,total_price) from sale_detail where shop_name='tt';

COMPRESS

  • 命令格式

    binary compress(string <str>)
    binary compress(binary <bin>)
  • 命令說明

    strbin按照GZIP演算法進行壓縮。

  • 參數說明

    • str:必填。STRING類型。

    • bin:必填。BINARY類型。

  • 傳回值說明

    返回BINARY類型。輸入參數為NULL時,返回結果為NULL。

  • 樣本

    • 樣本1:對字串hello按照GZIP演算法進行壓縮。命令樣本如下。

      --返回=1F=8B=08=00=00=00=00=00=00=03=CBH=CD=C9=C9=07=00=86=A6=106=05=00=00=00。
      select compress('hello');
    • 樣本2:輸入參數為空白。命令樣本如下。

      --返回=1F=8B=08=00=00=00=00=00=00=03=03=00=00=00=00=00=00=00=00=00。
      select compress('');
    • 樣本3:輸入參數為NULL。命令樣本如下。

      --返回NULL。
      select compress(null);

CRC32

  • 命令格式

    bigint crc32(string|binary <expr>)
  • 命令說明

    計算字串或二進位類型的expr的迴圈冗餘校正值。

  • 參數說明

    expr:必填。STRING或BINARY類型。

  • 傳回值說明

    返回BIGINT類型。返回規則如下:

    • 輸入參數為NULL時,返回結果為NULL。

    • 輸入參數為空白時,返回0。

  • 樣本

    • 樣本1:計算字串ABC的迴圈冗餘校正值。命令樣本如下。

      --返回2743272264。
      select crc32('ABC');
    • 樣本2:輸入參數為NULL。命令樣本如下。

      --返回NULL。
      select crc32(null);

DECODE

  • 命令格式

    decode(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])
  • 命令說明

    實現if-then-else分支選擇的功能。

  • 參數說明

    • expression:必填。要比較的運算式。

    • search:必填。與expression進行比較的搜尋項。

    • result:必填。searchexpression的值匹配時的傳回值。

    • default:可選。如果所有的搜尋項都不匹配,則返回default值,如果未指定,則返回NULL。

    說明
    • 所有的result資料類型必須一致或為NULL。不一致的資料類型會返回報錯。

    • 所有的searchexpression資料類型必須一致,否則會返回報錯。

  • 傳回值說明

    • 如果匹配,返回result

    • 如果沒有匹配,返回default

    • 如果沒有指定default,返回NULL。

    • 如果search選項有重複且匹配時,會返回第一個值。

    • 通常,MaxCompute SQL在計算NULL=NULL時返回NULL,但在該函數中,NULL與NULL的值是相等的。

  • 樣本

    例如表sale_detail的欄位為shop_name string, customer_id string, total_price double,包含資料如下。

    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2013       | china      |
    | s2         | c2          | 100.2       | 2013       | china      |
    | s3         | c3          | 100.3       | 2013       | china      |
    | null       | c5          | NULL        | 2014       | shanghai   |
    | s6         | c6          | 100.4       | 2014       | shanghai   |
    | s7         | c7          | 100.5       | 2014       | shanghai   |
    +------------+-------------+-------------+------------+------------+

    命令樣本如下。

    --當customer_id的值為c1時,返回Taobao;值為c2時,返回Alipay;值為c3時,返回Aliyun;值為NULL時,返回N/A;其他情境返回Others。
    select
    decode(customer_id,
    'c1', 'Taobao',
    'c2', 'Alipay',
    'c3', 'Aliyun',
    Null, 'N/A',
    'Others') as result
    from sale_detail;
    --等效於如下語句。
    if customer_id = c1 then
    result := 'Taobao';
    elsif customer_id = c2 then
    result := 'Alipay';
    elsif customer_id = c3 then
    result := 'Aliyun';
    ...
    else
    result := 'Others';
    end if;

    返回結果如下。

    +------------+
    | result     |
    +------------+
    | Others     |
    | Others     |
    | Others     |
    | Taobao     |
    | Alipay     |
    | Aliyun     |
    +------------+

DECOMPRESS

  • 命令格式

    binary decompress(binary <bin>)
  • 命令說明

    bin按照GZIP演算法進行解壓。

  • 參數說明

    bin:必填。BINARY類型。

  • 傳回值說明

    返回BINARY類型。輸入參數為NULL時,返回結果為NULL。

  • 樣本

    • 樣本1:對字串hello, world的壓縮結果進行解壓並轉換為字串格式。命令樣本如下。

      --返回hello, world。
      select cast(decompress(compress('hello, world')) as string);
    • 樣本2:輸入參數為NULL。命令樣本如下。

      --返回NULL。
      select decompress(null);

GET_IDCARD_AGE

  • 命令格式

    get_idcard_age(<idcardno>)
  • 命令說明

    根據社會安全號碼碼返回當前的年齡,即當前年份減去社會安全號碼碼中標識的出生年份的差值。

  • 參數說明

    idcardno:必填。STRING類型,15位或18位社會安全號碼碼。在計算時會根據省份代碼以及最後一位校正碼檢查身份證的合法性。如果校正不通過會返回NULL。

  • 傳回值說明

    返回BIGINT類型。輸入為NULL時,返回NULL。

GET_IDCARD_BIRTHDAY

  • 命令格式

    get_idcard_birthday(<idcardno>)
  • 命令說明

    根據社會安全號碼碼返回出生日期。

  • 參數說明

    idcardno:必填。STRING類型,15位或18位社會安全號碼碼。在計算時,會根據省份代碼以及最後一位校正碼檢查身份證的合法性。如果校正不通過,則返回NULL。

  • 傳回值說明

    返回DATETIME類型。輸入為NULL時,返回NULL。

GET_IDCARD_SEX

  • 命令格式

    get_idcard_sex(<idcardno>)
  • 命令說明

    根據社會安全號碼碼返回性別,值為M(男)或F(女)。

  • 參數說明

    idcardno:必填。STRING類型,15位或18位社會安全號碼。在計算時會根據省份代碼以及最後一位校正碼檢查身份證的合法性。如果校正不通過,則返回NULL。

  • 傳回值說明

    返回STRING類型。輸入為NULL時,返回NULL。

GET_USER_ID

  • 命令格式

    get_user_id()
  • 命令說明

    擷取當前帳號的帳號ID,即使用者ID或UID。

  • 參數說明

    無需輸入參數。

  • 傳回值說明

    返回當前帳號的帳號ID。

  • 樣本

    select get_user_id();
    --返回結果如下。
    +------------+
    | _c0        |
    +------------+
    | 1117xxxxxxxx8519 |
    +------------+

GREATEST

  • 命令格式

    greatest(<var1>, <var2>[,...])
  • 命令說明

    返回輸入參數中的最大值。

  • 參數說明

    var1var2:必填。BIGINT、DOUBLE、DECIMAL、DATETIME或STRING類型。

  • 傳回值說明

    • 返回輸入參數中的最大值。當不存在隱式轉換時,傳回值與輸入參數資料類型相同。

    • NULL為最小值。

    • 當輸入參數資料類型不相同時,DOUBLE、BIGINT、DECIMAL、STRING之間的比較會轉換為DOUBLE類型;STRING、DATETIME的比較會轉換為DATETIME類型。不允許其他的隱式轉換。

    • set odps.sql.hive.compatible=true;時,任意參數輸入為NULL,返回結果為NULL。

HASH

  • 命令格式

    • 當MaxCompute專案為Hive相容模式時,命令格式如下。

      int hash(<value1>, <value2>[, ...]);
    • 當MaxCompute專案非Hive相容模式時,命令格式如下。

      bigint hash(<value1>, <value2>[, ...]);
  • 命令說明

    value1value2進行散列運算得到一個Hash值。

  • 參數說明

    value1value2:必填。待計算Hash值的參數,各參數的類型可以不相同。Hive及非Hive相容模式下支援的資料類型不相同,具體如下:

    • Hive相容模式:TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、BOOLEAN、STRING、CHAR、VARCHAR、DATETIME、DATE。

    • 非Hive相容模式:BIGINT、DOUBLE、BOOLEAN、STRING、DATETIME。

    說明

    對於兩個相同的輸入,返回的Hash值一定是相同的。但是反過來,如果兩個Hash值相同,並不保證輸入值一定相等(可能出現雜湊碰撞)。

  • 傳回值說明

    返回INT或BIGINT類型。當輸入參數為空白或NULL時,返回結果為0。

  • 樣本

    • 樣本1:計算相同資料類型的輸入參數的Hash值。命令樣本如下。

      --返回66。
      select hash(0, 2, 4);
    • 樣本2:計算不同資料類型的輸入參數的Hash值。命令樣本如下。

      --返回97。
      select hash(0, 'a');
    • 樣本3:任一輸入參數為空白或NULL。命令樣本如下。

      --返回0。
      select hash(0, null);
      --返回0。
      select hash(0, '');

IF

  • 命令格式

    if(<testCondition>, <valueTrue>, <valueFalseOrNull>)
  • 命令說明

    判斷testCondition是否為真。如果為真,返回valueTrue的值,否則返回valueFalseOrNull的值。

  • 參數說明

    • testCondition:必填。要判斷的運算式,BOOLEAN類型。

    • valueTrue:必填。運算式testCondition為True時,返回的值。

    • valueFalseOrNull:運算式testCondition為False時,返回的值,可以設為NULL。

  • 傳回值說明

    傳回值類型和參數valueTruevalueFalseOrNull的資料類型一致。

  • 樣本

    --返回200。
    select if(1=2, 100, 200); 

LEAST

  • 命令格式

    least(<var1>, <var2>[,...])
  • 命令說明

    返回輸入參數中的最小值。

  • 參數說明

    var :必填。輸入參數值。BIGINT、DOUBLE、DECIMAL、DATETIME或STRING類型。

  • 傳回值說明

    • 輸入參數中的最小值。當不存在隱式轉換時,傳回值與輸入參數類型相同。

    • 當有類型轉換時,DOUBLE、BIGINT、STRING之間的轉換返回DOUBLE類型;STRING、DATETIME之間的轉換返回DATETIME類型;DECIMAL、DOUBLE、BIGINT和STRING之間的轉換返回DECIMAL類型。不允許其他的隱式類型轉換。

    • NULL為最小值。

    • 如果所有參數值都為NULL,返回結果為NULL。

  • 樣本

    --返回2。
    select least(5, 2, 7);

MAX_PT

  • 命令格式

    max_pt(<table_full_name>)
  • 命令說明

    返回分區表的一級分區中有資料的分區的最大值,按字母排序,且讀取該分區下對應的資料。

    max_pt函數也可以使用標準SQL實現,select * from table where pt = max_pt("table");可以改寫為select * from table where pt = (select max(pt) from table);

    說明

    MaxCompute未提供min_pt函數。如果您需要擷取分區表中有資料的最小分區,無法使用SQL語句select * from table where pt = min_pt("table");實現與max_pt函數類似的功能,但可以使用標準SQL語句select * from table where pt = (select min(pt) from table);實現相同的效果。

  • 參數說明

    table_full_name:必填。STRING類型。指定表名。必須對錶具有讀許可權。

  • 傳回值說明

    返回最大的一級分區的值。

    說明

    如果只是用alter table的方式新加了一個分區,但是此分區中並無任何資料,則此分區不會作為傳回值。

  • 樣本

    • 樣本1:例如tbl是分區表,該表對應的分區為20120901和20120902,且都有資料。則以下語句中max_pt傳回值為‘20120902’。MaxCompute SQL語句會讀出pt=‘20120902’分區下的資料。命令樣本如下。

      select * from tbl where pt=max_pt('myproject.tbl');
      --等效於如下語句。
      select * from tbl where pt = (select max(pt) from myproject.tbl);
    • 樣本2:在多級分區情境,使用標準SQL實現擷取最大分區下的資料。命令樣本如下。

      select * from table where pt1 = (select max(pt1) from table) and pt2 = (select max(pt2) from table where pt1 = (select max(pt1) from table));

NULLIF

  • 命令格式

    T nullif(T <expr1>, T <expr2>)
  • 命令說明

    比較expr1expr2的值,二者相等時返回NULL,否則返回expr1

  • 參數說明

    expr1expr2:必填。任意類型的運算式。T指代輸入資料類型,可以是MaxCompute支援的所有資料類型。

  • 傳回值說明

    返回NULL或expr1

  • 樣本

    --返回2。
    select nullif(2, 3);
    --返回NULL。
    select nullif(2, 2);
    --返回3。
    select nullif(3, null);

NVL

  • 命令格式

    nvl(T <value>, T <default_value>)
  • 命令說明

    如果value值為NULL,返回default_value,否則返回value。兩個參數的資料類型必須一致。

  • 參數說明

    • value:必填。輸入參數。T指代輸入資料類型,可以是MaxCompute支援的所有資料類型。

    • default_value:必填。替換後的值。必須與value的資料類型保持一致。

  • 樣本

    例如表t_data的3個列分別為c1 stringc2 bigintc3 datetime。表中資料如下。

    +----+------------+------------+
    | c1 | c2 | c3 |
    +----+------------+------------+
    | NULL | 20 | 2017-11-13 05:00:00 |
    | ddd | 25 | NULL |
    | bbb | NULL | 2017-11-12 08:00:00 |
    | aaa | 23 | 2017-11-11 00:00:00 |
    +----+------------+------------+

    通過nvl函數將c1中為NULL的值輸出為00000,c2中為NULL的值輸出為0,c3中為NULL的值輸出為-,命令樣本如下。

    select nvl(c1,'00000'),nvl(c2,0),nvl(c3,'-') from nvl_test;
    --返回結果如下。
    +-----+------------+-----+
    | _c0 | _c1 | _c2 |
    +-----+------------+-----+
    | 00000 | 20 | 2017-11-13 05:00:00 |
    | ddd | 25 | - |
    | bbb | 0 | 2017-11-12 08:00:00 |
    | aaa | 23 | 2017-11-11 00:00:00 |
    +-----+------------+-----+

ORDINAL

  • 命令格式

    ordinal(bigint <nth>, <var1>, <var2>[,...])
  • 命令說明

    將輸入變數按從小到大排序後,返回排在第nth位的值。

  • 參數說明

    • nth:必填,位置序號,起始從1開始。BIGINT類型。指定要返回的位置值為NULL時,返回NULL。

    • var:必填,待排序的值。BIGINT、DOUBLE、DATETIME或STRING類型。

  • 傳回值說明

    • 排在第nth位的值,當不存在隱式轉換時傳回值與輸入參數資料類型相同。

    • 當有類型轉換時,DOUBLE、BIGINT、STRING之間的轉換返回DOUBLE類型;STRING、DATETIME之間的轉換返回DATETIME類型。不允許其他的隱式轉換。

    • NULL為最小值。

  • 樣本

    --返回3。
    SELECT ordinal(3, 1, 3, 7, 5, 2, 4, 6); 

PARTITION_EXISTS

  • 命令格式

    boolean partition_exists(string <table_name>, string... <partitions>)
  • 命令說明

    查詢指定的分區是否存在。

  • 參數說明

    • table_name:必填。表名稱,STRING類型。表名稱中可以指定專案空間名稱,例如my_proj.my_table。如果不指定專案空間名稱則預設為當前專案空間。

    • partitions : 必填。分區名稱,STRING類型。按照表分區列的順序依次寫出分區值,分區值數目必須與分區列數目一致。

  • 傳回值說明

    返回BOOLEAN類型。如果指定的分區存在返回True,否則返回False。

  • 樣本

    --建立分區表foo。
    create table foo (id bigint) partitioned by (ds string, hr string);
    --為分區表foo新增分區。
    alter table foo add partition (ds='20190101', hr='1');
    --查詢分區ds='20190101'和hr='1'是否存在。返回結果為True。
    select partition_exists('foo', '20190101', '1');

SAMPLE

  • 命令格式

    boolean sample(<x>, <y>, [<column_name1>, <column_name2>[,...]])
  • 命令說明

    基於所有讀入的column_name的值,系統根據xy的設定做採樣,並過濾掉不滿足採樣條件的行。

  • 參數說明

    • xyx必填。BIGINT類型,取值範圍為大於0的整型常量。表示雜湊為x份,取第y份。

      y可選,省略時預設取第一份。如果省略參數中的y,則必須同時省略column_name

      xy為其它類型或小於等於0時拋出異常,如果y大於x時也返回異常。xy任一輸入為NULL時,返回NULL。

    • column_name:可選。採樣的目標列。該參數省略時將根據xy的值隨機採樣。任意類型,列的值可以為NULL。不做隱式類型轉換。如果column_name為常量NULL,則返回報錯。

      說明
      • 為避免NULL值帶來的資料扭曲,對於column_name中為NULL的值,會在x份中進行均勻雜湊。如果不指定column_name,則資料量比較少時輸出不一定均勻,在這種情況下建議指定column_name,以獲得較好的輸出結果。

      • 目前僅支援對如下資料類型的列做隨機採樣:bigint、datetime、boolean、double、string、binary、char、varchar。

  • 傳回值說明

    返回BOOLEAN類型。

  • 樣本

    例如存在表tbla,表內有列名為cola的列。

    --表示數值會根據cola hash為4份,取第1份。傳回值為True。
    select * from tbla where sample (4, 1 , cola);
    --表示數值會對每行資料做隨機雜湊分配為4份,取第2份。傳回值為True。
    select * from tbla where sample (4, 2);

SHA

  • 命令格式

    string sha(string|binary <expr>)
  • 命令說明

    計算字串或者二進位類型的expr的SHA-1雜湊值,並以十六進位字串格式返回。

  • 參數說明

    expr:必填。STRING或BINARY類型。

  • 傳回值說明

    返回STRING類型。輸入參數為NULL時,返回結果為NULL。

  • 樣本

    • 樣本1:計算字串ABC的SHA雜湊值。命令樣本如下。

      --返回3c01bdbb26f358bab27f267924aa2c9a03fcfdb8。
      select sha('ABC');
    • 樣本2:輸入參數為NULL。命令樣本如下。

      --返回NULL。
      select sha(null);

SHA1

  • 命令格式

    string sha1(string|binary <expr>)
  • 命令說明

    計算字串或者二進位類型的expr的SHA-1雜湊值,並以十六進位字串格式返回。

  • 參數說明

    expr:必填。STRING或BINARY類型。

  • 傳回值說明

    返回STRING類型。輸入參數為NULL時,返回結果為NULL。

  • 樣本

    • 樣本1:計算字串ABC的SHA-1雜湊值。命令樣本如下。

      --返回3c01bdbb26f358bab27f267924aa2c9a03fcfdb8。
      select sha1('ABC');
    • 樣本2:輸入參數為NULL。命令樣本如下。

      --返回NULL。
      select sha1(null);

SHA2

  • 命令格式

    string sha2(string|binary <expr>, bigint <number>)
  • 命令說明

    計算字串或者二進位類型的expr的SHA-2雜湊值,以指定的number格式返回。

  • 參數說明

    • expr:必填。STRING或BINARY類型。

    • number:必填。BIGINT類型。雜湊位長,取值必須是224、256、384、512、0(同256)。

  • 傳回值說明

    返回STRING類型。返回規則如下:

    • 任一輸入參數為NULL時,返回結果為NULL。

    • 如果number取值不在允許的取值範圍內,返回結果為NULL。

  • 樣本

    • 樣本1:計算字串ABC的SHA-2雜湊值。命令樣本如下。

      --返回b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78。
      select sha2('ABC', 256);
    • 樣本2:任一輸入參數為NULL。命令樣本如下。

      --返回NULL。
      select sha2('ABC', null);

STACK

  • 命令格式

    stack(n, expr1, ..., exprk) 
  • 命令說明

    expr1, ..., exprk分割為n行,除非另有說明,否則輸出結果使用預設的列名col0、col1...

  • 參數說明

    • n:必填。分割的行數。

    • expr:必填。待分割的參數。expr1, ..., exprk必須是整型,且參數數目必須是n的整數倍,需要能分割為完整的n行,否則返回報錯。

  • 傳回值說明

    返回n行,列數為參數數量除以n的商的資料集。

  • 樣本

    --將1, 2, 3, 4, 5, 6排為3行。
    select stack(3, 1, 2, 3, 4, 5, 6);
    --返回結果如下。
    +------+------+
    | col0 | col1 |
    +------+------+
    | 1    | 2    |
    | 3    | 4    |
    | 5    | 6    |
    +------+------+
    
    --將'A',10,date '2015-01-01','B',20,date '2016-01-01'排為兩行.
    select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') as (col0,col1,col2);
    --返回結果如下。
    +------+------+------+
    | col0 | col1 | col2 |
    +------+------+------+
    | A    | 10   | 2015-01-01 |
    | B    | 20   | 2016-01-01 |
    +------+------+------+
    
    --將a、b、c、d排列為兩行,源表如果有多行,按行執行stack的分行處理。
    select stack(2,a,b,c,d) as (col,value)
    from values 
        (1,1,2,3,4),
        (2,5,6,7,8),
        (3,9,10,11,12),
        (4,13,14,15,null)
    as t(key,a,b,c,d);
    --返回結果如下。
    +------+-------+
    | col  | value |
    +------+-------+
    | 1    | 2     |
    | 3    | 4     |
    | 5    | 6     |
    | 7    | 8     |
    | 9    | 10    |
    | 11   | 12    |
    | 13   | 14    |
    | 15   | NULL  |
    +------+-------+
    
    --配合lateral view使用。
    select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20, date '2016-01-01') tf as col0,col1,col2;
    --返回結果如下。
    +------+------+------+
    | col0 | col1 | col2 |
    +------+------+------+
    | A    | 10   | 2015-01-01 |
    | B    | 20   | 2016-01-01 |
    +------+------+------+

STR_TO_MAP

  • 命令格式

    str_to_map([string <mapDupKeyPolicy>,] <text> [, <delimiter1> [, <delimiter2>]])
  • 命令說明

    使用delimiter1text分割成Key-Value對,然後使用delimiter2分割每個Key-Value對的Key和Value。

  • 參數說明

    • mapDupKeyPolicy:可選。STRING類型。指定出現重複Key時的處理方式。取值範圍如下:

      • exception:如果出現重複的Key,返回報錯。

      • last_win:如果出現重複的Key,後邊的值將覆蓋前邊的值。

      該參數也可以在Session層級通過odps.sql.map.key.dedup.policy參數進行設定,例如set odps.sql.map.key.dedup.policy=exception;,不設定時該參數預設值為last_win

      說明

      MaxCompute的行為實現優先以函數中mapDupKeyPolicy的取值為準,當函數未配置mapDupKeyPolicy時,以odps.sql.map.key.dedup.policy參數的取值為準。

    • text:必填。STRING類型,指被分割的字串。

    • delimiter1:可選。STRING類型,分隔字元,不指定時預設為英文逗號(,)。

    • delimiter2:可選。STRING類型,分隔字元,不指定時預設為等號(=)。

      說明

      當分隔字元是Regex或特殊字元時,要在前面加兩個反斜線(\\)來做轉義。特殊字元包括冒號(:)、英文句點(.)、問號(?)、加號(+)或星號(*)。

  • 傳回值說明

    傳回值類型為map<string, string>。傳回值是textdelimiter1delimiter2分割後的結果。

  • 樣本

    --返回{test1:1, test2:2}。
    select str_to_map('test1&1-test2&2','-','&');
    --返回{test1:1, test2:2}。
    select str_to_map("test1.1,test2.2", ",", "\\.");
    --返回{test1:1, test2:3}。
    select str_to_map("test1.1,test2.2,test2.3", ",", "\\.");

TABLE_EXISTS

  • 命令格式

    boolean table_exists(string <table_name>)
  • 命令說明

    查詢指定的表是否存在。

  • 參數說明

    table_name:必填。表名稱。STRING類型。表名稱中可以指定專案名稱(例如my_proj.my_table)。如果不指定專案名稱則預設為當前專案。

  • 傳回值說明

    返回BOOLEAN類型。如果指定的表存在返回True,否則返回False。

  • 樣本

    --在select列表中使用。
    select if(table_exists('abd'), col1, col2) from src;

TRANS_ARRAY

  • 使用限制

    • 所有作為key的列必須位於前面,而要轉置的列必須放在後面。

    • 在一個select中只能有一個UDTF,不可以再出現其他的列。

    • 不可以與group bycluster bydistribute bysort by一起使用。

  • 命令格式

    trans_array (<num_keys>, <separator>, <key1>,<key2>,…,<col1>,<col2>,<col3>) as (<key1>,<key2>,...,<col1>, <col2>)
  • 命令說明

    將一行資料轉換為多行的UDTF,將列中儲存的以固定分隔字元格式分隔的數群組轉換為多行。

  • 參數說明

    • num_keys:必填。 BIGINT類型常量,值必須>=0。在轉為多行時作為轉置key的列的個數。

    • separator:必填。STRING類型常量,用於將字串拆分成多個元素的分隔字元。為空白時返回報錯。

    • keys:必填。轉置時作為key的列, 個數由num_keys指定。如果num_keys指定所有的列都作為key(即num_keys等於所有列的個數),則只返回一行。

    • cols: 必填。要轉為行的數組,keys之後的所有列視為要轉置的數組,必須為STRING類型,儲存的內容是字串格式的數組,例如Hangzhou;Beijing;Shanghai,是以分號(;)分隔的數組。

  • 傳回值說明

    返迴轉置後的行,新的列名由as指定。作為key的列類型保持不變,其餘所有的列是STRING類型。拆分成的行數以個數多的數組為準,不足的補NULL。

  • 樣本

    • 樣本1:例如表t_table中的資料如下。

      +----------+----------+------------+
      | login_id | login_ip | login_time |
      +----------+----------+------------+
      | wangwangA | 192.168.0.1,192.168.0.2 | 20120101010000,20120102010000 |
      | wangwangB | 192.168.45.10,192.168.67.22,192.168.6.3 | 20120111010000,20120112010000,20120223080000 |
      +----------+----------+------------+
      --執行SQL。
      select trans_array(1, ",", login_id, login_ip, login_time) as (login_id,login_ip,login_time) from t_table;
      --返回結果如下。
      +----------+----------+------------+
      | login_id | login_ip | login_time |
      +----------+----------+------------+
      | wangwangB | 192.168.45.10 | 20120111010000 |
      | wangwangB | 192.168.67.22 | 20120112010000 |
      | wangwangB | 192.168.6.3 | 20120223080000 |
      | wangwangA | 192.168.0.1 | 20120101010000 |
      | wangwangA | 192.168.0.2 | 20120102010000 |
      +----------+----------+------------+
      
      --如果表中的資料如下所示。
      Login_id LOGIN_IP LOGIN_TIME 
      wangwangA 192.168.0.1,192.168.0.2 20120101010000
      --會對數組中不足的資料補NULL。 
      Login_id Login_ip Login_time 
      wangwangA 192.168.0.1 20120101010000
      wangwangA 192.168.0.2 NULL
    • 樣本2:例如表mf_fun_array_test_t中的資料如下。

      +------------+------------+------------+------------+
      | id         | name       | login_ip   | login_time |
      +------------+------------+------------+------------+
      | 1          | Tom        | 192.168.100.1,192.168.100.2 | 20211101010101,20211101010102 |
      | 2          | Jerry      | 192.168.100.3,192.168.100.4 | 20211101010103,20211101010104 |
      +------------+------------+------------+------------+
      
      --用兩個key,id和name進行轉數組,執行SQL。
      select trans_array(2, ",", Id,Name, login_ip, login_time) as (Id,Name,login_ip,login_time) from mf_fun_array_test_t;
      --返回結果如下,已經對key,id和name進行分組拆解。
      +------------+------------+------------+------------+
      | id         | name       | login_ip   | login_time |
      +------------+------------+------------+------------+
      | 1          | Tom        | 192.168.100.1 | 20211101010101 |
      | 1          | Tom        | 192.168.100.2 | 20211101010102 |
      | 2          | Jerry      | 192.168.100.3 | 20211101010103 |
      | 2          | Jerry      | 192.168.100.4 | 20211101010104 |
      +------------+------------+------------+------------+

TRANS_COLS

  • 使用限制

    • 所有作為key的列必須處在前面,而要轉置的列必須放在後面。

    • 在一個select中只能有一個UDTF,不可以再出現其他的列。

  • 命令格式

    trans_cols (<num_keys>, <key1>,<key2>,…,<col1>, <col2>,<col3>) as (<idx>, <key1>,<key2>,…,<col1>, <col2>)
  • 命令說明

    將一行資料轉換為多行資料的UDTF,將不同的列拆分為不同的行。

  • 參數說明

    • num_keys:必填。BIGINT類型常量,值必須>=0。在轉為多行時作為轉置key的列的個數。

    • keys:必填。轉置時作為key的列, 個數由num_keys指定。如果num_keys指定所有的列都作為key(即num_keys等於所有列的個數),則只返回一行。

    • idx:必填。轉換後的行號。

    • cols:必填。 要轉換為行的列。

  • 傳回值說明

    返迴轉置後的行,新的列名由as指定。輸出的第一列是轉置的下標,下標從1開始。作為key的列類型保持不變,其餘所有的列與原來的資料類型一致。

  • 樣本

    例如表t_table中的資料如下。

    +----------+----------+------------+
    | Login_id | Login_ip1 | Login_ip2 |
    +----------+----------+------------+
    | wangwangA | 192.168.0.1 | 192.168.0.2 |
    +----------+----------+------------+
    --執行SQL。
    select trans_cols(1, login_id, login_ip1, login_ip2) as (idx, login_id, login_ip) from t_table;
    --返回結果如下。
    idx    login_id    login_ip
    1    wangwangA    192.168.0.1
    2    wangwangA    192.168.0.2

UNBASE64

  • 命令格式

    binary unbase64(string <str>)
  • 命令說明

    將BASE64編碼格式字串str轉換為二進位表示格式。

  • 參數說明

    str:必填。STRING類型。待轉換BASE64編碼格式字串。

  • 傳回值說明

    返回BINARY類型。輸入參數為NULL時,返回結果為NULL。

  • 樣本

    • 樣本1:將字串YWxpYmFiYQ==轉換為二進位表示值。命令樣本如下:

      --返回alibaba。
      select unbase64('YWxpYmFiYQ==');
    • 樣本2:輸入參數為NULL。命令樣本如下:

      --返回NULL。
      select unbase64(null);

UNIQUE_ID

  • 命令格式

    string unique_id()
  • 命令說明

    返回一個隨機的唯一ID,格式樣本為29347a88-1e57-41ae-bb68-a9edbdd9****_1。該函數的運行效率高於UUID,且返回的ID較長,相較於UUID多一個底線(_)和一個數字編號,例如_1

UUID

  • 命令格式

    string uuid()
  • 命令說明

    返回一個隨機ID,格式樣本為29347a88-1e57-41ae-bb68-a9edbdd9****

    說明

    UUID返回的是一個隨機的全域ID,其重複的機率很小。