MaxCompute：內建函數概述

注意事項

各類型函數與開源函數的對照關係，請參見與Hive、MySQL、Oracle內建函數對照表。

日期函數

MaxCompute SQL提供了如下日期函數，您可以根據實際需要選擇合適的日期函數，完成日期計算、日期轉換。

數學函數

視窗函數

• 視窗函數文法

  視窗函數的文法聲明如下。

  <function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
  <function_name>([distinct][<expression> [, ...]]) over <window_name>

  • function_name：內建視窗函數、彙總函式或使用者自訂彙總函式UDAF。

  • expression：函數格式，具體格式以實際函數文法為準。

  • windowing_definition：視窗定義。詳細文法格式請參見windowing_definition部分。

  • window_name：視窗名稱。您可以使用window關鍵字自訂視窗，為windowing_definition定義名稱。自訂語句（named_window_def）如下：

    window <window_name> as (<window_definition>)

    自訂語句在SQL中的位置如下：

    select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]

• windowing_definition

  windowing_definition的文法聲明如下。

  --partition_clause：
  [partition by <expression> [, ...]]
  --orderby_clause：
  [order by <expression> [asc|desc][nulls {first|last}] [, ...]]
  [<frame_clause>]

  在SELECT語句中加入視窗函數，計算視窗函數的結果時，資料會按照視窗定義中的partition by和order by語句進行分區和排序。如果沒有partition by語句，則僅有一個分區，包含全部資料。如果沒有order by語句，則分區內的資料會按照任意順序排布，最終產生一個確定的資料流。之後對於每一行資料（當前行），會按照視窗定義中的frame_clause從資料流中截取一段資料，構成當前行的視窗。視窗函數會根據視窗中包含的資料，計算得到視窗函數針對當前行對應的輸出結果。

  • partition by <expression> [, ...]：可選。指定分區。分區列的值相同的行被視為在同一個視窗內。詳細格式請參見表操作。

  • order by <expression> [asc|desc][nulls {first|last}] [, ...]：可選。指定資料在一個視窗內如何排序。

    說明

    當遇到相同的order by值時，排序結果不穩定。為減少隨機性，應當儘可能保持order by值的唯一性。

  • frame_clause：可選。用於確定資料邊界，更多frame_clause資訊，請參見frame_clause部分。

• frame_clause

  frame_clause的文法聲明如下。

  --格式一。
  {ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
  --格式二。
  {ROWS|RANGE|GROUPS} between <frame_start> and <frame_end> [<frame_exclusion>]

  frame_clause是一個閉區間，用於確定資料邊界，包含frame_start和frame_end位置的資料行。

  • ROWS|RANGE|GROUPS：必填。frame_clause的類型，各類型的frame_start和frame_end實現規則不相同。其中：

    • ROWS類型：通過資料行數確定資料邊界。

    • RANGE類型：通過比較order by列值的大小關係來確定資料邊界。一般在視窗定義中會指定order by，未指定order by時，一個分區中的所有資料行具有相同的order by列值。NULL與NULL被認為是相等的。

    • GROUPS：一個分區中所有具有相同order by列值的資料群組成一個GROUP。未指定order by時，分區中的所有資料群組成一個GROUP。NULL與NULL被認為是相等的。

  • frame_start、frame_end：表示視窗的起始和終止邊界。frame_start必填。frame_end可選，省略時預設值為CURRENT ROW。

    frame_start確定的位置必須在frame_end確定的位置的前面，或者等於frame_end的位置，即frame_start相比frame_end更靠近分區頭部。分區頭部是指資料按視窗定義中的order by語句排序之後第1行資料的位置。ROWS、RANGE、GROUPS類型對應的取值範圍及實現邏輯如下。

    frame_clause類型	frame_start/frame_end取值	說明
    ROWS、RANGE、GROUPS	UNBOUNDED PRECEDING	表示分區的第一行，從1開始計數。
    	UNBOUNDED FOLLOWING	表示分區的最後一行。
    ROWS	CURRENT ROW	指當前行的位置。每一行資料都會對應一個視窗函數的結果值，當前行是指在給哪一行資料計算視窗函數的結果。
    	offset PRECEDING	指從當前行位置，向分區頭部位置移動offset行的位置。例如0 PRECEDING指當前行，1 PRECEDING指前一行。offset必須為非負整數。
    	offset FOLLOWING	指從當前行位置，向分區尾部移動offset行的位置。例如0 FOLLOWING指當前行，1 FOLLOWING指下一行。offset必須為非負整數。
    RANGE	CURRENT ROW	作為frame_start時，指第一條與當前行具有相同order by列值的資料的位置。 作為frame_end時，指最後一條與當前行具有相同order by列值的資料的位置。
    	offset PRECEDING	frame_start和frame_end的位置與order by的順序相關。假設視窗按照X進行排序，Xi表示第i行資料對應的X值，Xc表示當前行資料對應X值。位置說明如下： 當order by為升序時： frame_start：指第一條滿足Xc - Xi <= offset資料的位置。 frame_end：指最後一條滿足Xc - Xi >= offset資料的位置。 當order by為降序時： frame_start：指第一條滿足Xi - Xc <= offset資料的位置。 frame_end：指最後一條滿足Xi - Xc >= offset資料的位置。 order by的列支援的資料類型為：TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、DATETIME、DATE、TIMESTAMP。 日期類型資料的offset文法如下： N：表示N天或N秒。非負整數。對於DATETIME和TIMESTAMP，表示N秒；對於DATE，表示N天。 interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}：表示N年/月/日/小時/分鐘/秒。例如INTERVAL '3' YEAR表示3年。 INTERVAL 'N-M' YEAR TO MONTH：表示N年M月。例如INTERVAL '1-3' YEAR TO MONTH表示1年3個月。 INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND：表示D天H小時M分鐘S秒N納秒。例如INTERVAL '1 2:3:4:5' DAY TO SECOND表示1天2小時3分鐘4秒5納秒。
    	offset FOLLOWING	frame_start和frame_end的位置與order by的順序相關。假設視窗按照X進行排序，Xi表示第i行資料對應的X值，Xc表示當前行資料對應X值。位置說明如下： 當order by為升序時： frame_start：指第一條滿足Xi - Xc >= offset資料的位置。 frame_end：指最後一條滿足Xi - Xc <= offset資料的位置。 當order by為降序時： frame_start：指第一條滿足Xc - Xi >= offset資料的位置。 frame_end：指最後一條滿足Xc - Xi <= offset資料的位置。
    GROUPS	CURRENT ROW	作為frame_start時，指當前行所屬GROUP的第一條資料。 作為frame_end時，指當前行所屬GROUP的最後一行資料。
    	offset PRECEDING	作為frame_start時，指從當前行所屬GROUP開始，朝分區頭部移動offset個GROUP之後，所在GROUP的第一條資料的位置。 作為frame_end時，指從當前行所屬GROUP開始，朝分區頭部移動offset個GROUP之後，所在GROUP的最後一條資料的位置。 說明 frame_start不能設定為UNBOUNDED FOLLOWING，frame_end不能設定為UNBOUNED PRECEDING。
    	offset FOLLOWING	作為frame_start時，指從當前行所屬GROUP開始，朝分區尾部移動offset個GROUP之後，所在GROUP的第一條資料的位置。 作為frame_end時，指從當前行所屬GROUP開始，朝分區尾部移動offset個GROUP之後，所在GROUP的最後一條資料的位置。 說明 frame_start不能設定為UNBOUNDED FOLLOWING，frame_end不能設定為UNBOUNED PRECEDING。

  • frame_exclusion：可選。用於從視窗中剔除一部分資料。取值範圍如下：

    • EXCLUDE NO OTHERS：表示不剔除任何資料。

    • EXCLUDE CURRENT ROW：表示剔除當前行。

    • EXCLUDE GROUP：表示剔除整個GROUP，即分區中與當前行具有相同order by值的所有資料。

    • EXCLUDE TIES：表示剔除整個GROUP，但保留當前行。

  預設frame_clause

  未顯示設定frame_clause時，MaxCompute會使用預設的frame_clause來決定視窗所包含資料的邊界。預設的frame_clause為：

  • 當開啟Hive相容模式（set odps.sql.hive.compatible=true;）時，預設的frame_clause如下，與大部分SQL系統相同。

    RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS

  • 當關閉Hive相容模式（set odps.sql.hive.compatible=false;），同時視窗定義中指定了order by語句，且視窗函數為AVG、COUNT、MAX、MIN、STDDEV、STEDEV_POP、STDDEV_SAMP或SUM時，會使用ROWS類型的預設frame_clause。

    ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS

  視窗邊界樣本

  假設表tbl結構為pid: bigint, oid: bigint, rid: bigint，表中包含如下資料：

  +------------+------------+------------+
  | pid        | oid        | rid        |
  +------------+------------+------------+
  | 1          | NULL       | 1          |
  | 1          | NULL       | 2          |
  | 1          | 1          | 3          |
  | 1          | 1          | 4          |
  | 1          | 2          | 5          |
  | 1          | 4          | 6          |
  | 1          | 7          | 7          |
  | 1          | 11         | 8          |
  | 2          | NULL       | 9          |
  | 2          | NULL       | 10         |
  +------------+------------+------------+

  您可以將如下SQL語句中的...替換為視窗定義語句windowing_definition來展示每一條資料所對應的視窗包含的資料列表：

  說明

  Window列為NULL時，不包含任何資料。

  • ROW類型視窗

    • 視窗定義1

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
      --SQL語句如下。
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

      返回結果如下：

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1]    |
      | 1          | NULL       | 2          | [1, 2] |
      | 1          | 1          | 3          | [1, 2, 3] |
      | 1          | 1          | 4          | [1, 2, 3, 4] |
      | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 2          | NULL       | 9          | [9]    |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

    • 視窗定義2

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
      --SQL語句如下。
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as window from tbl;

      返回結果如下：

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 1          | 3          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 1          | 4          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 2          | 5          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 2          | NULL       | 9          | [9, 10] |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

    • 視窗定義3

      partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
      --SQL語句如下。
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid ROWS between 1 FOLLOWING and 3 FOLLOWING) as window from tbl;

      返回結果如下：

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [2, 3, 4] |
      | 1          | NULL       | 2          | [3, 4, 5] |
      | 1          | 1          | 3          | [4, 5, 6] |
      | 1          | 1          | 4          | [5, 6, 7] |
      | 1          | 2          | 5          | [6, 7, 8] |
      | 1          | 4          | 6          | [7, 8] |
      | 1          | 7          | 7          | [8]    |
      | 1          | 11         | 8          | NULL   |
      | 2          | NULL       | 9          | [10]   |
      | 2          | NULL       | 10         | NULL   |
      +------------+------------+------------+--------+

    • 視窗定義4

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
      --SQL語句如下。
      select pid, 
      oid, 
      rid, 
      collect_list(rid) over(partition by pid order by 
      oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) as window from tbl;

      返回結果如下：

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | NULL   |
      | 1          | NULL       | 2          | [1]    |
      | 1          | 1          | 3          | [1, 2] |
      | 1          | 1          | 4          | [1, 2, 3] |
      | 1          | 2          | 5          | [1, 2, 3, 4] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
      | 2          | NULL       | 9          | NULL   |
      | 2          | NULL       | 10         | [9]    |
      +------------+------------+------------+--------+

    • 視窗定義5

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
      --SQL語句如下。
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP) as window from tbl;

      返回結果如下：

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | NULL   |
      | 1          | NULL       | 2          | NULL   |
      | 1          | 1          | 3          | [1, 2] |
      | 1          | 1          | 4          | [1, 2] |
      | 1          | 2          | 5          | [1, 2, 3, 4] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
      | 2          | NULL       | 9          | NULL   |
      | 2          | NULL       | 10         | NULL   |
      +------------+------------+------------+--------+

    • 視窗定義6

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
      --SQL語句如下。
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES) as window from tbl;                            

      返回結果如下：

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1]    |
      | 1          | NULL       | 2          | [2]    |
      | 1          | 1          | 3          | [1, 2, 3] |
      | 1          | 1          | 4          | [1, 2, 4] |
      | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 2          | NULL       | 9          | [9]    |
      | 2          | NULL       | 10         | [10]   |
      +------------+------------+------------+--------+

      對比本樣本與前一個樣本中rid為2、4、10的window結果，可以觀察到EXCLUDE CURRENT ROW與EXCLUDE GROUP的差異，即對於EXCLUDE GROUP，同一個分區中（pid相等），與當前行為相同oid的資料都被剔除了。

  • RANGE類型視窗

    • 視窗定義1

      partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
      --SQL語句如下。
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

      返回結果如下：

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1, 2] |
      | 1          | NULL       | 2          | [1, 2] |
      | 1          | 1          | 3          | [1, 2, 3, 4] |
      | 1          | 1          | 4          | [1, 2, 3, 4] |
      | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
      | 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
      | 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
      | 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 2          | NULL       | 9          | [9, 10] |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

      CURRENT ROW作為frame_end時，取與當前行具有相同order by值oid的最後一條資料，因此rid為1的記錄的window結果為[1, 2]。

    • 視窗定義2

      partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
      --SQL語句如下。
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING) as window from tbl;

      返回結果如下：

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
      | 1          | 1          | 3          | [3, 4, 5, 6, 7, 8] |
      | 1          | 1          | 4          | [3, 4, 5, 6, 7, 8] |
      | 1          | 2          | 5          | [5, 6, 7, 8] |
      | 1          | 4          | 6          | [6, 7, 8] |
      | 1          | 7          | 7          | [7, 8] |
      | 1          | 11         | 8          | [8]    |
      | 2          | NULL       | 9          | [9, 10] |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

    • 視窗定義3

      partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
      --SQL語句如下。
      
      select pid, 
             oid, 
             rid, 
      collect_list(rid) over(partition by pid order by 
      oid RANGE between 3 PRECEDING and 1 PRECEDING) as window from tbl;

      返回結果如下：

      +------------+------------+------------+--------+
      | pid        | oid        | rid        | window |
      +------------+------------+------------+--------+
      | 1          | NULL       | 1          | [1, 2] |
      | 1          | NULL       | 2          | [1, 2] |
      | 1          | 1          | 3          | NULL   |
      | 1          | 1          | 4          | NULL   |
      | 1          | 2          | 5          | [3, 4] |
      | 1          | 4          | 6          | [3, 4, 5] |
      | 1          | 7          | 7          | [6]    |
      | 1          | 11         | 8          | NULL   |
      | 2          | NULL       | 9          | [9, 10] |
      | 2          | NULL       | 10         | [9, 10] |
      +------------+------------+------------+--------+

      order by值oid為NULL的行，對於offset {PRECEDING|FOLLOWING}，只要offset不為UNBOUNDED，則作為frame_start，指向分區中第一條order by值為NULL的資料；作為frame_end，指向最後一條order by值為NULL的資料。

  • GROUPS類型視窗

    視窗定義如下：

    partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
    --SQL語句如下。
    select pid, 
           oid, 
           rid, 
    collect_list(rid) over(partition by pid order by 
    oid GROUPS between 2 PRECEDING and CURRENT ROW) as window from tbl;

    返回結果如下：

    +------------+------------+------------+--------+
    | pid        | oid        | rid        | window |
    +------------+------------+------------+--------+
    | 1          | NULL       | 1          | [1, 2] |
    | 1          | NULL       | 2          | [1, 2] |
    | 1          | 1          | 3          | [1, 2, 3, 4] |
    | 1          | 1          | 4          | [1, 2, 3, 4] |
    | 1          | 2          | 5          | [1, 2, 3, 4, 5] |
    | 1          | 4          | 6          | [3, 4, 5, 6] |
    | 1          | 7          | 7          | [5, 6, 7] |
    | 1          | 11         | 8          | [6, 7, 8] |
    | 2          | NULL       | 9          | [9, 10] |
    | 2          | NULL       | 10         | [9, 10] |
    +------------+------------+------------+--------+

彙總函式

字串函數

複雜類型函數

加密函數

其他函數

JSON函數的使用限制

• 目前支援的開發套件括odpscmd用戶端，Studio和DataWorks，暫不支援Dataphin等外圍生態。如果需要跟外部系統做組合使用時，請先確認後再使用。使用odpscmd用戶端和Studio時需要關注以下內容。

  使用odpscmd用戶端	使用Studio
  需要將用戶端升級到最新版本，否則無法使用desc json_table命令。 需要將用戶端安裝路徑下的conf\odps_config.ini檔案中的參數use_instance_tunnel設定為false，否則查詢會報錯。	Studio只支援查詢JSON類型的操作，不支援上傳、下載JSON類型資料。

• 如果表存在其他引擎讀取情況，比如Hologres等，目前不支援讀取JSON資料類型。

• 暫不支援對一張表新增JSON列。

• 暫不支援對JSON類型的比較操作，也不支援對JSON類型進行ORDER BY、GROUP BY或作為JOIN的key等。

• 目前JSON NUMBER的整數和小數分別使用BIGINT和DOUBLE類型進行儲存。當整數部分超出BIGINT範圍時會溢出，小數轉為DOUBLE時會損失精度。

• 產生JSON類型資料所用的字串裡不支援UNICODE\u0000。

• Java UDF和Python UDF暫不支援JSON類型。

• 目前JSON類型不支援Cluster表。

字串函數的使用限制

以下函數只支援英文字元的轉換：

• TRIM/RTRIM/LTRIM：trimChars只支援英文字元。

• REVERSE：在Hive模式下只支援英文字元。

• SOUNDEX：僅轉換英文字元。

• TOLOWER：將字串中的英文字元轉換為小寫形式。

• TOUPPER：將字串中的英文字元轉換為大寫形式。

• INITCAP：將字串中每個單詞首字母（英文字元）轉化為大寫形式，其餘為小寫。