MaxCompute自身预置了诸多函数，可以满足大部分业务场景的数据处理需求。本文为您介绍MaxCompute提供的函数类型及函数使用相关说明。

背景信息

使用MaxCompute内建函数过程中，需要注意的事项请参见注意事项。

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

MaxCompute内建函数的典型案例、错误码和常见问题请参见ROUND函数精度问题案例、实现GROUP_CONCAT函数能力案例、内建函数常见错误码和内建函数常见问题。

注意事项

在使用内建函数时，需要注意：
  • 内建函数的入参类型、入参数量、函数格式必须满足函数语法要求，否则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返回指定字段值落入的分组编号。

窗口函数

MaxCompute SQL提供了如下窗口函数，使用窗口函数对指定开窗列的数据灵活地进行分析处理工作。
函数功能
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>
    • function_name：内建窗口函数、聚合函数或用户自定义聚合函数UDAF。
    • expression：函数格式，具体格式以实际函数语法为准。
    • windowing_definition：窗口定义。详细语法格式请参见windowing_definition部分。
    • window_name：窗口名称。您可以使用window关键字自定义窗口，为windowing_definition定义名称。自定义语句（named_window_def）如下：
      window <window_name> as (<window_definition>)
      自定义语句在SQL中的位置如下：
      select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]
  • windowing_definition
    windowing_definition的语法声明如下。
    --partition_clause：
[partition by <expression> [, ...]]
--orderby_clause：
[order by <expression> [asc|desc][nulls {first|last}] [, ...]]
[<frame_clause>]

    在SELECT语句中加入窗口函数，计算窗口函数的结果时，数据会按照窗口定义中的partition by和order by语句进行分区和排序。如果没有partition by语句，则仅有一个分区，包含全部数据。如果没有order by语句，则分区内的数据会按照任意顺序排布，最终生成一个确定的数据流。之后对于每一行数据（当前行），会按照窗口定义中的frame_clause从数据流中截取一段数据，构成当前行的窗口。窗口函数会根据窗口中包含的数据，计算得到窗口函数针对当前行对应的输出结果。

    • partition by <expression> [, ...]：可选。指定分区。分区列的值相同的行被视为在同一个窗口内。详细格式请参见表操作。
    • order by <expression> [asc|desc][nulls {first|last}] [, ...]：可选。指定数据在一个窗口内如何排序。
      说明 当遇到相同的order by值时，排序结果不稳定。为减少随机性，应当尽可能保持order by值的唯一性。
    • frame_clause：可选。用于确定数据边界，更多frame_clause信息，请参见frame_clause部分。
  • frame_clause
    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：必填。frame_clause的类型，各类型的frame_start和frame_end实现规则不相同。其中：
      • ROWS类型：通过数据行数确定数据边界。
      • RANGE类型：通过比较order by列值的大小关系来确定数据边界。一般在窗口定义中会指定order by，未指定order by时，一个分区中的所有数据行具有相同的order by列值。NULL与NULL被认为是相等的。
      • GROUPS：一个分区中所有具有相同order by列值的数据组成一个GROUP。未指定order by时，分区中的所有数据组成一个GROUP。NULL与NULL被认为是相等的。
    • frame_start、frame_end：表示窗口的起始和终止边界。frame_start必填。frame_end可选，省略时默认值为CURRENT ROW。

      frame_start确定的位置必须在frame_end确定的位置的前面，或者等于frame_end的位置，即frame_start相比frame_end更靠近分区头部。分区头部是指数据按窗口定义中的order by语句排序之后第1行数据的位置。ROWS、RANGE、GROUPS类型对应的取值范围及实现逻辑如下。

      frame_clause类型frame_start/frame_end取值说明
      ROWS、RANGE、GROUPSUNBOUNDED PRECEDING表示分区的第一行，从1开始计数。
      UNBOUNDED FOLLOWING表示分区的最后一行。
      ROWSCURRENT ROW指当前行的位置。每一行数据都会对应一个窗口函数的结果值，当前行是指在给哪一行数据计算窗口函数的结果。
      offset PRECEDING指从当前行位置，向分区头部位置移动offset行的位置。例如0 PRECEDING指当前行，1 PRECEDING指前一行。offset必须为非负整数。
      offset FOLLOWING指从当前行位置，向分区尾部移动offset行的位置。例如0 FOLLOWING指当前行，1 FOLLOWING指下一行。offset必须为非负整数。
      RANGECURRENT ROW
      • 作为frame_start时，指第一条与当前行具有相同order by列值的数据的位置。
      • 作为frame_end时，指最后一条与当前行具有相同order by列值的数据的位置。
      offset PRECEDINGframe_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 FOLLOWINGframe_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数据的位置。
      GROUPSCURRENT ROW
      • 作为frame_start时，指当前行所属GROUP的第一条数据。
      • 作为frame_end时，指当前行所属GROUP的最后一行数据。
      offset PRECEDING
      • 作为frame_start时，指从当前行所属GROUP开始，朝分区头部移动offset个GROUP之后，所在GROUP的第一条数据的位置。
      • 作为frame_end时，指从当前行所属GROUP开始，朝分区头部移动offset个GROUP之后，所在GROUP的最后一条数据的位置。
      说明 frame_start不能设置为UNBOUNDED FOLLOWING，frame_end不能设置为UNBOUNED PRECEDING。
      offset FOLLOWING
      • 作为frame_start时，指从当前行所属GROUP开始，朝分区尾部移动offset个GROUP之后，所在GROUP的第一条数据的位置。
      • 作为frame_end时，指从当前行所属GROUP开始，朝分区尾部移动offset个GROUP之后，所在GROUP的最后一条数据的位置。
      说明 frame_start不能设置为UNBOUNDED FOLLOWING，frame_end不能设置为UNBOUNED PRECEDING。
    • frame_exclusion：可选。用于从窗口中剔除一部分数据。取值范围如下：
      • EXCLUDE NO OTHERS：表示不剔除任何数据。
      • EXCLUDE CURRENT ROW：表示剔除当前行。
      • EXCLUDE GROUP：表示剔除整个GROUP，即分区中与当前行具有相同order by值的所有数据。
      • EXCLUDE TIES：表示剔除整个GROUP，但保留当前行。
    默认frame_clause
    未显示设置frame_clause时，MaxCompute会使用默认的frame_clause来决定窗口所包含数据的边界。默认的frame_clause为：
    • 当开启Hive兼容模式（set odps.sql.hive.compatible=true;）时，默认的frame_clause如下，与大部分SQL系统相同。
      RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
    • 当关闭Hive兼容模式（set odps.sql.hive.compatible=false;），同时窗口定义中指定了order by语句，且窗口函数为AVG、COUNT、MAX、MIN、STDDEV、STEDEV_POP、STDDEV_SAMP或SUM时，会使用ROWS类型的默认frame_clause。
      ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
    窗口边界示例
    假设表tbl结构为pid: bigint, oid: bigint, rid: bigint，表中包含如下数据：
    +------------+------------+------------+
| 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         |
+------------+------------+------------+
    您可以将如下SQL语句中的...替换为窗口定义语句windowing_definition来展示每一条数据所对应的窗口包含的数据列表：
    说明 Window列为NULL时，不包含任何数据。
    • ROW类型窗口
      • 窗口定义1
        partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
--SQL语句如下。
select pid, 
       oid, 
       rid, 
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] |
+------------+------------+------------+--------+
      • 窗口定义2
        partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
--SQL语句如下。
select pid, 
       oid, 
       rid, 
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] |
+------------+------------+------------+--------+
      • 窗口定义3
        partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
--SQL语句如下。
select pid, 
       oid, 
       rid, 
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   |
+------------+------------+------------+--------+
      • 窗口定义4
        partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
--SQL语句如下。
select pid, 
oid, 
rid, 
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]    |
+------------+------------+------------+--------+
      • 窗口定义5
        partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
--SQL语句如下。
select pid, 
       oid, 
       rid, 
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   |
+------------+------------+------------+--------+
      • 窗口定义6
        partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
--SQL语句如下。
select pid, 
       oid, 
       rid, 
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]   |
+------------+------------+------------+--------+
        对比本示例与前一个示例中rid为2、4、10的window结果，可以观察到EXCLUDE CURRENT ROW与EXCLUDE GROUP的差异，即对于EXCLUDE GROUP，同一个分区中（pid相等），与当前行为相同oid的数据都被剔除了。
    • RANGE类型窗口
      • 窗口定义1
        partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
--SQL语句如下。
select pid, 
       oid, 
       rid, 
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] |
+------------+------------+------------+--------+
        CURRENT ROW作为frame_end时，取与当前行具有相同order by值oid的最后一条数据，因此rid为1的记录的window结果为[1, 2]。
      • 窗口定义2
        partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
--SQL语句如下。
select pid, 
       oid, 
       rid, 
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] |
+------------+------------+------------+--------+
      • 窗口定义3
        partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
--SQL语句如下。

select pid, 
       oid, 
       rid, 
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] |
+------------+------------+------------+--------+
        order by值oid为NULL的行，对于offset {PRECEDING|FOLLOWING}，只要offset不为UNBOUNDED，则作为frame_start，指向分区中第一条order by值为NULL的数据；作为frame_end，指向最后一条order by值为NULL的数据。
    • GROUPS类型窗口

      窗口定义如下：

      partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
--SQL语句如下。
select pid, 
       oid, 
       rid, 
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] |
+------------+------------+------------+--------+

聚合函数

MaxCompute SQL支持的聚合函数如下。
函数功能
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>)]
    • <aggregate_name>(<expression>[,...])：内建聚合函数或用户自定义聚合函数UDAF，具体格式以实际聚合函数语法为准。
    • within group (order by <col1>[,<col2>…])：当聚合函数中携带该表达式时，默认会对<col1>[,<col2>…]的输入数据进行升序排列。如果需要降序排列，表达式为within group (order by <col1>[,<col2>…] [desc])。
      在使用该表达式时，您需要注意：
      • 仅支持WM_CONCAT、COLLECT_LIST、COLLECT_SET及UDAF使用该表达式。
      • 一个SELECT语句中如果多个聚合函数携带within group (order by <col1>[,<col2>…])表达式时，order by <col1>[,<col2>…]必须相同。
      • 如果聚合函数的参数中携带了DISTINCT关键字，order by <col1>[,<col2>…]中必须包含DISTINCT的列。
      命令示例如下：
      --示例一，对输入数据升序排列后输出。
select 
  x,
  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 
  x,
  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      |
+------------+------------+
    • [filter (where <where_condition>)]：当聚合函数中携带该表达式时，聚合函数只会处理满足<where_condition>的数据。更多<where_condition>信息，请参见WHERE子句（where_condition）。
      在使用该表达式时，您需要注意：
      • 仅支持内建聚合函数使用该表达式，不支持UDAF使用该表达式。
      • count(*)不支持[filter (where <where_condition>)]表达式。如果您需要对count(*)添加过滤条件，可以使用COUNT_IF。
      • COUNT_IF也不支持[filter (where <where_condition>)]表达式。
      命令示例如下：
      --示例一，过滤并聚合数据。
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          |
+------------+------------+------------+
  • 过滤条件表达式
    • 使用限制。
      • 仅MaxCompute内置的聚合函数支持添加过滤条件表达式，自定义聚合函数UDAF暂不支持。
      • count(*)不能与过滤条件表达式同时使用，请使用COUNT_IF函数。
    • 命令格式。
      <aggregate_name>(<expression>[,...]) [filter (where <where_condition>)]
    • 命令说明。

      聚合函数都可以增加过滤条件表达式。如果指定了过滤条件，则只有满足过滤条件的行数据才会传给对应的聚合函数进行处理。

    • 参数说明。
      • aggregate_name：必填。聚合函数名称，请根据实际需求选择下方的聚合函数。
      • expression：必填。聚合函数的参数。请根据各个聚合函数的参数说明填写。
      • where_condition：可选。过滤条件。更多过滤条件信息，请参见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       |
+------------+------------+------------+

字符串函数

MaxCompute SQL支持的字符串函数如下。
函数功能
ASCII返回字符串的第一个字符的ASCII码。
CHAR_MATCHCOUNT计算A字符串出现在B字符串中的字符个数。
CHR将指定ASCII码转换成字符。
CONCAT将字符串连接在一起。
CONCAT_WS将参数中的所有字符串按照指定的分隔符连接在一起。
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在字符串中查找所有出现的正则表达式匹配的子字符串，并把找到的字符串以数组形式返回。

复杂类型函数

MaxCompute SQL支持的复杂类型函数如下。
函数类别函数功能
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字符串。

加密函数

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

其他函数

MaxCompute SQL支持的其他类型函数如下。
函数功能
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。