:ビルトイン関数に関するよくある質問

2010/1/3 のような日付を 2010-01-03 に変換するにはどうすればよいですか?

ゼロ埋めされた入力 (例: 2010/01/03) の場合は、TO_DATE と TO_CHAR を組み合わせます。

SELECT TO_CHAR(TO_DATE('2010/01/03', 'yyyy/mm/dd'), 'yyyy-mm-dd');
-- Returns: 2010-01-03

ゼロ埋めされていない入力（例：2010/1/3）の場合、ビルトイン関数では直接解析できません。ユーザー定義関数 (UDF) を作成して、可変長の日付部分を処理します。このトピックでは、MaxCompute でサポートされている UDF のタイプ、シナリオ、開発プロセス、および使用上の注意事項について説明します。

詳細については、「TO_DATE」と「TO_CHAR」をご参照ください。

UNIX タイムスタンプを DATETIME 値に変換するにはどうすればよいですか?

FROM_UNIXTIME を使用します。

SELECT FROM_UNIXTIME(1609459200);
-- Returns: 2021-01-01 08:00:00

詳細については、「FROM_UNIXTIME」をご参照ください。

現在のシステム時刻を取得するにはどうすればよいですか?

GETDATE を使用します。

SELECT GETDATE();
-- Returns the current date and time, e.g., 2026-02-15 10:30:00

詳細については、「GETDATE」をご参照ください。

YEAR、QUARTER、MONTH、または DAY で「解決できません」エラーが発生するのはなぜですか?

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

YEAR、QUARTER、MONTH、および DAY は、MaxCompute V2.0 で導入された拡張関数です。これらには V2.0 データ型エディションが必要です。

SQL 文の前に次の設定を追加します。

SET odps.sql.type.system.odps2 = true;
SELECT YEAR(GETDATE());

TO_DATE が「分部分がありません」エラーで失敗するのはなぜですか?

FAILED: ODPS-0121095:Invalid arguments - format string has second part, but doesn't have minute part : yyyy-MM-dd HH:mm:ss

MaxCompute では、mm と MM は両方とも月を表します。分には mi を使用します。

-- Incorrect
SELECT TO_DATE('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mm:ss');

-- Correct
SELECT TO_DATE('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mi:ss');

これは、mm が分を表す他の多くの SQL プラットフォームとは異なります。MaxCompute では、分コンポーネントには常に mi を使用します。

ROUND(4.515, 2) が 4.52 ではなく 4.51 を返すのはなぜですか?

DOUBLE 値は、精度が限られた 8 バイトの浮動小数点数です。4.515 という値は内部的に約 4.514999999... として保存されるため、小数点以下2桁に丸めると 4.51 になります。

SELECT ROUND(4.515, 2), ROUND(125.315, 2);
-- Returns: 4.51, 125.32

正確な丸めを行うには、DECIMAL 型を使用します。

SELECT ROUND(4.515BD, 2);
-- Returns: 4.52

DECIMAL 型は、浮動小数点表現の問題を回避し、正確な精度で値を保存します。

自動増分シーケンスを生成するにはどうすればよいですか?

ROW_NUMBER をウィンドウ関数として使用します。

SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_num, *
FROM my_table;

詳細については、「ROW_NUMBER」をご参照ください。

列の値を連結するにはどうすればよいですか?

WM_CONCAT を使用します。

SELECT WM_CONCAT(',', name) AS all_names
FROM my_table;
-- Returns: Alice,Bob,Charlie

詳細については、「WM_CONCAT」をご参照ください。

MaxCompute は MD5 をサポートしていますか?

はい。MD5 は文字列のハッシュを計算します。

SELECT MD5('hello');
-- Returns: 5d41402abc4b2a76b9719d911017c592

詳細については、「MD5」をご参照ください。

文字列をゼロで左埋めするにはどうすればよいですか?

LPAD を使用します。

SELECT LPAD('42', 6, '0');
-- Returns: 000042

詳細については、「LPAD」をご参照ください。

MaxCompute は SUBSTRING_INDEX をサポートしていますか?

はい。SUBSTRING_INDEX は MySQL と同じように動作します。

SELECT SUBSTRING_INDEX('www.example.com', '.', 2);
-- Returns: www.example

詳細については、「SUBSTRING_INDEX」をご参照ください。

REGEXP_COUNT はパターンパラメーターでネストされたクエリをサポートしていますか?

いいえ。REGEXP_COUNT の pattern パラメーターは、ネストされたクエリ文をサポートしていません。

詳細については、「REGEXP_COUNT」をご参照ください。

MaxCompute は Oracle の TO_CHAR 数値書式設定をサポートしていますか?

MaxCompute は Oracle の TO_CHAR(Data, FM9999.00) 構文をサポートしていません。代わりに FORMAT_NUMBER を使用します。

SELECT FORMAT_NUMBER(12332.123456, '#,###,###,###.###');
-- Returns: 12,332.123

詳細については、「FORMAT_NUMBER」をご参照ください。

条件に一致する JSON フィールドを集計するにはどうすればよいですか?

LIKE などの SQL 条件でレコードをフィルタリングし、ARRAY または MAP を使用して結果から複合型を構築します。結果を TO_JSON で JSON 文字列に変換します。

SELECT TO_JSON(ARRAY(col1, col2))
FROM my_table
WHERE col1 LIKE '%keyword%';

JSON キーを個別の列として抽出するにはどうすればよいですか?

GET_JSON_OBJECT を使用します。

SELECT
  GET_JSON_OBJECT(json_col, '$.name') AS name,
  GET_JSON_OBJECT(json_col, '$.age') AS age
FROM my_table;

詳細については、「GET_JSON_OBJECT」をご参照ください。

JSON 文字列を配列に変換するにはどうすればよいですか?

ターゲット型を文字列として FROM_JSON を使用します。

SELECT FROM_JSON(json_col, 'array<bigint>');
-- Converts a JSON array string like "[1, 2, 3]" to a MaxCompute ARRAY<BIGINT>

詳細については、「FROM_JSON」をご参照ください。

MaxCompute は IFNULL をサポートしていません。代わりに何を使用すればよいですか?

MaxCompute には IFNULL 関数がありません。使用しようとすると、次のエラーが発生します。

Semantic analysis exception - Invalid function : line 1:41 'ifnull'

次のいずれかの代替手段を使用します。

ほとんどの場合、NVL が最も簡単なドロップイン代替です。

-- MySQL
SELECT IFNULL(col, 0) FROM my_table;

-- MaxCompute equivalent
SELECT NVL(col, 0) FROM my_table;

詳細については、「NVL」、「COALESCE」、「CASE WHEN 式」、および「MaxCompute、Hive、MySQL、Oracle の関数マッピング」をご参照ください。

1 行を複数行に分割するにはどうすればよいですか?

TRANS_COLS を使用します。出力にはインデックスキー列と、それに続く入れ替えられたデータ列が含まれるため、エイリアスの数は 1 + データ列の数と等しくなければなりません。

SELECT TRANS_COLS(2, col1, col2) AS (idx, key, value)
FROM my_table;

詳細については、「TRANS_COLS」をご参照ください。

COALESCE を使用すると「GROUP BY キーにない式」というエラーが発生するのはなぜですか?

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

このエラーは、COALESCE 内の非集計式が、GROUP BY にリストされていない列を参照している場合に発生します。

次の例では、decode 内の get_json_object(extended_x, '$.table') が集計されておらず、GROUP BY にも含まれていないため、クエリが失敗します。

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
FROM e_orders
WHERE pt = '20170425'
GROUP BY aid, bid;

decode(get_json_object(extended_x, '$.table'), null, 0, 1) 式は個々の行で動作しますが、集計関数の外側に現れます。これを集計関数でラップするか、GROUP BY 句に含める必要があります。

MaxCompute V2.0 を有効にした後、暗黙の型変換エラーが発生するのはなぜですか?

MaxCompute V2.0 データ型エディションが有効化されている場合 (odps.sql.type.system.odps2=true)、次の暗黙の型変換は無効になります。

これらの変換は精度損失を引き起こす可能性があるため、V2.0 はデフォルトでこれらをブロックします。これを解決するには:

• 明示的な変換 (推奨): CAST を使用して型を明示的に変換します。詳細については、「CAST」をご参照ください。

    SELECT CAST(string_col AS BIGINT) FROM my_table;

• V2.0 データ型を無効にする: odps.sql.type.system.odps2=false を設定して、暗黙の変換を再度有効にします。この設定は、プロジェクトレベルの構成に応じて、YEAR、QUARTER、MONTH、および DAY などの V2.0 拡張関数も無効にする可能性があることに注意してください。