云原生大数据计算服务 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_DATE和TO_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函数的第二个参数格式有误。mm和MM都表示月份，分钟需要使用mi。

• 解决措施

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

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

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等，查询得到满足指定条件的数据，然后将这些数据通过ARRAY或MAP函数构造为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内建函数时，隐式类型转换报错，如何解决？