MaxCompute:その他の関数

構文

string base64(binary <value>)

説明

バイナリ value を Base64 でエンコードされた文字列に変換します。

パラメーター

value: 必須の BINARY 型の値。これは変換される値です。

戻り値

STRING 型の値を返します。入力パラメーターが null の場合、この関数は null を返します。

例

• 例 1: cast ('alibaba' as binary) のバイナリ結果を Base64 でエンコードされた文字列に変換します。サンプル文:

  -- 戻り値は YWxpYmFiYQ== です。
  select base64(cast ('alibaba' as binary));

• 例 2: 入力パラメーターが null です。サンプル文:

  -- 戻り値は null です。
  select base64(null);

構文

<a> [NOT] between <b> and <c>

説明

a の値が b と c の範囲内にあるか、または b と c の範囲内にないかを確認します。

パラメーター

• a: 必須。値を確認するフィールド。

• 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): 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);
    -- 戻り値は false です。
    +------+
    | _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);
  
  -- odps.sql.decimal.tostring.trimzero パラメーターを true に設定するか、odps.sql.decimal.tostring.trimzero パラメーターを設定しません。
  set odps.sql.decimal.tostring.trimzero=true;
  -- 小数点以下の末尾の 0 を削除します。
  select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot;
  -- 次の結果が返されます:
  +------------+------------+
  | _c0        | _c1        |
  +------------+------------+
  | 12.455     | 12.34      |
  +------------+------------+
  
  -- odps.sql.decimal.tostring.trimzero パラメーターを false に設定します。
  set odps.sql.decimal.tostring.trimzero=false;
  -- 小数点以下の末尾の 0 を保持します。
  select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot;
  -- 次の結果が返されます:
  +------------+------------+
  | _c0        | _c1        |
  +------------+------------+
  | 12.455     | 12.340     |
  +------------+------------+
  
  -- odps.sql.decimal.tostring.trimzero パラメーターは静的な値には影響しません。
  set odps.sql.decimal.tostring.trimzero=false;
  select cast(round(12345.120BD,3) as string);
  -- 次の結果が返されます:
  +------------+
  | _c0        |
  +------------+
  | 12345.12   |
  +------------+

構文

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 の場合、エラーが返されます。不正なサンプル文:

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

説明

expr の巡回冗長検査値を計算します。`expr` の値は STRING 型または BINARY 型である必要があります。

パラメーター

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

戻り値

例

• 例 1: 文字列 ABC の巡回冗長検査値を計算します。サンプル文:

  -- 戻り値は 2743272264 です。
  select crc32('ABC');

• 例 2: 入力パラメーターが null です。サンプル文:

  -- 戻り値は null です。
  select crc32(null);

構文

decode(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])

説明

if-then-else 条件ロジックを実装します。

パラメーター

• expression: 必須。比較する式。

• search: 必須。expression と比較する検索項目。

• result: 必須。search が expression と一致する場合に返される値。

• default: オプション。式に一致する検索項目がない場合、default 値が返されます。このパラメーターが指定されていない場合、null が返されます。

戻り値

• 検索項目が式に一致する場合、対応する result が返されます。

• 式に一致する検索項目がない場合、default 値が返されます。

• default パラメーターが指定されていない場合、null が返されます。

• 複数の search 項目が式に一致する場合、最初に一致した項目の結果が返されます。

• MaxCompute SQL は通常、NULL=NULL を評価するときに null を返します。ただし、DECODE 関数は 2 つの null 値を等しいものとして扱います。

例

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

-- customer_id の値が c1 の場合は Taobao が返され、c2 の場合は Alipay が返され、c3 の場合は Aliyun が返されます。値が null の場合は N/A が返されます。その他の場合は Others が返されます。
select
decode(customer_id,
'c1', 'Taobao',
'c2', 'Alipay',
'c3', 'Aliyun',
Null, 'N/A',
'Others') as result
from sale_detail;
-- 上記の文は、次の文と同等です:
if customer_id = c1 then
result := 'Taobao';
elsif customer_id = c2 then
result := 'Alipay';
elsif customer_id = c3 then
result := 'Aliyun';
...
else
result := 'Others';
end if;

+------------+
| result     |
+------------+
| Others     |
| Others     |
| Others     |
| Taobao     |
| Alipay     |
| Aliyun     |
+------------+

構文

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(<idcardno>)

説明

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

パラメーター

idcardno: 必須の 15 桁または 18 桁の STRING 型の ID カード番号。この関数は、省コードと最後の桁に基づいて ID カード番号を検証します。検証に失敗した場合、この関数は null を返します。

戻り値

BIGINT 型の値を返します。入力パラメーターが null の場合、この関数は null を返します。

構文

get_idcard_birthday(<idcardno>)

説明

ID カード番号から生年月日を取得します。

パラメーター

idcardno: 必須の 15 桁または 18 桁の STRING 型の ID カード番号。この関数は、省コードと最後の桁に基づいて ID カード番号を検証します。検証に失敗した場合、この関数は null を返します。

戻り値

DATETIME 型の値を返します。入力パラメーターが null の場合、この関数は null を返します。

構文

get_idcard_sex(<idcardno>)

説明

ID カード番号から性別を取得します。有効な戻り値は M (男性) と F (女性) です。

パラメーター

idcardno: 必須の 15 桁または 18 桁の STRING 型の ID カード番号。この関数は、省コードと最後の桁に基づいて ID カード番号を検証します。検証に失敗した場合、この関数は null を返します。

戻り値

STRING 型の値を返します。入力パラメーターが null の場合、この関数は null を返します。

構文

get_user_id()

説明

ユーザー ID (UID) とも呼ばれる現在のアカウントの ID を取得します。

パラメーター

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

戻り値

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

例

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

構文

greatest(<var1>, <var2>[,...])

説明

入力パラメーターのリストから最大値を返します。

パラメーター

var1 および var2: 必須。パラメーターは BIGINT、DOUBLE、DECIMAL、DATETIME、または STRING 型である必要があります。

戻り値

• 入力パラメーターの中で最大値を返します。暗黙的な変換が不要な場合、戻り値は入力パラメーターと同じデータ型になります。

• null 値は最小値として扱われます。

• 入力パラメーターのデータ型が異なる場合、DOUBLE、BIGINT、DECIMAL、STRING 型のパラメーターは比較のために DOUBLE 型に変換されます。STRING 型と DATETIME 型のパラメーターは比較のために DATETIME 型に変換されます。他のデータ型の暗黙的な変換はサポートされていません。

• set odps.sql.hive.compatible=true; が指定され、入力パラメーターが null の場合、この関数は null を返します。

構文

• MaxCompute プロジェクトが Hive 互換モードの場合は、次の構文を使用します。

  int hash(<value1>, <value2>[, ...]);

• MaxCompute プロジェクトが Hive 互換モードでない場合は、次の構文を使用します。

  bigint hash(<value1>, <value2>[, ...]);

説明

value1 と value2 に基づいてハッシュ値を返します。

パラメーター

戻り値

INT 型または BIGINT 型の値を返します。入力パラメーターが空の文字列または null の場合、0 が返されます。

例

• 例 1: 同じデータ型の入力パラメーターのハッシュ値を計算します。サンプル文:

  -- 戻り値は 66 です。
  select hash(0, 2, 4);

• 例 2: 異なるデータ型の入力パラメーターのハッシュ値を計算します。サンプル文:

  -- 戻り値は 97 です。
  select hash(0, 'a');

• 例 3: 入力パラメーターが空の文字列または null です。サンプル文:

  -- 戻り値は 0 です。
  select hash(0, null);
  -- 戻り値は 0 です。
  select hash(0, '');

構文

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

構文

least(<var1>, <var2>[,...])

説明

入力パラメーターのリストから最小値を返します。

パラメーター

var: 必須。入力パラメーター。パラメーターは BIGINT、DOUBLE、DECIMAL、DATETIME、または STRING 型である必要があります。

戻り値

• 入力パラメーターの中で最小値を返します。暗黙的な変換が不要な場合、戻り値は入力パラメーターと同じデータ型になります。

• DOUBLE、BIGINT、STRING 型の間でデータ型変換が発生した場合、DOUBLE 型の値が返されます。STRING 型と DATETIME 型の間でデータ型変換が発生した場合、DATETIME 型の値が返されます。DECIMAL、DOUBLE、BIGINT、STRING 型の間でデータ型変換が発生した場合、DECIMAL 型の値が返されます。他のデータ型の暗黙的な変換はサポートされていません。

• null 値は最小値として扱われます。

• すべての入力パラメーターが null の場合、この関数は null を返します。

例

-- 戻り値は 2 です。
select least(5, 2, 7);

構文

MAX_PT(<table_full_name>)

説明

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

パラメーター

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

戻り値

最大のパーティションの名前を返します。

例

• 例 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 でサポートされている任意のデータ型にすることができます。

戻り値

expr1 または null の値を返します。

例

-- 戻り値は 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 を返します。`value` パラメーターと `default_value` パラメーターは、同じデータの型である必要があります。

パラメーター

• value: 必須の入力パラメーター。T は入力データ型を指定し、MaxCompute でサポートされている任意のデータ型にすることができます。

• default_value: null を置き換えるために使用される必須の値。`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: 返される値のランクを指定する必須の BIGINT 型の値。ランクは 1 から始まります。このパラメーターが null の場合、関数は null を返します。

• var: ソートする必須の値。値は BIGINT、DOUBLE、DATETIME、または STRING 型である必要があります。

戻り値

• nth ランクの値を返します。暗黙的な変換が不要な場合、戻り値は入力パラメーターと同じデータ型になります。

• DOUBLE、BIGINT、STRING 型の間でデータ型変換が発生した場合、DOUBLE 型の値が返されます。STRING 型と DATETIME 型の間でデータ型変換が発生した場合、DATETIME 型の値が返されます。他のデータ型の暗黙的な変換はサポートされていません。

• null 値は最小値として扱われます。

例

-- 戻り値は 3 です。
SELECT ordinal(3, 1, 3, 7, 5, 2, 4, 6); 

構文

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>[,...]])

説明

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

パラメーター

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

構文

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 の戻り値は 0 の戻り値と同じです。

戻り値

例

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

説明

text を delimiter1 を使用してキーと値のペアに分割し、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;

制限

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

制限

• key として使用されるすべての列は、入れ替える列の前に配置する必要があります。

• select 文では 1 つのユーザー定義テーブル関数 (UDTF) のみ許可されます。他の列は許可されません。

構文

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

説明

1 行のデータを複数行に入れ替えます。この UDTF は、列を行に入れ替えます。

パラメーター

• num_keys: 必須の BIGINT 型の定数。値は 0 以上である必要があります。このパラメーターは、1 行を複数行に入れ替えるときに key として使用する列の数を指定します。

• keys: 入れ替え操作の key として使用する必須の列。キーの数は num_keys で指定されます。num_keys がすべての列を key として使用することを指定する場合 (つまり、num_keys が列の総数と等しい場合)、1 行のみが返されます。

• idx: 必須。入れ替えられた後の行の ID。

• 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

構文

binary unbase64(string <str>)

説明

Base64 でエンコードされた文字列 str をバイナリ値に変換します。

パラメーター

str: 変換する必須の STRING 型の Base64 エンコード文字列。

戻り値

BINARY 型の値を返します。入力パラメーターが null の場合、この関数は null を返します。

例

• 例 1: 文字列 YWxpYmFiYQ== をバイナリ値に変換します。サンプル文:

  -- 戻り値は alibaba です。
  select unbase64('YWxpYmFiYQ==');

• 例 2: 入力パラメーターが null です。サンプル文:

  -- 戻り値は null です。
  select unbase64(null);

構文

string unique_id()

説明

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

構文

string uuid()

説明

29347a88-1e57-41ae-bb68-a9edbdd9**** のようなランダムな ID を返します。