文档

内建函数概述

更新时间:

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 byorder 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_startframe_end位置的数据行。

    • ROWS|RANGE|GROUPS:必填。frame_clause的类型,各类型的frame_startframe_end实现规则不相同。其中:

      • ROWS类型:通过数据行数确定数据边界。

      • RANGE类型:通过比较order by列值的大小关系来确定数据边界。一般在窗口定义中会指定order by,未指定order by时,一个分区中的所有数据行具有相同的order by列值。NULL与NULL被认为是相等的。

      • GROUPS:一个分区中所有具有相同order by列值的数据组成一个GROUP。未指定order by时,分区中的所有数据组成一个GROUP。NULL与NULL被认为是相等的。

    • frame_startframe_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_startframe_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_startframe_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 byoid的最后一条数据,因此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 byoid为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_CONCATCOLLECT_LISTCOLLECT_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

将参数中的所有字符串按照指定的分隔符连接在一起。

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

在字符串中查找所有出现的正则表达式匹配的子字符串,并把找到的字符串以数组形式返回。

复杂类型函数

MaxCompute SQL支持的复杂类型函数如下。其中JSON函数的使用限制请参见JSON函数的使用限制

函数类别

函数

功能

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类型的转换。

加密函数

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。

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 BYGROUP 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:将字符串中每个单词首字母(英文字符)转化为大写形式,其余为小写。