MaxCompute SQL允許資料類型之間的轉換,類型轉換方式包括顯式類型轉換和隱式類型轉換。
顯式類型轉換
顯式類型轉換是通過CAST函數將一種資料類型的值轉換為另一種類型的值,在MaxCompute SQL中支援的顯式類型轉換,如下表所示。 關於CAST的介紹請參見CAST。
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表示不可以轉換,不涉及表示不需要轉換。不支援的顯式類型轉換會失敗並報錯退出。
樣本資料:
說明本文中使用樣本涉及
user
表的樣本,都可使用此樣本資料。create table if not exists user ( user_name string, user_id bigint, age bigint ); insert into user values ('zhangsan',111,20);
使用樣本:
SELECT CAST(user_id AS DOUBLE) AS new_id from user; SELECT CAST('2015-10-01 00:00:00' AS DATETIME) AS new_date; SELECT CAST(ARRAY(1,2,3) AS ARRAY<STRING>); SELECT CONCAT_WS(',', CAST(ARRAY(1, 2) AS ARRAY<STRING>));
使用說明和限制
將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
。部分類型之間不可以通過顯式的類型轉換,但可以通過SQL內建函數進行轉換,例如從BOOLEAN類型轉換到STRING類型,可使用函數
TO_CHAR
,詳情請參見TO_CHAR。而TO_DATE
函數同樣支援從STRING類型到DATETIME類型的轉換,詳情請參見TO_DATE。DECIMAL超出範圍,
CAST STRING TO DECIMAL
可能會出現最高位溢出報錯、最低位溢出截斷等情況。DECIMAL類型顯示轉換為DOUBLE、FLOAT等類型會產生精度損失,對於精度有要求的情境,例如計算金額、費率等,建議使用DECIMAL類型。
MaxCompute支援複雜類型的類型轉換功能。其中複雜類型的隱式類型轉換要求子類型能夠隱式轉換,而顯示轉換要求子類型能夠顯示轉換。STRUCT類型轉換不要求欄位名稱一致,但是要求欄位的數量一致,且對應的欄位能夠隱式或顯示轉換。例如:
ARRAY<BIGINT>
能隱式轉換或顯示轉換為ARRAY<STRING>
。ARRAY<BIGINT>
能顯示轉換為ARRAY<INT>
,但是不能隱式轉換。ARRAY<BIGINT>
不能隱式轉換或顯示轉換為ARRAY<DATETIME>
。STRUCT<a:BIGINT,b:INT>
能隱式轉換為STRUCT<col1:STRING,col2:BIGINT>
,但是不能隱式或顯示轉換為STRUCT<a:STRING>
。
隱式類型轉換及其範圍
隱式類型轉換是指在運行時,由MaxCompute依據上下文使用環境及類型轉換規則自動進行的類型轉換。MaxCompute支援的隱式類型轉換規則,如下表所示。
From/To | BOOLEAN | TINYINT | SMALLINT | INT | BIGINT | FLOAT |
BOOLEAN | 不涉及 | N | N | N | N | N |
TINYINT | N | 不涉及 | Y | Y | Y | Y |
SMALLINT | N | N | 不涉及 | Y | Y | Y |
INT | N | N | Y | 不涉及 | Y | Y |
BIGINT | N | N | N | N | 不涉及 | Y |
FLOAT | N | N | N | N | Y | 不涉及 |
From/To | DOUBLE | DECIMAL | STRING | VARCHAR | TIMESTAMP | BINARY |
DOUBLE | 不涉及 | Y | Y | Y | N | N |
DECIMAL | N | 不涉及 | Y | Y | N | N |
STRING | Y | Y | 不涉及 | Y | N | N |
VARCHAR | Y | Y | Y | 不涉及 | N | N |
TIMESTAMP | N | N | Y | Y | 不涉及 | N |
BINARY | N | N | N | N | N | 不涉及 |
其中,Y表示可以轉換,N表示不可以轉換,不涉及表示不需要轉換。不支援的隱式類型轉換會導致異常。如果在執行時轉換失敗,也會導致異常。
MaxCompute 2.0新增了DECIMAL類型與DATETIME的常量定義方式,100BD是數值為100的DECIMAL,
2017-11-11 00:00:00
是DATETIME類型的常量。VALUES子句和VALUES表中可以直接使用常量定義。由於隱式類型轉換是MaxCompute依據上下文使用環境自動進行的類型轉換,因此推薦您在類型不符時,顯式地用CAST進行轉換。
隱式類型轉換規則是有發生範圍的。在某些範圍中,只有一部分規則可以生效。
樣本
SELECT user_id+age+'12345', CONCAT(user_name,user_id,age) FROM user;
不同運算子作用下的隱式轉換如下:
關係運算子作用下的隱式轉換
關係運算子包括
=、<>、<、<=、>、>=、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右側的VALUE值列表中的資料類型必須保持一致。
當KEY與VALUES之間比較時,如果資料類型包含BIGINT、DOUBLE、STRING,建議統一轉換為DOUBLE類型;如果資料類型包含DATETIME、STRING,建議統一轉換為DATETIME類型。除此之外不允許其他類型之間的轉換。
算術運算子作用下的隱式轉換
算術運算子包括
+、-、*、/、%
,其隱式轉換規則如下:只有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
格式。
MaxCompute提供了TO_DATE函數,用於將不滿足日期格式的STRING類型資料轉換為DATETIME類型。詳情請參見TO_DATE。