MaxCompute SQL允許資料類型之間的轉換,類型轉換方式包括顯式類型轉換和隱式類型轉換。
顯式類型轉換
顯式類型轉換是用CAST將一種資料類型的值轉換為另一種類型的值的行為,在MaxCompute SQL中支援的顯式類型轉換,如下表所示:
From/To | BIGINT | DOUBLE | STRING | DATETIME | BOOLEAN | DECIMAL | FLOAT |
---|---|---|---|---|---|---|---|
BIGINT | – | Y | Y | N | Y | Y | Y |
DOUBLE | Y | – | Y | N | Y | Y | Y |
STRING | Y | Y | – | Y | Y | Y | Y |
DATETIME | N | N | Y | – | N | N | N |
BOOLEAN | Y | Y | Y | N | – | Y | Y |
DECIMAL | Y | Y | Y | N | Y | – | Y |
FLOAT | Y | Y | Y | N | Y | Y | - |
其中,Y 表示可以轉換,N 表示不可以轉換,– 表示不需要轉換。
樣本如下:
select cast(user_id as double) as new_id from user;
select cast('2015-10-01 00:00:00' as datetime) as new_date from user;
说明
- 將Double類型轉為Bigint類型時,小數部分會被截斷,例如
cast(1.6 as bigint) = 1
。 - 滿足Double格式的String類型轉換為Bigint時,會先將String轉換為Double,再將Double轉換為Bigint,因此,小數部分會被截斷,例如
cast(“1.6” as bigint) = 1
。 - 滿足Bigint格式的String類型可以被轉換為Double類型,小數點後保留一位,例如
cast(“1” as double) = 1.0
。 - 不支援的顯式類型轉換會導致異常。
- 如果在執行時轉換失敗,報錯退出。
- 日期類型轉換時採用預設格式yyyy-mm-dd hh:mi:ss,詳情請參見String類型與Datetime類型之間的轉換。
- 部分類型之間不可以通過顯式的類型轉換,但可以通過SQL內建函數進行轉換,例如從Boolean類型轉換到String類型,可使用函數to_char,詳情請參見TO_CHAR ,而to_date函數同樣支援從String類型到Datetime類型的轉換,詳情請參見TO_DATE。
- 關於cast的介紹請參見CAST。
- DECIMAL超出範圍,CAST STRING TO DECIMAL可能會出現最高位溢出報錯,最低位溢出截斷等情況。
隱式類型轉換及其範圍
隱式類型轉換是指在運行時,由MaxCompute依據上下文使用環境及類型轉換規則自動進行的類型轉換。MaxCompute支援的隱式類型轉換規則,如下表所示:
boolean | tinyint | smallint | int | bigint | float | string | varchar | timestamp | binary | |
---|---|---|---|---|---|---|---|---|---|---|
boolean to | Y | N | N | N | N | N | N | N | N | N |
tinyint to | N | Y | Y | Y | Y | Y | Y | Y | N | N |
smallint to | N | N | Y | Y | Y | Y | Y | Y | N | N |
int to | N | N | Y | Y | Y | Y | Y | Y | N | – |
bigint to | N | N | N | N | Y | Y | Y | Y | N | N |
float to | N | N | N | N | Y | Y | Y | Y | N | – |
double to | N | N | N | N | N | N | Y | Y | N | N |
decimal to | N | N | N | N | N | N | Y | Y | N | N |
string tof | N | N | N | N | N | N | Y | Y | N | N |
varchar to | N | N | N | N | Y | Y | N | N | – | – |
timestamp to | N | N | N | N | N | N | Y | Y | Y | N |
binary to | N | N | N | N | N | N | N | N | N | Y |
其中,T表示可以轉換,F表示不可以轉換。
说明
- MaxCompute2.0新增了DECIMAL類型與Datetime的常量定義方式,100BD就是數值為100的DECIMAL,Datetime
2017-11-11 00:00:00
就是Datetime類型的常量。常量定義的方便之處在於可以直接用到values子句和values表中。 - 舊版MaxCompute中,因為曆史原因,Double可以隱式的轉換為Bigint,這個轉換潛在可能有資料丟失,一般資料庫系統都不允許。
常見用法如下所示:
select user_id+age+'12345',
concat(user_name,user_id,age)
from user;
说明
- 不支援的隱式類型轉換會導致異常。
- 如果在執行時轉換失敗,也會導致異常。
- 由於隱式類型轉換是MaxCompute依據上下文使用環境自動進行的類型轉換,因此推薦您在類型不符時,顯式的用cast進行轉換。
- 隱式類型轉換規則是有發生範圍的。在某些範圍中,只有一部分規則可以生效。詳情請參見隱式類型轉換的範圍。
- 關係運算子作用下的隱式轉換
關係運算子包括=、<>、<、<=、>、>=、IS NULL、IS NOT NULL、LIKE、RLIKE和IN。由於LIKE、RLIKE和IN的隱式類型轉換規則不同於其他關係運算子,將單獨拿出章節對這三種關係運算子做出說明。本小節的說明不包含這三種特殊的關係運算子。
當不同類型的資料共同參與關係運算時,按照下述原則進行隱式類型轉換。From/To Bigint Double String Datetime Boolean Decimal Bigint – Double Double N N Decimal Double Double – Double N N Decimal String Double Double – Datetime N Decimal Datetime N N Datetime – N N Boolean N N N N – N Decimal Decimal Decimal Decimal N N - 说明- 如果進行比較的兩個類型間不能進行隱式類型轉換,則該關係運算不能完成,報錯退出。
- 關係運算子的更多詳情,請參見關係操作符。
- 特殊的關係運算子作用下的隱式轉換
特殊的關係運算子包括LIKE、RLIKE和IN。
- LIKE和RLIKE的使用方式,如下所示:
source like pattern; source rlike pattern;
兩者在隱式類型轉換中的注意事項,如下所示:- LIKE和RLIKE的source和pattern參數均僅接受String類型。
-
其他類型不允許參與運算,也不能進行到String類型的隱式類型轉換。
-
IN的使用方式,如下所示:
key in (value1, value2, …)
In的隱式轉換規則:- In右側的value值列表中的資料類型必須一致。
- 當key與values之間比較時,若Bigint、Double、String之間比較,統一轉為Double,若Datetime和String之間比較,統一轉為Datetime。除此之外不允許其它類型之間的轉換。
- LIKE和RLIKE的使用方式,如下所示:
- 算術運算子作用下的隱式轉換
算術運算子包括+、-、* 、/、%,其隱式轉換規則,如下所示:
- 只有String、Bigint、Double和Decimal才能參與算術運算。
- String在參與運算前會進行隱式類型轉換到Double。
- Bigint和Double共同參與計算時,會將Bigint隱式轉換為Double。
- 日期型和布爾型不允許參與算數運算。
- 邏輯運算子作用下的隱式轉換
邏輯運算子包括and、or和not,其隱式轉換規則,如下所示:
- 只有Boolean才能參與邏輯運算。
- 其他類型不允許參與邏輯運算,也不允許其他類型的隱式類型轉換。
內建函數涉及到隱式轉換
MaxCompute SQL提供了大量的系統函數,以方便您對任意行的一列或多列進行計算,輸出任意種的資料類型。其隱式轉換規則,如下所示:
- 在調用函數時,如果輸入參數的資料類型與函數定義的參數資料類型不一致,把輸入參數的資料類型轉換為函數定義的資料類型。
- 每個MaxCompute SQL內建函數的參數對於允許的隱式類型轉換的要求不同,詳情請參見內建函數。
CASE WHEN作用下的隱式轉換
CASE WHEN的詳情介紹請參見CASE WHEN運算式。它的隱式轉換規則,如下所示:
- 如果傳回型別只有Bigint、Double,統一轉為Double。
- 如果傳回型別中有String類型,統一轉為String,如果不能轉則報錯(如Boolean類型)。
- 除此之外不允許其它類型之間的轉換。
String與Datetime類型之間的轉換
MaxCompute支援String類型和Datetime類型之間的相互轉換。轉換時使用的格式為yyyy-mm-dd hh:mi:ss
。
單位 | 字串(忽略大小寫) | 有效範圍 |
---|---|---|
年 | yyyy | 0001 - 9999 |
月 | mm | 01 - 12 |
日 | dd | 01 - 28,29,30,31 |
時 | hh | 00 - 23 |
分 | mi | 00 - 59 |
秒 | ss | 00 - 59 |
说明
- 各個單位的範圍中,如果首位為0,不可省略,例如
2014-1-9 12:12:12
就是非法的Datetime格式,無法從這個String類型資料轉換為Datetime類型,必須寫為2014-01-09 12:12:12
。 - 只有符合上述格式描述的String類型才能夠轉換為Datetime類型,例如
cast(“2013-12-31 02:34:34” as datetime)
,將會把String類型2013-12-31 02:34:34
轉換為Datetime類型。同理,Datetime轉換為String時,預設轉換為yyyy-mm-dd hh:mi:ss的格式。
類似於下面的轉換嘗試,將會失敗導致異常,如下所示:
cast("2013/12/31 02/34/34" as datetime)
cast("20131231023434" as datetime)
cast("2013-12-31 2:34:34" as datetime)
dd部分的閾值上限取決於月份實際擁有的天數,如果超出對應月份實際擁有的天數,將會導致異常退出,如下所示:
cast("2013-02-29 12:12:12" as datetime) -- 異常返回,2013年2月沒有29日
cast("2013-11-31 12:12:12" as datetime) -- 異常返回,2013年11月沒有31日
MaxCompute提供了TO_DATE函數,用以將不滿足日期格式的String類型資料轉換為Datetime類型。詳情請參見TO_DATE。