MaxCompute SQL提供了開發過程中常見的其他函數,您可以根據實際需要選擇合適的函數。本文為您提供MaxCompute SQL支援的CAST、DECODE、LEAST等函數的命令格式、參數說明及樣本。
函數 | 功能 |
將二進位表示值轉換為BASE64編碼格式字串。 | |
篩選滿足區間條件的資料。 | |
根據運算式的計算結果,靈活地返回不同的值。 | |
將運算式的結果轉換為目標資料類型。 | |
返回參數列表中第一個非NULL的值。 | |
對STRING或BINARY類型輸入參數按照GZIP演算法進行壓縮。 | |
計算字串或位元據的迴圈冗餘校正值。 | |
實現 | |
對BINARY類型輸入參數按照GZIP演算法進行解壓。 | |
根據社會安全號碼碼返回當前的年齡。 | |
根據社會安全號碼碼返回出生日期。 | |
根據社會安全號碼碼返回性別。 | |
擷取當前帳號的帳號ID。 | |
返回輸入參數中的最大值。 | |
根據輸入參數計算Hash值。 | |
判斷指定的條件是否為真。 | |
返回輸入參數中最小的值。 | |
返回分區表的一級分區的最大值。 | |
比較兩個入參是否相等。 | |
指定值為NULL的參數的返回結果。 | |
將輸入變數按從小到大排序後,返回指定位置的值。 | |
查詢指定的分區是否存在。 | |
對所有讀入的列值,採樣並過濾掉不滿足採樣條件的行。 | |
計算字串或位元據的SHA-1雜湊值。 | |
計算字串或位元據的SHA-1雜湊值。 | |
計算字串或位元據的SHA-2雜湊值。 | |
將指定的參數組分割為指定的行數。 | |
將字串按照指定的分隔字元分割得到Key和Value。 | |
查詢指定的表是否存在。 | |
將一行資料轉為多行的UDTF,將列中儲存的以固定分隔字元格式分隔的數組轉為多行。 | |
將一行資料轉換為多行資料的UDTF,將不同的列拆分為不同的行。 | |
將BASE64編碼格式字串轉換為二進位表示值。 | |
返回一個隨機ID,運行效率高於UUID函數。 | |
返回一個隨機ID。 |
BASE64
命令格式
string base64(binary <value>)
命令說明
將value從二進位轉換為BASE64編碼格式字串。
參數說明
value:必填。BINARY類型。待轉換參數值。
傳回值說明
返回STRING類型。輸入參數為NULL時,返回結果為NULL。
樣本
樣本1:將
cast ('alibaba' as binary)
二進位結果轉換為BASE64編碼格式字串。命令樣本如下:--返回YWxpYmFiYQ==。 select base64(cast ('alibaba' as binary));
樣本2:輸入參數為NULL。命令樣本如下:
--返回NULL。 select base64(null);
BETWEEN AND運算式
命令格式
<a> [NOT] between <b> and <c>
命令說明
篩選滿足a的值位於b和c之間或不在b和c之間的資料。
參數說明
a:必填。待篩選的欄位。
b、c:必填。指定的區間條件。資料類型必須與a的資料類型保持一致。
傳回值說明
返回滿足條件的資料。
如果a、b或c為空白,返回結果為空白。
樣本
例如表
emp
中的資料如下。| empno | ename | job | mgr | hiredate| sal| comm | deptno | 7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20 7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20 7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30 7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10 7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20 7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10 7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30 7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20 7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30 7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20 7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10 7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10 7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10 7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10
查詢
sal
大於等於1000小於等於1500之間的資料,命令樣本如下。select * from emp where sal between 1000 and 1500;
返回結果如下。
+-------+-------+-----+------------+------------+------------+------------+------------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----+------------+------------+------------+------------+------------+ | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.0 | 500.0 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.0 | 1400.0 | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.0 | 0.0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.0 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.0 | NULL | 10 | | 7956 | TEBAGE | CLERK | 7748 | 1982-12-30 00:00:00 | 1300.0 | NULL | 10 | +-------+-------+-----+------------+------------+------------+------------+------------+
CASE WHEN運算式
命令格式
MaxCompute提供以下兩種
case when
格式:case <value> when <value1> then <result1> when <value2> then <result2> ... else <resultn> end
case when (<_condition1>) then <result1> when (<_condition2>) then <result2> when (<_condition3>) then <result3> ... else <resultn> end
命令說明
根據value或_condition的計算結果,靈活地返回不同的result值。
參數說明
value:必填。比較的值。
_condition:必填。指定判斷條件。
result:必填。傳回值。
傳回值說明
如果result類型只有BIGINT、DOUBLE,統一轉為DOUBLE後,再返回結果。
如果result類型中有STRING類型,則統一轉為STRING後,再返回結果。如果無法進行類型轉換,例如BOOLEAN類型無法轉換為STRING類型,則會返回報錯。
不允許其他類型之間的轉換。
樣本
例如表
sale_detail
的欄位為shop_name string, customer_id string, total_price double
,包含資料如下。+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | | s3 | c3 | 100.3 | 2013 | china | | null | c5 | NULL | 2014 | shanghai | | s6 | c6 | 100.4 | 2014 | shanghai | | s7 | c7 | 100.5 | 2014 | shanghai | +------------+-------------+-------------+------------+------------+
命令樣本如下。
select case when region='china' then 'default_region' when region like 'shang%' then 'sh_region' end as region from sale_detail;
返回結果如下。
+------------+ | region | +------------+ | default_region | | default_region | | default_region | | sh_region | | sh_region | | sh_region | +------------+
CAST
命令格式
cast(<expr> as <type>)
命令說明
將expr的結果轉換成目標資料類型type。
參數說明
expr:必填。待轉換資料來源。
type:必填。目標資料類型。用法如下:
cast(double as bigint)
:將DOUBLE資料類型值轉換成BIGINT資料類型。cast(string as bigint)
:在將字串轉為BIGINT資料類型時,如果字串中是以整型表達的數字,則會直接將其轉為BIGINT類型。如果字串中是以浮點數或指數形式表達的數字,則會先轉為DOUBLE資料類型,再轉為BIGINT資料類型。cast(string as datetime)
或cast(datetime as string)
:會採用預設的日期格式yyyy-mm-dd hh:mi:ss
。
傳回值說明
傳回值為轉換後的目標資料類型。
如果設定了
setproject odps.function.strictmode=false
,則會返回字母前的數字。如果設定了
setproject odps.function.strictmode=true
,則會返回錯誤。當轉化成Decimal類型時,如果設定了
odps.sql.decimal.tostring.trimzero=true
:去掉小數點後末尾的零;如果設定了odps.sql.decimal.tostring.trimzero=false
:保留小數點後末尾的零。重要目前
odps.sql.decimal.tostring.trimzero
參數只對錶裡取數生效,對靜態值不生效。
樣本
樣本1:常見用法。命令樣本如下。
--返回1。 select cast('1' as bigint);
樣本2:將STRING資料類型值轉換成BOOLEAN資料類型,當STRING為空白字串時返回
false
,否則返回true
。命令樣本如下。STRING為空白字串。
select cast("" as boolean); --返回 +------+ | _c0 | +------+ | false | +------+
STRING為非Null 字元串。
select cast("false" as boolean); --返回true +------+ | _c0 | +------+ | true | +------+
樣本3:將字串轉換成日期。
--將字串轉換成日期 select cast("2022-12-20" as date); --返回 +------------+ | _c0 | +------------+ | 2022-12-20 | +------------+ --將帶時分秒的日期文字轉成日期 select cast("2022-12-20 00:01:01" as date); --返回 +------------+ | _c0 | +------------+ | NULL | +------------+ --如果需要正常顯示,需要設定以下參數: set odps.sql.executionengine.enable.string.to.date.full.format= true; select cast("2022-12-20 00:01:01" as date); --返回 +------------+ | _c0 | +------------+ | 2022-12-20 | +------------+
說明預設參數
odps.sql.executionengine.enable.string.to.date.full.format
的值為false
,如果需要轉換帶時分秒的日期文字,需要把該參數值設定成true
。樣本4(錯誤命令樣本):異常用法,如果轉換不成功或遇到不支援的類型轉換,會引發異常。錯誤命令樣本如下。
select cast('abc' as bigint);
樣本5:設定了
setproject odps.function.strictmode=false
的情境樣本。setprojectodps.function.strictmode=false; select cast('123abc'as bigint); --返回 +------------+ |_c0| +------------+ |123| +------------+
樣本6:設定了
setproject odps.function.strictmode=true
的情境樣本。setprojectodps.function.strictmode=true; select cast('123abc' as bigint); --返回 FAILED:ODPS-0130071:[0,0]Semanticanalysisexception-physicalplangenerationfailed:java.lang.NumberFormatException:ODPS-0123091:Illegaltypecast-Infunctioncast,value'123abc'cannotbecastedfromStringtoBigint.
樣本7:設定了
odps.sql.decimal.tostring.trimzero
的情境樣本。--建立表 create table mf_dot (dcm1 decimal(38,18), dcm2 decimal(38,18)); --insert資料 insert into table mf_dot values (12.45500BD,12.3400BD); --Flag為true或者不設定時 set odps.sql.decimal.tostring.trimzero=true; --去掉小數點後末尾的零 select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot; --傳回值 +------------+------------+ | _c0 | _c1 | +------------+------------+ | 12.455 | 12.34 | +------------+------------+ --Flag為false時 set odps.sql.decimal.tostring.trimzero=false; --保留小數點後末尾的零 select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot; --傳回值 +------------+------------+ | _c0 | _c1 | +------------+------------+ | 12.455 | 12.340 | +------------+------------+ --對靜態值不生效 set odps.sql.decimal.tostring.trimzero=false; select cast(round(12345.120BD,3) as string); --返回: +------------+ | _c0 | +------------+ | 12345.12 | +------------+
COALESCE
命令格式
coalesce(<expr1>, <expr2>, ...)
命令說明
返回
<expr1>, <expr2>, ...
中第一個非NULL的值。參數說明
expr:必填。待驗證的值。
傳回值說明
傳回值類型和參數資料類型相同。
樣本
樣本1:常見用法。命令樣本如下。
--返回1。 select coalesce(null,null,1,null,3,5,7);
樣本2:參數實值型別未定義,返回報錯。
錯誤命令樣本
--參數abc未定義類型,系統引擎無法識別,導致返回報錯。 select coalesce(null,null,1,null,abc,5,7);
正確命令樣本
select coalesce(null,null,1,null,'abc',5,7);
樣本3:在非表讀取的情況下,如果參數值全部為空白,則返回報錯。錯誤命令樣本如下。
--返回報錯,會提示至少有一個參數值非NULL。 select coalesce(null,null,null,null);
樣本4:在表讀取的情況下,如果參數值全部為空白,則返回NULL。
未經處理資料表:
+-----------+-------------+------------+ | shop_name | customer_id | toal_price | +-----------+-------------+------------+ | ad | 10001 | 100.0 | | jk | 10002 | 300.0 | | ad | 10003 | 500.0 | | tt | NULL | NULL | +-----------+-------------+------------+
由未經處理資料表可以看出,tt的值全部為空白。執行以下語句後傳回值為NULL。
select coalesce(customer_id,total_price) from sale_detail where shop_name='tt';
COMPRESS
命令格式
binary compress(string <str>) binary compress(binary <bin>)
命令說明
將str或bin按照GZIP演算法進行壓縮。
參數說明
str:必填。STRING類型。
bin:必填。BINARY類型。
傳回值說明
返回BINARY類型。輸入參數為NULL時,返回結果為NULL。
樣本
--返回=1F=8B=08=00=00=00=00=00=00=03=CBH=CD=C9=C9=07=00=86=A6=106=05=00=00=00。 select compress('hello');
樣本2:輸入參數為空白。命令樣本如下。
--返回=1F=8B=08=00=00=00=00=00=00=03=03=00=00=00=00=00=00=00=00=00。 select compress('');
樣本3:輸入參數為NULL。命令樣本如下。
--返回NULL。 select compress(null);
CRC32
命令格式
bigint crc32(string|binary <expr>)
命令說明
計算字串或二進位類型的expr的迴圈冗餘校正值。
參數說明
expr:必填。STRING或BINARY類型。
傳回值說明
返回BIGINT類型。返回規則如下:
輸入參數為NULL時,返回結果為NULL。
輸入參數為空白時,返回0。
樣本
樣本1:計算字串
ABC
的迴圈冗餘校正值。命令樣本如下。--返回2743272264。 select crc32('ABC');
樣本2:輸入參數為NULL。命令樣本如下。
--返回NULL。 select crc32(null);
DECODE
命令格式
decode(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])
命令說明
實現
if-then-else
分支選擇的功能。參數說明
expression:必填。要比較的運算式。
search:必填。與expression進行比較的搜尋項。
result:必填。search和expression的值匹配時的傳回值。
default:可選。如果所有的搜尋項都不匹配,則返回default值,如果未指定,則返回NULL。
說明所有的result資料類型必須一致或為NULL。不一致的資料類型會返回報錯。
所有的search和expression資料類型必須一致,否則會返回報錯。
傳回值說明
如果匹配,返回result。
如果沒有匹配,返回default。
如果沒有指定default,返回NULL。
如果search選項有重複且匹配時,會返回第一個值。
通常,MaxCompute SQL在計算
NULL=NULL
時返回NULL,但在該函數中,NULL與NULL的值是相等的。
樣本
例如表
sale_detail
的欄位為shop_name string, customer_id string, total_price double
,包含資料如下。+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | | s3 | c3 | 100.3 | 2013 | china | | null | c5 | NULL | 2014 | shanghai | | s6 | c6 | 100.4 | 2014 | shanghai | | s7 | c7 | 100.5 | 2014 | shanghai | +------------+-------------+-------------+------------+------------+
命令樣本如下。
--當customer_id的值為c1時,返回Taobao;值為c2時,返回Alipay;值為c3時,返回Aliyun;值為NULL時,返回N/A;其他情境返回Others。 select decode(customer_id, 'c1', 'Taobao', 'c2', 'Alipay', 'c3', 'Aliyun', Null, 'N/A', 'Others') as result from sale_detail; --等效於如下語句。 if customer_id = c1 then result := 'Taobao'; elsif customer_id = c2 then result := 'Alipay'; elsif customer_id = c3 then result := 'Aliyun'; ... else result := 'Others'; end if;
返回結果如下。
+------------+ | result | +------------+ | Others | | Others | | Others | | Taobao | | Alipay | | Aliyun | +------------+
DECOMPRESS
命令格式
binary decompress(binary <bin>)
命令說明
將bin按照GZIP演算法進行解壓。
參數說明
bin:必填。BINARY類型。
傳回值說明
返回BINARY類型。輸入參數為NULL時,返回結果為NULL。
樣本
樣本1:對字串
hello, world
的壓縮結果進行解壓並轉換為字串格式。命令樣本如下。--返回hello, world。 select cast(decompress(compress('hello, world')) as string);
樣本2:輸入參數為NULL。命令樣本如下。
--返回NULL。 select decompress(null);
GET_IDCARD_AGE
命令格式
get_idcard_age(<idcardno>)
命令說明
根據社會安全號碼碼返回當前的年齡,即當前年份減去社會安全號碼碼中標識的出生年份的差值。
參數說明
idcardno:必填。STRING類型,15位或18位社會安全號碼碼。在計算時會根據省份代碼以及最後一位校正碼檢查身份證的合法性。如果校正不通過會返回NULL。
傳回值說明
返回BIGINT類型。輸入為NULL時,返回NULL。
GET_IDCARD_BIRTHDAY
命令格式
get_idcard_birthday(<idcardno>)
命令說明
根據社會安全號碼碼返回出生日期。
參數說明
idcardno:必填。STRING類型,15位或18位社會安全號碼碼。在計算時,會根據省份代碼以及最後一位校正碼檢查身份證的合法性。如果校正不通過,則返回NULL。
傳回值說明
返回DATETIME類型。輸入為NULL時,返回NULL。
GET_IDCARD_SEX
命令格式
get_idcard_sex(<idcardno>)
命令說明
根據社會安全號碼碼返回性別,值為
M
(男)或F
(女)。參數說明
idcardno:必填。STRING類型,15位或18位社會安全號碼。在計算時會根據省份代碼以及最後一位校正碼檢查身份證的合法性。如果校正不通過,則返回NULL。
傳回值說明
返回STRING類型。輸入為NULL時,返回NULL。
GET_USER_ID
命令格式
get_user_id()
命令說明
擷取當前帳號的帳號ID,即使用者ID或UID。
參數說明
無需輸入參數。
傳回值說明
返回當前帳號的帳號ID。
樣本
select get_user_id(); --返回結果如下。 +------------+ | _c0 | +------------+ | 1117xxxxxxxx8519 | +------------+
GREATEST
命令格式
greatest(<var1>, <var2>[,...])
命令說明
返回輸入參數中的最大值。
參數說明
var1、var2:必填。BIGINT、DOUBLE、DECIMAL、DATETIME或STRING類型。
傳回值說明
返回輸入參數中的最大值。當不存在隱式轉換時,傳回值與輸入參數資料類型相同。
NULL為最小值。
當輸入參數資料類型不相同時,DOUBLE、BIGINT、DECIMAL、STRING之間的比較會轉換為DOUBLE類型;STRING、DATETIME的比較會轉換為DATETIME類型。不允許其他的隱式轉換。
當
set odps.sql.hive.compatible=true;
時,任意參數輸入為NULL,返回結果為NULL。
HASH
命令格式
當MaxCompute專案為Hive相容模式時,命令格式如下。
int hash(<value1>, <value2>[, ...]);
當MaxCompute專案非Hive相容模式時,命令格式如下。
bigint hash(<value1>, <value2>[, ...]);
命令說明
對value1、value2進行散列運算得到一個Hash值。
參數說明
value1、value2:必填。待計算Hash值的參數,各參數的類型可以不相同。Hive及非Hive相容模式下支援的資料類型不相同,具體如下:
Hive相容模式:TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、BOOLEAN、STRING、CHAR、VARCHAR、DATETIME、DATE。
非Hive相容模式:BIGINT、DOUBLE、BOOLEAN、STRING、DATETIME。
說明對於兩個相同的輸入,返回的Hash值一定是相同的。但是反過來,如果兩個Hash值相同,並不保證輸入值一定相等(可能出現雜湊碰撞)。
傳回值說明
返回INT或BIGINT類型。當輸入參數為空白或NULL時,返回結果為0。
樣本
樣本1:計算相同資料類型的輸入參數的Hash值。命令樣本如下。
--返回66。 select hash(0, 2, 4);
樣本2:計算不同資料類型的輸入參數的Hash值。命令樣本如下。
--返回97。 select hash(0, 'a');
樣本3:任一輸入參數為空白或NULL。命令樣本如下。
--返回0。 select hash(0, null); --返回0。 select hash(0, '');
IF
命令格式
if(<testCondition>, <valueTrue>, <valueFalseOrNull>)
命令說明
判斷testCondition是否為真。如果為真,返回valueTrue的值,否則返回valueFalseOrNull的值。
參數說明
testCondition:必填。要判斷的運算式,BOOLEAN類型。
valueTrue:必填。運算式testCondition為True時,返回的值。
valueFalseOrNull:運算式testCondition為False時,返回的值,可以設為NULL。
傳回值說明
傳回值類型和參數valueTrue或valueFalseOrNull的資料類型一致。
樣本
--返回200。 select if(1=2, 100, 200);
LEAST
命令格式
least(<var1>, <var2>[,...])
命令說明
返回輸入參數中的最小值。
參數說明
var :必填。輸入參數值。BIGINT、DOUBLE、DECIMAL、DATETIME或STRING類型。
傳回值說明
輸入參數中的最小值。當不存在隱式轉換時,傳回值與輸入參數類型相同。
當有類型轉換時,DOUBLE、BIGINT、STRING之間的轉換返回DOUBLE類型;STRING、DATETIME之間的轉換返回DATETIME類型;DECIMAL、DOUBLE、BIGINT和STRING之間的轉換返回DECIMAL類型。不允許其他的隱式類型轉換。
NULL為最小值。
如果所有參數值都為NULL,返回結果為NULL。
樣本
--返回2。 select least(5, 2, 7);
MAX_PT
命令格式
max_pt(<table_full_name>)
命令說明
返回分區表的一級分區中有資料的分區的最大值,按字母排序,且讀取該分區下對應的資料。
max_pt
函數也可以使用標準SQL實現,select * from table where pt = max_pt("table");
可以改寫為select * from table where pt = (select max(pt) from table);
。說明MaxCompute未提供
min_pt
函數。如果您需要擷取分區表中有資料的最小分區,無法使用SQL語句select * from table where pt = min_pt("table");
實現與max_pt
函數類似的功能,但可以使用標準SQL語句select * from table where pt = (select min(pt) from table);
實現相同的效果。參數說明
table_full_name:必填。STRING類型。指定表名。必須對錶具有讀許可權。
傳回值說明
返回最大的一級分區的值。
說明如果只是用
alter table
的方式新加了一個分區,但是此分區中並無任何資料,則此分區不會作為傳回值。樣本
樣本1:例如tbl是分區表,該表對應的分區為20120901和20120902,且都有資料。則以下語句中
max_pt
傳回值為‘20120902’
。MaxCompute SQL語句會讀出pt=‘20120902’
分區下的資料。命令樣本如下。select * from tbl where pt=max_pt('myproject.tbl'); --等效於如下語句。 select * from tbl where pt = (select max(pt) from myproject.tbl);
樣本2:在多級分區情境,使用標準SQL實現擷取最大分區下的資料。命令樣本如下。
select * from table where pt1 = (select max(pt1) from table) and pt2 = (select max(pt2) from table where pt1 = (select max(pt1) from table));
NULLIF
命令格式
T nullif(T <expr1>, T <expr2>)
命令說明
比較expr1和expr2的值,二者相等時返回NULL,否則返回expr1。
參數說明
expr1、expr2:必填。任意類型的運算式。
T
指代輸入資料類型,可以是MaxCompute支援的所有資料類型。傳回值說明
返回NULL或expr1。
樣本
--返回2。 select nullif(2, 3); --返回NULL。 select nullif(2, 2); --返回3。 select nullif(3, null);
NVL
命令格式
nvl(T <value>, T <default_value>)
命令說明
如果value值為NULL,返回default_value,否則返回value。兩個參數的資料類型必須一致。
參數說明
value:必填。輸入參數。
T
指代輸入資料類型,可以是MaxCompute支援的所有資料類型。default_value:必填。替換後的值。必須與value的資料類型保持一致。
樣本
例如表
t_data
的3個列分別為c1 string
、c2 bigint
、c3 datetime
。表中資料如下。+----+------------+------------+ | c1 | c2 | c3 | +----+------------+------------+ | NULL | 20 | 2017-11-13 05:00:00 | | ddd | 25 | NULL | | bbb | NULL | 2017-11-12 08:00:00 | | aaa | 23 | 2017-11-11 00:00:00 | +----+------------+------------+
通過
nvl
函數將c1
中為NULL的值輸出為00000,c2
中為NULL的值輸出為0,c3
中為NULL的值輸出為-
,命令樣本如下。select nvl(c1,'00000'),nvl(c2,0),nvl(c3,'-') from nvl_test; --返回結果如下。 +-----+------------+-----+ | _c0 | _c1 | _c2 | +-----+------------+-----+ | 00000 | 20 | 2017-11-13 05:00:00 | | ddd | 25 | - | | bbb | 0 | 2017-11-12 08:00:00 | | aaa | 23 | 2017-11-11 00:00:00 | +-----+------------+-----+
ORDINAL
命令格式
ordinal(bigint <nth>, <var1>, <var2>[,...])
命令說明
將輸入變數按從小到大排序後,返回排在第nth位的值。
參數說明
nth:必填,位置序號,起始從1開始。BIGINT類型。指定要返回的位置值為NULL時,返回NULL。
var:必填,待排序的值。BIGINT、DOUBLE、DATETIME或STRING類型。
傳回值說明
排在第nth位的值,當不存在隱式轉換時傳回值與輸入參數資料類型相同。
當有類型轉換時,DOUBLE、BIGINT、STRING之間的轉換返回DOUBLE類型;STRING、DATETIME之間的轉換返回DATETIME類型。不允許其他的隱式轉換。
NULL為最小值。
樣本
--返回3。 SELECT ordinal(3, 1, 3, 7, 5, 2, 4, 6);
PARTITION_EXISTS
命令格式
boolean partition_exists(string <table_name>, string... <partitions>)
命令說明
查詢指定的分區是否存在。
參數說明
table_name:必填。表名稱,STRING類型。表名稱中可以指定專案空間名稱,例如
my_proj.my_table
。如果不指定專案空間名稱則預設為當前專案空間。partitions : 必填。分區名稱,STRING類型。按照表分區列的順序依次寫出分區值,分區值數目必須與分區列數目一致。
傳回值說明
返回BOOLEAN類型。如果指定的分區存在返回True,否則返回False。
樣本
--建立分區表foo。 create table foo (id bigint) partitioned by (ds string, hr string); --為分區表foo新增分區。 alter table foo add partition (ds='20190101', hr='1'); --查詢分區ds='20190101'和hr='1'是否存在。返回結果為True。 select partition_exists('foo', '20190101', '1');
SAMPLE
命令格式
boolean sample(<x>, <y>, [<column_name1>, <column_name2>[,...]])
命令說明
基於所有讀入的column_name的值,系統根據x、y的設定做採樣,並過濾掉不滿足採樣條件的行。
參數說明
x、y:x必填。BIGINT類型,取值範圍為大於0的整型常量。表示雜湊為x份,取第y份。
y可選,省略時預設取第一份。如果省略參數中的y,則必須同時省略column_name。
x、y為其它類型或小於等於0時拋出異常,如果y大於x時也返回異常。x、y任一輸入為NULL時,返回NULL。
column_name:可選。採樣的目標列。該參數省略時將根據x、y的值隨機採樣。任意類型,列的值可以為NULL。不做隱式類型轉換。如果column_name為常量NULL,則返回報錯。
說明為避免NULL值帶來的資料扭曲,對於column_name中為NULL的值,會在x份中進行均勻雜湊。如果不指定column_name,則資料量比較少時輸出不一定均勻,在這種情況下建議指定column_name,以獲得較好的輸出結果。
目前僅支援對如下資料類型的列做隨機採樣:bigint、datetime、boolean、double、string、binary、char、varchar。
傳回值說明
返回BOOLEAN類型。
樣本
例如存在表
tbla
,表內有列名為cola
的列。--表示數值會根據cola hash為4份,取第1份。傳回值為True。 select * from tbla where sample (4, 1 , cola); --表示數值會對每行資料做隨機雜湊分配為4份,取第2份。傳回值為True。 select * from tbla where sample (4, 2);
SHA
命令格式
string sha(string|binary <expr>)
命令說明
計算字串或者二進位類型的expr的SHA-1雜湊值,並以十六進位字串格式返回。
參數說明
expr:必填。STRING或BINARY類型。
傳回值說明
返回STRING類型。輸入參數為NULL時,返回結果為NULL。
樣本
樣本1:計算字串
ABC
的SHA雜湊值。命令樣本如下。--返回3c01bdbb26f358bab27f267924aa2c9a03fcfdb8。 select sha('ABC');
樣本2:輸入參數為NULL。命令樣本如下。
--返回NULL。 select sha(null);
SHA1
命令格式
string sha1(string|binary <expr>)
命令說明
計算字串或者二進位類型的expr的SHA-1雜湊值,並以十六進位字串格式返回。
參數說明
expr:必填。STRING或BINARY類型。
傳回值說明
返回STRING類型。輸入參數為NULL時,返回結果為NULL。
樣本
樣本1:計算字串
ABC
的SHA-1雜湊值。命令樣本如下。--返回3c01bdbb26f358bab27f267924aa2c9a03fcfdb8。 select sha1('ABC');
樣本2:輸入參數為NULL。命令樣本如下。
--返回NULL。 select sha1(null);
SHA2
命令格式
string sha2(string|binary <expr>, bigint <number>)
命令說明
計算字串或者二進位類型的expr的SHA-2雜湊值,以指定的number格式返回。
參數說明
expr:必填。STRING或BINARY類型。
number:必填。BIGINT類型。雜湊位長,取值必須是224、256、384、512、0(同256)。
傳回值說明
返回STRING類型。返回規則如下:
任一輸入參數為NULL時,返回結果為NULL。
如果number取值不在允許的取值範圍內,返回結果為NULL。
樣本
樣本1:計算字串
ABC
的SHA-2雜湊值。命令樣本如下。--返回b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78。 select sha2('ABC', 256);
樣本2:任一輸入參數為NULL。命令樣本如下。
--返回NULL。 select sha2('ABC', null);
STACK
命令格式
stack(n, expr1, ..., exprk)
命令說明
將
expr1, ..., exprk
分割為n行,除非另有說明,否則輸出結果使用預設的列名col0、col1...
。參數說明
n:必填。分割的行數。
expr:必填。待分割的參數。
expr1, ..., exprk
必須是整型,且參數數目必須是n的整數倍,需要能分割為完整的n行,否則返回報錯。
傳回值說明
返回n行,列數為參數數量除以n的商的資料集。
樣本
--將1, 2, 3, 4, 5, 6排為3行。 select stack(3, 1, 2, 3, 4, 5, 6); --返回結果如下。 +------+------+ | col0 | col1 | +------+------+ | 1 | 2 | | 3 | 4 | | 5 | 6 | +------+------+ --將'A',10,date '2015-01-01','B',20,date '2016-01-01'排為兩行. select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') as (col0,col1,col2); --返回結果如下。 +------+------+------+ | col0 | col1 | col2 | +------+------+------+ | A | 10 | 2015-01-01 | | B | 20 | 2016-01-01 | +------+------+------+ --將a、b、c、d排列為兩行,源表如果有多行,按行執行stack的分行處理。 select stack(2,a,b,c,d) as (col,value) from values (1,1,2,3,4), (2,5,6,7,8), (3,9,10,11,12), (4,13,14,15,null) as t(key,a,b,c,d); --返回結果如下。 +------+-------+ | col | value | +------+-------+ | 1 | 2 | | 3 | 4 | | 5 | 6 | | 7 | 8 | | 9 | 10 | | 11 | 12 | | 13 | 14 | | 15 | NULL | +------+-------+ --配合lateral view使用。 select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20, date '2016-01-01') tf as col0,col1,col2; --返回結果如下。 +------+------+------+ | col0 | col1 | col2 | +------+------+------+ | A | 10 | 2015-01-01 | | B | 20 | 2016-01-01 | +------+------+------+
STR_TO_MAP
命令格式
str_to_map([string <mapDupKeyPolicy>,] <text> [, <delimiter1> [, <delimiter2>]])
命令說明
使用delimiter1將text分割成Key-Value對,然後使用delimiter2分割每個Key-Value對的Key和Value。
參數說明
mapDupKeyPolicy:可選。STRING類型。指定出現重複Key時的處理方式。取值範圍如下:
exception:如果出現重複的Key,返回報錯。
last_win:如果出現重複的Key,後邊的值將覆蓋前邊的值。
該參數也可以在Session層級通過
odps.sql.map.key.dedup.policy
參數進行設定,例如set odps.sql.map.key.dedup.policy=exception;
,不設定時該參數預設值為last_win。說明MaxCompute的行為實現優先以函數中mapDupKeyPolicy的取值為準,當函數未配置mapDupKeyPolicy時,以
odps.sql.map.key.dedup.policy
參數的取值為準。text:必填。STRING類型,指被分割的字串。
delimiter1:可選。STRING類型,分隔字元,不指定時預設為英文逗號(
,
)。delimiter2:可選。STRING類型,分隔字元,不指定時預設為等號(
=
)。說明當分隔字元是Regex或特殊字元時,要在前面加兩個反斜線(\\)來做轉義。特殊字元包括冒號(:)、英文句點(.)、問號(?)、加號(+)或星號(*)。
傳回值說明
傳回值類型為
map<string, string>
。傳回值是text被delimiter1和delimiter2分割後的結果。樣本
--返回{test1:1, test2:2}。 select str_to_map('test1&1-test2&2','-','&'); --返回{test1:1, test2:2}。 select str_to_map("test1.1,test2.2", ",", "\\."); --返回{test1:1, test2:3}。 select str_to_map("test1.1,test2.2,test2.3", ",", "\\.");
TABLE_EXISTS
命令格式
boolean table_exists(string <table_name>)
命令說明
查詢指定的表是否存在。
參數說明
table_name:必填。表名稱。STRING類型。表名稱中可以指定專案名稱(例如
my_proj.my_table
)。如果不指定專案名稱則預設為當前專案。傳回值說明
返回BOOLEAN類型。如果指定的表存在返回True,否則返回False。
樣本
--在select列表中使用。 select if(table_exists('abd'), col1, col2) from src;
TRANS_ARRAY
使用限制
所有作為
key
的列必須位於前面,而要轉置的列必須放在後面。在一個
select
中只能有一個UDTF,不可以再出現其他的列。不可以與
group by
、cluster by
、distribute by
、sort by
一起使用。
命令格式
trans_array (<num_keys>, <separator>, <key1>,<key2>,…,<col1>,<col2>,<col3>) as (<key1>,<key2>,...,<col1>, <col2>)
命令說明
將一行資料轉換為多行的UDTF,將列中儲存的以固定分隔字元格式分隔的數群組轉換為多行。
參數說明
num_keys:必填。 BIGINT類型常量,值必須
>=0
。在轉為多行時作為轉置key
的列的個數。separator:必填。STRING類型常量,用於將字串拆分成多個元素的分隔字元。為空白時返回報錯。
keys:必填。轉置時作為
key
的列, 個數由num_keys指定。如果num_keys指定所有的列都作為key
(即num_keys等於所有列的個數),則只返回一行。cols: 必填。要轉為行的數組,
keys
之後的所有列視為要轉置的數組,必須為STRING類型,儲存的內容是字串格式的數組,例如Hangzhou;Beijing;Shanghai
,是以分號(;
)分隔的數組。
傳回值說明
返迴轉置後的行,新的列名由
as
指定。作為key
的列類型保持不變,其餘所有的列是STRING類型。拆分成的行數以個數多的數組為準,不足的補NULL。樣本
樣本1:例如表
t_table
中的資料如下。+----------+----------+------------+ | login_id | login_ip | login_time | +----------+----------+------------+ | wangwangA | 192.168.0.1,192.168.0.2 | 20120101010000,20120102010000 | | wangwangB | 192.168.45.10,192.168.67.22,192.168.6.3 | 20120111010000,20120112010000,20120223080000 | +----------+----------+------------+ --執行SQL。 select trans_array(1, ",", login_id, login_ip, login_time) as (login_id,login_ip,login_time) from t_table; --返回結果如下。 +----------+----------+------------+ | login_id | login_ip | login_time | +----------+----------+------------+ | wangwangB | 192.168.45.10 | 20120111010000 | | wangwangB | 192.168.67.22 | 20120112010000 | | wangwangB | 192.168.6.3 | 20120223080000 | | wangwangA | 192.168.0.1 | 20120101010000 | | wangwangA | 192.168.0.2 | 20120102010000 | +----------+----------+------------+ --如果表中的資料如下所示。 Login_id LOGIN_IP LOGIN_TIME wangwangA 192.168.0.1,192.168.0.2 20120101010000 --會對數組中不足的資料補NULL。 Login_id Login_ip Login_time wangwangA 192.168.0.1 20120101010000 wangwangA 192.168.0.2 NULL
樣本2:例如表mf_fun_array_test_t中的資料如下。
+------------+------------+------------+------------+ | id | name | login_ip | login_time | +------------+------------+------------+------------+ | 1 | Tom | 192.168.100.1,192.168.100.2 | 20211101010101,20211101010102 | | 2 | Jerry | 192.168.100.3,192.168.100.4 | 20211101010103,20211101010104 | +------------+------------+------------+------------+ --用兩個key,id和name進行轉數組,執行SQL。 select trans_array(2, ",", Id,Name, login_ip, login_time) as (Id,Name,login_ip,login_time) from mf_fun_array_test_t; --返回結果如下,已經對key,id和name進行分組拆解。 +------------+------------+------------+------------+ | id | name | login_ip | login_time | +------------+------------+------------+------------+ | 1 | Tom | 192.168.100.1 | 20211101010101 | | 1 | Tom | 192.168.100.2 | 20211101010102 | | 2 | Jerry | 192.168.100.3 | 20211101010103 | | 2 | Jerry | 192.168.100.4 | 20211101010104 | +------------+------------+------------+------------+
TRANS_COLS
使用限制
所有作為
key
的列必須處在前面,而要轉置的列必須放在後面。在一個
select
中只能有一個UDTF,不可以再出現其他的列。
命令格式
trans_cols (<num_keys>, <key1>,<key2>,…,<col1>, <col2>,<col3>) as (<idx>, <key1>,<key2>,…,<col1>, <col2>)
命令說明
將一行資料轉換為多行資料的UDTF,將不同的列拆分為不同的行。
參數說明
num_keys:必填。BIGINT類型常量,值必須
>=0
。在轉為多行時作為轉置key的列的個數。keys:必填。轉置時作為key的列, 個數由num_keys指定。如果num_keys指定所有的列都作為key(即num_keys等於所有列的個數),則只返回一行。
idx:必填。轉換後的行號。
cols:必填。 要轉換為行的列。
傳回值說明
返迴轉置後的行,新的列名由
as
指定。輸出的第一列是轉置的下標,下標從1開始。作為key的列類型保持不變,其餘所有的列與原來的資料類型一致。樣本
例如表
t_table
中的資料如下。+----------+----------+------------+ | Login_id | Login_ip1 | Login_ip2 | +----------+----------+------------+ | wangwangA | 192.168.0.1 | 192.168.0.2 | +----------+----------+------------+ --執行SQL。 select trans_cols(1, login_id, login_ip1, login_ip2) as (idx, login_id, login_ip) from t_table; --返回結果如下。 idx login_id login_ip 1 wangwangA 192.168.0.1 2 wangwangA 192.168.0.2
UNBASE64
命令格式
binary unbase64(string <str>)
命令說明
將BASE64編碼格式字串str轉換為二進位表示格式。
參數說明
str:必填。STRING類型。待轉換BASE64編碼格式字串。
傳回值說明
返回BINARY類型。輸入參數為NULL時,返回結果為NULL。
樣本
樣本1:將字串
YWxpYmFiYQ==
轉換為二進位表示值。命令樣本如下:--返回alibaba。 select unbase64('YWxpYmFiYQ==');
樣本2:輸入參數為NULL。命令樣本如下:
--返回NULL。 select unbase64(null);
UNIQUE_ID
命令格式
string unique_id()
命令說明
返回一個隨機的唯一ID,格式樣本為
29347a88-1e57-41ae-bb68-a9edbdd9****_1
。該函數的運行效率高於UUID,且返回的ID較長,相較於UUID多一個底線(_)和一個數字編號,例如_1
。
UUID
命令格式
string uuid()
命令說明
返回一個隨機ID,格式樣本為
29347a88-1e57-41ae-bb68-a9edbdd9****
。說明UUID返回的是一個隨機的全域ID,其重複的機率很小。