MaxCompute:その他の関数

コマンドフォーマット

<a> [NOT] BETWEEN <b> AND <c>

説明

a の値が b と c の間にある、または b と c の間にないデータをフィルタリングします。

パラメーター

• a：必須。フィルター対象のフィールド。

• b および c：必須。指定する範囲。b と c のデータ型は、a のデータ型と同じである必要があります。

戻り値

条件を満たすデータを返します。

a、b、または c のいずれかが NULL の場合、結果は NULL になります。

例

| 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

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  |
+-------+-------+-----+------------+------------+------------+------------+------------+

コマンドフォーマット

説明

value または _condition の結果に基づいて、異なる result 値を返します。

パラメーター

• value：必須。比較する値。

• _condition：必須。評価する条件。

• result：必須。戻り値。

戻り値

• result に BIGINT 型と DOUBLE 型のみが含まれる場合、すべての値は結果が返される前に DOUBLE 型に変換されます。

• result に STRING 型が含まれる場合、すべての値は結果が返される前に STRING 型に変換されます。データ型の変換がサポートされていない場合、エラーが返されます。たとえば、BOOLEAN 型のデータは STRING 型に変換できません。

• その他の型変換は許可されていません。

例

+------------+-------------+-------------+------------+------------+
| 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(<expr> AS <type>)

説明

expr の結果をターゲットデータ型 type に変換します。

パラメーター

• expr：必須。変換するソースデータ。

• type：必須。ターゲットデータ型。使用方法は次のとおりです。

  • cast(double as bigint)：DOUBLE データ型の値を BIGINT データ型に変換します。

  • cast(string as bigint)：文字列を BIGINT データ型に変換する場合、文字列に整数として表現された数値が含まれている場合は、直接 BIGINT 型に変換されます。文字列に浮動小数点または指数形式で表現された数値が含まれている場合は、まず DOUBLE データ型に変換され、次に BIGINT データ型に変換されます。

  • cast(string as datetime) または cast(datetime as string)：デフォルトの日付形式 yyyy-mm-dd hh:mi:ss が使用されます。

戻り値

• 戻り値はターゲットデータ型です。

• 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 パラメーターは、テーブルから取得されたデータと静的な値の両方に有効です。

例

コマンドフォーマット

COALESCE(<expr1>, <expr2>, ...)

説明

<expr1>, <expr2>, ... のリスト内で最初に見つかった NULL 以外の値を返します。

パラメーター

expr：必須。チェックする値。

戻り値

戻り値のデータ型は、パラメーターのデータ型と同じです。

例

• 例 1：一般的な使用方法。コマンドの例を次に示します。

  -- 1 を返します。
  select coalesce(null,null,1,null,3,5,7);

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

  • 無効なコマンド

    -- パラメーター 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';

コマンドフォーマット

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);

コマンドフォーマット

BIGINT CRC32(STRING|BINARY <expr>)

説明

STRING または BINARY 式 expr の巡回冗長検査 (CRC) 値を計算します。

パラメーター

expr：必須。STRING 型または BINARY 型の値。

戻り値

例

• 例 1：文字列 ABC の CRC 値を計算します。コマンドの例を次に示します。

  -- 2743272264 を返します。
  select crc32('ABC');

• 例 2：入力パラメーターが NULL です。コマンドの例を次に示します。

  -- NULL を返します。
  select crc32(null);

コマンドフォーマット

BINARY DECOMPRESS(BINARY <bin>)

説明

GZIP アルゴリズムを使用して bin を解凍します。

パラメーター

bin：必須。BINARY 型の値。

戻り値

BINARY 型の値を返します。入力パラメーターが NULL の場合、NULL が返されます。

例

• 例 1：文字列 hello, world の圧縮結果を解凍し、STRING 形式に変換します。コマンドの例を次に示します。

  -- hello, world を返します。
  select cast(decompress(compress('hello, world')) as string);

• 例 2：入力パラメーターが NULL です。コマンドの例を次に示します。

  -- NULL を返します。
  select decompress(null);

コマンドフォーマット

get_idcard_age(<idcardno>)

説明

中国の ID カード番号に基づいて現在の年齢を返します。年齢は、現在の年から ID カード番号の生年を引いて計算されます。

パラメーター

idcardno：必須。STRING 型の 15 桁または 18 桁の中国の ID カード番号。この関数は、省コードと最後のチェックディジットに基づいて ID カード番号の有効性をチェックします。チェックに失敗した場合、NULL が返されます。

戻り値

BIGINT 型の値を返します。入力が NULL の場合、NULL が返されます。

コマンドフォーマット

get_idcard_birthday(<idcardno>)

説明

中国の ID カード番号に基づいて生年月日を返します。

パラメーター

idcardno：必須。STRING 型の 15 桁または 18 桁の中国の ID カード番号。この関数は、省コードと最後のチェックディジットに基づいて ID カード番号の有効性をチェックします。チェックに失敗した場合、NULL が返されます。

戻り値

DATETIME 型の値を返します。入力が NULL の場合、NULL が返されます。

コマンドフォーマット

get_idcard_sex(<idcardno>)

説明

中国の ID カード番号に基づいて性別を返します。値は M (男性) または F (女性) です。

パラメーター

idcardno：必須。STRING 型の 15 桁または 18 桁の中国の ID カード番号。この関数は、省コードと最後のチェックディジットに基づいて ID カード番号の有効性をチェックします。チェックに失敗した場合、NULL が返されます。

戻り値

STRING 型の値を返します。入力が NULL の場合、NULL が返されます。

コマンドフォーマット

get_user_id()

説明

現在のアカウントの ID を取得します。これは、ユーザー ID または UID とも呼ばれます。

パラメーター

パラメーターは不要です。

戻り値

現在のアカウントの ID を返します。

例

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

コマンドフォーマット

• MaxCompute プロジェクトが Hive 互換モードの場合、コマンドフォーマットは次のとおりです。

  INT HASH(<value1>, <value2>[, ...]);

• MaxCompute プロジェクトが Hive 互換モードでない場合、コマンドフォーマットは次のとおりです。

  BIGINT HASH(<value1>, <value2>[, ...]);

説明

value1 と value2 のハッシュ値を計算します。

パラメーター

戻り値

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(<testCondition>, <valueTrue>, <valueFalseOrNull>)

説明

testCondition が true かどうかをチェックします。true の場合、関数は valueTrue の値を返します。それ以外の場合は、valueFalseOrNull の値を返します。

パラメーター

• testCondition：必須。評価する式。BOOLEAN 型である必要があります。

• valueTrue：必須。testCondition 式が true の場合に返す値。

• valueFalseOrNull：testCondition 式が false の場合に返す値。これは NULL に設定できます。

戻り値

戻り値のデータ型は、valueTrue または valueFalseOrNull パラメーターのデータ型と同じです。

例

-- 200 を返します。
select if(1=2, 100, 200); 

コマンドフォーマット

MAX_PT(<table_full_name>)

説明

パーティションテーブル内のデータを含むレベル 1 パーティションの最大値を返します。値はアルファベット順にソートされます。その後、関数はそのパーティションからデータを読み取ります。

パラメーター

table_full_name：必須。STRING 型の値。テーブルの名前。テーブルに対する読み取り権限が必要です。

戻り値

最大のレベル 1 パーティションの値を返します。

例

• 例 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));

コマンドフォーマット

T NULLIF(T <expr1>, T <expr2>)

説明

expr1 と expr2 の値を比較します。それらが等しい場合、関数は NULL を返します。それ以外の場合は、expr1 を返します。

パラメーター

expr1 および expr2：必須。任意の型の式。T は入力データ型を示します。MaxCompute がサポートする任意のデータ型を指定できます。

戻り値

NULL または expr1 を返します。

例

-- 2 を返します。
select nullif(2, 3);
-- NULL を返します。
select nullif(2, 2);
-- 3 を返します。
select nullif(3, null);

コマンドフォーマット

nvl(T <value>, T <default_value>)

説明

value の値が NULL の場合、関数は default_value を返します。それ以外の場合は、value を返します。2 つのパラメーターは同じデータ型である必要があります。

パラメーター

• value：必須。入力パラメーター。T は入力データ型を示します。MaxCompute がサポートする任意のデータ型を指定できます。

• default_value：必須。置換値。value と同じデータ型である必要があります。

例

+----+------------+------------+
| 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 |
+----+------------+------------+

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(BIGINT <nth>, <var1>, <var2>[,...])

説明

入力変数を昇順にソートし、nth 番目の位置の値を返します。

パラメーター

• nth：必須。1 から始まる位置番号。BIGINT 型の値。指定された位置の値が 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));

コマンドフォーマット

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');

コマンドフォーマット

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

説明

システムは、x と y の設定に基づいて、読み取られた column_name のすべての値をサンプリングし、サンプリング条件を満たさない行をフィルターで除外します。

パラメーター

• x および y：x は必須です。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 つのパートにハッシュされ、1 番目のパートが取得されます。戻り値は True です。
select * from tbla where sample (4, 1 , cola);
-- データの各行はランダムに 4 つのパートにハッシュされ、2 番目のパートが取得されます。戻り値は True です。
select * from tbla where sample (4, 2);

コマンドフォーマット

STRING SHA(STRING|BINARY <expr>)

説明

STRING または BINARY 式 expr の SHA-1 ハッシュ値を計算し、16 進数文字列として返します。

パラメーター

expr：必須。STRING 型または BINARY 型の値。

戻り値

STRING 型の値を返します。入力パラメーターが NULL の場合、NULL が返されます。

例

• 例 1：文字列 ABC の SHA ハッシュ値を計算します。コマンドの例を次に示します。

  -- 3c01bdbb26f358bab27f267924aa2c9a03fcfdb8 を返します。
  select sha('ABC');

• 例 2：入力パラメーターが NULL です。コマンドの例を次に示します。

  -- NULL を返します。
  select sha(null);

コマンドフォーマット

string sha1(string|binary <expr>)

説明

STRING または BINARY 式 expr の SHA-1 ハッシュ値を計算し、16 進数文字列として返します。

パラメーター

expr：必須。STRING 型または BINARY 型の値。

戻り値

STRING 型の値を返します。入力パラメーターが NULL の場合、NULL が返されます。

例

• 例 1：文字列 ABC の SHA-1 ハッシュ値を計算します。コマンドの例を次に示します。

  -- 3c01bdbb26f358bab27f267924aa2c9a03fcfdb8 を返します。
  select sha1('ABC');

• 例 2：入力パラメーターが NULL です。コマンドの例を次に示します。

  -- NULL を返します。
  select sha1(null);

コマンドフォーマット

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

説明

STRING または BINARY 式 expr の SHA-2 ハッシュ値を計算し、number で指定された形式で返します。

パラメーター

• expr：必須。STRING 型または BINARY 型の値。

• number：必須。BIGINT 型の値。ハッシュビット長。値は 224、256、384、512、または 0 (256 と同じ) である必要があります。

戻り値

例

• 例 1：文字列 ABC の SHA-2 ハッシュ値を計算します。コマンドの例を次に示します。

  -- b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78 を返します。
  select sha2('ABC', 256);

• 例 2：入力パラメーターが NULL です。コマンドの例を次に示します。

  -- NULL を返します。
  select sha2('ABC', null);

コマンドフォーマット

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([STRING <mapDupKeyPolicy>,] <text> [, <delimiter1> [, <delimiter2>]])

説明

delimiter1 を使用して text をキーと値のペアに分割し、次に delimiter2 を使用して各キーと値のペアをキーと値に分割します。

パラメーター

• 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> 型です。戻り値は、text を delimiter1 と delimiter2 で分割した結果です。

例

-- {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", ",", "\\.");

コマンドフォーマット

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;

制限事項

• keys として使用されるすべての列は最初に配置する必要があり、転置する列はその後ろに配置する必要があります。

• 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 である必要があります。複数行に変換する際に転置 keys として使用する列の数。

• separator：必須。STRING 定数。文字列を複数の要素に分割するために使用される区切り文字。このパラメーターが空の場合、エラーが返されます。

• keys：必須。転置中に keys として使用する列。num_keys で指定された列の数。num_keys がすべての列を keys として使用することを指定した場合 (つまり、num_keys が列の総数と等しい場合)、1 行のみが返されます。

• cols：必須。行に変換する配列。keys の後にあるすべての列は、転置する配列と見なされます。これらは STRING 型で、Hangzhou;Beijing;Shanghai のように文字列形式で配列を格納する必要があります。これはセミコロン (;) で区切られた配列です。

戻り値

転置された行を返します。新しい列名は AS で指定されます。keys として使用される列のデータ型は変更されません。他のすべての列は 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 |
  +------------+------------+------------+------------+

制限事項

• keys として使用されるすべての列は最初に配置する必要があり、転置する列はその後ろに配置する必要があります。

• SELECT ステートメントには、1 つの UDTF のみを含めることができます。他の列を含めることはできません。

コマンドフォーマット

TRANS_COLS (<num_keys>, <key1>,<key2>,…,<col1>, <col2>,<col3>) AS (<idx>, <key1>,<key2>,…,<col1>, <col2>)

説明

1 行のデータを複数行に変換するユーザー定義テーブル関数 (UDTF) です。異なる列を異なる行に分割します。

パラメーター

• num_keys：必須。BIGINT 定数。値は >=0 である必要があります。複数行に変換する際に転置 keys として使用する列の数。

• keys：必須。転置中に keys として使用する列。num_keys で指定された列の数。num_keys がすべての列を keys として使用することを指定した場合 (つまり、num_keys が列の総数と等しい場合)、1 行のみが返されます。

• idx：必須。変換後の行番号。

• cols：必須。行に変換する列。

戻り値

転置された行を返します。新しい列名は AS で指定されます。出力の最初の列は転置インデックスで、1 から始まります。キーとして使用される列のデータ型は変更されません。他のすべての列は元のデータ型を保持します。

例

+----------+----------+------------+
| 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

コマンドフォーマット

string unique_id()

説明

ランダムな一意の ID (例：29347a88-1e57-41ae-bb68-a9edbdd9****_1) を返します。この関数は UUID 関数よりも効率的で、より長い ID を返します。UUID と比較して、この ID にはアンダースコア (_) と数字 (例：_1) が追加されています。

コマンドフォーマット

string uuid()

説明

ランダムな ID (例：29347a88-1e57-41ae-bb68-a9edbdd9****) を返します。