その他のデータ処理関数のリファレンス - MaxCompute - Alibaba Cloud - MaxCompute

MaxCompute SQL は、他にもいくつかの一般的な関数を提供しています。このトピックでは、CAST、FAILIF、HASH などの関数の構文、パラメーター、および使用例について説明します。

関数	機能
BETWEEN AND 式	指定された範囲内のデータをフィルターできます。
CASE WHEN 式	式の計算結果に基づいて値を返します。
CAST	式の結果を指定されたデータの型に変換します。
COALESCE	パラメーターリスト内の最初の null でない値を返します。
COMPRESS	GZIP アルゴリズムを使用して、STRING 型または BINARY 型の入力パラメーターを圧縮します。
CRC32	STRING 型または BINARY 型の値の巡回冗長検査値を計算します。
DECOMPRESS	GZIP アルゴリズムを使用して、BINARY 型の入力パラメーターを解凍します。
FAILIF	式の評価結果に基づいて、true またはカスタム情報を含むエラーメッセージを返します。
GET_IDCARD_AGE	ID カード番号に基づいて年齢を年単位で返します。
GET_IDCARD_BIRTHDAY	ID カード番号に基づいて生年月日を返します。
GET_IDCARD_SEX	ID カード番号に基づいて性別を返します。
GET_USER_ID	現在のアカウントの ID を取得します。
HASH	入力パラメーターに基づいてハッシュ値を計算します。
IF	指定された条件が true かどうかをチェックします。
MAX_PT	パーティションテーブル内の最大のハッシュパーティションの名前を返します。
NULLIF	2 つの入力パラメーターの値が同じかどうかをチェックします。
NVL	値が null のパラメーターの戻り値を指定します。
ORDINAL	入力変数の値を昇順にソートし、指定された位置にランク付けされた値を返します。
PARTITION_EXISTS	指定されたパーティションがテーブルに存在するかどうかをチェックします。
SAFE_CAST	式 (expr) をターゲットデータの型 (type) に変換します。
SAMPLE	読み取られたすべての列値をサンプリングし、サンプリング条件を満たさない行をフィルターで除外します。
SHA	STRING 型または BINARY 型の値の SHA-1 ハッシュ値を計算します。
SHA1	STRING 型または BINARY 型の値の SHA-1 ハッシュ値を計算します。
SHA2	STRING 型または BINARY 型の値の SHA-2 ハッシュ値を計算します。
STACK	指定されたパラメーターグループを指定された行数に分割します。
STR_TO_MAP	指定されたデリミタで文字列を分割し、キーと値のペアを返します。
TABLE_EXISTS	指定されたテーブルが存在するかどうかをチェックします。
TRANS_ARRAY	1 行のデータを複数の行に入れ替えます。この関数は、列内の固定デリミタで区切られた配列を複数の行に入れ替えるユーザー定義テーブル関数 (UDTF) です。
TRANS_COLS	1 つ以上の列を分割して単一の行を複数の行に変換するユーザー定義テーブル関数 (UDTF) です。
UNIQUE_ID	一意の ID を返します。この関数は UUID 関数よりも効率的です。
UUID	ランダムな ID を返します。

BETWEEN AND 式

構文
```
<a> [NOT] between <b> and <c>
```
説明
a の値が b と c の範囲内にあるか、または b と c の範囲外にあるかを確認します。
パラメーター
- a：必須。チェックするフィールド。
- b と c：必須。これらのパラメーターは値の範囲を指定します。b と c のデータの型は、a パラメーターのデータの型と同じである必要があります。
戻り値
条件を満たすデータが返されます。
パラメーター a、b、または c が null の場合、この関数は null を返します。

例

テーブル emp には、次のデータが含まれています。

| empno | ename | job | mgr | hiredate| sal| comm | deptno |
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

sal の値が 1,000 から 1,500 までのデータをクエリします。文の例：

select * from emp where sal between 1000 and 1500;

次の結果が返されます。

+-------+-------+-----+------------+------------+------------+------------+------------+
| empno | ename | job | mgr        | hiredate   | sal        | comm       | deptno     |
+-------+-------+-----+------------+------------+------------+------------+------------+
| 7521  | WARD  | SALESMAN | 7698  | 1981-02-22 00:00:00 | 1250.0     | 500.0      | 30  |
| 7654  | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.0     | 1400.0     | 30 |
| 7844  | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.0     | 0.0        | 30 |
| 7876  | ADAMS | CLERK | 7788  | 1987-05-23 00:00:00 | 1100.0     | NULL     | 20   |
| 7934  | MILLER | CLERK | 7782  | 1982-01-23 00:00:00 | 1300.0     | NULL      | 10  |
| 7956  | TEBAGE | CLERK | 7748  | 1982-12-30 00:00:00 | 1300.0     | NULL      | 10  |
+-------+-------+-----+------------+------------+------------+------------+------------+

CASE WHEN 式

構文

MaxCompute は、CASE WHEN 式に対して次の 2 つのフォーマットを提供します。

case <value>
when <value1> then <result1>
when <value2> then <result2>
...
else <resultn>
end

case
when (<_condition1>) then <result1>
when (<_condition2>) then <result2>
when (<_condition3>) then <result3>
...
else <resultn>
end

説明
値または_条件の評価に基づいて、結果を返します。
パラメーター
- value：必須。比較する値。
- _condition：必須。評価する条件。
- result：必須。返す値。
戻り値
- すべての result 値が BIGINT 型または DOUBLE 型の場合、返される前に DOUBLE 型に変換されます。
- いずれかの result 値が STRING 型の場合、すべての result 値は返される前に STRING 型に変換されます。データの型変換がサポートされていない場合、エラーが返されます。たとえば、BOOLEAN 型のデータは STRING 型に変換できません。
- 他のデータの型間の変換はサポートされていません。

例

テーブル sale_detail には、shop_name string、customer_id string、total_price double の列が含まれています。テーブルには次のデータが含まれています。

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

以下はコマンドの例です。

select 
case  
when region='china' then 'default_region'
when region like 'shang%' then 'sh_region'
end as region 
from sale_detail;

次の結果が返されます。

+------------+
| region     |
+------------+
| default_region |
| default_region |
| default_region |
| sh_region  |
| sh_region  |
| sh_region  |
+------------+

CAST

構文
```
cast(<expr> as <type>)
```
説明
expr の値をターゲットデータの型 type に変換します。
パラメーター
- expr：必須。変換する式。
- type：必須。ターゲットデータの型。次の例は使用方法を示しています。
  - cast(double as bigint)：DOUBLE 型の値を BIGINT 型に変換します。
  - cast(string as bigint)：STRING 型の値を BIGINT 型に変換します。文字列に整数のみが含まれている場合、直接 BIGINT 型に変換されます。文字列に浮動小数点数が含まれているか、指数形式である場合、まず DOUBLE 型に変換され、次に BIGINT 型に変換されます。
  - デフォルトの日付形式 yyyy-mm-dd hh:mi:ss は cast(string as datetime) または cast(datetime as string) に使用されます。
戻り値
- ターゲットデータの型の値を返します。
- setproject odps.function.strictmode=false を実行すると、文字列の数値プレフィックスが返されます。
- setproject odps.function.strictmode=true コマンドを実行すると、エラーが返されます。
- 値を DECIMAL 型に変換する場合、odps.sql.decimal.tostring.trimzero=true を設定すると小数点以下の末尾のゼロは削除されます。odps.sql.decimal.tostring.trimzero=false を設定すると、末尾のゼロは保持されます。
  重要
  odps.sql.decimal.tostring.trimzero パラメーターは、テーブルから取得したデータと静的な値の両方に影響します。

例

例 1：一般的な使用方法。

--1 を返します。
select cast('1' as bigint);

例 2：STRING 値を BOOLEAN 値に変換します。STRING が空の場合、false が返されます。それ以外の場合、true が返されます。
- STRING は空です。
```
select cast("" as boolean);
--戻り値
+------+
| _c0  |
+------+
| false |
+------+
```
- STRING は空ではありません。
```
select cast("false" as boolean);
--true を返します
+------+
| _c0  |
+------+
| true |
+------+
```

例 3：文字列を日付に変換します。

--文字列を日付に変換します。
select cast("2022-12-20" as date);
--戻り値
+------------+
| _c0        |
+------------+
| 2022-12-20 |
+------------+

--時間部分を含む日付文字列を日付に変換します。
select cast("2022-12-20 00:01:01" as date);
--戻り値
+------------+
| _c0        |
+------------+
| NULL       |
+------------+
--値が正しく表示されるようにするには、次のパラメーターを設定します。
set odps.sql.executionengine.enable.string.to.date.full.format= true;
select cast("2022-12-20 00:01:01" as date);
--戻り値
+------------+
| _c0        |
+------------+
| 2022-12-20 |
+------------+

説明

デフォルトでは、odps.sql.executionengine.enable.string.to.date.full.format パラメーターは false です。時間部分を含む日付文字列を変換するには、このパラメーターを true に設定します。

例 4 (不正なコマンドの例)：無効な使用方法。変換が失敗した場合、またはサポートされていない場合は、例外がスローされます。次のコマンドは、不正な使用方法の例です。
```
select cast('abc' as bigint);
```

例 5：setproject odps.function.strictmode=false が設定されているシナリオの例。

setprojectodps.function.strictmode=false;
select cast('123abc'as bigint);
--戻り値
+------------+
|_c0|
+------------+
|123|
+------------+

例 6：setproject odps.function.strictmode=true が設定されているシナリオの例。

setprojectodps.function.strictmode=true;
select cast('123abc' as bigint);
--戻り値
FAILED:ODPS-0130071:[0,0]Semanticanalysisexception-physicalplangenerationfailed:java.lang.NumberFormatException:ODPS-0123091:Illegaltypecast-Infunctioncast,value'123abc'cannotbecastedfromStringtoBigint.

例 7：odps.sql.decimal.tostring.trimzero が設定されているシナリオの例。

--テーブルを作成します。
create table mf_dot (dcm1 decimal(38,18),
                     dcm2 decimal(38,18));
--データを挿入します。
insert into table mf_dot values (12.45500BD,12.3400BD);

--フラグが true または設定されていない場合。
set odps.sql.decimal.tostring.trimzero=true;
--小数点以下の末尾のゼロを削除します。
select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot;
--戻り値
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 12.455     | 12.34      |
+------------+------------+

--フラグが false の場合。
set odps.sql.decimal.tostring.trimzero=false;
--小数点以下の末尾のゼロを保持します。
select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot;
--戻り値
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 12.455     | 12.340     |
+------------+------------+

--このパラメーターは静的な値にも適用されます。
set odps.sql.decimal.tostring.trimzero=false;
select cast(round(12345.120BD,3) as string);
--戻り値：
+------------+
| _c0        |
+------------+
| 12345.120  |
+------------+

COALESCE

構文
```
coalesce(<expr1>, <expr2>, ...)
```
説明
式リスト <expr1>, <expr2>, ... の中で、最初の null でない値を返します。
パラメーター
expr：必須。検証する値。
戻り値
戻り値はパラメーターと同じデータの型を持ちます。

例

例 1：一般的な使用例。文の例：

-- 戻り値は 1 です。
select coalesce(null,null,1,null,3,5,7);

例 2：パラメーター値の型が定義されていない場合、エラーが発生します。

不正な文の例

-- 値 abc のデータの型が定義されていないため、値 abc は識別できません。エラーが返されます。
select coalesce(null,null,1,null,abc,5,7);

正しい文の例

select coalesce(null,null,1,null,'abc',5,7);

例 3：テーブルからデータが読み取られず、すべての入力パラメーターが null の場合、エラーが返されます。不正な文の例：
```
--エラーが返され、少なくとも 1 つのパラメーターが非 NULL である必要があることを示します。
select coalesce(null,null,null,null);
```

例 4：テーブルからデータが読み取られ、すべての入力パラメーターが null の場合、この関数は null を返します。

元のデータテーブル：

+-----------+-------------+------------+
| shop_name | customer_id | toal_price |
+-----------+-------------+------------+
| ad        | 10001       | 100.0      |
| jk        | 10002       | 300.0      |
| ad        | 10003       | 500.0      |
| tt        | NULL        | NULL       |
+-----------+-------------+------------+

ソーステーブルの tt ショップのフィールド値はすべて null です。次の文が実行されると、null が返されます。

select coalesce(customer_id,total_price) from sale_detail where shop_name='tt';

COMPRESS

構文

binary compress(string <str>)
binary compress(binary <bin>)

説明
GZIP アルゴリズムを使用して str または bin を圧縮します。
パラメーター
- str：必須。STRING 型の値。
- bin：必須。BINARY 型の値。
戻り値
BINARY 型の値を返します。入力が null の場合、戻り値は null です。

例

例 1：GZIP アルゴリズムを使用して文字列 hello を圧縮します。文の例：

-- 戻り値は =1F=8B=08=00=00=00=00=00=00=03=CBH=CD=C9=C9=07=00=86=A6=106=05=00=00=00 です。
select compress('hello');

例 2：入力パラメーターは空の文字列です。文の例：

-- 戻り値は =1F=8B=08=00=00=00=00=00=00=03=03=00=00=00=00=00=00=00=00=00 です。
select compress('');

例 3：入力パラメーターは null です。文の例：
```
-- 戻り値は null です。
select compress(null);
```

CRC32

構文
```
bigint crc32(string|binary <expr>)
```
説明
expr の巡回冗長検査値を計算します。`expr` の値は STRING 型または BINARY 型である必要があります。
パラメーター
expr：必須。STRING 型または BINARY 型の値。
戻り値
BIGINT 型の値を返します。戻り値は次のルールに従います。
- 入力が null の場合、戻り値は null です。
- 入力が空の文字列の場合、戻り値は 0 です。
例
- 例 1：文字列 ABC の巡回冗長検査値を計算します。文の例：
```
-- 戻り値は 2743272264 です。
select crc32('ABC');
```
- 例 2：入力パラメーターは null です。文の例：
```
-- 戻り値は null です。
select crc32(null);
```

DECOMPRESS

構文
```
binary decompress(binary <bin>)
```
説明
GZIP アルゴリズムを使用して bin を解凍します。
パラメーター
bin：必須。BINARY 型の値。
戻り値
BINARY 型の値を返します。入力が null の場合、戻り値は null です。
例
- 例 1：圧縮された文字列 hello, world を解凍し、結果を文字列に変換します。文の例：
```
-- 戻り値は hello, world です。
select cast(decompress(compress('hello, world')) as string);
```
- 例 2：入力パラメーターは null です。文の例：
```
-- 戻り値は null です。
select decompress(null);
```

GET_IDCARD_AGE

構文
```
get_idcard_age(<idcardno>)
```
説明
ID カード番号に基づいて現在の年齢を計算します。年齢は、現在の年から誕生年を引いて計算されます。
パラメーター
idcardno：必須。STRING 型の 15 桁または 18 桁の ID カード番号。この関数は、省コードと最後の桁に基づいて ID カード番号を検証します。検証に失敗した場合、関数は null を返します。
戻り値
BIGINT 型の値を返します。入力が null の場合、戻り値は null です。

GET_IDCARD_BIRTHDAY

構文
```
get_idcard_birthday(<idcardno>)
```
説明
ID カード番号から生年月日を取得します。
パラメーター
idcardno：必須。STRING 型の 15 桁または 18 桁の ID カード番号。この関数は、省コードと最後の桁に基づいて ID カード番号を検証します。検証に失敗した場合、関数は null を返します。
戻り値
DATETIME 型の値を返します。入力が null の場合、戻り値は null です。

GET_IDCARD_SEX

構文
```
get_idcard_sex(<idcardno>)
```
説明
ID カード番号から性別を取得します。有効な戻り値は M (男性) と F (女性) です。
パラメーター
idcardno：必須。STRING 型の 15 桁または 18 桁の ID カード番号。この関数は、省コードと最後の桁に基づいて ID カード番号を検証します。検証に失敗した場合、関数は null を返します。
戻り値
STRING 型の値を返します。入力が null の場合、戻り値は null です。

GET_USER_ID

構文
```
get_user_id()
```
説明
現在のアカウントの ID (ユーザー ID (UID) とも呼ばれます) を取得します。
パラメーター
パラメーターは不要です。
戻り値
現在のアカウントの ID を返します。

例

select get_user_id();
-- 次の結果が返されます。
+------------+
| _c0        |
+------------+
| 1117xxxxxxxx8519 |
+------------+

HASH

構文
- MaxCompute プロジェクトが Hive 互換モードの場合、次の構文を使用します。
```
INT HASH(<value1>, <value2>[, ...]);
```
- MaxCompute プロジェクトが Hive 互換モードでない場合、次の構文を使用します。
```
BIGINT HASH(<value1>, <value2>[, ...]);
```
説明
value1 と value2 に基づいてハッシュ値を返します。
パラメーター
value1 と value2：必須。ハッシュ化するパラメーター。パラメーターは異なるデータの型を持つことができます。サポートされるデータの型は、Hive 互換モードと非 Hive 互換モードで異なります。
- Hive 互換モード：TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、BOOLEAN、STRING、CHAR、VARCHAR、DATETIME、および DATE。
- 非 Hive 互換モード：BIGINT、DOUBLE、BOOLEAN、STRING、および DATETIME。
説明
2 つの入力パラメーターが同一の場合、返されるハッシュ値も同一です。ただし、返された 2 つのハッシュ値が同一であっても、ハッシュ衝突の可能性があるため、入力パラメーターが同一であるとは限りません。
戻り値
INT 型または BIGINT 型の値を返します。入力パラメーターが空の文字列または null の場合、戻り値は 0 です。
例
- 例 1：同じデータの型の入力パラメーターのハッシュ値を計算します。文の例：
```
-- 戻り値は 66 です。
SELECT HASH(0L, 2L, 4L);
```
- 例 2：異なるデータの型の入力パラメーターのハッシュ値を計算します。文の例：
```
-- 戻り値は 97 です。
SELECT HASH(0L, 'a');
```
- 例 3：入力パラメーターは空の文字列または null です。文の例：
```
-- 戻り値は 0 です。
SELECT HASH(0L, null);
-- 戻り値は 0 です。
SELECT HASH(0L, '');
```

IF

構文

if(<testCondition>, <valueTrue>, <valueFalseOrNull>)

説明
testCondition が true かどうかをチェックします。`testCondition` が true の場合、この関数は valueTrue を返します。それ以外の場合は、valueFalseOrNull を返します。
パラメーター
- testCondition：必須。評価する式。値は BOOLEAN 型である必要があります。
- valueTrue：必須。testCondition が true の場合に返す値。
- valueFalseOrNull：testCondition が false の場合に返す値。このパラメーターを null に設定できます。
戻り値
戻り値のデータの型は、valueTrue と valueFalseOrNull の共通のデータの型です。

例

-- 戻り値は 200 です。
select if(1=2, 100, 200);

MAX_PT

構文
```
MAX_PT(<table_full_name>)
```
説明
パーティションテーブル内でデータを含む最大のパーティションの名前を返します。パーティションはアルファベット順にソートされます。この関数は通常、`WHERE` 句で最新のパーティションからデータを読み取るために使用されます。
注意事項
- MAX_PT 関数は、標準の SQL ステートメントを使用して実装することもできます。たとえば、SELECT * FROM table WHERE pt=MAX_PT("table"); は SELECT * FROM table WHERE pt = (SELECT MAX(pt) FROM table); と書き換えることができます。
  説明
  MaxCompute は MIN_PT 関数を提供していません。パーティションテーブル内でデータを含む最小のパーティションを見つけるには、MAX_PT 関数の場合のように SQL ステートメント SELECT * FROM table WHERE pt=MIN_PT("table"); を使用することはできません。代わりに、標準の SQL ステートメント SELECT * FROM table WHERE pt= (SELECT MIN(pt) FROM table); を使用してください。
- テーブル内のすべてのパーティションが空の場合、MAX_PT 関数は失敗します。少なくとも 1 つのパーティションにデータが含まれていることを確認してください。
- MAX_PT 関数は、OSS 外部テーブルと内部テーブルの両方でサポートされています。関数の動作は両方のテーブルタイプで同じです。
パラメーター
table_full_name：必須。テーブル名を指定する STRING 型の値。テーブルに対する読み取り権限が必要です。
戻り値
最大のパーティションの名前を返します。
説明
ALTER TABLE 文を使用して作成されたがデータを含まないパーティションは返されません。
例
- 例 1：tbl テーブルは、パーティション 20120901 と 20120902 を持つパーティションテーブルで、両方ともデータを含んでいます。次の文では、MAX_PT 関数は '20120902' を返します。MaxCompute SQL ステートメントは pt='20120902' パーティションからデータを読み取ります。文の例：
```
SELECT * FROM tbl WHERE pt= MAX_PT('tbl');
-- 上記の文は次の文と同じです：
SELECT * FROM tbl WHERE pt= (SELECT MAX(pt) FROM tbl);
```
- 例 2：テーブルに複数のパーティションレベルがある場合、標準の SQL ステートメントを使用して最大のパーティションからデータを取得します。文の例：
```
SELECT * FROM table WHERE pt1 = (SELECT MAX(pt1) FROM table) AND pt2 = (SELECT MAX(pt2) FROM table WHERE pt1= (SELECT MAX(pt1) FROM table));
```

NULLIF

構文
```
T nullif(T <expr1>, T <expr2>)
```
説明
expr1 と expr2 を比較します。値が同じ場合、関数は null を返します。値が異なる場合、関数は expr1 の値を返します。
パラメーター
expr1 と expr2：必須。任意のデータの型の式。T は入力データの型を指定し、MaxCompute がサポートする任意のデータの型にすることができます。
戻り値
expr1 の値または null を返します。

例

-- 戻り値は 2 です。
select nullif(2, 3);
-- 戻り値は null です。
select nullif(2, 2);
-- 戻り値は 3 です。
select nullif(3, null);

NVL

構文
```
nvl(T <value>, T <default_value>)
```
説明
value が null の場合、default_value を返します。それ以外の場合、この関数は value を返します。value パラメーターと default_value パラメーターは、同じデータの型である必要があります。
パラメーター
- value：必須。入力パラメーター。T は入力データの型を指定し、MaxCompute がサポートする任意のデータの型にすることができます。
- default_value：必須。null の代わりに使用する値。`default_value` のデータの型は value のデータの型と同じである必要があります。

例

t_data という名前のテーブルには、c1 string、c2 bigint、c3 datetime の 3 つの列が含まれています。このテーブルには次のデータが含まれています。

+----+------------+------------+
| c1 | c2 | c3 |
+----+------------+------------+
| NULL | 20 | 2017-11-13 05:00:00 |
| ddd | 25 | NULL |
| bbb | NULL | 2017-11-12 08:00:00 |
| aaa | 23 | 2017-11-11 00:00:00 |
+----+------------+------------+

nvl 関数が呼び出された後、c1 の null 値は `00000` に置き換えられ、c2 の null 値は `0` に置き換えられ、c3 の null 値はハイフン (-) に置き換えられます。文の例：

select nvl(c1,'00000'),nvl(c2,0),nvl(c3,'-') from nvl_test;
-- 次の結果が返されます。
+-----+------------+-----+
| _c0 | _c1 | _c2 |
+-----+------------+-----+
| 00000 | 20 | 2017-11-13 05:00:00 |
| ddd | 25 | - |
| bbb | 0 | 2017-11-12 08:00:00 |
| aaa | 23 | 2017-11-11 00:00:00 |
+-----+------------+-----+

ORDINAL

構文

ORDINAL(BIGINT <nth>, <var1>, <var2>[,...])

説明
入力変数を昇順にソートし、nth 番目のランクの値を返します。
パラメーター
- nth：必須。返す値のランクを指定する BIGINT 型の値。ランクは 1 から始まります。このパラメーターが null の場合、関数は null を返します。
- var：必須。ソートする値。値は BIGINT、DOUBLE、DATETIME、または STRING 型である必要があります。
戻り値
- nth 番目のランクの値を返します。暗黙的な変換が不要な場合、戻り値は入力パラメーターと同じデータの型を持ちます。
- DOUBLE、BIGINT、STRING 型の間でデータの型変換が発生した場合、DOUBLE 型の値が返されます。STRING と DATETIME 型の間でデータの型変換が発生した場合、DATETIME 型の値が返されます。他のデータの型の暗黙的な変換はサポートされていません。
- null 値は最小値として扱われます。

例

-- 戻り値は 3 です。 
SELECT ORDINAL(CAST(3 AS BIGINT), CAST(1 AS BIGINT), cast(3 AS BIGINT), cast(7 AS BIGINT), cast(5 AS BIGINT), cast(2 AS BIGINT), cast(4 AS BIGINT), cast(6 AS BIGINT));

PARTITION_EXISTS

構文

boolean partition_exists(string <table_name>, string... <partitions>)

説明
指定されたパーティションがテーブルに存在するかどうかをチェックします。
パラメーター
- table_name：必須。テーブル名。STRING 型です。テーブル名にプロジェクト名を指定できます (例：my_proj.my_table)。プロジェクト名を指定しない場合、現在のプロジェクトが使用されます。
- partitions：必須。パーティション名。STRING 型です。パーティションキー列の値は、テーブルで定義されているのと同じシーケンスで指定する必要があります。値の数はパーティションキー列の数と一致する必要があります。
戻り値
BOOLEAN 型の値を返します。指定されたパーティションが存在する場合、関数は `true` を返します。それ以外の場合は `false` を返します。

例

-- foo という名前のパーティションテーブルを作成します。
create table foo (id bigint) partitioned by (ds string, hr string);
-- パーティションテーブル foo にパーティションを追加します。
alter table foo add partition (ds='20190101', hr='1');
-- パーティション ds='20190101' と hr='1' が存在するかどうかをチェックします。True が返されます。
select partition_exists('foo', '20190101', '1');

SAFE_CAST

SAFE_CAST AS INT

構文
```
SAFE_CAST (<expr> AS INT)
```
パラメーター
expr：必須。変換する式。
戻り値
INT 値を返します。変換が成功した場合、関数は対応する値を返します。変換が失敗した場合、エラーを発生させる代わりに NULL を返します。これが SAFE_CAST と CAST を区別するコアの動作です。
- '123abc' のような非数値文字を含む文字列の場合：
  - 非厳格モード (デフォルト、odps.sql.udf.strict.mode=false) では、操作は文字の前の数値部分を返します。
  - 厳格モード (odps.sql.udf.strict.mode=true) では、NULL が返されます。
- 'abc' のような非数値文字のみを含む文字列の場合：
  - 非厳格モード (デフォルト) (odps.sql.udf.strict.mode=false) では、NULL が返されます。
  - 厳格モード (odps.sql.udf.strict.mode=true) では、NULL が返されます。

例

-- 非厳格モード (デフォルト)
SELECT  SAFE_CAST('123abc' AS INT) AS toint
        ,SAFE_CAST('abc' AS INT) AS toint2
;

-- 結果
+-------+--------+
| toint | toint2 |
+-------+--------+
| 123   | NULL   |
+-------+--------+

-- 厳格モード
SET odps.sql.udf.strict.mode=true;
SELECT  SAFE_CAST('123abc' AS INT) AS toint
        ,SAFE_CAST('abc' AS INT) AS toint2
;

-- 結果
+-------+--------+
| toint | toint2 |
+-------+--------+
| NULL  | NULL   |
+-------+--------+

SAFE_CAST AS BIGINT

構文
```
SAFE_CAST (<expr> AS BIGINT)
```
パラメーター
expr：必須。変換する式。
戻り値
BIGINT 値を返します。変換が成功した場合、関数は対応する値を返します。変換が失敗した場合、エラーを発生させる代わりに NULL を返します。これが SAFE_CAST と CAST を区別するコアの動作です。
- '123abc' のような非数値文字を含む文字列の場合：
  - 非厳格モード (デフォルト) (odps.sql.udf.strict.mode=false) では、関数は文字の前の数値部分を返します。
  - 厳格モード (odps.sql.udf.strict.mode=true) では、NULL が返されます。
- 'abc' のような非数値文字のみを含む文字列の場合：
  - 非厳格モード (デフォルト) (odps.sql.udf.strict.mode=false) では、0 が返されます。
  - 厳格モード (odps.sql.udf.strict.mode=true) では、NULL が返されます。

例

-- 非厳格モード (デフォルト)
SELECT  SAFE_CAST('123abc' AS BIGINT) AS tobigint
        ,SAFE_CAST('abc' AS BIGINT) AS tobigint2
;

-- 結果
+------------+------------+
| tobigint   | tobigint2  |
+------------+------------+
| 123        | 0          |
+------------+------------+

-- 厳格モード
SET odps.sql.udf.strict.mode=true;
SELECT  SAFE_CAST('123abc' AS BIGINT) AS tobigint
        ,SAFE_CAST('abc' AS BIGINT) AS tobigint2
;

-- 結果
+------------+------------+
| tobigint   | tobigint2  |
+------------+------------+
| NULL       | NULL       |
+------------+------------+

SAMPLE

構文

boolean sample(<x>, <y>, [<column_name1>, <column_name2>[,...]])

説明
x と y に基づいて column_name からすべての値をサンプリングし、サンプリング条件を満たさない行を除外します。
パラメーター
- x と y：x は必須です。`x` と `y` は 0 より大きい BIGINT 型の整数定数でなければなりません。これらのパラメーターは、値がハッシュ関数に基づいて x 個の部分に分割され、y 番目の部分が選択されることを示します。
  y はオプションです。y が指定されていない場合、デフォルトで最初の部分が選択され、column_name を指定する必要はありません。
  x または y が別のデータの型であるか、0 以下であるか、または y が x より大きい場合、エラーが返されます。x または y が null の場合、関数は null を返します。
- column_name：オプション。サンプリングを実行する列の名前。このパラメーターが指定されていない場合、x と y の値に基づいてランダムサンプリングが実行されます。列は任意のデータの型にすることができ、その値は null にすることができます。暗黙的な変換は実行されません。column_name 自体が null の場合、エラーが返されます。
  説明
  - null 値によるデータスキューを防ぐため、column_name の null 値に対して x 個の部分にわたって均一なハッシュ化が実行されます。column_name が指定されておらず、データ量が少ない場合、出力が均一でない可能性があります。この場合、均一な出力を得るために column_name を指定することを推奨します。
  - ランダムサンプリングは、BIGINT、DATETIME、BOOLEAN、DOUBLE、STRING、BINARY、CHAR、VARCHAR のデータの型の列でのみ実行できます。
戻り値
BOOLEAN 型の値を返します。

例

tbla テーブルには cola 列が含まれています。

-- cola 列の値はハッシュ関数に基づいて 4 つの部分に分割され、最初の部分が使用されます。True が返されます。
select * from tbla where sample (4, 1 , cola);
-- 各行の値はランダムに 4 つの部分にハッシュ化され、2 番目の部分が使用されます。True が返されます。
select * from tbla where sample (4, 2);

SHA

構文
```
string sha(string|binary <expr>)
```
説明
expr の SHA-1 ハッシュ値を計算し、ハッシュ値を 16 進数文字列として返します。`expr` パラメーターは STRING 型または BINARY 型である必要があります。
パラメーター
expr：必須。STRING 型または BINARY 型の値。
戻り値
STRING 型の値を返します。入力が null の場合、戻り値は null です。
例
- 例 1：文字列 ABC の SHA ハッシュ値を計算します。文の例：
```
-- 戻り値は 3c01bdbb26f358bab27f267924aa2c9a03fcfdb8 です。
select sha('ABC');
```
- 例 2：入力パラメーターは null です。文の例：
```
-- 戻り値は null です。
select sha(null);
```

SHA1

構文
```
string sha1(string|binary <expr>)
```
説明
expr の SHA-1 ハッシュ値を計算し、ハッシュ値を 16 進数文字列として返します。`expr` パラメーターは STRING 型または BINARY 型である必要があります。
パラメーター
expr：必須。STRING 型または BINARY 型の値。
戻り値
STRING 型の値を返します。入力が null の場合、戻り値は null です。
例
- 例 1：文字列 ABC の SHA-1 ハッシュ値を計算します。文の例：
```
-- 戻り値は 3c01bdbb26f358bab27f267924aa2c9a03fcfdb8 です。
select sha1('ABC');
```
- 例 2：入力パラメーターは null です。文の例：
```
-- 戻り値は null です。
select sha1(null);
```

SHA2

構文

string sha2(string|binary <expr>, bigint <number>)

説明
expr の SHA-2 ハッシュ値を計算し、number で指定されたフォーマットでハッシュ値を返します。`expr` パラメーターは STRING 型または BINARY 型である必要があります。
パラメーター
- expr：必須。STRING 型または BINARY 型の値。
- number：必須。ハッシュビット長を指定する BIGINT 型の値。有効な値は 224、256、384、512、および 0 です。256 の戻り値は 0 の戻り値と同じです。
戻り値
STRING 型の値を返します。戻り値は次のルールに従います。
- 入力パラメーターが null の場合、この関数は null を返します。
- number の値が有効な範囲内にない場合、関数は null を返します。

例

例 1：文字列 ABC の SHA-2 ハッシュ値を計算します。文の例：

-- 戻り値は b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78 です。
select sha2('ABC', 256);

例 2：入力パラメーターは null です。文の例：
```
-- 戻り値は null です。
select sha2('ABC', null);
```

STACK

構文
```
stack(n, expr1, ..., exprk) 
```
説明
expr1, ..., exprk を `n` 行に分割します。特に指定がない限り、出力列はデフォルトで col0, col1... という名前になります。
パラメーター
- n：必須。作成する行数。
- expr：必須。分割する式。expr1, ..., exprk は整数型でなければなりません。式の数は n の整数倍でなければならず、n 個の完全な行に分割できるようにする必要があります。それ以外の場合、エラーが返されます。
戻り値
`n` 行のデータセットを返します。列数は、式の総数を `n` で割ったものです。

例

-- パラメーターグループ 1, 2, 3, 4, 5, 6 を 3 行に分割します。
select stack(3, 1, 2, 3, 4, 5, 6);
-- 次の結果が返されます。
+------+------+
| col0 | col1 |
+------+------+
| 1    | 2    |
| 3    | 4    |
| 5    | 6    |
+------+------+

-- 'A',10,date '2015-01-01','B',20,date '2016-01-01' を 2 行に分割します。
select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') as (col0,col1,col2);
-- 次の結果が返されます。
+------+------+------+
| col0 | col1 | col2 |
+------+------+------+
| A    | 10   | 2015-01-01 |
| B    | 20   | 2016-01-01 |
+------+------+------+

-- a, b, c, d を 2 行に分割します。ソーステーブルに複数の行が含まれている場合、この関数は各行に対して呼び出されます。
select stack(2,a,b,c,d) as (col,value)
from values 
    (1,1,2,3,4),
    (2,5,6,7,8),
    (3,9,10,11,12),
    (4,13,14,15,null)
as t(key,a,b,c,d);
-- 次の結果が返されます。
+------+-------+
| col  | value |
+------+-------+
| 1    | 2     |
| 3    | 4     |
| 5    | 6     |
| 7    | 8     |
| 9    | 10    |
| 11   | 12    |
| 13   | 14    |
| 15   | NULL  |
+------+-------+

-- lateral view 句でこの関数を使用します。
select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20, date '2016-01-01') tf as col0,col1,col2;
-- 次の結果が返されます。
+------+------+------+
| col0 | col1 | col2 |
+------+------+------+
| A    | 10   | 2015-01-01 |
| B    | 20   | 2016-01-01 |
+------+------+------+

STR_TO_MAP

構文

str_to_map([string <mapDupKeyPolicy>,] <text> [, <delimiter1> [, <delimiter2>]])

説明
デリミタ 1 を使用してテキストをキーと値のペアに分割し、その後デリミタ 2 を使用して各ペアのキーと値を分離します。
パラメーター
- mapDupKeyPolicy：オプション。STRING 型の値。このパラメーターは、重複キーを処理するために使用されるメソッドを指定します。有効な値：
  - exception：エラーが返されます。
  - last_win：後のキーが前のキーを上書きします。
  また、セッションレベルで odps.sql.map.key.dedup.policy パラメーターを指定して、重複キーを処理するために使用されるメソッドを設定することもできます。たとえば、odps.sql.map.key.dedup.policy を exception に設定できます。このパラメーターを指定しない場合、デフォルト値の last_win が使用されます。
  
  説明
  MaxCompute の動作実装は mapDupKeyPolicy に基づいて決定されます。mapDupKeyPolicy を指定しない場合、odps.sql.map.key.dedup.policy の値が使用されます。
- text：必須。分割する文字列。値は STRING 型である必要があります。
- delimiter1：オプションの STRING 型のデリミタ。このパラメーターが指定されていない場合、デフォルトでカンマ (,) が使用されます。
- delimiter2：オプションの STRING 型のデリミタ。このパラメーターが指定されていない場合、デフォルトで等号 (=) が使用されます。
  説明
  デリミタが正規表現または特殊文字である場合は、2 つのバックスラッシュ (\\) でエスケープする必要があります。デリミタとして使用できる特殊文字には、コロン (:)、ピリオド (.)、疑問符 (?)、プラス記号 (+)、アスタリスク (*) があります。
戻り値
map<string, string> 型の値を返します。この関数は、delimiter1 と delimiter2 を使用して text 文字列を分割します。

例

-- 戻り値は {test1:1, test2:2} です。
select str_to_map('test1&1-test2&2','-','&');
-- 戻り値は {test1:1, test2:2} です。
select str_to_map("test1.1,test2.2", ",", "\\.");
-- 戻り値は {test1:1, test2:3} です。
select str_to_map("test1.1,test2.2,test2.3", ",", "\\.");

TABLE_EXISTS

構文

boolean table_exists(string <table_name>)

説明
指定されたテーブルが存在するかどうかをチェックします。
パラメーター
table_name：必須。テーブルの名前。STRING 型です。テーブル名にプロジェクト名を指定できます (例：my_proj.my_table)。プロジェクト名を指定しない場合、現在のプロジェクトが使用されます。
戻り値
BOOLEAN 型の値を返します。指定されたテーブルが存在する場合、関数は `true` を返します。それ以外の場合は `false` を返します。

例

-- SELECT 文のリストでこの関数を使用します。
select if(table_exists('abd'), col1, col2) from src;

TRANS_ARRAY

使用制限
- key として使用されるすべての列は、入れ替える列の前に配置する必要があります。
- select 文では、1 つのユーザー定義テーブル関数 (UDTF) のみ許可されます。他の列は許可されません。
- この関数は、group by、cluster by、distribute by、または sort by 句と一緒には使用できません。

構文

trans_array (<num_keys>, <separator>, <key1>,<key2>,…,<col1>,<col2>,<col3>) as (<key1>,<key2>,...,<col1>, <col2>)

説明
1 行のデータを複数の行に入れ替えます。この UDTF は、固定デリミタで区切られた配列を含む列を複数の行に入れ替えます。
パラメーター
- num_keys：必須。BIGINT 型の定数。値は 0 以上である必要があります。このパラメーターは、1 行を複数の行に入れ替える際に key として使用する列の数を指定します。
- separator：必須。文字列を複数の要素に分割するために使用される STRING 型の定数。このパラメーターが空の文字列の場合、エラーが返されます。
- keys：必須。入れ替えの key として使用する列。キーの数は num_keys で指定されます。num_keys がすべての列を key として使用することを指定する場合 (つまり、num_keys が列の総数と等しい場合)、1 行のみが返されます。
- cols：必須。このパラメーターは、行に入れ替えたい配列を指定します。keys に続くすべての列は、入れ替える配列と見なされます。このパラメーターの値は、Hangzhou;Beijing;Shanghai のような文字列形式で配列を格納するために STRING 型である必要があります。この配列の値はセミコロン (;) で区切られます。
戻り値
入れ替えられた行を返します。新しい列名は as で指定されます。key として使用される列のデータの型は変更されません。他のすべての列は STRING 型です。入れ替えられた行の数は、最も多くの要素を持つ配列によって決定されます。他の配列の要素が少ない場合、欠損値には null が使用されます。

例

例 1：t_table テーブルには次のデータが含まれています。

+----------+----------+------------+
| login_id | login_ip | login_time |
+----------+----------+------------+
| wangwangA | 192.168.0.1,192.168.0.2 | 20120101010000,20120102010000 |
| wangwangB | 192.168.45.10,192.168.67.22,192.168.6.3 | 20120111010000,20120112010000,20120223080000 |
+----------+----------+------------+
-- SQL ステートメントを実行します。
select trans_array(1, ",", login_id, login_ip, login_time) as (login_id,login_ip,login_time) from t_table;
-- 次の結果が返されます。
+----------+----------+------------+
| login_id | login_ip | login_time |
+----------+----------+------------+
| wangwangB | 192.168.45.10 | 20120111010000 |
| wangwangB | 192.168.67.22 | 20120112010000 |
| wangwangB | 192.168.6.3 | 20120223080000 |
| wangwangA | 192.168.0.1 | 20120101010000 |
| wangwangA | 192.168.0.2 | 20120102010000 |
+----------+----------+------------+

-- テーブルに次のデータが含まれている場合：
Login_id LOGIN_IP LOGIN_TIME 
wangwangA 192.168.0.1,192.168.0.2 20120101010000
-- データが不十分な配列を補うために null 値が追加されます。 
Login_id Login_ip Login_time 
wangwangA 192.168.0.1 20120101010000
wangwangA 192.168.0.2 NULL

例 2：mf_fun_array_test_t テーブルには次のデータが含まれています。

+------------+------------+------------+------------+
| id         | name       | login_ip   | login_time |
+------------+------------+------------+------------+
| 1          | Tom        | 192.168.100.1,192.168.100.2 | 20211101010101,20211101010102 |
| 2          | Jerry      | 192.168.100.3,192.168.100.4 | 20211101010103,20211101010104 |
+------------+------------+------------+------------+

-- 2 つのキー、id と name を使用して配列を入れ替えます。SQL ステートメントを実行します。
select trans_array(2, ",", Id,Name, login_ip, login_time) as (Id,Name,login_ip,login_time) from mf_fun_array_test_t;
-- 次の結果が返されます。データはキー id と name で分割およびグループ化されます。
+------------+------------+------------+------------+
| id         | name       | login_ip   | login_time |
+------------+------------+------------+------------+
| 1          | Tom        | 192.168.100.1 | 20211101010101 |
| 1          | Tom        | 192.168.100.2 | 20211101010102 |
| 2          | Jerry      | 192.168.100.3 | 20211101010103 |
| 2          | Jerry      | 192.168.100.4 | 20211101010104 |
+------------+------------+------------+------------+

TRANS_COLS

使用制限
- key として使用されるすべての列は、入れ替える列の前に配置する必要があります。
- select 文では、1 つの UDTF のみ許可されます。他の列は許可されません。

構文

trans_cols (<num_keys>, <key1>,<key2>,…,<col1>, <col2>,<col3>) as (<idx>, <key1>,<key2>,…,<col1>, <col2>)

説明
列を別々の行に分割することで、単一の行を複数の行に変換するユーザー定義テーブル関数 (UDTF) です。
パラメーター
- num_keys：必須。BIGINT 型の定数。値は 0 以上である必要があります。このパラメーターは、1 行を複数の行に入れ替える際に key として使用する列の数を指定します。
- keys：必須。入れ替え操作の key として使用する列。キーの数は num_keys で指定されます。num_keys がすべての列を key として使用することを指定する場合 (つまり、num_keys が列の総数と等しい場合)、1 行のみが返されます。
- idx：必須。変換された行番号を指定します。
- cols：必須。行に入れ替えたい列。
戻り値
入れ替えられた行を返します。新しい列名は as で指定されます。最初の出力列は入れ替えられた添字で、1 から始まります。キーとして使用される列のデータの型は変更されず、他の列のデータの型も変更されません。

例

t_table テーブルには次のデータが含まれています。

+----------+----------+------------+
| Login_id | Login_ip1 | Login_ip2 |
+----------+----------+------------+
| wangwangA | 192.168.0.1 | 192.168.0.2 |
+----------+----------+------------+
-- SQL ステートメントを実行します。
select trans_cols(1, login_id, login_ip1, login_ip2) as (idx, login_id, login_ip) from t_table;
-- 次の結果が返されます。
idx    login_id    login_ip
1    wangwangA    192.168.0.1
2    wangwangA    192.168.0.2

UNIQUE_ID

構文
```
string unique_id()
```
説明
一意の ID (例：29347a88-1e57-41ae-bb68-a9edbdd9****_1) を返します。この関数は UUID 関数よりも効率的です。返される ID はより長く、アンダースコア (_) と数字で構成されるサフィックス (例：_1) を含みます。

UUID

構文
```
string uuid()
```
説明
ランダムな ID (例：29347a88-1e57-41ae-bb68-a9edbdd9****) を返します。
説明
戻り値はランダムなグローバル一意識別子 (GUID) であり、ほとんどの場合で一意です。