內建函數概述 - MaxCompute

MaxCompute預置了多種函數，可滿足大部分資料處理需求。本文介紹MaxCompute提供的函數類型及函數使用相關說明。

函數類型

函數類型	說明
日期與時間函數	支援處理DATE、DATETIME、TIMESTAMP等日期類型資料，實現加減日期、計算日期差值、提取日期欄位、擷取目前時間、轉換日期格式等業務處理能力。
數學函數	支援處理BIGINT、DOUBLE、DECIMAL、FLOAT等數實值型別資料，實現進位轉換、數學運算、四捨五入、擷取隨機數等業務處理能力。
視窗函數	支援在指定的開窗列中，實現求和、求最大值和最小值、求平均值、求中間值、數值排序、數值位移、抽樣等業務處理能力。
彙總函式	支援將多條輸入記錄彙總成一條輸出值，實現求和、求平均值、求最大值和最小值、參數彙總、字串串連等業務處理能力。
HyperLogLog++函數	近似彙總函式。當資料量非常大時，HyperLogLog++ 函數能夠使用較小的記憶體空間快速完成資料去重，實現查詢加速。
字串函數	支援處理STRING類型字串，實現截取字串、替換字串、尋找字串、轉換大小寫、轉換字串格式等業務處理能力。其中字串函數的使用限制請參見字串函數使用限制。
ARRAY函數	支援處理ARRAY類型資料，實現數組構建、去重元素、彙總元素、元素排序、合并元素等業務處理能力。
MAP函數	支援處理MAP類型資料，實現索引值對提取、MAP構建、MAP合并等業務處理能力。
STRUCT函數	支援處理STRUCT類型資料，實現STRUCT數組展開、STRUCT構建等業務處理能力。
JSON函數	支援處理JSON類型資料，實現JSON欄位值提取、產生JSON對象/數組、插入/更新JSON資料、處理複雜結構資料等功能。其中JSON函數的使用限制請參見JSON函數使用限制。
加密函數	支援處理STRING、BINARY類型的表資料，實現加密、解密等業務處理能力。
網路函數	支援處理STRING、BINARY類型網路相關資料，實現IP地址格式轉換、URL解析、擷取網路遮罩等業務處理能力。
非結構化處理函數	支援通過多種方式對接數倉或資料湖中儲存的非結構化資料及其元資訊。
其他函數	除上述函數之外，還提供支援其他業務情境的函數。

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

注意事項

使用內建函數時，請注意以下事項：

內建函數的入參類型、入參數量、函數格式必須滿足函數文法要求，否則MaxCompute無法成功解析函數，SQL運行會報錯。
如果內建函數的入參涉及2.0新資料類型（例如TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY），您需要開啟2.0新資料類型開關，否則運行會報錯。開關開啟檔案如下：
- Session層級：您需要在SQL語句前加上SET odps.sql.type.system.odps2=true;，與SQL語句一起提交執行。該配置僅對本次啟動並執行SQL有效。
- Project層級：Project Owner可根據需要對MaxCompute專案進行設定，等待10~15分鐘後才會生效。該配置對後續啟動並執行所有SQL有效。
```
SETPROJECT odps.sql.type.system.odps2=true;
```
當MaxCompute專案開啟2.0新資料類型開關時，部分隱式類型轉換會被禁用，包括STRING轉換為BIGINT、STRING轉換為DATETIME、DOUBLE轉換為BIGINT、DECIMAL轉換為DOUBLE、DECIMAL轉換為BIGINT，這些轉換都有精度損失或報錯的風險。此時，您可以通過CAST函數執行強制轉換解決該問題，或關閉2.0新資料類型開關。
當自訂函數的名稱與內建函數的名稱相同時，自訂函數會覆蓋同名的內建函數。例如，MaxCompute中存在一個名稱為CONCAT的自訂函數，則系統預設會調用自訂的CONCAT，不會調用內建函數CONCAT。如果您希望調用內建函數，需要在內建函數前增加::符號，例如SELECT ::CONCAT('ab', 'c');。
如果MaxCompute專案的全域屬性設定不同，會導致內建函數結果不一致，您可以通過SETPROJECT;命令查看專案的全域屬性資訊。

JSON函數使用限制

SDK版本要求
- 僅支援Java SDK V0.44.0及以上版本。
- 僅支援PyODPS V0.11.4.1及以上版本。
表操作限制
- 暫不支援對一張表新增JSON列。
- 暫不支援Cluster表。
- 暫不支援Delta Table類型表。
SQL操作限制
- 暫不支援對JSON類型的比較操作。
- 暫不支援對JSON類型進行ORDER BY、GROUP BY或作為JOIN的key。
資料精度
- 目前JSON NUMBER的整數部分使用BIGINT類型儲存，當整數部分超出BIGINT範圍時會溢出。
- 目前JSON NUMBER的小數部分使用DOUBLE類型儲存，當小數部分轉換為DOUBLE時會損失精度。
字元限制：產生JSON類型資料所用的字串裡不支援UNICODE\u0000。
引擎相容性：如果表存在其他引擎讀取情況，比如Hologres等，目前不支援讀取JSON資料類型。
Java UDF和Python UDF暫不支援JSON類型。
JSON資料類型可以被嵌套使用，最多支援不超過20層的嵌套。
開發工具
目前支援的開發套件括odpscmd用戶端，Studio和DataWorks，暫不支援Dataphin等外圍生態。如果需要跟外部系統組合使用，請先確認後再使用。使用odpscmd用戶端時需要關注以下內容：
- 需要將用戶端升級到V0.46.5及以上版本，否則無法使用DESC json_table命令且無法通過Tunnel下載JSON類型資料。
- 需要將用戶端安裝路徑下的conf\odps_config.ini檔案中的參數use_instance_tunnel設定為false，否則查詢會報錯。

字串函數使用限制

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

TRIM/RTRIM/LTRIM：trimChars只支援英文字元。
REVERSE：在Hive模式下只支援英文字元。
SOUNDEX：僅轉換英文字元。
TOLOWER：將字串中的英文字元轉換為小寫形式。
TOUPPER：將字串中的英文字元轉換為大寫形式。
INITCAP：將字串中每個單詞首字母（英文字元）轉化為大寫形式，其餘為小寫。