MaxCompute的预置函数有哪些_云原生大数据计算服务 MaxCompute-阿里云帮助中心

注意事项

在使用内建函数时，需要注意：

内建函数的入参类型、入参数量、函数格式必须满足函数语法要求，否则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; 命令查看项目的全局属性信息。

各类型函数与开源函数的对照关系，请参见与Hive、MySQL、Oracle内建函数对照表。

日期函数

MaxCompute SQL提供了如下日期函数，您可以根据实际需要选择合适的日期函数，完成日期计算、日期转换。

函数	功能
DATEADD	按照指定的单位和幅度修改日期值。
DATE_ADD	按照指定的幅度增减天数，与 `date_sub` 的增减逻辑相反。
DATE_FORMAT	将日期值转换为指定格式的字符串。
DATE_SUB	按照指定的幅度增减天数，与 `date_add` 的增减逻辑相反。
DATEDIFF	计算两个日期的差值并按照指定的单位表示。
DATEPART	提取日期中符合指定时间单位的字段值。
DATETRUNC	提取日期按照指定时间单位截取后的值。
FROM_UNIXTIME	将数字型的UNIX值转换为日期值。
GETDATE	获取当前系统时间。
ISDATE	判断一个日期字符串能否根据指定的格式串转换为一个日期值。
LASTDAY	获取日期所在月的最后一天。
TO_DATE	将指定格式的字符串转换为日期值。
TO_CHAR	将日期按照指定格式转换为字符串。
UNIX_TIMESTAMP	将日期转换为整型的UNIX格式的日期值。
WEEKDAY	返回日期值是当前周的第几天。
WEEKOFYEAR	返回日期值位于当年的第几周。
ADD_MONTHS	计算日期值增加指定月数后的日期。
CURRENT_TIMESTAMP	返回当前TIMESTAMP类型的时间戳。
CURRENT_TIMEZONE	返回当前系统的时区信息。
DAY	返回日期值的天。
DAYOFMONTH	返回日部分的值。
DAYOFWEEK	返回日期的星期值。
DAYOFYEAR	返回日期是当年中的第几天。
EXTRACT	获取日期TIMESTAMP中指定单位的部分。
FROM_UTC_TIMESTAMP	将一个UTC时区的时间戳转换为一个指定时区的时间戳。
HOUR	返回日期小时部分的值。
LAST_DAY	返回日期值所在月份的最后一天日期。
MINUTE	返回日期分钟部分的值。
MONTH	返回日期值所属月份。
MONTHS_BETWEEN	返回指定日期值间的月数。
NEXT_DAY	返回大于日期值且与指定周相匹配的第一个日期。
QUARTER	返回日期值所属季度。
SECOND	返回日期秒数部分的值。
TO_MILLIS	将指定日期转换为以毫秒为单位的UNIX时间戳。
YEAR	返回日期值的年。

数学函数

MaxCompute SQL提供了如下数学函数供您在开发过程中使用，您可以根据实际需要选择合适的数学函数，进行数据计算、数据转换相关操作。

函数	功能
ABS	计算绝对值。
ACOS	计算反余弦值。
ASIN	计算反正弦值。
ATAN	计算反正切值。
ATAN2	计算 expr1/expr2 的反正切函数。
CEIL	计算向上取整值。
CONV	计算进制转换值。
COS	计算余弦值。
COSH	计算双曲余弦值。
COT	计算余切值。
EXP	计算指数值。
FLOOR	计算向下取整值。
ISNAN	判断表达式的值是否是NaN。
LN	计算自然对数。
LOG	计算log对数值。
NEGATIVE	返回表达式的负值。
POSITIVE	返回表达式的值。
POW	计算幂值。
RAND	返回随机数。
ROUND	返回四舍五入到指定小数点位置的值。
SIN	计算正弦值。
SINH	计算双曲正弦值。
SQRT	计算平方根。
TAN	计算正切值。
TANH	计算双曲正切值。
TRUNC	返回截取到指定小数点位置的值。
BIN	计算二进制代码值。
CBRT	计算立方根值。
CORR	计算皮尔逊系数。
DEGREES	将弧度转换为角度。
E	返回e的值。
FACTORIAL	计算阶乘值。
FORMAT_NUMBER	将数字转化为指定格式的字符串。
HEX	返回整数或字符串的十六进制格式。
LOG2	计算以2为底的对数。
LOG10	计算以10为底的对数。
PI	返回π的值。
RADIANS	将角度转换为弧度。
SIGN	返回输入参数的符号。
SHIFTLEFT	计算按位左移值。
SHIFTRIGHT	计算按位右移值。
SHIFTRIGHTUNSIGNED	计算无符号按位右移值。
UNHEX	返回十六进制字符串所代表的字符串。
WIDTH_BUCKET	返回指定字段值落入的分组编号。

函数	功能
ROW_NUMBER	计算行号。从1开始递增。
RANK	计算排名。排名可能不连续。
DENSE_RANK	计算排名。排名是连续的。
PERCENT_RANK	计算排名。输出百分比格式。
CUME_DIST	计算累计分布。
NTILE	将数据顺序切分成N等份，返回数据所在等份的编号（从1到N）。
LAG	取当前行往前（朝分区头部方向）第N行数据的值。
LEAD	取当前行往后（朝分区尾部方向）第N行数据的值。
FIRST_VALUE	取当前行所对应窗口的第一条数据的值。
LAST_VALUE	取当前行所对应窗口的最后一条数据的值。
NTH_VALUE	取当前行所对应窗口的第N条数据的值。
CLUSTER_SAMPLE	用户随机抽样。返回True表示该行数据被抽中。
COUNT	计算窗口中的记录数。
MIN	计算窗口中的最小值。
MAX	计算窗口中的最大值。
AVG	对窗口中的数据求平均值。
SUM	对窗口中的数据求和。
MEDIAN	计算窗口中的中位数。
STDDEV	计算总体标准差。是STDDEV_POP的别名。
STDDEV_SAMP	计算样本标准差。

```
<function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
<function_name>([distinct][<expression> [, ...]]) over <window_name>
```
- ```
window <window_name> as (<window_definition>)
```
```
select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]
```
```
--partition_clause：
[partition by <expression> [, ...]]
--orderby_clause：
[order by <expression> [asc|desc][nulls {first|last}] [, ...]]
[<frame_clause>]
```

--格式一。
{ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
--格式二。
{ROWS|RANGE|GROUPS} between <frame_start> and <frame_end> [<frame_exclusion>]

frame_clause类型	frame_start/frame_end取值	说明
ROWS、RANGE、GROUPS	UNBOUNDED PRECEDING	表示分区的第一行，从1开始计数。
ROWS、RANGE、GROUPS	UNBOUNDED FOLLOWING	表示分区的最后一行。
ROWS	CURRENT ROW	指当前行的位置。每一行数据都会对应一个窗口函数的结果值，当前行是指在给哪一行数据计算窗口函数的结果。
	offset PRECEDING	指从当前行位置，向分区头部位置移动 `offset` 行的位置。例如 `0 PRECEDING` 指当前行， `1 PRECEDING` 指前一行。 `offset` 必须为非负整数。
	offset FOLLOWING	指从当前行位置，向分区尾部移动 `offset` 行的位置。例如 `0 FOLLOWING` 指当前行， `1 FOLLOWING` 指下一行。 `offset` 必须为非负整数。
RANGE	CURRENT ROW	作为 frame_start 时，指第一条与当前行具有相同 `order by` 列值的数据的位置。作为 frame_end 时，指最后一条与当前行具有相同 `order by` 列值的数据的位置。
	offset PRECEDING	frame_start 和 frame_end 的位置与 `order by` 的顺序相关。假设窗口按照X进行排序，Xi表示第i行数据对应的X值，Xc表示当前行数据对应X值。位置说明如下：当 `order by` 为升序时： frame_start ：指第一条满足 `Xc - Xi <= offset` 数据的位置。 frame_end ：指最后一条满足 `Xc - Xi >= offset` 数据的位置。当 `order by` 为降序时： frame_start ：指第一条满足 `Xi - Xc <= offset` 数据的位置。 frame_end ：指最后一条满足 `Xi - Xc >= offset` 数据的位置。 `order by` 的列支持的数据类型为：TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、DATETIME、DATE、TIMESTAMP。日期类型数据的 `offset` 语法如下： `N` ：表示N天或N秒。非负整数。对于DATETIME和TIMESTAMP，表示N秒；对于DATE，表示N天。 `interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}` ：表示N年/月/日/小时/分钟/秒。例如 `INTERVAL '3' YEAR` 表示3年。 `INTERVAL 'N-M' YEAR TO MONTH` ：表示N年M月。例如 `INTERVAL '1-3' YEAR TO MONTH` 表示1年3个月。 `INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND` ：表示D天H小时M分钟S秒N纳秒。例如 `INTERVAL '1 2:3:4:5' DAY TO SECOND` 表示1天2小时3分钟4秒5纳秒。
	offset FOLLOWING	frame_start 和 frame_end 的位置与 `order by` 的顺序相关。假设窗口按照X进行排序，Xi表示第i行数据对应的X值，Xc表示当前行数据对应X值。位置说明如下：当 `order by` 为升序时： frame_start ：指第一条满足 `Xi - Xc >= offset` 数据的位置。 frame_end ：指最后一条满足 `Xi - Xc <= offset` 数据的位置。当 `order by` 为降序时： frame_start ：指第一条满足 `Xc - Xi >= offset` 数据的位置。 frame_end ：指最后一条满足 `Xc - Xi <= offset` 数据的位置。
GROUPS	CURRENT ROW	作为 frame_start 时，指当前行所属GROUP的第一条数据。作为 frame_end 时，指当前行所属GROUP的最后一行数据。
	offset PRECEDING	作为 frame_start 时，指从当前行所属GROUP开始，朝分区头部移动 `offset` 个GROUP之后，所在GROUP的第一条数据的位置。作为 frame_end 时，指从当前行所属GROUP开始，朝分区头部移动 `offset` 个GROUP之后，所在GROUP的最后一条数据的位置。
	offset FOLLOWING	作为 frame_start 时，指从当前行所属GROUP开始，朝分区尾部移动 `offset` 个GROUP之后，所在GROUP的第一条数据的位置。作为 frame_end 时，指从当前行所属GROUP开始，朝分区尾部移动 `offset` 个GROUP之后，所在GROUP的最后一条数据的位置。

```
RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```
```
ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```

+------------+------------+------------+
| pid        | oid        | rid        |
+------------+------------+------------+
| 1          | NULL       | 1          |
| 1          | NULL       | 2          |
| 1          | 1          | 3          |
| 1          | 1          | 4          |
| 1          | 2          | 5          |
| 1          | 4          | 6          |
| 1          | 7          | 7          |
| 1          | 11         | 8          |
| 2          | NULL       | 9          |
| 2          | NULL       | 10         |
+------------+------------+------------+

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
--SQL语句如下。
select pid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1]    |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9]    |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
--SQL语句如下。
select pid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as window from tbl;

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 3          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 4          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
--SQL语句如下。
select pid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between 1 FOLLOWING and 3 FOLLOWING) as window from tbl;

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [2, 3, 4] |
| 1          | NULL       | 2          | [3, 4, 5] |
| 1          | 1          | 3          | [4, 5, 6] |
| 1          | 1          | 4          | [5, 6, 7] |
| 1          | 2          | 5          | [6, 7, 8] |
| 1          | 4          | 6          | [7, 8] |
| 1          | 7          | 7          | [8]    |
| 1          | 11         | 8          | NULL   |
| 2          | NULL       | 9          | [10]   |
| 2          | NULL       | 10         | NULL   |
+------------+------------+------------+--------+

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
--SQL语句如下。
select pid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) as window from tbl;

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | NULL   |
| 1          | NULL       | 2          | [1]    |
| 1          | 1          | 3          | [1, 2] |
| 1          | 1          | 4          | [1, 2, 3] |
| 1          | 2          | 5          | [1, 2, 3, 4] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
| 2          | NULL       | 9          | NULL   |
| 2          | NULL       | 10         | [9]    |
+------------+------------+------------+--------+

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
--SQL语句如下。
select pid, 
collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP) as window from tbl;

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | NULL   |
| 1          | NULL       | 2          | NULL   |
| 1          | 1          | 3          | [1, 2] |
| 1          | 1          | 4          | [1, 2] |
| 1          | 2          | 5          | [1, 2, 3, 4] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
| 2          | NULL       | 9          | NULL   |
| 2          | NULL       | 10         | NULL   |
+------------+------------+------------+--------+

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
--SQL语句如下。
select pid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES) as window from tbl;

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1]    |
| 1          | NULL       | 2          | [2]    |
| 1          | 1          | 3          | [1, 2, 3] |
| 1          | 1          | 4          | [1, 2, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9]    |
| 2          | NULL       | 10         | [10]   |
+------------+------------+------------+--------+

partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
--SQL语句如下。
select pid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3, 4] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
--SQL语句如下。
select pid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING) as window from tbl;

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 3          | [3, 4, 5, 6, 7, 8] |
| 1          | 1          | 4          | [3, 4, 5, 6, 7, 8] |
| 1          | 2          | 5          | [5, 6, 7, 8] |
| 1          | 4          | 6          | [6, 7, 8] |
| 1          | 7          | 7          | [7, 8] |
| 1          | 11         | 8          | [8]    |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
--SQL语句如下。
select pid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between 3 PRECEDING and 1 PRECEDING) as window from tbl;

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | NULL   |
| 1          | 1          | 4          | NULL   |
| 1          | 2          | 5          | [3, 4] |
| 1          | 4          | 6          | [3, 4, 5] |
| 1          | 7          | 7          | [6]    |
| 1          | 11         | 8          | NULL   |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
--SQL语句如下。
select pid, 
collect_list(rid) over(partition by pid order by 
oid GROUPS between 2 PRECEDING and CURRENT ROW) as window from tbl;

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3, 4] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [3, 4, 5, 6] |
| 1          | 7          | 7          | [5, 6, 7] |
| 1          | 11         | 8          | [6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

函数	功能
AVG	计算平均值。
COUNT	计算记录数。
COUNT_IF	计算指定表达式为True的记录数。
MAX	计算最大值。
MIN	计算最小值。
MEDIAN	计算中位数。
STDDEV	计算总体标准差。
STDDEV_SAMP	计算样本标准差。
SUM	计算汇总值。
WM_CONCAT	用指定的分隔符连接字符串。
ANY_VALUE	在指定范围内任选一个值返回。
APPROX_DISTINCT	返回输入的非重复值的近似数目。
ARG_MAX	返回指定列的最大值对应行的列值。
ARG_MIN	返回指定列的最小值对应行的列值。
MAX_BY	返回指定列的最大值对应行的列值。
MIN_BY	返回指定列的最小值对应行的列值。
COLLECT_LIST	将指定的列聚合为一个数组。
COLLECT_SET	将指定的列聚合为一个无重复元素的数组。
COVAR_POP	计算指定两个数值列的总体协方差。
COVAR_SAMP	计算指定两个数值列的样本协方差。
NUMERIC_HISTOGRAM	统计指定列的近似直方图。
PERCENTILE	计算精确百分位数，适用于小数据量。
PERCENTILE_APPROX	计算近似百分位数，适用于大数据量。
VARIANCE/VAR_POP	计算指定数值列的方差。
VAR_SAMP	计算指定数值列的样本方差。
BITWISE_OR_AGG	计算输入Value的bit OR聚合值。
BITWISE_AND_AGG	计算输入Value的bit AND聚合值。
MAP_AGG	构造两个输入字段的Map。
MULTIMAP_AGG	构造两个输入字段的Map，第一个字段作为Map的Key，第二个字段构造数组作为Map的Value。
MAP_UNION	对输入Map进行Union操作来构造输出Map。
MAP_UNION_SUM	对输入Map进行Union操作并对相同Key的Value求和来构造输出Map。
HISTOGRAM	构造输入Map的Key值出现次数的Map。

<aggregate_name>(<expression>[,...]) [within group (order by <col1>[,<col2>…])] [filter (where <where_condition>)]

```
--示例一，对输入数据升序排列后输出。
select 
  wm_concat(',', y) within group (order by y)
from values('k', 1),('k', 3),('k', 2) as t(x, y)
group by x;
--返回结果如下。
+------------+------------+
| x          | _c1        |
+------------+------------+
| k          | 1,2,3      |
+------------+------------+
--示例二，对输入数据降序排列后输出。
select 
  wm_concat(',', y) within group (order by y desc)
from values('k', 1),('k', 3),('k', 2) as t(x, y)
group by x;
--返回结果如下。
+------------+------------+
| x          | _c1        |
+------------+------------+
| k          | 3,2,1      |
+------------+------------+
```

--示例一，过滤并聚合数据。
select
  sum(x),
  sum(x) filter (where y > 1),
  sum(x) filter (where y > 2)
  from values(null, 1),(1, 2),(2, 3),(3, null) as t(x, y);
--返回结果如下。
+------------+------------+------------+
| _c0        | _c1        | _c2        |
+------------+------------+------------+
| 6          | 3          | 2          |
+------------+------------+------------+
--示例二，使用多个聚合函数过滤并聚合数据。
select
  count_if(x > 2),
  sum(x) filter (where y > 1),
  sum(x) filter (where y > 2)
  from values(null, 1),(1, 2),(2, 3),(3, null) as t(x, y);
--返回结果如下。
+------------+------------+------------+
| _c0        | _c1        | _c2        |
+------------+------------+------------+
| 1          | 3          | 2          |
+------------+------------+------------+

- ```
<aggregate_name>(<expression>[,...]) [filter (where <where_condition>)]
```
- ```
select sum(sal) filter (where deptno=10), sum(sal) filter (where deptno=20), sum(sal) filter (where deptno=30) from emp;
```
```
+------------+------------+------------+
| _c0        | _c1        | _c2        |
+------------+------------+------------+
| 17500      | 10875      | 9400       |
+------------+------------+------------+
```

函数	功能
ASCII	返回字符串的第一个字符的ASCII码。
CHAR_MATCHCOUNT	计算A字符串出现在B字符串中的字符个数。
CHR	将指定ASCII码转换成字符。
CONCAT	将字符串连接在一起。
CONCAT_WS	将参数中的所有字符串按照指定的分隔符连接在一起。
DECODE	将字符串按照指定编码格式解码。
ENCODE	将字符串按照指定编码格式编码。
FIND_IN_SET	在以逗号分隔的字符串中查找指定字符串的位置。
FORMAT_NUMBER	将数字转化为指定格式的字符串。
FROM_JSON	根据给定的JSON字符串和输出格式信息，返回ARRAY、MAP或STRUCT类型。
GET_JSON_OBJECT	在一个标准JSON字符串中，按照指定方式抽取指定的字符串。
INITCAP	将字符串转换为固定格式的字符串，单词之间以空格分隔。转换后的格式为：字符串中每个单词首字母大写，其余小写。
INSTR	计算A字符串在B字符串中的位置。
IS_ENCODING	判断字符串是否可以从指定的A字符集转换为B字符集。
KEYVALUE	将字符串拆分为Key-Value对，并将Key-Value对分开，返回Key对应的Value。
KEYVALUE_TUPLE	将字符串拆分为多个Key-Value对，并将Key-Value对分开，返回多个Key对应的Value。
LENGTH	计算字符串的长度。
LENGTHB	计算字符串以字节为单位的长度。
LOCATE	在字符串中查找另一指定字符串的位置。
LTRIM	去除字符串的左端字符。
MD5	计算字符串的MD5值。
PARSE_URL	对URL进行解析返回指定部分的信息。
PARSE_URL_TUPLE	对URL进行解析返回多个部分的信息。
REGEXP_COUNT	计算字符串从指定位置开始，匹配指定规则的子串数。
REGEXP_EXTRACT	将字符串按照指定规则拆分为组后，返回指定组的字符串。
REGEXP_INSTR	返回字符串从指定位置开始，与指定规则匹配指定次数的子串的起始或结束位置。
REGEXP_REPLACE	将字符串中，与指定规则在指定次数匹配的子串替换为另一字符串。
REGEXP_SUBSTR	返回字符串中，从指定位置开始，与指定规则匹配指定次数的子串。
REPEAT	返回将字符串重复指定次数后的结果。
REVERSE	返回倒序字符串。
RTRIM	去除字符串的右端字符。
SPACE	生成空格字符串。
SPLIT_PART	按照分隔符拆分字符串，返回指定部分的子串。
SUBSTR	返回STRING类型字符串从指定位置开始，指定长度的子串。
SUBSTRING	返回STRING或BINARY类型字符串从指定位置开始，指定长度的子串。
TO_CHAR	将BOOLEAN、BIGINT、DECIMAL或DOUBLE类型值转为对应的STRING类型表示。
TO_JSON	将指定的复杂类型输出为JSON字符串。
TOLOWER	将字符串中的英文字符转换为小写形式。
TOUPPER	将字符串中的英文字符转换为大写形式。
TRIM	去除字符串的左右两端字符。
URL_DECODE	将字符串从 `application/x-www-form-urlencoded MIME` 格式转为常规字符。
URL_ENCODE	将字符串编码为 `application/x-www-form-urlencoded MIME` 格式。
JSON_TUPLE	在一个标准的JSON字符串中，按照输入的一组键抽取各个键指定的字符串。
LPAD	将字符串向左补足到指定位数。
RPAD	将字符串向右补足到指定位数。
REPLACE	将字符串中与指定字符串匹配的子串替换为另一字符串。
SOUNDEX	将普通字符串替换为SOUNDEX字符串。
SUBSTRING_INDEX	截取字符串指定分隔符前的字符串。
TRANSLATE	将A出现在B中的字符串替换为C字符串。
REGEXP_EXTRACT_ALL	在字符串中查找所有出现的正则表达式匹配的子字符串，并把找到的字符串以数组形式返回。

函数类别	函数	功能
ARRAY函数	ALL_MATCH	判断ARRAY数组中是否所有元素都满足指定条件。
	ANY_MATCH	判断ARRAY数组中是否存在满足指定条件的元素。
	ARRAY	使用给定的值构造ARRAY。
	ARRAY_CONTAINS	检测指定的ARRAY中是否包含指定的值。
	ARRAY_DISTINCT	去除ARRAY数组中的重复元素。
	ARRAY_EXCEPT	找出在ARRAY A中，但不在ARRAY B中的元素，并去掉重复的元素后，以ARRAY形式返回结果。
	ARRAY_INTERSECT	计算两个ARRAY数组的交集。
	ARRAY_JOIN	将ARRAY数组中的元素按照指定字符串进行拼接。
	ARRAY_MAX	计算ARRAY数组中的最大值。
	ARRAY_MIN	计算ARRAY数组中的最小值。
	ARRAY_POSITION	计算指定元素在ARRAY数组中第一次出现的位置。
	ARRAY_REDUCE	将ARRAY数组的元素进行聚合。
	ARRAY_REMOVE	在ARRAY数组中删除指定元素。
	ARRAY_REPEAT	返回将指定元素重复指定次数后的ARRAY数组。
	ARRAY_SORT	将ARRAY数组的元素进行排序。
	ARRAY_UNION	计算两个ARRAY数组的并集并去掉重复元素。
	ARRAYS_OVERLAP	判断两个ARRAY数组中是否包含相同元素。
	ARRAYS_ZIP	合并多个ARRAY数组。
	CONCAT	将ARRAY数组或字符串连接在一起。
	EXPLODE	将一行数据转为多行的UDTF。
	FILTER	将ARRAY数组中的元素进行过滤。
	INDEX	返回ARRAY数组指定位置的元素值。
	POSEXPLODE	将指定的ARRAY展开，每个Value一行，每行两列分别对应数组从0开始的下标和数组元素。
	SIZE	返回指定ARRAY中的元素数目。
	SLICE	对ARRAY数据切片，返回从指定位置开始、指定长度的数组。
	SORT_ARRAY	为指定的数组中的元素排序。
	TRANSFORM	将ARRAY数组中的元素进行转换。
	ZIP_WITH	将2个ARRAY数组按照位置进行元素级别的合并。
MAP函数	EXPLODE	将一行数据转为多行的UDTF。
	INDEX	返回MAP类型参数中满足指定条件的Value。
	MAP	使用指定的Key-Value对建立MAP。
	MAP_CONCAT	返回多个MAP的并集。
	MAP_ENTRIES	将MAP中的Key、Value键值映射转换为STRUCT结构数组。
	MAP_FILTER	将MAP中的元素进行过滤。
	MAP_FROM_ARRAYS	通过给定的ARRAY数组构造MAP。
	MAP_FROM_ENTRIES	通过给定的结构体数组构造MAP。
	MAP_KEYS	将参数MAP中的所有Key作为数组返回。
	MAP_VALUES	将参数MAP中的所有Value作为数组返回。
	MAP_ZIP_WITH	对输入的两个MAP进行合并得到一个新MAP。
	SIZE	返回指定MAP中的K/V对数。
	TRANSFORM_KEYS	对MAP进行变换，保持Value不变，根据指定函数计算新的Key。
	TRANSFORM_VALUES	对MAP进行变换，保持Key不变，根据指定函数计算新的Value。
STRUCT函数	FIELD	获取STRUCT中的成员变量的取值。
	INLINE	将指定的STRUCT数组展开。每个数组元素对应一行，每行每个STRUCT元素对应一列。
	STRUCT	使用给定Value列表建立STRUCT。
	NAMED_STRUCT	使用给定的Name、Value列表建立STRUCT。
JSON函数	FROM_JSON	根据给定的JSON字符串和输出格式信息，返回ARRAY、MAP或STRUCT类型。
	GET_JSON_OBJECT	在一个标准JSON字符串中，按照指定方式抽取指定的字符串。
	JSON_TUPLE	在一个标准的JSON字符串中，按照输入的一组键抽取各个键指定的字符串。
	TO_JSON	将指定的复杂类型输出为JSON字符串。
	JSON_OBJECT	生成JSON OBJECT，要求key和value成对出现。
	JSON_ARRAY	生成JSON ARRAY。将一个可能为空的JSON类型对象，转换为包含这些类型的数组。
	JSON_EXTRACT	解析JSON表达式中对应json_path的数据，注意json_path非法时会报错。
	JSON_EXISTS	查看json_path对应的JSON值是否存在。
	JSON_PRETTY	美化JSON，增加换行及空格。
	JSON_TYPE	返回JSON数据所属的数据类型名称。
	JSON_FORMAT	将JSON数据转换成STRING类型，默认不自动进行美化。
	JSON_PARSE	将STRING类型转成JSON类型，非JSON格式转换为字符串会报错。
	JSON_VALID	检查字符串是否为合法的JSON格式。
	CAST	支持基本类型与JSON类型的转换。

函数	功能
SYM_ENCRYPT	对表里的指定列做随机性加密，返回BINARY类型的密文。
SYM_DECRYPT	对表里的指定已经随机性加密的列做解密，BINARY类型的明文。

函数	功能
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哈希值。
SIGN	判断正负值属性。
SPLIT	将字符串按照指定的分隔符分割后返回数组。
STACK	将指定的参数组分割为指定的行数。
STR_TO_MAP	将字符串按照指定的分隔符分割得到Key和Value。
TABLE_EXISTS	查询指定的表是否存在。
TRANS_ARRAY	将一行数据转为多行的UDTF，将列中存储的以固定分隔符格式分隔的数组转为多行。
TRANS_COLS	将一行数据转为多行数据的UDTF，将不同的列拆分为不同的行。
UNBASE64	将BASE64编码格式字符串转换为二进制表示值。
UNIQUE_ID	返回一个随机ID，运行效率高于UUID函数。
UUID	返回一个随机ID。

使用odpscmd客户端	使用Studio
需要将客户端升级到最新版本，否则无法使用 `desc json_table` 命令。需要将客户端安装路径下的 conf\odps_config.ini 文件中的参数 `use_instance_tunnel` 设置为false，否则查询会报错。	Studio只支持查询JSON类型的操作，不支持上传、下载JSON类型数据。

函数类型	说明
日期函数	支持处理DATE、DATETIME、TIMESTAMP等日期类型数据，实现加减日期、计算日期差值、提取日期字段、获取当前时间、转换日期格式等业务处理能力。
数学函数	支持处理BIGINT、DOUBLE、DECIMAL、FLOAT等数值类型数据，实现转换进制、数学运算、四舍五入、获取随机数等业务处理能力。
窗口函数	支持在指定的开窗列中，实现求和、求最大最小值、求平均值、求中间值、数值排序、数值偏移、抽样等业务处理能力。
聚合函数	支持将多条输入记录聚合成一条输出值，实现求和、求平均值、求最大最小值、求平均值、参数聚合、字符串连接等业务处理能力。
字符串函数	支持处理STRING类型字符串，实现截取字符串、替换字符串、查找字符串、转换大小写、转换字符串格式等业务处理能力。
复杂类型函数	支持处理MAP、ARRAY、STRUCT及JSON类型数据，实现去重元素、聚合元素、元素排序、合并元素等业务处理能力。
加密函数	支持处理STRING、BINARY类型的表数据，实现加密、解密等业务处理能力。
其他函数	除上述函数之外，提供支持其他业务场景的函数。

背景信息

注意事项

日期函数

数学函数

窗口函数

聚合函数

字符串函数

复杂类型函数

加密函数

其他函数

JSON函数的使用限制

字符串函数的使用限制