Oracle Cloud Infrastructureドキュメント

関数(データ・フロー)リファレンス

次の関数は、式を作成できるように、データ・フローの演算子とともに使用されます。

集計関数
関数説明例
COUNT(value[, value]*)指定された1つ以上の式がすべてnullでない行の数を返します。COUNT(expr1)
COUNT(*)取得された行の合計数(nullのある行を含む)を返します。COUNT(*)
MAX(value)引数の最大値を返します。MAX(expr)
MIN(value)引数の最小値を返します。MIN(expr)
SUM(numeric)グループの値から計算された合計を返します。SUM(expr1)
AVG(numeric)式の数値の平均を返します。AVG(AGGREGATE_1.src1.attribute1)
LISTAGG(column[, delimiter]) WITHIN GROUP (order_by_clause)

order句に基づいてグループごとに、指定したデリミタで入力列の値を連結します。

columnには、結果で連結する値が含まれます。

delimiterは、結果でcolumn値を分離します。delimiterを指定しないと、空白文字が使用されます。

order_by_clauseは、連結した値を返す順序を決定します。

この関数はアグリゲータとしてのみ使用できます。また、グループ化の有無に関係なく使用できます。グループ化せずに使用すると、結果が単一行になります。グループ化して使用すると、各グループに1行ずつ返されます。

idとnameという2つの列を含む表について考えてみます。この表には3つの行があります。id列の値は101、102、102です。name列の値は、A、B、Cです。

+-----+--------+
| id  | name  |
+-----+--------+
| 101 | A     |
+-----+--------+
| 102 | B     |
+-----+--------+
| 102 | C     |
+-----+--------+

例1: グループ化なし

LISTAGG(id, '-') WITHIN GROUP (ORDER BY id)は、値がA-B-Cのname列を返します

+--------+
| name   |
+--------+
| A-B-C  |
+--------+

例2: idでグループ化

LISTAGG(id, '-') WITHIN GROUP (ORDER BY id)は、値がAとB-Cの2つに分かれたname列を返します。

+--------+
| name   |
+--------+
| A      |
+--------+
| B-C    |
+--------+
分析関数
関数 説明 例
FIRST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ] ) ウィンドウ・フレームの最初の行である行で評価された値を返します。 FIRST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)は、現在行とその行の後の1行の間にある行を計算し、BANK_IDでパーティション化し、BANK_NAMEで昇順にしたときの、ウィンドウ内のBANK_IDの最初の値を返します。
LAG(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) パーティション内の現在行より前の指定のオフセットにある行で評価された値を返します。そのような行がない場合、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1にデフォルト設定され、デフォルトはNULLになります。 LAG(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME DESC)は、BANK_IDでパーティション化し、BANK_NAMEの降順にしたときの、現在行より2行前のBANK_IDの値を返します。そのような値がない場合は、helloが返されます。
LAST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ]) ウィンドウ・フレームの最後の行である行で評価された値を返します。 LAST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)は、現在行とその行の後の1行の間にある行を計算し、BANK_IDでパーティション化し、BANK_NAMEで昇順にしたときの、ウィンドウ内のBANK_IDの最後の値を返します。
LEAD(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) パーティション内の現在行より後の特定のオフセットにある行で評価された値を返します。そのような行がない場合、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1にデフォルト設定され、デフォルトはNULLになります。 LEAD(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME)は、BANK_IDでパーティション化し、BANK_NAMEの昇順にしたときの、現在行より2行後のBANK_IDの値を返します。そのような値がない場合は、helloが返されます。
RANK() OVER([ partition_clause ] order_by_clause) ギャップを含む現在行のランクを返します(1からカウント)。 RANK() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME)は、BANK_IDのパーティション・グループ内の各行のランクをBANK_NAMEの昇順で返します。
ROW_NUMBER() OVER([ partition_clause ] order_by_clause) パーティション内の現在行の一意の番号を返します(1からカウント)。 ROW_NUMBER() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME)は、BANK_IDのパーティション・グループ内の各行の一意の行番号をBANK_NAMEの昇順で返します。
算術関数
関数説明例
ABS(numeric)numeric値の絶対乗を返します。ABS(-1)
CEIL(numeric)numeric値を超えない最小の整数を返しますCEIL(-1,2)
FLOOR(numeric)numeric値を超えない最大の整数を返します。FLOOR(-1,2)
MOD(numeric1, numeric2)numeric1をnumeric2で除算した後の剰余を返します。MOD(8,2)
POWER(numeric1, numeric2)numeric1をnumeric2で累乗します。POWER(2,3)
ROUND(numeric1, numeric2)numeric1を小数点以下numeric2桁に丸めて返します。ROUND(2.5,0)
TRUNC(numeric1, numeric2)numeric1を小数点以下numeric2桁に切り捨てて返します。TRUNC(2.5,0)
TO_NUMBER(expr[, format, locale])指定されたformatおよびlocale(オプション)に基づいて、exprを数値に変換します。デフォルトのロケールはen-USです。サポートされる言語タグ。

サポートされるフォーマット・パターン:

  • 0: 数字
  • #: 数字。ゼロは存在しないことを示します
  • .: 小数点セパレータのプレースホルダ
  • ,: グループ化セパレータのプレースホルダ
  • E: 指数フォーマットの仮数と指数を区切ります
  • -: デフォルトの否定接頭辞
  • ¤: 通貨シンボルで置換される通貨記号。二重の場合、国際通貨記号で置換されます。パターン内に存在する場合は、小数点セパレータのかわりに通貨小数点セパレータが使用されます。

TO_NUMBER('5467.12')は5467.12を返します

TO_NUMBER('-USD45,677.7', '¤¤##,###.#', 'en-US')は-45677.7を返します

配列関数

式演算子のみが配列関数をサポートします。

関数説明例
ARRAY_POSITION(array(...), element)指定された配列内で指定された要素の最初の出現位置を返します。

位置はゼロベースではなく、1で始まります。

ARRAY_POSITION(array(3, 2, 1, 4, 1), 1)は3を返します
REVERSE(array(...)) 指定された要素の配列を逆の順序で返します。 REVERSE(array(2, 1, 4, 3))は[3,4,1,2]を返します
ELEMENT_AT(array(...), index) 指定されたインデックス位置で指定された配列の要素を返します。

索引はゼロベースではなく、1で始まります。

index = -1の場合、最後の要素を返します。

 ELEMENT_AT(array(1, 2, 3), 2)は2を返します
条件ファンクション
関数説明例
COALESCE(value, value [, value]*)存在する場合は最初のnullでない引数を返し、それ以外の場合はnullを返します。COALESCE(NULL, 1, NULL)は1を返します
NULLIF(value, value)2つの値が等しい場合はnullを返し、それ以外の場合は最初の値を返します。NULLIF('ABC','XYZ')はABCを返します
日付と時間関数
関数 説明 例
CURRENT_DATE 現在の日付を返します。 CURRENT_DATEは、2023-05-26などの今日の日付を返します
CURRENT_TIMESTAMP セッション・タイムゾーンに対する現在の日付と時刻を返します。 CURRENT_TIMESTAMPは、今日の日付と現在の時刻(2023-05-26 12:34:56など)を返します
DATE_ADD(date, number_of_days) 指定したdateからnumber日後の日付を返します。 DATE_ADD('2017-07-30', 1)は2017-07-31を返します
DATE_FORMAT(expr, format[, locale])

指定されたformatおよびlocale(オプション)に基づいて、日付のexprをフォーマットします。デフォルトのロケールはen-USです。サポートされる言語タグ。

サポートされる日付フォーマット・パターン:

  • yy: 2桁の年
  • yyyy: 4桁の年
  • M: 月番号(例: 1月は1)
  • MM: 月番号(例: 1月は01)
  • MMM: 月の略称(例: Jan)
  • MMMM: 月の完全名(例: January)
  • d: 該当月の日を表す数字(例: 6月1日は1)
  • dd: 該当月の日を表す数字(例: 6月1日は01)
  • DDD: 001から366までの年の日(1月2日の場合は002など)
  • F: 月の曜日(6月の第3月曜日の場合は3など)の数値。
  • EEEまたはE: 曜日の略称(例: 日曜日はSun)
  • EEEE: 曜日の名前(例: 日曜日)
  • HH: 24時間フォーマット(00から23まで)
  • H: 24時間フォーマット(0から23まで)
  • hh: 12時間フォーマット(01から12まで)
  • h: 12時間フォーマット(1から12まで)
  • mm: 分(00から59まで)
  • ss: 秒(00から59まで)
  • SSS: 000から999までのミリ秒
  • a: AMまたはPM
  • z: PDTなどのタイムゾーン

DATE_FORMAT(Date '2020-10-11', 'yyyy-MM-dd')は'2020-10-11'を返します。最初の引数は、2020年10月11日を表すDateオブジェクトです。

DATE_FORMAT(Date '2018-junio-17', 'yyyy/MMMM/dd', 'es-ES')は'2018/junio/17'を返します
DAYOFMONTH(date) 特定の日(月間)を返します。 DAYOFMONTH('2020-12-25')は25を返します。
DAYOFWEEK(date) 特定の日(週間)を返します。 DAYOFWEEK('2020-12-25')は金曜日の6を返します。米国では、日曜日は1、月曜日は2などとみなされます。
DAYOFYEAR(date) 特定の日(年間)を返します。 DAYOFYEAR('2020-12-25')は360を返します
WEEKOFYEAR(date) 日付が年内の何番目の週かを返します。

WEEKOFYEAR('2022-07-28')は30を返します

WEEKOFYEAR('2022-07-28 13:24:30')は30を返します
HOUR(datetime) 日時の時間の値を返します。 HOUR('2020-12-25 15:10:30')は15を返します
LAST_DAY(date) 月末の日付を返します。 LAST_DAY('2020-12-25')は31を返します
MINUTE(datetime) 日時の分の値を返します。 HOUR('2020-12-25 15:10:30')は10を返します
MONTH(date) 日付の月の値を返します。 MONTH('2020-06-25')は6を返します。
QUARTER(date) 日付が属する四半期を返します。 QUARTER('2020-12-25')は4を返します。
SECOND(datetime) 日時の秒の値を返します。 SECOND('2020-12-25 15:10:30')は30を返します
TO_DATE(string, format_string[, localeStr]) format_string式を含む文字列式を日付に解析します。ロケールはオプションです。デフォルトはen-USです。サポートされる言語タグ。

パイプライン式では、format_stringはstrftimeフォーマット・コードを使用する必要があります。それ以外の場合は、大/小文字を区別する次のフォーマット文字列がサポートされます:

  • yy: 2桁の年
  • yyyy: 4桁の年
  • M: 月番号(例: 1月は1)
  • MM: 月番号(例: 1月は01)
  • MMM: 月の略称(例: Jan)
  • MMMM: 月の完全名(例: January)
  • d: 該当月の日を表す数字(例: 6月1日は1)
  • dd: 該当月の日を表す数字(例: 6月1日は01)
  • DDD: 001から366までの年の日(1月2日の場合は002など)
  • F: 月の曜日(6月の第3月曜日の場合は3など)の数値。
  • EEEまたはE: 曜日の略称(例: 日曜日はSun)
  • EEEE: 曜日の名前(例: 日曜日)
  • HH: 24時間フォーマット(00から23まで)
  • H: 24時間フォーマット(0から23まで)
  • hh: 12時間フォーマット(01から12まで)
  • h: 12時間フォーマット(1から12まで)
  • mm: 分(00から59まで)
  • ss: 秒(00から59まで)
  • SSS: 000から999までのミリ秒
  • a: AMまたはPM
  • z: PDTなどのタイムゾーン

TO_DATE('31 December 2016', 'dd MMMM yyyy')は、日付値2016-12-31を返します

TO_DATE('2018/junio/17', 'yyyy/MMMM/dd', 'es-ES')は、日付値2018-06-17を返します
TO_TIMESTAMP(expr, format_string[, localeStr]) 指定されたformat_stringおよびlocaleStr(オプション)に基づいて、VARCHARのexprをTIMESTAMPの値に変換します。

パイプライン式では、format_stringはstrftimeフォーマット・コードを使用する必要があります。それ以外の場合、次のフォーマット・パターンがサポートされます:

  • yy: 2桁の年
  • yyyy: 4桁の年
  • M: 月番号(例: 1月は1)
  • MM: 月番号(例: 1月は01)
  • MMM: 月の略称(例: Jan)
  • MMMM: 月の完全名(例: January)
  • d: 該当月の日を表す数字(例: 6月1日は1)
  • dd: 該当月の日を表す数字(例: 6月1日は01)
  • DDD: 001から366までの年の日(1月2日の場合は002など)
  • F: 月の曜日(6月の第3月曜日の場合は3など)の数値。
  • EEEまたはE: 曜日の略称(例: 日曜日はSun)
  • EEEE: 曜日の名前(例: 日曜日)
  • HH: 24時間フォーマット(00から23まで)
  • H: 24時間フォーマット(0から23まで)
  • hh: 12時間フォーマット(01から12まで)
  • h: 12時間フォーマット(1から12まで)
  • mm: 分(00から59まで)
  • ss: 秒(00から59まで)
  • SSS: 000から999までのミリ秒
  • a: AMまたはPM
  • z: PDTなどのタイムゾーン
 TO_TIMESTAMP('2020-10-11 11:10:10', 'yyyy-MM-dd HH:mm:ss')は、11am 10:10 Oct 11th, 2020を表すTIMESTAMPオブジェクトを返します
WEEK(date)

日付の週の値を返します。

 WEEK('2020-06-25')は4を返します
YEAR(date) 日付の年の値を返します。 YEAR('2020-06-25') returns 2020
ADD_MONTHS(date_expr, number_months) 指定した日付、タイムスタンプまたは文字列(yyyy-MM-ddまたはyyyy-MM-dd HH:mm:ss.SSSのようなフォーマット)に指定した数の月を追加した日付を返します。

ADD_MONTHS('2017-07-30', 1)は2017-08-30を返します

ADD_MONTHS('2017-07-30 09:07:21', 1)は2017-08-30を返します
MONTHS_BETWEEN(start_date_expr, end_date_expr)

start_date_exprとend_date_exprの間の月数を返します。start_date_exprとend_date_exprには、yyyy-MM-ddまたはyyyy-MM-dd HH:mm:ss.SSSのようなフォーマットの日付、タイムスタンプまたは文字列を指定できます

整数が返されるのは、両方の日付の日の部分が同じ場合、または両方が月の最終日の場合です。それ以外の場合は、1か月を31日として差が計算されます。

MONTHS_BETWEEN('2022-01-01', '2022-01-31')は1を返します

MONTHS_BETWEEN('2022-07-28', '2020-07-25')は24を返します

MONTHS_BETWEEN('2022-07-28 13:24:30', '2020-07-25 13:24:30')は24を返します
FROM_UTC_TIMESTAMP(time_stamp, time_zone)

日付、タイムスタンプまたは文字列をUTC時間として解釈し、その時間を指定したタイムゾーンのタイムスタンプに変換します。

文字列の場合は、yyyy-MM-ddまたは yyyy-MM-dd HH:mm:ss.SSSのようなフォーマットを使用します

タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。

 FROM_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1')は2017-07-14 03:40:00.0を返します
TO_UTC_TIMESTAMP(time_stamp, time_zone)

指定したタイムゾーンの日付、タイムスタンプまたは文字列をUTCタイムスタンプに変換します。

文字列の場合は、yyyy-MM-ddまたは yyyy-MM-dd HH:mm:ss.SSSのようなフォーマットを使用します

タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。

 TO_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1')は2017-07-14 01:40:00.0を返します
FROM_UNIXTIME(unix_time[, fmt])

指定したUnix時間すなわちエポックを、現在のシステム・タイム・ゾーンおよび指定したフォーマットで、その時点のタイムスタンプを表す文字列に変換します。

ノート: Unix時間とは、1970年1月1日00:00:00 UTCから経過した秒数です。

fmtを省略した場合、デフォルトのフォーマットはyyyy-MM-dd HH:mm:ssです

FROM_UNIXTIME(1255033470)は'2009-10-08 13:24:30'を返します

FROM_UNIXTIME(1637258854)は'2021-11-18 10:07:34'を返します

例のデフォルトのタイムゾーンはPSTです
UNIX_TIMESTAMP([time_expr[, fmt]])

現在時刻または指定した時刻をUnixタイムスタンプ(秒単位)に変換します。

time_exprは、yyyy-MM-ddまたはyyyy-MM-dd HH:mm:ss.SSSのようなフォーマットの、日付、タイムスタンプまたは文字列です

time_exprを指定しないと、現在時刻が変換されます。

time_exprが文字列で、fmtを省略した場合、デフォルトはyyyy-MM-dd HH:mm:ssです

UNIX_TIMESTAMP('1970-01-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')は28800を返します

この例のデフォルトのタイムゾーンはPSTです
INTERVAL 'year' YEAR[(year_precision)]

期間を年単位で返します。

year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。

INTERVAL '1' YEARは1年の期間を返します

INTERVAL '200' YEAR(3)は200年の期間を返します
INTERVAL 'year month' YEAR[(year_precision)] TO MONTH

期間を年と月の単位で返します。yearおよびmonthフィールドを使用して期間を格納するために使用します。

year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。

 INTERVAL '100-5' YEAR(3) TO MONTHは100年と5か月の期間を返します。先頭の年の精度として3を指定する必要があります。
INTERVAL 'month' MONTH[(month_precision)]

期間を月単位で返します。

month_precisionはmonthフィールドの桁数で、範囲は0から9です。month_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。

 INTERVAL '200' MONTH(3)は200か月の期間を返します。月の精度として3を指定する必要があります。
INTERVAL 'day time' DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]

日、時、分、秒で期間を返します。

day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。

fractional_seconds_precisionは、timeフィールドの秒値の小数部の桁数であり、範囲は0から9です。

 INTERVAL '11 10:09:08.555' DAY TO SECOND(3)は、11日10時間9分8秒555ミリ秒の期間を返します
INTERVAL 'day time' DAY[(day_precision)] TO MINUTE[(minute_precision)]

日、時、分で期間を返します。

day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。

minute_precisionは「分」フィールドの桁数で、範囲は0から2です。デフォルトは2です。

 INTERVAL '11 10:09' DAY TO MINUTEは、11日10時間9分の期間を返します
INTERVAL 'day time' DAY[(day_precision)] TO HOUR[(hour_precision)]

日数と時間数で期間を返します。

day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。

hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。

 INTERVAL '100 10' DAY(3) TO HOURは、100日10時間の期間を返します
INTERVAL 'day' DAY[(day_precision)]

日数で期間を返します。

day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。

 INTERVAL '999' DAY(3)は999日の期間を返します
INTERVAL 'time' HOUR[(hour_precision)] TO SECOND[(fractional_seconds_precision)]

時、分、秒で期間を返します。

hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。

fractional_seconds_precisionは、timeフィールドの秒値の小数部の桁数であり、範囲は0から9です。

 INTERVAL '09:08:07.6666666' HOUR TO SECOND(7)は、9時間8分7.6666666秒の期間を返します
INTERVAL 'time' HOUR[(hour_precision)] TO MINUTE[(minute_precision)]

時間と分数で期間を返します。

hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。

minute_precisionは「分」フィールドの桁数で、範囲は0から2です。デフォルトは2です。

 INTERVAL '09:30' HOUR TO MINUTEは、9時間30分の期間を返します
INTERVAL 'hour' HOUR[(hour_precision)]

時間数で期間を返します。

hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。

 INTERVAL '40' HOURは40時間の期間を返します
INTERVAL 'minute' MINUTE[(minute_precision)]

分数で期間を返します。

minute_precisionは「分」フィールドの桁数で、範囲は0から2です。デフォルトは2です。

 INTERVAL '15' MINUTEは15分間の期間を返します
INTERVAL 'time' MINUTE[(minute_precision)] TO SECOND[(fractional_seconds_precision)]

分数と秒数で期間を返します。

minute_precisionは「分」フィールドの桁数で、範囲は0から2です。デフォルトは2です。

fractional_seconds_precisionは、timeフィールドの秒値の小数部の桁数であり、範囲は0から9です。

 INTERVAL '15:30' MINUTE TO SECONDは15分30秒の期間を返します
INTERVAL 'second' SECOND[(fractional_seconds_precision)]

秒数で期間を返します。

fractional_seconds_precisionは、secondフィールドの小数部の桁数で、範囲は0から9です。デフォルトは3です。

 INTERVAL '15.678' SECONDは15.678秒の期間を返します
ハッシュ関数
関数説明例
MD5(all data types)データ型のMD5チェックサムを計算し、文字列値を返します。 MD5(column_name)
SHA1(all data types)データ型のSHA-1ハッシュ値を計算し、文字列値を返します。 SHA1(column_name)
SHA2(all data types, bitLength)データ型のSHA-2ハッシュ値を計算し、文字列値を返します。bitLengthは整数です。 SHA2 (column_name, bitLength can be set to 0 (equivalent to 256), 256, 384, or 512)。
ORA_HASH(expr, [max_bucket], [seed_value])

exprのハッシュ値を計算し、NUMBER値を返します。

exprには、式、列、リテラルを指定できます。

max_bucketは、0から4294967295 (デフォルト)までの返された最大バケット値です。

seed_valueは、0 (デフォルト)から4294967295までの値です。

Oracleは、ハッシュ・ファンクションをexprとseed_valueの組合せに適用して、同じデータ・セットに対して様々な結果を生成します。

ORA_HASH('1')

ORA_HASH('b', 2)

ORA_HASH(100, 10, 10)

ORA_HASH(EXPRESSION_1.CUSTOMERS.SSN, 2)
階層関数
関数説明例
SCHEMA_OF_JSON(string)JSON文字列を解析し、スキーマのDDLフォーマットを推測します。

SCHEMA_OF_JSON('[{\"Zipcode\":704,\"ZipCodeType\":\"STANDARD\",\"City\":\"ORACLECITY\",\"State\":\"OC\"}]')は'ARRAY<STRUCT<City:string,State:string,ZipCodeType:string,Zipcode:bigint>>'を返します

SCHEMA_OF_JSON('[{\"col\":0}]')は'ARRAY<STRUCT<col: BIGINT>>'を返します

FROM_JSON(column, string)

JSON文字列を含む列を解析し、指定スキーマを含む次のいずれかの型に解析します。

  • Map (キー・タイプがString)
  • Struct
  • Array

FROM_JSON('{\"Zipcode\":704,\"City\":\"ORACLE CITY\"}', 'STRUCT<Zipcode: BIGINT, City: STRING>')は、指定スキーマ{704, ORACLE CITY}のStruct型列を返します

FROM_JSON('{\"a\":1, \"b\":0.8}', 'STRUCT<a: BIGINT, b: DOUBLE>')は、指定スキーマ{1, 0.8}のStruct型列を返します

TO_JSON(column)StructまたはStruct配列あるいはMapまたはMap配列の型を含む列をJSON文字列に変換します。TO_JSON(TO_STRUCT('s1', TO_ARRAY(1,2,3), 's2', TO_MAP('key', 'value')))はJSON文字列{"s1":[1,2,3],"s2":{"key":"value"}}を返します
TO_MAP(string,column[,string,column]*)Map型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。入力キー列をNULLにすることはできません。すべて同じデータ型である必要があります。入力値列はすべて同じデータ型である必要があります。

TO_MAP('Ename',Expression_1.attribute1)は、Map型の列{"ENAME" -> 100}を返します

TO_MAP('block', EXPRESSION_1.MYSOURCE.address.block, 'unit', EXPRESSION_1.MYSOURCE.address.unit)は、Map型の列{"block" -> 1,"unit" -> 1}を返します

TO_STRUCT(string,column[,string,column]*)Struct型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。

TO_STRUCT('Ename',Expression_1.attribute1)は{100}を返します

TO_STRUCT('Id',Expression_1.attribute1, 'Name', Expression_1.attribute2)は{100, "John"}を返します

TO_ARRAY(column[,column]*)Array型の新しい列を作成します。入力列はすべて同じデータ型である必要があります。

TO_Array(Expression_1.attribute1)は[100]を返します

TO_ARRAY(EXPRESSION_1.attribute2,EXPRESSION_1.attribute3)は["John","Friend"]を返します

上位関数

式および階層データ型の作成をサポートするデータ・フロー演算子では、上位の関数を使用できます。

サポートされている演算子は次のとおりです:

  • 集計

  • 式

  • フィルタ

  • 結合

  • 検索

  • 分割済

  • ピボット

関数説明例
TRANSFORM(column, lambda_function)配列と無名関数を取得し、各要素に関数を適用して結果を出力配列に割り当てることによって、新しい配列を設定します。整数[1, 2, 3]の入力配列の場合、TRANSFORM(array, x -> x + 1)は[2, 3, 4]の新しい配列を返します。
TRANSFORM_KEYS(column, lambda_function)2つの引数(キーと値)を持つマップと関数を取得し、キーがラムダ関数の結果のタイプを持ち、値が列マップ値のタイプを持つマップを返します。整数キーおよび文字列値が{1 -> 'value1', 2 -> 'value2', 3 -> 'value3'}の入力マップの場合、TRANSFORM_KEYS(map, (k, v) -> k * 2 + 1)は{3 -> 'value1', 5 -> 'value2', 7 -> 'value3'}の新しいマップを返します。
TRANSFORM_VALUES(column, lambda_function)2つの引数(キーと値)を持つマップと関数を取得し、値がラムダ関数の結果のタイプを持ち、キーが列マップ・キーのタイプを持つマップを返します。 文字列キーおよび文字列値が{'a' -> 'value1', 'b' -> 'value2', 'c' -> 'value3'}の入力マップの場合、TRANSFORM_VALUES(map, (k, v) -> k || __ '' || v)は{'a' -> 'a_value1', 'b' -> 'b_value2', 'c' -> 'c_value3'}の新しいマップを返します。
ARRAY_SORT(array(...), lambda_function)

式演算子のみがARRAY_SORTをサポートします。

配列を取得し、2つの引数を取る指定された関数に従ってソートします。

この関数は、最初のエレメントが2番目のエレメントより小さいか、等しいか、または大きいかに応じて-1、0、または1を返す必要があります。

関数を省略すると、配列は昇順にソートされます。

 
array_sort(to_array(5, 6, 1),
                   (left, right) -> CASE WHEN left < right THEN -1
                                         WHEN left > right THEN 1 ELSE 0 END)

返される配列は次のとおりです。

[1,5,6]
演算子(比較)関数
関数説明例
CASE WHEN condition1 THEN result1 ELSE result2 END条件を満たす値を返します。CASE WHEN 1 > 0 THEN 'ABC' ELSE 'XYZ' ENDは、1> 0の場合はABCを返し、それ以外の場合はXYZを返します
AND論理AND演算子。両方のオペランドがtrueの場合はtrueを返し、それ以外の場合はfalseを返します。(x = 10 AND y = 20)は、xが10でyが20の場合、trueを返します。いずれか一方がtrueでない場合は、falseを返します
OR論理OR演算子。いずれかのオペランドがtrueであるか、両方ともtrueである場合はtrueを返し、それ以外の場合はfalseを返します。(x = 10 OR y = 20)は、xが10ではなく、かつyが20でない場合、falseを返します。いずれか一方がtrueの場合は、trueを返します
NOT論理NOT演算子。
LIKEstring1がstring2のパターンに一致するかどうかにかかわらず、文字列パターン・マッチングを実行します。
=等価かどうかをテストします。expr1がexpr2と等しい場合はtrueを返し、それ以外の場合はfalseを返します。x = 10は、xの値が10の場合はtrueを返し、それ以外の場合はfalseを返します
!=非等価かどうかをテストします。expr1がexpr2と等しくない場合はtrueを返し、それ以外の場合はfalseを返します。x != 10は、xの値が10の場合はfalseを返し、それ以外の場合はtrueを返します
>式の大なりをテストします。expr1がexpr2より大きい場合は、trueを返します。x > 10は、xの値が10より大きい場合はtrueを返し、それ以外の場合はfalseを返します
>=式の大なりイコールをテストします。expr1がexpr2以上の場合は、trueを返します。x > =10は、xの値が10以上の場合はtrueを返し、それ以外の場合はfalseを返します
<式の小なりをテストします。expr1がexpr2より小さい場合は、trueを返します。x < 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します
<=式の小なりイコールをテストします。expr1がexpr2以下の場合は、trueを返します。x <= 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します
||2つの文字列を連結します。'XYZ' || 'hello'は'XYZhello'を返します
BETWEEN範囲を評価します。FILTER_1.BANK.BANK_ID BETWEEN 1003 AND 1007
IN式が値リストと一致するかどうかをテストします。FILTER_2.ORDERS.ORDER_ID IN (1003, 1007)
文字列関数
関数説明例
CAST(value AS type)指定されたタイプの指定された値を返します。CAST("10" AS INT)は10を返します
CONCAT(string, string)文字列または列を結合した値を返しますCONCAT('Oracle','SQL')はOracleSQLを返します
CONCAT_WS(separator, expression1, expression2, expression3,...) 指定したセパレータを使用して、文字列または列を結合した値を文字列または列間で返します。

セパレータは必須であり、文字列である必要があります。

セパレータの後に少なくとも1つの式を指定する必要があります。たとえば: CONCAT_WS(',' col1)

CONCAT_WS('-', 'Hello', 'Oracle')はHello-Oracleを返します

CONCAT_WS(' ', address, city, postal_code)は123 MyCity 987654を返します

関数の子が配列の場合、配列はフラット化されます。

CONCAT_WS(',', 1,2,3, to_array(4,5,6), to_array(7,8), 9)は1,2,3,4,5,6,7,8,9を返します
INITCAP(string)各単語の最初の文字を大文字、残りの文字をすべて小文字にした文字列を、単語どうしを空白で区切って返します。INITCAP('oRACLE sql')はOracle Sqlを返します
INSTR(string, substring[start_position])stringで最初に出現するsubstringの(1から始まる)索引を返しますINSTR('OracleSQL', 'SQL')は7を返します
LOWER(string)すべての文字を小文字に変更して文字列を返します。LOWER('ORACLE')はoracleを返します
LENGTH(string)文字列の文字長またはバイナリ・データのバイト数を返します。文字列の長さには末尾のスペースも含まれます。LENGTH('Oracle')は6を返します
LTRIM(string)先頭のスペースを左から除去して文字列を返します。LTRIM(' Oracle')
NVL(expr1, epxr2)nullでない引数を返します。NVL(EXPRESSION_3.CUSTOMERS_JSON.CONTINENT_ID, ROWID())
REGEXP_SUBSTR(string, regexp[, RegexGroupIdx])入力文字列から正規表現パターンに一致する文字列を検索して抽出します。オプションのキャプチャ・グループ索引が指定されている場合、この関数は特定のグループを抽出します。

REGEXP_SUBSTR('https://www.oracle.com/products', 'https://([[:alnum:]]+\.?){3,4}/?')はhttps://www.oracle.comを返します

REGEXP_SUBSTR('22;33;44', '([0-9.]*);([0-9.]*);([0-9.]*)', 1)は22を返します
REPLACE(string, search, replacement)出現するすべてのsearchをreplacementに置換します。

文字列にsearchが見つからない場合、stringがそのまま返されます。

replacementが指定されていない場合、または空の文字列の場合、stringから削除されるsearchは何にも置換されません。

REPLACE('ABCabc', 'abc', 'DEF')はABCDEFを返します
RTRIM(string)先頭のスペースを右から除去して文字列を返します。RTRIM('Oracle ')
SUBSTRING(string, position[, substring_length])位置から始まる部分文字列を返します。 SUBSTRING('Oracle SQL' FROM 2 FOR 3)はracを返します
数値の場合、TO_CHAR(expr)および日付の場合、TO_CHAR(expr, format[, locale])数値および日付を文字列に変換します。数値の場合、書式は必要ありません。日付の場合は、「日時関数」で説明されているDATE_FORMATと同じ形式を使用します。デフォルトのロケールはen-USです。「サポートされている言語タグ」を参照してください。

パイプライン式では、format_stringはstrftimeフォーマット・コードを使用する必要があります。それ以外の場合、次の日付フォーマット・パターンがサポートされます:

  • yy: 2桁の年
  • yyyy: 4桁の年
  • M: 月番号(例: 1月は1)
  • MM: 月番号(例: 1月は01)
  • MMM: 月の略称(例: Jan)
  • MMMM: 月の完全名(例: January)
  • d: 該当月の日を表す数字(例: 6月1日は1)
  • dd: 該当月の日を表す数字(例: 6月1日は01)
  • DDD: 001から366までの年の日(1月2日の場合は002など)
  • F: 月の曜日(6月の第3月曜日の場合は3など)の数値。
  • EEEまたはE: 曜日の略称(例: 日曜日はSun)
  • EEEE: 曜日の名前(例: 日曜日)
  • HH: 24時間フォーマット(00から23まで)
  • H: 24時間フォーマット(0から23まで)
  • hh: 12時間フォーマット(01から12まで)
  • h: 12時間フォーマット(1から12まで)
  • mm: 分(00から59まで)
  • ss: 秒(00から59まで)
  • SSS: 000から999までのミリ秒
  • a: AMまたはPM
  • z: PDTなどのタイムゾーン

数値の例: TO_CHAR(123)は123を返します

日付の例: TO_CHAR(Date '2020-10-30', 'yyyy.MM.dd', 'en-US')は、文字列2020.10.30を返します。最初の引数は、2020年10月30日を表すDateオブジェクトです。

UPPER(string)すべての文字を大文字に変更して文字列を返します。UPPER('oracle')はORACLEを返します
LPAD(str, len[, pad])文字列の左側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合のデフォルトは空白です。LPAD('ABC', 5, '*')は'**ABC'を返します
RPAD(str, len[, pad])文字列の右側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合のデフォルトは空白です。RPAD('XYZ', 6, '+' ) returns 'XYZ+++'
一意ID関数
関数説明例
NUMERIC_ID()各行に対して64ビットの数値の汎用一意識別子を生成します。NUMERIC_ID()は、たとえば3458761969522180096および3458762008176885761を返します
ROWID()単調に増加する64ビット数を生成します。ROWID()は、たとえば、0、1、2などを返します
UUID()各行に対して128ビットの文字列の汎用一意識別子を生成します。UUID()は、たとえば20d45c2f-0d56-4356-8910-162f4f40fb6dを返します
MONOTONICALLY_INCREASING_ID() 単調に増加する一意の64ビット整数を生成します。連続した数字にはなりません。 MONOTONICALLY_INCREASING_ID()は、たとえば、8589934592や25769803776を返します