本文為您介紹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類型資料四捨五入,為何結果存在偏差?
使用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
等,查詢得到滿足指定條件的資料,然後將這些資料通過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內建函數時,隱式類型轉換報錯,如何解決?
當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
)。