其他函数 - 云原生大数据计算服务 MaxCompute

MaxCompute SQL提供了开发过程中常见的其他函数，您可以根据实际需要选择合适的函数。本文为您提供MaxCompute SQL支持的CAST、DECODE、LEAST等函数的命令格式、参数说明及示例。

函数	功能
BASE64	将二进制表示值转换为BASE64编码格式字符串。
BETWEEN AND表达式	筛选满足区间条件的数据。
CASE WHEN表达式	根据表达式的计算结果，灵活地返回不同的值。
CAST	将表达式的结果转换为目标数据类型。
COALESCE	返回参数列表中第一个非NULL的值。
COMPRESS	对STRING或BINARY类型输入参数按照GZIP算法进行压缩。
CRC32	计算字符串或二进制数据的循环冗余校验值。
DECODE	实现`if-then-else`分支选择的功能。
DECOMPRESS	对BINARY类型输入参数按照GZIP算法进行解压。
GET_IDCARD_AGE	根据身份证号码返回当前的年龄。
GET_IDCARD_BIRTHDAY	根据身份证号码返回出生日期。
GET_IDCARD_SEX	根据身份证号码返回性别。
GET_USER_ID	获取当前账号的账号ID。
GREATEST	返回输入参数中的最大值。
HASH	根据输入参数计算Hash值。
IF	判断指定的条件是否为真。
LEAST	返回输入参数中最小的值。
MAX_PT	返回分区表的一级分区的最大值。
NULLIF	比较两个入参是否相等。
NVL	指定值为NULL的参数的返回结果。
ORDINAL	将输入变量按从小到大排序后，返回指定位置的值。
PARTITION_EXISTS	查询指定的分区是否存在。
SAMPLE	对所有读入的列值，采样并过滤掉不满足采样条件的行。
SHA	计算字符串或二进制数据的SHA-1哈希值。
SHA1	计算字符串或二进制数据的SHA-1哈希值。
SHA2	计算字符串或二进制数据的SHA-2哈希值。
STACK	将指定的参数组分割为指定的行数。
STR_TO_MAP	将字符串按照指定的分隔符分割得到Key和Value。
TABLE_EXISTS	查询指定的表是否存在。
TRANS_ARRAY	将一行数据转为多行的UDTF，将列中存储的以固定分隔符格式分隔的数组转为多行。
TRANS_COLS	将一行数据转换为多行数据的UDTF，将不同的列拆分为不同的行。
UNBASE64	将BASE64编码格式字符串转换为二进制表示值。
UNIQUE_ID	返回一个随机ID，运行效率高于UUID函数。
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为非空字符串。
```
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。

示例

示例1：对字符串hello按照GZIP算法进行压缩。命令示例如下。

--返回=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类型，分隔符，不指定时默认为等于号（=）。
  说明
  当分隔符是正则表达式或特殊字符时，要在前面加两个反斜线（\\）来做转义。特殊字符包括冒号（:）、英文句点（.）、问号（?）、加号（+）或星号（*）。
返回值说明
返回值类型为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，其重复的概率很小。