云原生大数据计算服务 MaxCompute：内建函数概述

注意事项

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

日期函数

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

数学函数

窗口函数

• 窗口函数语法

  窗口函数的语法声明如下。

  <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、GROUPS	UNBOUNDED PRECEDING	表示分区的第一行，从1开始计数。
    	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的最后一条数据的位置。 说明 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] |
    +------------+------------+------------+--------+

聚合函数

字符串函数

复杂类型函数

加密函数

其他函数

JSON函数的使用限制

• 目前支持的开发工具包括odpscmd客户端和Studio，暂不支持DataWorks，Dataphin等外围生态，如果需要跟外部系统做组合使用时，请先确认后再使用。使用odpscmd客户端和Studio时需要关注以下内容。

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

• 如果表存在其他引擎读取情况，比如Hologres等，目前不支持读取JSON数据类型。

• 暂不支持对一张表新增JSON列。

• 暂不支持对JSON类型的比较操作，也不支持对JSON类型进行ORDER BY、GROUP BY或作为JOIN的key等。

• 目前JSON NUMBER的整数和小数分别使用BIGINT和DOUBLE类型进行存储。当整数部分超出BIGINT范围时会溢出，小数转为DOUBLE时会损失精度。

• 生成JSON类型数据所用的字符串里不支持UNICODE\u0000。

• Java UDF和Python UDF暂不支持JSON类型。

字符串函数的使用限制

以下函数只支持英文字符的转换：

• TRIM/RTRIM/LTRIM：trimChars只支持英文字符。

• REVERSE：在Hive模式下只支持英文字符。

• SOUNDEX：仅转换英文字符。

• TOLOWER：将字符串中的英文字符转换为小写形式。

• TOUPPER：将字符串中的英文字符转换为大写形式。

• INITCAP：将字符串中每个单词首字母（英文字符）转化为大写形式，其余为小写。