内建函数常见问题

本文为您介绍MaxCompute内建函数的常见问题。

问题类别

常见问题

日期函数

数学函数

使用ROUND函数对DOUBLE类型数据四舍五入,为何结果存在偏差?

窗口函数

MaxCompute可以通过哪个函数设置自增序列?

聚合函数

如何连接相同字段?

字符串函数

复杂类型函数

其他函数

隐式转换

使用MaxCompute内建函数时,隐式类型转换报错,如何解决?

MaxCompute是否支持将2010/1/3格式转换为2010-01-03?

如果日期为2010/01/03格式,可以通过to_char(to_date('2010/01/03', 'yyyy/mm/dd'), 'yyyy-mm-dd')函数进行转换,请参见TO_DATETO_CHAR

如果日期为2010/1/3格式,您需要自行编写UDF函数实现日期转换。编写UDF指导请参见MaxCompute UDF概述

如何将UNIX时间转为日期值?

您可以使用FROM_UNIXTIME函数将数字型的UNIX时间转为日期值,请参见FROM_UNIXTIME

如何获取系统当前时间?

您可以使用GETDATE函数获取系统当前时间,请参见GETDATE

使用YEAR、QUARTER、MONTH和DAY日期函数时,报错cannot be resolved,如何解决?

  • 问题现象

    使用YEAR、QUARTER、MONTH和DAY日期函数时,返回报错如下。

    FAILED: ODPS-0130071:[1,8] Semantic analysis exception - function or view 'year' cannot be resolved
  • 产生原因

    YEAR、QUARTER、MONTH和DAY为MaxCompute 2.0扩展函数,需要打开2.0数据类型开关。

  • 解决措施

    您需要在SQL语句前增加set语句set odps.sql.type.system.odps2 = true;,打开2.0新数据类型开关。

执行TO_DATE函数时,报错没有分钟部分,如何解决?

  • 问题现象

    执行SQL语句to_date('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mm:ss')时,返回报错如下。

    FAILED: ODPS-0121095:Invalid arguments - format string has second part, but doesn’t have minute part : yyyy-MM-dd HH:mm:ss                   
  • 产生原因

    TO_DATE函数的第二个参数格式有误。mmMM都表示月份,分钟需要使用mi

  • 解决措施

    修改SQL语句为to_date('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mi:ss')

使用ROUND函数对DOUBLE类型数据四舍五入,为何结果存在偏差?

使用round函数对DOUBLE类型的数据进行四舍五入,发现4.515四舍五入结果为4.51。SQL示例如下。

select round(4.515, 2),round(125.315, 2);                   

DOUBLE类型是8字节双精度浮点数,存在一定的精度差。4.515的DOUBLE类型表示结果为4.514999999...,因此四舍五入时被计算为4.51。

MaxCompute可以通过哪个函数设置自增序列?

您可以使用ROW_NUMBER函数设置自增序列,请参见ROW_NUMBER

如何连接相同字段?

您可以使用WM_CONCAT函数连接相同字段,请参见WM_CONCAT

MaxCompute是否支持MD5函数?

支持,请参见MD5

如何实现对固定长度字符串,不足部分前面补零?

您可以使用LPAD函数,请参见LPAD

MySQL支持的SUBSTRING_INDEX函数在MaxCompute中支持吗?

支持,请参见SUBSTRING_INDEX

REGEXP_COUNT函数的参数pattern是否支持嵌入查询语句?

不支持,使用方法请参见REGEXP_COUNT

MaxCompute是否支持Oracle的to_char(数据, FM9999.00)用法?

不支持。如果只涉及调整数字显示格式,您可以使用FORMAT_NUMBER函数实现。命令示例如下。

--返回12,332.123。
select format_number(12332.123456, '#,###,###,###.###');

在使用IFNULL函数时,报错Invalid function,如何解决?

  • 问题现象

    在如下SQL语句中使用IFNULL函数。

    select a.id as id > , ifnull(concat('phs\xxx', a.insy, '\xxxb\xxx', ifnull()))

    返回报错如下。

    Semantic analysis exception - Invalid function : line 1:41 'ifnull'
  • 产生原因

    MaxCompute没有提供ifnull函数导致报错。

  • 解决措施

    您需要使用case when表达式或coalesce命令,请参见CASE WHEN表达式COALESCE

如何将符合某条件的字段都拼到一个JSON中?

MaxCompute SQL通过过滤条件,例如like等,查询得到满足指定条件的数据,然后将这些数据通过ARRAYMAP函数构造为MAP、ARRAY复杂类型数据,然后再用TO_JSON函数聚合。

如何将一个JSON字符串中的每个Key当做一个字段?

您可以使用GET_JSON_OBJECT函数来提取字段。

如何将JSON字符串转换为ARRAY格式?

您可以使用FROM_JSON函数进行转换,例如select from_json(<col_name>, "array<bigint>");

MySQL的IFNULL函数对应MaxCompute的哪个函数?

MySQL的IFNULL函数对应MaxCompute的NVL函数。MaxCompute内建函数与Hive、MySQL及Oracle函数的对照关系请参见与Hive、MySQL、Oracle内建函数对照表

如何实现一行转多行?

您可以使用TRANS_COLS函数来将一行数据转为多行数据。

使用COALESCE函数时,报错Expression not in GROUP BY key,如何解决?

  • 问题现象

    使用COALESCE函数时,只要超过一个Expression,就会返回如下报错。

    FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 8:9 "$.table"                    

    报错SQL如下。

    select  
    md5(concat(aid,bid)) as id
    ,aid
    , bid
    , sum(amountdue) as amountdue
    , coalesce(
    sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
    decode(get_json_object(extended_x, '$.table'), null, 0, 1)
    ) as tableparty
    , decode(sum(headcount),null,0,sum(headcount) ) as headcount
    , 'a' as pt
    from e_orders
    where pt='20170425'
    group by aid, bid;
  • 产生原因

    GROUP BY后面缺少分组字段,因此报错。

  • 解决措施

    如下表达式的返回值实际上是字段,需要把整个表达式写在GROUP BY后面。

    coalesce(
    sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
    decode(get_json_object(extended_x, '$.table'), null, 0, 1)
    ) as tableparty
    , decode(sum(headcount),null,0,sum(headcount) ) as headcount

使用MaxCompute内建函数时,隐式类型转换报错,如何解决?

当MaxCompute项目打开2.0新数据类型开关(odps.sql.type.system.odps2=true)时,如下隐式类型转换会被禁用,存在精度损失或报错的风险:

  • STRING->BIGINT

  • STRING->DATETIME

  • DOUBLE->BIGINT

  • DECIMAL->DOUBLE

  • DECIMAL->BIGINT

此时,您可以通过CAST函数执行强制转换解决该问题或者关闭2.0新数据类型开关(odps.sql.type.system.odps2=false)。