內建函數概述 - MaxCompute

MaxCompute自身預置了諸多函數，可以滿足大部分業務情境的資料處理需求。本文為您介紹MaxCompute提供的函數類型及函數使用相關說明。

背景資訊

MaxCompute預置的函數類型如下。

函數類型	說明
日期與時間函數	支援處理DATE、DATETIME、TIMESTAMP等日期類型資料，實現加減日期、計算日期差值、提取日期欄位、擷取目前時間、轉換日期格式等業務處理能力。
數學函數	支援處理BIGINT、DOUBLE、DECIMAL、FLOAT等數實值型別資料，實現轉換進位、數學運算、四捨五入、擷取隨機數等業務處理能力。
視窗函數	支援在指定的開窗列中，實現求和、求最大最小值、求平均值、求中間值、數值排序、數值位移、抽樣等業務處理能力。
彙總函式	支援將多條輸入記錄彙總成一條輸出值，實現求和、求平均值、求最大最小值、求平均值、參數彙總、字串串連等業務處理能力。
字串函數	支援處理STRING類型字串，實現截取字串、替換字串、尋找字串、轉換大小寫、轉換字串格式等業務處理能力。
複雜類型函數	支援處理MAP、ARRAY、STRUCT及JSON類型資料，實現去重元素、彙總元素、元素排序、合并元素等業務處理能力。其中JSON函數的使用限制請參見JSON函數的使用限制。
加密函數	支援處理STRING、BINARY類型的表資料，實現加密、解密等業務處理能力。
其他函數	除上述函數之外，提供支援其他業務情境的函數。

各類型函數與開源函數的對照關係，請參見與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函數的使用限制

目前支援的開發套件括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：將字串中每個單詞首字母（英文字元）轉化為大寫形式，其餘為小寫。