10g リリース2（10.2）
B19201-02
 目次 |
 索引 |
| Oracle Database SQLリファレンス 10g リリース2（10.2） B19201-02 |
|
ファンクションは、データ項目を操作し、結果を戻すという点で演算子に似ていますが、ファンクションと演算子は引数を指定する書式が異なります。次の書式によって、ファンクションでは0（ゼロ）以上の引数を操作できます。
function(argument, argument, ...)
引数を指定しないファンクションは疑似列に似ています（第3章「疑似列」を参照）。ただし、疑似列は、通常、結果セット内の各行に対して異なる値を戻しますが、引数を指定しないファンクションは、各行に対して同じ値を戻します。
この章では、次の内容を説明します。
SQLファンクションは、Oracleデータベースに組み込まれており、適切なSQL文で使用できます。SQLファンクションと、PL/SQLで記述されたユーザー定義ファンクションを混同しないでください。
SQLファンクションが戻す値のデータ型以外のデータ型の引数でSQLファンクションをコールすると、OracleはSQLファンクションを実行する前に、その引数を必要なデータ型に変換します。NULLを引数としてSQLファンクションをコールすると、SQLファンクションは自動的にNULLを戻します。この規則に従わないSQLファンクションは、CONCAT、NVL、REPLACEおよびREGEXP_REPLACEのみです。
SQLファンクションの構文図では、データ型とともに引数が示されています。SQL構文にパラメータfunctionが指定されている場合は、この項で説明するファンクションの1つに置き換えます。ファンクションは、引数のデータ型および戻り値によってグループ化されています。
|
参照:
|
次に、ファンクションのカテゴリを表す構文を示します。
次の項に、前述の図のユーザー定義ファンクション以外の各グループにおける組込みSQLファンクションを示します。すべての組込みSQLファンクションを、アルファベット順に説明します。
単一行ファンクションは、問合せ対象の表またはビューの各行に対して1つの結果行を戻します。これらのファンクションは、SELECT構文のリスト、WHERE句、START WITH句、CONNECT BY句およびHAVING句に指定できます。
数値ファンクションは入力として数値を受け取り、結果として数値を戻します。数値ファンクションのほとんどは、38桁（10進）のNUMBER値を戻します。超越関数（COS、COSH、EXP、LN、LOG、SIN、SINH、SQRT、TANおよびTANH）は、36桁（10進）の値を戻します。超越関数のACOS、ASIN、ATAN、ATAN2は、30桁（10進）の値を戻します。数値ファンクションを次に示します。
文字値を戻す文字ファンクションは、特に指定がないかぎり、次のデータ型の値を戻します。
ファンクションによって戻される値の長さは、戻されるデータ型の最大長によって制限されます。
CHARまたはVARCHAR2型の値を戻すファンクションの戻り値の長さが制限を超えた場合、Oracleデータベースは戻り値から制限を超えた部分を切り捨てて、エラー・メッセージを表示せずにその結果を戻します。
CLOB型の値を戻すファンクションの戻り値の長さが制限を超えた場合、Oracleはエラーを表示し、データを戻しません。
文字値を戻す文字ファンクションを次に示します。
NLS文字ファンクションは、キャラクタ・セットの情報を戻します。NLS文字ファンクションを次に示します。
数値を戻す文字ファンクションの引数には、すべての文字データ型を指定できます。
数値を戻す文字ファンクションを次に示します。
日時ファンクションは、日付（DATE）、タイムスタンプ（TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE）および期間（INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH）の値を操作します。
一部の日付ファンクションは、OracleのDATEデータ型（ADD_MONTHS、CURRENT_DATE、LAST_DAY、NEW_TIMEおよびNEXT_DAY）用に設計されています。これらのファンクションの引数にタイムスタンプ値を指定すると、Oracleデータベースは入力された型をDATE値に内部的に変換し、DATE値を戻します。ただし、MONTHS_BETWEENファンクションは数値を戻し、ROUNDおよびTRUNCファンクションはタイムスタンプ値または期間値を受け入れません。
その他の日時ファンクションは、3種類のすべてのデータ型（日付、タイムスタンプ、期間）を受け入れ、それらのいずれかのデータ型の値を戻すように設計されています。
日時ファンクションを次に示します。
一般的な比較ファンクションは、値の集合から最大値または最小値（あるいはその両方）を決定します。一般的な比較ファンクションを次に示します。
変換ファンクションは、あるデータ型から他のデータ型に値を変換します。一般に、ファンクション名はdatatype TO datatypeの書式で指定されます。最初のデータ型は入力データ型です。2番目のデータ型は出力データ型です。SQL変換ファンクションを次に示します。
ラージ・オブジェクト・ファンクションは、LOBを操作します。ラージ・オブジェクト・ファンクションを次に示します。
収集ファンクションは、ネストした表およびVARRAYを操作します。SQL収集ファンクションを次に示します。
階層ファンクションは、階層パス情報を結果セットに適用します。
データ・マイニング・ファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIを使用して作成したモデルを操作します。SQLデータ・マイニング・ファンクションを次に示します。
XMLファンクションは、XML文書またはフラグメントを操作または戻します。出力の書式設定など、これらのファンクションを使用したXMLデータの選択および問合せの詳細は、『Oracle XML DB開発者ガイド』を参照してください。SQL XMLファンクションを次に示します。
エンコーディング・ファンクションおよびデコーディング・ファンクションでは、データベース内のデータを調査およびデコードできます。
NULL関連ファンクションは、NULL処理を簡単にします。NULL関連ファンクションを次に示します。
環境ファンクションおよび識別子ファンクションは、インスタンスとセッションの情報を提供します。これらのファンクションを次に示します。
集計ファンクションは、単一行に基づく結果行を戻すのではなく、行のグループに基づく単一結果行を戻します。集計ファンクションは、SELECT構文のリスト、ORDER BYおよびHAVING句に指定できます。集計ファンクションは、通常、SELECT文のGROUP BY句で使用され、この句の中で問合せ対象の表またはビューの行がグループ化されます。GROUP BY句を含む問合せでは、SELECT構文のリストの要素は、集計ファンクション、GROUP BY式、定数またはこれらのいずれかを含む式になります。Oracleは、集計ファンクションを行の各グループに適用し、各グループに単一の結果行を戻します。
GROUP BY句を指定しないと、SELECT構文のリスト内の集計ファンクションは、問合せ対象の表またはビューのすべての行に適用されます。HAVING句で集計ファンクションを使用して、グループを出力しないこともできます。このとき、出力は、問合せ対象の表またはビューの各行の値ではなく、集計ファンクションの結果に基づきます。
単一の引数を指定する多くの集計ファンクションには、次の句があります。
たとえば、1、1、1、3の平均値はDISTINCTでは2となり、ALLでは1.5となります。どの句も指定しない場合、デフォルトでALLが使用されます。
COUNT(*)およびGROUPINGを除くすべての集計ファンクションは、NULLを無視します。集計ファンクションに対する引数にNVLファンクションを使用して、NULLをある値で置き換えることができます。COUNTはNULLを戻しません。数字または0（ゼロ）を戻します。その他の集計ファンクションでは、データ・セットに行がない場合、または集計ファンクションに対する引数としてNULLを持つ行のみがある場合はNULLを戻します。
集計ファンクションMIN、MAX、SUM、AVG、COUNT、VARIANCEおよびSTDDEVの後に、KEEPキーワードを指定すると、FIRSTまたはLASTファンクションと組み合せて使用することができ、与えられたソート指定に対して、FIRSTまたはLASTとしてランク付けされた一連の行の一連の値を操作できます。詳細は、「FIRST」を参照してください。
集計ファンクションはネストできます。たとえば、次の例では、サンプル・スキーマhrのすべての部門における最高額の給与の平均を計算します。
SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id; AVG(MAX(SALARY)) ---------------- 10925
この計算では、GROUP BY句（department_id）で定義されている各グループごとの内部集計（MAX（salary））を評価し、その結果をもう一度集計しています。
集計ファンクションを次に示します。
分析ファンクションは、行のグループに基づいて集計値を計算します。各グループに対して複数の行を戻す点で、集計ファンクションと異なります。行のグループをウィンドウといい、analytic_clauseで定義されます。各行に対して、行のスライディング・ウィンドウが定義されます。このウィンドウによって、カレント行の計算に使用される行の範囲が決定されます。ウィンドウの大きさは、行の物理数値または時間などのロジカル・インターバルに基づきます。
分析ファンクションは、問合せで最後に実行される演算（最後のORDER BY句を除く）の集合です。すべての結合およびすべてのWHERE、GROUP BYおよびHAVING句は、分析ファンクションが処理される前に実行されます。そのため、分析ファンクションは、SELECT構文のリストまたはORDER BY句のみに指定できます。
通常、分析ファンクションは、累積集計、移動集計、センター集計およびレポート集計の実行に使用されます。
次に、この構文のセマンティクスを示します。
分析ファンクションの名前を指定します（セマンティクスの説明の後に示す分析ファンクションのリストを参照）。
分析ファンクションには引数を0〜3個指定します。引数には、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を指定できます。Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換します。個々のファンクションに特に指定がないかぎり、戻り型もその引数のデータ型となります。
OVER analytic_clause句は、ファンクションが問合せ結果セットを操作することを示します。FROM、WHERE、GROUP BYおよびHAVING句の後に計算されます。SELECT構文のリストのこの句またはORDER BY句に分析ファンクションを指定できます。分析ファンクションに基づいて、問合せの結果をフィルタするには、これらのファンクションを親問合せ内でネストした後、ネストされた副問合せの結果をフィルタします。
analytic_clauseには、次の注意事項があります。
analytic_clauseのどの部分にも、分析ファンクションを指定できません。つまり、分析ファンクションはネストできません。ただし、副問合せで分析ファンクションを指定して、別の分析ファンクションを計算することはできます。
OVER analytic_clauseには、組込み分析ファンクションと同様に、ユーザー定義の分析ファンクションを指定できます。「CREATE FUNCTION」を参照してください。
PARTITION BY句を使用すると、1つ以上のvalue_exprに基づいて、問合せ結果セットをグループに分割できます。この句を省略すると、ファンクションは問合せ結果セットのすべての行を単一のグループとして扱います。
分析ファンクションでquery_partition_clauseを使用するには、構文の上位ブランチ（カッコなし）を使用します。この句をモデルの問合せ（model_column_clauses内）またはパーティション化された外部結合（outer_join_clause内）で使用するには、構文の下位ブランチ（カッコ付き）を使用します。
同じまたは異なるPARTITION BYキーで、同じ問合せに複数の分析ファンクションを指定できます。
問い合せているオブジェクトにパラレル属性があり、query_partition_clauseで分析ファンクションを指定する場合は、ファンクションの計算もパラレル化されます。
有効な値のvalue_exprは、定数、列、非分析ファンクション、ファンクション式、またはこれらのいずれかを含む式です。
order_by_clauseを使用すると、パーティション内でのデータの順序付け方法を指定できます。PERCENTILE_CONTおよび（単一キーのみを適用する）PERCENTILE_DISC以外の分析ファンクションでは、各キーがvalue_exprで定義され、順序付けシーケンスで修飾された複数キーのパーティションの値を順序付けできます。
各ファンクションには、複数の順序式を指定できます。これは、2番目の式が最初の式にある同一値との間の関連性を変換できるため、値をランク付けするファンクションを使用する場合に特に有効です。
order_by_clauseの結果が複数行の個々の値である場合、ファンクションは各行の同じ値を戻します。この動作の詳細は、「SUM」の分析例を参照してください。
ORDER BY句には次の制限事項があります。
order_by_clauseを分析ファンクションで使用する場合、式（expr）が必要です。SIBLINGSキーワードは無効です（これは、階層問合せでのみ有効です）。位置（position）および列別名（c_alias）も無効です。それ以外で使用する場合、このorder_by_clauseは、問合せまたは副問合せ全体を順序付ける場合に使用するものと同じです。
RANGEキーワードを使用する分析ファンクションでは、次の2つのウィンドウのいずれかを指定する場合に、ORDER BY句で複数のソート・キーを使用できます。
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。この短縮形は、RANGE UNBOUNDED PRECEDINGです。
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING。この短縮形は、RANGE UNBOUNDED FOLLOWINGです。
この2つ以外のウィンドウ境界では、分析ファンクションのORDER BY句でソート・キーを1つしか持てません。この制限事項は、ROWキーワードで指定したウィンドウ境界には適用されません。
順序付けシーケンス（昇順または降順）を指定します。デフォルトはASCです。
NULL値を含む戻された行が順序の最初にくるか、最後にくるかを指定します。
NULLS LASTは昇順のデフォルトで、NULLS FIRSTは降順のデフォルトです。
分析ファンクションは、常に、ファンクションのorder_by_clauseで指定された順序で行を操作します。ただし、ファンクションのorder_by_clauseは結果の順序を保証しません。最終結果の順序を保証するには、問合せのorder_by_clauseを使用してください。
一部の分析ファンクションでは、windowing_clauseを使用できます。7-16ページに示す分析ファンクションのリストでは、windowing_clauseを使用できるファンクションにアスタリスク（*）が付いています。
これらのキーワードは、各行に対して、ファンクションの結果の計算に使用されるウィンドウ（行の物理集合または論理集合）を定義します。ファンクションは、ウィンドウのすべての行に適用されます。ウィンドウは、問合せ結果セット内またはパーティションの上から下まで移動します。
order_by_clauseを指定しないと、この句を指定できません。RANGE句で定義したウィンドウ境界には、order_by_clauseで指定できる式が1つのみのものもあります。詳細は、「ORDER BY句の制限事項:」を参照してください。
分析ファンクションが論理オフセットで戻す値は、常に決定的なものです。ただし、分析ファンクションが物理オフセットで戻す値は、順序式の結果が一意の順序にならないかぎり、非決定的な結果を生成することがあります。order_by_clauseに複数の列を指定して、結果の順序を一意にする必要があります。
BETWEEN ... AND句を使用すると、ウィンドウにスタート・ポイントおよびエンド・ポイントを指定できます。最初の式（ANDの前）はスタート・ポイントを定義し、2番目の式（ANDの後）はエンド・ポイントを定義します。
BETWEENを省略してエンド・ポイントを1つのみ指定すると、Oracleはそれをスタート・ポイントとみなし、デフォルトでカレント行をエンド・ポイントに指定します。
UNBOUNDED PRECEDINGを指定すると、パーティションの最初の行で、ウィンドウが開始します。これはスタート・ポイントの指定で、エンド・ポイントの指定としては使用できません。
UNBOUNDED FOLLOWINGを指定すると、パーティションの最後の行で、ウィンドウが終了します。これはエンド・ポイントの指定で、スタート・ポイントの指定としては使用できません。
スタート・ポイントとして、ウィンドウがカレント行または値（それぞれROWまたはRANGEを指定したかどうかに基づく）で開始することを指定します。この場合、value_expr PRECEDINGをエンド・ポイントにできません。
エンド・ポイントとして、ウィンドウがカレント行または値（それぞれROWまたはRANGEを明示的に指定したかどうかに基づく）で終了することを指定します。この場合、value_expr FOLLOWINGをスタート・ポイントにできません。
RANGEまたはROWに対して、次のことがいえます。
value_expr FOLLOWINGがスタート・ポイントの場合、エンド・ポイントはvalue_expr FOLLOWINGである必要があります。
value_expr PRECEDINGがエンド・ポイントの場合、スタート・ポイントはvalue_expr PRECEDINGである必要があります。
数値形式の時間間隔で定義されている論理ウィンドウを定義する場合、変換ファンクションを使用する必要があります。
ROWSを指定した場合、次のことがいえます。
value_exprは物理オフセットになります。これは定数または式であり、正数値に評価する必要があります。
value_exprがスタート・ポイントの一部の場合、エンド・ポイントの前にある行に評価する必要があります。
RANGEを指定した場合、次のことがいえます。
value_exprは論理オフセットになります。これは、正数値または期間リテラルに評価する定数または式である必要があります。期間リテラルの詳細は、「リテラル」を参照してください。
order_by_clauseには、式を1つのみ指定できます。
value_exprが数値に対して評価を行う場合、ORDER BY exprは数値データ型またはDATEデータ型である必要があります。
value_exprが間隔値に対して評価を行う場合、ORDER BY exprはDATEデータ型である必要があります。
windowing_clauseを完全に省略した場合、デフォルトでRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWになります。
分析ファンクションは、通常、データ・ウェアハウス環境で使用されます。次に示す分析ファンクションのリストでは、windowing_clauseを含む完全な構文を使用できるファンクションには、アスタリスク（*）が付いています。
オブジェクト参照ファンクションは、指定されたオブジェクト型のオブジェクトへの参照となるREF値を操作します。オブジェクト参照ファンクションを次に示します。
モデル・ファンクションは、SELECT文のmodel_clauseでのみ使用できます。新しいモデル・ファンクションを次に示します。
この項では、SQLファンクションをアルファベット順に示し、説明します。
ABSは、nの絶対値を戻します。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。また、引数の数値データ型と同じデータ型を戻します。
次の例では、-15の絶対値を戻します。
SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15
ACOSは、nのアーク・コサインを戻します。引数nは-1〜1の範囲で、ファンクションによって戻される値は0〜π（ラジアン）の範囲です。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。引数がBINARY_FLOATの場合、このファンクションはBINARY_DOUBLEを戻します。それ以外の場合、引数と同じ数値データ型を戻します。
次の例では、.3のアーク・コサインを戻します。
SELECT ACOS(.3)"Arc_Cosine" FROM DUAL; Arc_Cosine ---------- 1.26610367
ADD_MONTHSは、日付dateに月数integerを加えて戻します。引数dateには、日時値、または暗黙的にDATEに変換可能な任意の値を指定できます。引数integerには、整数、または暗黙的に整数に変換可能な任意の値を指定できます。戻り型は、dateのデータ型に関係なく常にDATEです。dateが月の最終日の場合、または結果の月の日数がdateの日付コンポーネントよりも少ない場合、戻される値は結果の月の最終日となります。それ以外の場合、結果にはdateと同じ日付コンポーネントが含まれます。
次の例では、サンプル表employeesのhire_date後の月を戻します。
SELECT TO_CHAR( ADD_MONTHS(hire_date,1), 'DD-MON-YYYY') "Next month" FROM employees WHERE last_name = 'Baer'; Next Month ----------- 07-JUL-1994
APPENDCHILDXMLは、ユーザー指定の値を、XPath式で指定したノードの子としてターゲットXMLに追加します。
XMLType_instanceは、XMLTypeのインスタンスです。
XPath_stringは、1つ以上の子ノードが追加されるノードを1つ以上指定するXPath式です。先頭にスラッシュを付けて絶対XPath_stringを指定したり、先頭のスラッシュを省略して相対XPath_stringを指定できます。先頭のスラッシュを省略した場合、相対パスのコンテキストは、デフォルトでルート・ノードに設定されます。
value_exprは、XMLTypeの1つ以上のノードを指定します。これは文字列に変換する必要があります。
namespace_stringは、XPath_stringのネームスペース情報を提供します。このパラメータは、VARCHAR2型である必要があります。次の例では、/Buildingノードの値が「Rented」の場合に、/Ownerノードをoe.warehouses表のwarehouse_specの/Warehouse/Buildingノードに追加します。
UPDATE warehouses SET warehouse_spec = APPENDCHILDXML(warehouse_spec, 'Warehouse/Building', XMLType('<Owner>Grandco</Owner>')) WHERE EXTRACTVALUE(warehouse_spec, '/Warehouse/Building') = 'Rented'; SELECT warehouse_id, warehouse_name, EXTRACTVALUE(warehouse_spec, '/Warehouse/Building/Owner') "Prop.Owner" FROM warehouses WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Building/Owner') = 1; WAREHOUSE_ID WAREHOUSE_NAME Prop.Owner ------------ --------------- ---------- 2 San Francisco Grandco 3 New Jersey Grandco
ASCIISTRは、任意のキャラクタ・セットの文字列、または文字列に変換する式を引数として取り、データベース・キャラクタ・セットのASCII文字列を戻します。ASCII文字以外は、\xxxxという書式に変換されます。xxxxは、UTF-16のコード単位です。
次の例では、テキスト文字列「ABÄCDE」をASCII文字列で戻します。
SELECT ASCIISTR('ABÄCDE') FROM DUAL; ASCIISTR(' ---------- AB\00C4CDE
ASCIIは、charの最初の文字の、データベース・キャラクタ・セットでの10進表記を戻します。
charのデータ型は、CHAR、VARCHAR2、NCHARまたはNVARCHAR2です。戻り値のデータ型はNUMBERです。データベース・キャラクタ・セットが7ビットのASCIIの場合、このファンクションはASCII値を戻します。データベース・キャラクタ・セットがEBCDICコードの場合、このファンクションはEBCDIC値を戻します。このファンクションと一致するEBCDIC文字ファンクションは存在しません。
このファンクションは、CLOBデータを直接的にサポートしていません。ただし、暗黙的なデータ変換を使用してCLOBを引数として渡すことはできます。
次の例では、姓が文字「L」で始まり、ASCII表記が76の従業員を戻します。
SELECT last_name FROM employees WHERE ASCII(SUBSTR(last_name, 1, 1,)) = 76; LAST_NAME ------------------------- Ladwig Landry Lee Livingston
ASINは、nのアーク・サインを戻します。引数nは-1〜1の範囲で、ファンクションによって戻される値は-π/2〜π/2（ラジアン）の範囲です。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。引数がBINARY_FLOATの場合、このファンクションはBINARY_DOUBLEを戻します。それ以外の場合、引数と同じ数値データ型を戻します。
次の例では、.3のアーク・サインを戻します。
SELECT ASIN(.3) "Arc_Sine" FROM DUAL; Arc_Sine ---------- .304692654
ATANは、nのアーク・タンジェントを戻します。引数nの範囲に制限はなく、ファンクションによって戻される値は-π/2〜π/2（ラジアン）の範囲です。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。引数がBINARY_FLOATの場合、このファンクションはBINARY_DOUBLEを戻します。それ以外の場合、引数と同じ数値データ型を戻します。
次の例では、.3のアーク・タンジェントを戻します。
SELECT ATAN(.3) "Arc_Tangent" FROM DUAL; Arc_Tangent ---------- .291456794
ATAN2は、n1およびn2のアーク・タンジェントを戻します。引数n1の範囲に制限はなく、n1とn2の符号により、ファンクションによって戻される値は-π〜π（ラジアン）の範囲です。ATAN2(n1,n2)と、ATAN2(n1/n2)は同じです。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。いずれかの引数がBINARY_FLOATまたはBINARY_DOUBLEの場合、このファンクションはBINARY_DOUBLEを戻します。それ以外の場合、NUMBERを戻します。
次の例では、.3および.2のアーク・タンジェントを戻します。
SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL; Arc_Tangent2 ------------ .982793723
AVGは、exprの平均値を戻します。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。また、引数の数値データ型と同じデータ型を戻します。
DISTINCTを指定する場合は、analytic_clauseのquery_partition_clauseのみ指定できます。order_by_clauseおよびwindowing_clauseは指定できません。
次の例では、hr.employees表にあるすべての従業員の平均給与を計算します。
SELECT AVG(salary) "Average" FROM employees; Average -------- 6425
次の例では、employees表の各従業員について、ある期間内に雇用された従業員の平均給与を所属別に計算します。
SELECT manager_id, last_name, hire_date, salary, AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg FROM employees; MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG ---------- ------------------------- --------- ---------- ---------- 100 Kochhar 21-SEP-89 17000 17000 100 De Haan 13-JAN-93 17000 15000 100 Raphaely 07-DEC-94 11000 11966.6667 100 Kaufling 01-MAY-95 7900 10633.3333 100 Hartstein 17-FEB-96 13000 9633.33333 100 Weiss 18-JUL-96 8000 11666.6667 100 Russell 01-OCT-96 14000 11833.3333 . . .
BFILENAMEは、サーバーのファイル・システムの物理LOBバイナリ・ファイルに対応付けられているBFILEロケータを戻します。
directory'は、実際にファイルが存在するサーバーのファイル・システム上のフルパス名に対する別名となるデータベース・オブジェクトです。
filename'は、サーバーのファイル・システムにあるファイルの名前です。
SQL文、PL/SQL文、DBMS_LOBパッケージまたはOCIの操作で、これらをBFILENAMEへの引数として使用するには、まずディレクトリ・オブジェクトを作成し、BFILE値を物理ファイルと対応付ける必要があります。
次の2つの方法で、このファンクションを使用できます。
ディレクトリの引数の大/小文字は区別されます。そのため、データ・ディクショナリ内に存在する名前と同じ名前でディレクトリ・オブジェクト名を指定しているかを確認する必要があります。たとえば、CREATE DIRECTORY文で、大/小文字を組み合せた識別子を引用符で囲んで使用してAdminディレクトリ・オブジェクトを作成すると、BFILENAMEファンクションを使用する場合、ディレクトリ・オブジェクトを'Admin'として指定する必要があります。filename引数は、ご使用のオペレーティング・システムの大/小文字および記号の表記規則に従って指定する必要があります。
|
参照:
|
次の例では、サンプル表pm.print_mediaに行を挿入します。BFILENAMEを使用して、$ORACLE_HOME/demo/schema/product_mediaディレクトリにある、サーバーのファイル・システムのバイナリ・ファイルを識別します。次の例では、PMスキーマでのディレクトリのデータベース・オブジェクトmedia_dirの作成方法を示します。
CREATE DIRECTORY media_dir AS '/demo/schema/product_media'; INSERT INTO print_media (product_id, ad_id, ad_graphic) VALUES (3000, 31001, BFILENAME('MEDIA_DIR', 'modem_comp_ad.gif'));
BIN_TO_NUMは、ビット・ベクトルを同等の数値に変換します。このファンクションの各引数は、ビット・ベクトルのビットを表します。このファンクションは、引数として、任意の数値データ型、または暗黙的にNUMBERに変換可能な数値以外のデータ型を取ります。各exprは、0または1に評価される必要があります。このファンクションはOracleのNUMBER値を戻します。
BIN_TO_NUMは、データ・ウェアハウスのアプリケーションで、グルーピング・セットを使用して、マテリアライズド・ビューから対象グループを検索する場合に有効です。
|
参照:
|
次の例では、バイナリの値を数値に変換します。
SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL; BIN_TO_NUM(1,0,1,0) ------------------- 10
BITANDは、構成要素expr1、expr2のビットに対するAND操作の計算を行い、整数を戻します。expr1およびexpr2は、負でない整数に変換されるものである必要があります。次に示すとおり、このファンクションは、一般的にDECODEファンクションで使用されます。
AND操作は2つのビット値を比較します。値が同じ場合、演算子は1を戻します。値が異なる場合、演算子は0を戻します。重要なビットのみが比較されます。たとえば、整数の5（バイナリ101）と1（バイナリ001または1）のAND操作は、右端のビットのみを比較するため、1（バイナリ1）の値を戻します。
どちらの引数にも、任意の数値データ型、または暗黙的にNUMBERに変換可能な数値以外のデータ型を指定できます。戻り型はNUMBERです。
次の例は、サンプル表oe.ordersのorder_statusをビットごとに表示します。（この例では、オプションの合計が7であると指定されているため、order_statusが7を超える行は排除されます。）
SELECT order_id, customer_id, DECODE(BITAND(order_status, 1), 1, 'Warehouse', 'PostOffice') Location, DECODE(BITAND(order_status, 2), 2, 'Ground', 'Air') Method, DECODE(BITAND(order_status, 4), 4, 'Insured', 'Certified') Receipt FROM orders WHERE order_status < 8; ORDER_ID CUSTOMER_ID LOCATION METHOD RECEIPT ---------- ----------- ---------- ------ --------- 2458 101 PostOffice Air Certified 2397 102 Warehouse Air Certified 2454 103 Warehouse Air Certified 2354 104 PostOffice Air Certified 2358 105 PostOffice Ground Certified 2381 106 Warehouse Ground Certified 2440 107 Warehouse Ground Certified 2357 108 Warehouse Air Insured 2394 109 Warehouse Air Insured 2435 144 PostOffice Ground Insured 2455 145 Warehouse Ground Insured 2356 105 Warehouse Air Insured 2360 107 PostOffice Air Insured ...
CARDINALITYは、ネストした表内の要素数を戻します。戻り型は、NUMBERです。ネストした表が空であるかNULLの集合である場合、CARDINALITYはNULLを戻します。
次の例では、pm.print_mediaサンプル表のネストした表の列ad_textdocs_ntab内の要素数を示します。
SELECT product_id, CARDINALITY(ad_textdocs_ntab) FROM print_media; PRODUCT_ID CARDINALITY(AD_TEXTDOCS_NTAB) ---------- ----------------------------- 3060 3 2056 3 3106 3 2268 3
CASTは、ある組込みデータ型またはコレクション型の値を、別の組込みデータ型またはコレクション型の値に変換します。
CASTを使用すると、ある組込みデータ型またはコレクション型の値を、別の組込みデータ型またはコレクション型に変換できます。名前のないオペランド（日付や副問合せの結果セットなど）または名前付きのコレクション（VARRAYやネストした表など）を型互換の名前付きコレクションにキャストできます。type_nameは、組込みデータ型またはコレクション型の名前である必要があり、オペランドは、組込みデータ型であるか、またはその値がコレクション値である必要があります。
オペランドでは、exprは組込みデータ型、コレクション型またはANYDATA型のインスタンスのいずれかです。exprがANYDATA型のインスタンスである場合、CASTはANYDATAインスタンスの値を抽出し、その値がキャスト対象の型と一致する場合はその値を戻し、一致しない場合はNULLを戻します。MULTISETは、副問合せの結果セットをとり、コレクション値を戻すようにOracleデータベースに通知します。表5-1に、どの組込みデータ型が、どの組込みデータ型にキャストできるかを示します （CASTは、LONG型、LONG RAW型、またはOracleが提供する型をサポートしていません）。
CASTは、LOBデータ型のいずれも直接的にサポートしていません。CASTを使用して、CLOB値を文字データ型に変換するか、BLOB値をRAWデータ型に変換すると、データベースは暗黙的にLOB値を文字またはRAWデータに変換し、結果値を明示的にターゲットのデータ型にキャストします。結果値がターゲットの型より大きい場合、エラーが戻されます。
CAST ... MULTISETを使用してコレクション値を取得すると、CASTファンクションに渡される問合せ内のSELECT構文のリストのアイテムは、ターゲットのコレクション要素型に対応する属性型に変換されます。
注1: Datetime/Intervalには、DATE、TIMESTAMP、TIMESTAMP WITH TIMEZONE、INTERVAL DAY TO SECONDおよびINTERVAL YEAR TO MONTHが含まれます。
注2: UROWIDが索引構成表のROWIDの値を含んでいる場合、UROWIDをROWIDにキャストすることはできません。
名前付きコレクション型を別の名前付きコレクション型にキャストするには、両方のコレクションの要素が同じ型である必要があります。
副問合せの結果セットが複数行に評価される可能性がある場合は、MULTISETキーワードを指定する必要があります。副問合せの結果である行は、それらの行がキャストされたコレクション値の要素を形成します。MULTISETキーワードを省略すると、副問合せはスカラー副問合せとして処理されます。
次の例では、CASTファンクションをスカラー・データ型とともに使用します。
SELECT CAST('22-OCT-1997' AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual; SELECT product_id, CAST(ad_sourcetext AS VARCHAR2(30)) FROM print_media;
後に続くCASTの例は、サンプルの注文入力スキーマoeで使用されているcust_address_typに基づいています。
CREATE TYPE address_book_t AS TABLE OF cust_address_typ; / CREATE TYPE address_array_t AS VARRAY(3) OF cust_address_typ; / CREATE TABLE cust_address ( custno NUMBER, street_address VARCHAR2(40), postal_code VARCHAR2(10), city VARCHAR2(30), state_province VARCHAR2(10), country_id CHAR(2)); CREATE TABLE cust_short (custno NUMBER, name VARCHAR2(31)); CREATE TABLE states (state_id NUMBER, addresses address_array_t);
次の例では、副問合せをキャストします。
SELECT s.custno, s.name, CAST(MULTISET(SELECT ca.street_address, ca.postal_code, ca.city, ca.state_province, ca.country_id FROM cust_address ca WHERE s.custno = ca.custno) AS address_book_t) FROM cust_short s;
CASTでは、VARRAY型の列をネストした表に変換します。
SELECT CAST(s.addresses AS address_book_t) FROM states s WHERE s.state_id = 111;
次の例では、この後に示す例で使用するオブジェクトを作成します。
CREATE TABLE projects (employee_id NUMBER, project_name VARCHAR2(10)); CREATE TABLE emps_short (employee_id NUMBER, last_name VARCHAR2(10)); CREATE TYPE project_table_typ AS TABLE OF VARCHAR2(10); /
次のMULTISET式の例は、前述のオブジェクトを使用します。
SELECT e.last_name, CAST(MULTISET(SELECT p.project_name FROM projects p WHERE p.employee_id = e.employee_id ORDER BY p.project_name) AS project_table_typ) FROM emps_short e;
CEILは、n以上の最も小さい整数を戻します。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。また、引数の数値データ型と同じデータ型を戻します。
次の例では、指定した注文の合計以上である最小の整数を戻します。
SELECT order_total, CEIL(order_total) FROM orders WHERE order_id = 2434; ORDER_TOTAL CEIL(ORDER_TOTAL) ----------- ----------------- 268651.8 268652
CHARTOROWIDは、CHAR、VARCHAR2、NCHARまたはNVARCHAR2データ型の値をROWIDデータ型に変換します。
このファンクションは、CLOBデータを直接的にサポートしていません。ただし、暗黙的なデータ変換を使用してCLOBを引数として渡すことはできます。
次の例では、文字表記のROWIDをROWIDに変換します。（実際のROWIDは、各データベース・インスタンスによって異なります。）
SELECT last_name FROM employees WHERE ROWID = CHARTOROWID('AAAFd1AAFAAAABSAA/'); LAST_NAME ------------------------- Greene
CHRは、データベース・キャラクタ・セットまたは各国語キャラクタ・セット（USING NCHAR_CSを指定している場合）の中のnに等しい2進数を持つ文字を、VARCHAR2値として戻します。
シングルバイト・キャラクタ・セットの場合、Oracleデータベースは、n > 256に対してn mod 256に等しい2進数を戻します。マルチバイト・キャラクタ・セットの場合、nは1つのコードポイント全体として解決される必要があります。無効なコードポイントは検証されないため、無効なコードポイントを指定した場合の結果は、予測不能です。
このファンクションは、引数として、NUMBER値、または暗黙的にNUMBER型に変換可能な任意の値を取り、文字を戻します。
次の例は、データベース・キャラクタ・セットがWE8ISO8859P1と定義されているASCIIベースのマシンで実行されています。
SELECT CHR(67)||CHR(65)||CHR(84) "Dog" FROM DUAL; Dog --- CAT
キャラクタ・セットがWE8EBCDIC1047のEBCDICベースのマシンでも同じ結果を戻すには、前述の例を次のように修正する必要があります。
SELECT CHR(195)||CHR(193)||CHR(227) "Dog" FROM DUAL; Dog --- CAT
マルチバイト・キャラクタ・セットの場合、このように連結しても異なる結果となります。たとえば、a1a2（a1は1つ目のバイト、a2は2つ目のバイト）という16進数の値を持つマルチバイト文字の場合、nに対して「a1a2」に等しい2進数または41378を指定する必要があります。つまり、次のように指定する必要があります。
SELECT CHR(41378) FROM DUAL;
次の例のように、a2に等しい2進数と連結されたa1に等しい2進数を指定することはできません。
SELECT CHR(161)||CHR(162) FROM DUAL;
ただし、次の例のように、マルチバイト文字を連結するマルチバイトのコードポイント全体を連結することは可能です。この例では、a1a2とa1a3の16進数を持つマルチバイト文字を連結しています。
SELECT CHR(41378)||CHR(41379) FROM DUAL;
次の例では、各国語キャラクタ・セットがUTF16であると仮定します。
SELECT CHR (196 USING NCHAR_CS) FROM DUAL; CH -- Ä
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIを使用して作成したモデルのクラスタリングで使用するためのものです。このファンクションは、mining_attribute_clauseで指定した一連の予測子の内、最も確率の高い予測クラスタのクラスタ識別子を戻します。戻り値は、OracleのNUMBERです。
mining_attribute_clauseは、PREDICTIONファンクションと同様に動作します。詳細は、「mining_attribute_clause」を参照してください。
|
参照:
|
次の例では、指定したデータセットの顧客がグループ化されたクラスタを示します。
この例と前提条件のデータ・マイニング操作（dm_sh_clus_sampleモデルおよびdm_sh_sample_apply_preparedビューの作成など）は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmkmdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
SELECT CLUSTER_ID(km_sh_clus_sample USING *) AS clus, COUNT(*) AS cnt FROM km_sh_sample_apply_prepared GROUP BY CLUSTER_ID(km_sh_clus_sample USING *) ORDER BY cnt DESC; CLUS CNT ---------- ---------- 2 580 10 199 6 185 8 115 12 98 16 82 19 81 15 68 18 65 14 27 10 rows selected.
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIを使用して作成したモデルのクラスタリングで使用するためのものです。このファンクションは、指定したモデルに関連付けられたクラスタにある入力行のメンバーシップの確信度のメジャーを戻します。
cluster_idには、モデル内のクラスタの識別子を指定します。このファンクションでは、指定したクラスタの確率を戻します。この句を指定しない場合、最適な予測クラスタに関連付けられた確率が戻ります。cluster_idとCLUSTER_IDファンクションの組合せのない形式を使用して、クラスタIDと確率の最適な予測の組を取得できます。
mining_attribute_clauseは、PREDICTIONファンクションと同様に動作します。詳細は、「mining_attribute_clause」を参照してください。
参照:
次の例では、可能性に基づいて、クラスタ2で最も代表的な顧客を10人決定します。
この例と前提条件のデータ・マイニング操作（dm_sh_clus_sampleモデルおよびdm_sh_sample_apply_preparedビューの作成など）は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmkmdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
SELECT * FROM (SELECT cust_id, CLUSTER_PROBABILITY(km_sh_clus_sample, 2 USING *) prob FROM km_sh_sample_apply_prepared ORDER BY prob DESC) WHERE ROWNUM < 11; CUST_ID PROB ---------- ------ 100052 .9993 100962 .9993 101208 .9993 100281 .9993 100012 .9993 101009 .9992 100173 .9992 101176 .9991 100672 .9991 101420 .9991 10 rows selected.
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIを使用して作成したモデルのクラスタリングで使用するためのものです。このファンクションは、指定した行が属している可能性のあるクラスタを含むオブジェクトのVARRAYを戻します。VARRAYの各オブジェクトは、クラスタIDとクラスタ確率を含むスカラー値の組です。オブジェクト・フィールドには、CLUSTER_IDとPROBABILITYの名前が付き、両方ともOracleのNUMBERになります。
topN引数には、正の整数を指定します。指定すると、予測クラスタの集合は、上位Nの確率値のいずれか持つ予測クラスタに制限されます。topNを指定しないか、NULLに設定すると、すべてのクラスタはコレクションに戻されます。複数のクラスタがN番目の値に一致しても、N個の値のみが戻されます。
cutoff引数には、正の整数を指定し、戻されるクラスタを指定したカットオフ以上の確率を持つクラスタに制限します。NULLをtopNに指定し、必要なカットオフ値をcutoffに指定すると、cutoffのみをフィルタ処理できます。
topNとcutoffをともに指定すると、戻されるクラスタを上位Nの中で、しきい値を超える確率を持つクラスタに制限できます。
mining_attribute_clauseは、PREDICTIONファンクションと同様に動作します。詳細は、「mining_attribute_clause」を参照してください。
次の例では、20%を超える可能性で顧客101362が属する各クラスタで、最も関連性の高い属性（確信度が55%を超える）を示します。
この例と前提条件のデータ・マイニング操作（dm_sh_clus_sampleモデル、ビューおよび型の作成など）は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmkmdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
WITH clus_tab AS ( SELECT id, A.attribute_name aname, A.conditional_operator op, NVL(A.attribute_str_value, ROUND(DECODE(A.attribute_name, N.col, A.attribute_num_value * N.scale + N.shift, A.attribute_num_value),4)) val, A.attribute_support support, A.attribute_confidence confidence FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM('km_sh_clus_sample')) T, TABLE(T.rule.antecedent) A, km_sh_sample_norm N WHERE A.attribute_name = N.col (+) AND A.attribute_confidence > 0.55 ), clust AS ( SELECT id, CAST(COLLECT(Cattr(aname, op, TO_CHAR(val), support, confidence)) AS Cattrs) cl_attrs FROM clus_tab GROUP BY id ), custclus AS ( SELECT T.cust_id, S.cluster_id, S.probability FROM (SELECT cust_id, CLUSTER_SET(km_sh_clus_sample, NULL, 0.2 USING *) pset FROM km_sh_sample_apply_prepared WHERE cust_id = 101362) T, TABLE(T.pset) S ) SELECT A.probability prob, A.cluster_id cl_id, B.attr, B.op, B.val, B.supp, B.conf FROM custclus A, (SELECT T.id, C.* FROM clust T, TABLE(T.cl_attrs) C) B WHERE A.cluster_id = B.id ORDER BY prob DESC, cl_id ASC, conf DESC, attr ASC, val ASC; PROB CL_ID ATTR OP VAL SUPP CONF ------- ---------- --------------- --- --------------- ---------- ------- .7873 8 HOUSEHOLD_SIZE IN 9+ 126 .7500 .7873 8 CUST_MARITAL_ST IN Divorc. 118 .6000 ATUS .7873 8 CUST_MARITAL_ST IN NeverM 118 .6000 ATUS .7873 8 CUST_MARITAL_ST IN Separ. 118 .6000 ATUS .7873 8 CUST_MARITAL_ST IN Widowed 118 .6000 ATUS .2016 6 AGE >= 17 152 .6667 .2016 6 AGE <= 31.6 152 .6667 .2016 6 CUST_MARITAL_ST IN NeverM 168 .6667 ATUS 8 rows selected.
COALESCEは、式のリストの最初のNULLでないexprを戻します。1つ以上のexprが、リテラルNULL以外である必要があります。すべてのexprがNULLと評価された場合、このファンクションはNULLを戻します。
Oracleデータベースでは、短絡評価を使用します。つまり、データベースは、NULLかどうかを判断する前にexpr値のすべてを評価するのではなく、各expr値を評価して、NULLかどうかを判断します。
すべてのexprが数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型である場合、Oracleデータベースは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。
このファンクションはNVLファンクションを一般化したファンクションです。
COALESCEは、CASE式の変形として使用できます。次に例を示します。
COALESCE (expr1, expr2)
これは、次のCASE式と同じです。
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
次の例も同様です。
COALESCE (expr1, expr2, ..., exprn), for n>=3
これは、次のCASE式と同じです。
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE COALESCE (expr2, ..., exprn) END
次の例では、サンプル表oe.product_informationを使用して、製品のクリアランス・セールを企画します。製品の表示価格から10%値引きします。表示価格がない場合は、最小価格はセール価格となります。最小価格がない場合、セール価格は「5」となります。
SELECT product_id, list_price, min_price, COALESCE(0.9*list_price, min_price, 5) "Sale" FROM product_information WHERE supplier_id = 102050; PRODUCT_ID LIST_PRICE MIN_PRICE Sale ---------- ---------- ---------- ---------- 2382 850 731 765 3355 5 1770 73 73 2378 305 247 274.5 1769 48 43.2
COLLECTは、任意の型の列を引数に取り、選択された行から、入力された型のネストした表を作成します。このファンクションの結果を取得するには、このファンクションをCASTファンクション内で使用する必要があります。
column自体がコレクションである場合、COLLECTの出力はコレクションのネストした表になります。
次の例では、oe.customersサンプル表の電話番号のVARRAY列からネストした表を作成します。
CREATE TYPE phone_book_t AS TABLE OF phone_list_typ; / SELECT CAST(COLLECT(phone_numbers) AS phone_book_t) FROM customers;
COMPOSEは、引数として任意のデータ型の文字列または文字列に変換する式を取り、入力されたものと同じキャラクタ・セットで、完全に正規化された形式のUnicode文字列を戻します。charのデータ型は、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBです。たとえば、oウムラウト・コードポイントは、ウムラウト・コードポイントによって修飾されたoコードポイントとして戻されます。
暗黙的な変換を使用して、CLOBおよびNCLOBの値がサポートされます。charが文字のLOB値の場合、COMPOSE操作の前にVARCHAR値に変換されます。特定の開発環境で、LOB値のサイズがVARCHARのサポートする長さを超える場合、この操作は失敗します。
次の例は、oウムラウトのコードポイントを戻します。
SELECT COMPOSE ( 'o' || UNISTR('\0308') ) FROM DUAL; CO -- ö
CONCATは、char2に連結されているchar1を戻します。char1およびchar2は、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。char1と同じキャラクタ・セットの文字列が戻されます。そのデータ型は、引数のデータ型によって決まります。
2つの異なるデータ型を連結すると、可逆式変換となるデータ型が戻されます。したがって、引数の1つがLOBの場合、戻り値はLOBとなります。引数の1つが各国語データ型の場合は、戻り値は各国語データ型となります。たとえば、次のようになります。
CONCAT(CLOB, NCLOB)はNCLOBを戻します。
CONCAT(NCLOB, NCHAR)はNCLOBを戻します。
CONCAT(NCLOB, CHAR)はNCLOBを戻します。
CONCAT(NCHAR, CLOB)はNCLOBを戻します。
このファンクションは、連結演算子（||）と同等です。
次の例では、ネストを使用して3つの文字列を連結します。
SELECT CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job" FROM employees WHERE employee_id = 152; Job ------------------------------------------------------ Hall's job category is SA_REP
CONVERTは、文字列を、あるキャラクタ・セットから別のキャラクタ・セットに変換します。戻り値のデータ型はVARCHAR2です。
charは変換する値です。charは、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。
dest_char_setは、charが変換されるキャラクタ・セットの名前です。
source_char_setは、charをデータベースに格納しているキャラクタ・セットの名前です。デフォルト値はデータベース・キャラクタ・セットです。
変換先キャラクタ・セットと変換元キャラクタ・セットの引数として、リテラルまたはキャラクタ・セットの名前を含んでいる列を指定できます。
完全に文字を変換するには、変換先キャラクタ・セットが変換元キャラクタ・セットで定義されているすべての文字を表現できる必要があります。文字が変換先キャラクタ・セットに存在しないと、置換文字が使用されます。置換文字は、キャラクタ・セット定義の一部として定義できます。
次の例では、Latin-1文字列をASCIIに変換するキャラクタ・セットの変換を示します。これは、同じ文字列をWE8ISO8859P1データベースからUS7ASCIIデータベースへインポートした場合と同じ結果が得られます。
SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL; CONVERT('ÄÊÍÕØABCDE' --------------------- A E I ? ? A B C D E ?
一般的なキャラクタ・セットを次に示します。
CORRは、数値の組の集合に対する相関係数を戻します。これは、集計ファンクションまたは分析ファンクションとして使用できます。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。
Oracleデータベースは、expr1またはexpr2がNULLである組を排除した後、このファンクションを(expr1, expr2)の集合に適用します。その後、Oracleは次の計算を行います。
COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
ファンクションは、NUMBER型の値を戻します。ファンクションが空の集合に適用されると、NULLを戻します。
次の例では、oe.product_informationサンプル表の重さクラスごとの製品の表示価格と最小価格の相関係数を計算します。
SELECT weight_class, CORR(list_price, min_price) FROM product_information GROUP BY weight_class; WEIGHT_CLASS CORR(LIST_PRICE,MIN_PRICE) ------------ -------------------------- 1 .99914795 2 .999022941 3 .998484472 4 .999359909 5 .999536087
次の例では、会社での勤務年数と給与の相関を従業の役職別に示します。結果セットでは、指定した業務の従業員ごとに同じ相関を示します。
SELECT employee_id, job_id, TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH ) "Yrs-Mns", salary, CORR(SYSDATE-hire_date, salary) OVER(PARTITION BY job_id) AS "Correlation" FROM employees WHERE department_id in (50, 80) ORDER BY job_id, employee_id; EMPLOYEE_ID JOB_ID Yrs-Mns SALARY Correlation ----------- ---------- ------- ---------- ----------- 145 SA_MAN +08-07 14000 .912385598 146 SA_MAN +08-04 13500 .912385598 147 SA_MAN +08-02 12000 .912385598 148 SA_MAN +05-07 11000 .912385598 149 SA_MAN +05-03 10500 .912385598 150 SA_REP +08-03 10000 .80436755 151 SA_REP +08-02 9500 .80436755 152 SA_REP +07-09 9000 .80436755 153 SA_REP +07-01 8000 .80436755 154 SA_REP +06-05 7500 .80436755 155 SA_REP +05-06 7000 .80436755 ...
CORR_*ファンクションを次に示します。
CORRファンクション（「CORR」を参照）は、ピアソンの相関係数を計算し、入力として数式を必要とします。CORR_*ファンクションは、ノンパラメトリックまたは順位相関をサポートします。これらのファンクションを使用すると、式間の相関を順序尺度化して求めることができます（値の順序付けが可能な場合）。相関係数は-1〜1の範囲の値となります。1は完全な正相関、-1は完全な逆相関（一方の変数が減少すると他方の変数が増加する）、および0（ゼロ）に近い値は無相関を表します。
これらのファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。Oracleデータベースは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、計算を実行してNUMBERを戻します。
expr1およびexpr2は、分析対象の2つの変数です。3つ目の引数はVARCHAR2型の戻り値です。3つ目の引数を指定しない場合、デフォルトでCOEFFICIENTが戻り値になります。戻り値の意味を次の表に示します。
| 戻り値 | 意味 |
|---|---|
|
|
相関の係数 |
|
|
相関の正の片側有意 |
|
|
|
|
|
相関の負の片側有意 |
|
|
相関の両側有意 |
CORR_Sは、スピアマンの順位相関係数（ロー）を計算します。入力式は、観測値の組(xi, yi)の集合である必要があります。このファンクションは、最初に各値を順位に置き換えます。各xiの値は、標本内の他のすべてのxi中での順位に置き換えられ、各yiの値は、他のすべてのyi中での順位に置き換えられます。したがって、各xiおよび各yiは1〜nの値となります。ここでnは、値の組の合計数です。同順位の場合は、値がわずかに異なっていた場合に付けられる順位の平均が割り当てられます。その後このファンクションは、順位の線形相関係数を計算します。
次の例では、スピアマンのロー相関係数を使用して、salaryとcommission_pct、salaryとemployee_idの2つの異なる比較ごとに相関係数を導出します。
SELECT COUNT(*) count, CORR_S(salary, commission_pct) commission, CORR_S(salary, employee_id) empid FROM employees; COUNT COMMISSION EMPID ---------- ---------- ---------- 107 .735837022 -.04482358
CORR_Kは、ケンドールの順位相関係数（タウb）を計算します。CORR_Sと同様に、入力式は観測値の組(xi, yi)の集合です。係数を計算するために、このファンクションは一致した組と一致しない組の数をカウントします。xとyの値がいずれも大きい観測値の組は一致しています。xの値が大きくyの値が小さい観測値の組は一致していません。
タウbでの有意性は、タウbによって示される相関が偶然の結果である確率です（0〜1の値）。この値が小さい場合、タウbが正の値であれば有意な相関が存在します（タウbが負の値の場合は逆相関）。
次の例では、ケンドールのタウb相関係数を使用して、従業員の給与と歩合の割合（パーセント）間に相関があるかどうかを判断します。
SELECT CORR_K(salary, commission_pct, 'COEFFICIENT') coefficient, CORR_K(salary, commission_pct, 'TWO_SIDED_SIG') two_sided_p_value FROM hr.employees; COEFFICIENT TWO_SIDED_P_VALUE ----------- ----------------- .603079768 3.4702E-07
COSは、n（ラジアンで表された角度）のコサインを戻します。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。引数がBINARY_FLOATの場合、このファンクションはBINARY_DOUBLEを戻します。それ以外の場合、引数と同じ数値データ型を戻します。
次の例では、180度のコサインを戻します。
SELECT COS(180 * 3.14159265359/180) "Cosine of 180 degrees" FROM DUAL; Cosine of 180 degrees --------------------- -1
COSHは、nの双曲線コサインを戻します。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。引数がBINARY_FLOATの場合、このファンクションはBINARY_DOUBLEを戻します。それ以外の場合、引数と同じ数値データ型を戻します。
次の例では、0の双曲線コサインを戻します。
SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL; Hyperbolic cosine of 0 ---------------------- 1
COUNTは、問合せによって戻された行の数を戻します。これは、集計ファンクションまたは分析ファンクションとして使用できます。
DISTINCTを指定する場合は、analytic_clauseのquery_partition_clauseのみ指定できます。order_by_clauseおよびwindowing_clauseは指定できません。
exprを指定すると、COUNTはexprがNULLでない行数を戻します。exprのすべての行を数えるか、または異なる値のみを数えることができます。
アスタリスク（*）を指定すると、このファンクションは重複値およびNULL値を含むすべての行を戻します。COUNTはNULLを戻しません。
次の例では、COUNTを集計ファンクションとして使用します。
SELECT COUNT(*) "Total" FROM employees; Total ---------- 107 SELECT COUNT(*) "Allstars" FROM employees WHERE commission_pct > 0; Allstars --------- 35 SELECT COUNT(commission_pct) "Count" FROM employees; Count ---------- 35 SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees; Managers ---------- 18
次の例では、employees表の各従業員について、その従業員の給与より50ドル少ない金額から150ドル多い金額の範囲の給与を得ている従業員の数を計算します。
SELECT last_name, salary, COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS mov_count FROM employees; LAST_NAME SALARY MOV_COUNT ------------------------- ---------- ---------- Olson 2100 3 Markle 2200 2 Philtanker 2200 2 Landry 2400 8 Gee 2400 8 Colmenares 2500 10 Patel 2500 10 . . .
COVAR_POPは、数値の組の集合に対する母集団共分散を戻します。これは、集計ファンクションまたは分析ファンクションとして使用できます。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。
Oracleデータベースは、expr1またはexpr2がNULLであるすべての組を排除した後、このファンクションを(expr1, expr2)の組の集合に適用します。その後、Oracleは次の計算を行います。
(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n
ここで、nは(expr1, expr2)の組の数です（ただし、expr1およびexpr2の両方がNULLではない場合です）。
ファンクションは、NUMBER型の値を戻します。ファンクションが空の集合に適用されると、NULLを戻します。
次の例では、サンプル表hr.employeesを使用して、勤務時間（SYSDATE - hire_date）と給与の母集団共分散と標本共分散を計算します。
SELECT job_id, COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop, COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp FROM employees WHERE department_id in (50, 80) GROUP BY job_id; JOB_ID COVAR_POP COVAR_SAMP ---------- ----------- ----------- ST_MAN 436092.000 545115.000 SH_CLERK 782717.500 823913.158 SA_MAN 660700.000 825875.000 SA_REP 579988.466 600702.340 ST_CLERK 176577.250 185870.789
次の例では、デモ・スキーマoeの製品の表示価格および最小価格の累積標本共分散を計算します。
SELECT product_id, supplier_id, COVAR_POP(list_price, min_price) OVER (ORDER BY product_id, supplier_id) AS CUM_COVP, COVAR_SAMP(list_price, min_price) OVER (ORDER BY product_id, supplier_id) AS CUM_COVS FROM product_information p WHERE category_id = 29 ORDER BY product_id, supplier_id; PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS ---------- ----------- ---------- ---------- 1774 103088 0 1775 103087 1473.25 2946.5 1794 103096 1702.77778 2554.16667 1825 103093 1926.25 2568.33333 2004 103086 1591.4 1989.25 2005 103086 1512.5 1815 2416 103088 1475.97959 1721.97619 . . .
COVAR_SAMPは、数値の組の集合の標本共分散を戻します。これは、集計ファンクションまたは分析ファンクションとして使用できます。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。
Oracleデータベースは、expr1またはexpr2がNULLであるすべての組を排除した後、このファンクションを(expr1, expr2)の組の集合に適用します。その後、Oracleは次の計算を行います。
(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1)
ここで、nは(expr1, expr2)の組の数です（ただし、expr1およびexpr2の両方がNULLではない場合です）。
ファンクションは、NUMBER型の値を戻します。ファンクションが空の集合に適用されると、NULLを戻します。
「COVAR_POP」の集計の例を参照してください。
「COVAR_POP」の分析の例を参照してください。
CUME_DISTは、値のグループにある値の累積分布値を計算します。CUME_DISTが戻す値の範囲は、0より大きく1以下です。連結値は、常に同じ累積分布値に対して評価を行います。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。Oracleデータベースは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、計算を実行してNUMBERを戻します。
CUME_DISTは、ファンクションの引数および対応するソート指定によって識別される不確定な行rに対して、集計グループ内の行の中での行rの関連する位置を計算します。Oracleは、不確定な行rを集計される行のグループに挿入するように計算します。このファンクションの引数は、各集計グループ内の1つの不確定行を識別します。このため、すべての引数は、集計グループ内で定数式と評価される必要があります。定数引数式および集計のORDER BY句の式の位置は、一致します。このため、引数の数は同じであり、その型は互換性がある必要があります。
CUME_DISTは、値のグループにある特定の値の相対位置を計算します。行rについて、昇順で順序付けられているとします。rのCUME_DISTは、rの値以下の値の行数を、評価される行の数（問合せ結果セットまたはパーティション）で割った数です。
次の例では、サンプル表oe.employeesの従業員の中から、給与が15,500ドルであり、歩合が5%の不確定な従業員の累積分布を計算します。
SELECT CUME_DIST(15500, .05) WITHIN GROUP (ORDER BY salary, commission_pct) "Cume-Dist of 15500" FROM employees; Cume-Dist of 15500 ------------------ .972222222
次の例では、購買部門の各従業員の給与のパーセンタイルを計算します。たとえば、事務員の40%が、Himuroの給与以下の給与を得ていることがわかります。
SELECT job_id, last_name, salary, CUME_DIST() OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist FROM employees WHERE job_id LIKE 'PU%'; JOB_ID LAST_NAME SALARY CUME_DIST ---------- ------------------------- ---------- ---------- PU_CLERK Colmenares 2500 .2 PU_CLERK Himuro 2600 .4 PU_CLERK Tobias 2800 .6 PU_CLERK Baida 2900 .8 PU_CLERK Khoo 3100 1 PU_MAN Raphaely 11000 1
CURRENT_DATEは、セッション・タイムゾーンの現在の日付をDATEデータ型のグレゴリオ暦の値で戻します。
次の例では、CURRENT_DATEがセッション・タイムゾーンによって異なることを示します。
ALTER SESSION SET TIME_ZONE = '-5:0'; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE CURRENT_DATE --------------- -------------------- -05:00 29-MAY-2000 13:14:03 ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE CURRENT_DATE --------------- -------------------- -08:00 29-MAY-2000 10:14:33
CURRENT_TIMESTAMPは、セッション・タイムゾーンの現在の日付および時刻をTIMESTAMP WITH TIME ZONEデータ型の値で戻します。タイムゾーン・オフセットは、SQLセッションの現在のローカル時刻を反映します。精度の指定を省略した場合のデフォルトは6です。このファンクションとLOCALTIMESTAMPとの違いは、CURRENT_TIMESTAMPは、TIMESTAMP WITH TIME ZONEの値を戻し、LOCALTIMESTAMPはTIMESTAMPの値を戻す点です。
オプションの引数では、precisionは、戻される時刻の値の小数秒の精度を指定します。
次の例では、CURRENT_TIMESTAMPがセッション・タイムゾーンによって異なることを示します。
ALTER SESSION SET TIME_ZONE = '-5:0'; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE CURRENT_TIMESTAMP --------------- --------------------------------------------------- -05:00 04-APR-00 01.17.56.917550 PM -05:00 ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE CURRENT_TIMESTAMP --------------- ---------------------------------------------------- -08:00 04-APR-00 10.18.21.366065 AM -08:00
CURRENT_TIMESTAMPで書式マスクを使用する場合は、ファンクションが戻す値と書式マスクを一致させてください。たとえば、次の表の場合を考えます。
CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE);
ファンクションが戻す型のTIME ZONEの部分がマスクに含まれていないため、次の文は正常に実行されません。
INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
次の文では、CURRENT_TIMESTAMPの戻り値の型と一致する正しい書式マスクが使用されています。
INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ (CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));
CVファンクションは、SELECT文のmodel_clauseでのみ、かつモデル・ルールの右側でのみ使用できます。戻り値は、ルールの左側から右側に送られたディメンション列の現在の値です。このファンクションをmodel_clauseで使用するとディメンション列に対する相対索引を作成できます。戻り型は、ディメンション列のデータ型です。引数を指定しない場合、セル参照内のファンクションの相対位置に対応付けられたディメンション列がデフォルトで使用されます。
CVファンクションは、セル参照外でも使用できます。その場合は、dimension_columnが必要です。
次の例では、ディメンション列（マウス・パッドまたはスタンダード・マウス）の1999年および2000年の現在の値が表す製品の売上の合計を、その製品の2001年の売上に割り当てます。
SELECT country, prod, year, s FROM sales_view_ref MODEL PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale s) IGNORE NAV UNIQUE DIMENSION RULES UPSERT SEQUENTIAL ORDER ( s[FOR prod IN ('Mouse Pad', 'Standard Mouse'), 2001] = s[CV( ), 1999] + s[CV( ), 2000] ) ORDER BY country, prod, year; COUNTRY PROD YEAR S ---------- ----------------------------------- -------- --------- France Mouse Pad 1998 2509.42 France Mouse Pad 1999 3678.69 France Mouse Pad 2000 3000.72 France Mouse Pad 2001 6679.41 France Standard Mouse 1998 2390.83 France Standard Mouse 1999 2280.45 France Standard Mouse 2000 1274.31 France Standard Mouse 2001 3554.76 Germany Mouse Pad 1998 5827.87 Germany Mouse Pad 1999 8346.44 Germany Mouse Pad 2000 7375.46 Germany Mouse Pad 2001 15721.9 Germany Standard Mouse 1998 7116.11 Germany Standard Mouse 1999 6263.14 Germany Standard Mouse 2000 2637.31 Germany Standard Mouse 2001 8900.45 16 rows selected.
この例では、ビューsales_view_refが必要です。このビューを作成する方法については、「MODEL句の例:」を参照してください。
DBTIMEZONEファンクションは、データベース・タイムゾーンの値を戻します。戻り型は、タイムゾーン・オフセット（'[+|-]TZH:TZM'という書式の文字列型）またはタイムゾーン地域名です。これは、最近のCREATE DATABASEまたはALTER DATABASE文でユーザーが指定したデータベース・タイムゾーンの値によって異なります。
次の例では、データベース・タイムゾーンがUTCタイムゾーンに設定されていると想定します。
SELECT DBTIMEZONE FROM DUAL; DBTIME ------ +00:00
DECODEファンクションは、exprと各searchの値を1つずつ比較します。exprがsearchと等しい場合、Oracleデータベースは対応するresultを戻します。一致する値が見つからない場合は、defaultを戻します。defaultが省略されている場合は、NULLを戻します。
引数は、任意の数値型（NUMBER、BINARY_FLOAT、BINARY_DOUBLE）または文字列型です。
exprおよびsearchが文字データである場合、Oracleは、非空白埋め比較セマンティクスを使用してそれらを比較します。expr、searchおよびresultのデータ型は、CHAR、VARCHAR2、NCHARまたはNVARCHAR2です。戻される文字列は、VARCHAR2データ型で、最初のresultパラメータと同じキャラクタ・セットの文字列です。
search-resultの組が数値である場合、Oracleはすべてのsearch-resultの式と最初のexprを比較して、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。
search、resultおよびdefaultの値は、式から導出できます。Oracleデータベースでは、短絡評価を使用します。つまり、データベースは、search値のいずれかとexprを比較する前にすべてのsearch値を評価するのではなく、各search値とexprを比較する前にのみ、各search値を評価します。その結果、exprと等しいsearchが見つかると、Oracleはその後のsearchを評価しません。
比較する前に、Oracleはexprと各search値を、最初のsearch値のデータ型に自動的に変換します。Oracleは、戻り値を最初のresultと同じデータ型に自動的に変換します。最初のresultのデータ型がCHARの場合、または最初のresultがNULLの場合、Oracleは戻り値をVARCHAR2データ型の値に変換します。
DECODEファンクションでは、Oracleは2つのNULLを同等とみなします。exprがNULLの場合、Oracleは最初のsearch値のresultもNULLとして戻します。
DECODEファンクションのコンポーネントの最大数は、expr、search、result、defaultを含めて255です。
|
参照:
比較セマンティクスの詳細は、「データ型の比較規則」を参照してください。データ型変換については、「データ変換」を参照してください。浮動小数点の比較セマンティクスの詳細は、「浮動小数点数」を参照してください。暗黙的な変換のデメリットについては、「暗黙的なデータ変換と明示的なデータ変換」を参照してください。 |
この例では、warehouse_idの値をデコードします。warehouse_idが1の場合「Southlake」を、warehouse_idが2の場合は「San Francisco」を戻します。warehouse_idが1、2、3、4のいずれでもない場合、ファンクションは「Non domestic」を戻します。
SELECT product_id, DECODE (warehouse_id, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non domestic') "Location of inventory" FROM inventories WHERE product_id < 1775;
DECOMPOSEは、Unicodeキャラクタに対してのみ有効です。DECOMPOSEは、任意のデータ型の文字列を引数として取り、入力と同じキャラクタ・セットで分解されたUnicodeの文字列を戻します。たとえば、oウムラウト・コードポイントは、ウムラウト・コードポイントが続く「o」コードポイントとして戻されます。
stringは、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。
CANONICALを使用すると標準的な分解が行われ、（たとえば、COMPOSEファンクションを使用して）元の文字列への再構成が可能になります。これはデフォルトです。
COMPATIBILITYを使用すると互換モードで分解が行われます。このモードでは、再構成はできません。このモードは、半角および全角のカタカナ文字を分解する場合など、外的な書式またはスタイル情報なしに再構成を行うことが望ましくない場合に有効です。
暗黙的な変換を使用して、CLOBおよびNCLOBの値がサポートされます。charが文字のLOB値の場合、COMPOSE操作の前にVARCHAR値に変換されます。特定の開発環境で、LOB値のサイズがVARCHARのサポートする長さを超える場合、この操作は失敗します。
次の例では、文字列「Châteaux」をその要素のコードポイントに分解します。
SELECT DECOMPOSE ('Châteaux') FROM DUAL;
DECOMPOSE
---------
Cha^teaux
DELETEXMLは、ターゲットXMLのXPath式で一致する単一または複数のノードを削除します。
XMLType_instanceは、XMLTypeのインスタンスです。
XPath_stringは、削除するノードを1つ以上指定するXPath式です。先頭にスラッシュを付けて絶対XPath_stringを指定したり、先頭のスラッシュを省略して相対XPath_stringを指定できます。先頭のスラッシュを省略した場合、相対パスのコンテキストは、デフォルトでルート・ノードに設定されます。XPath_stringで指定したノードの子ノードも削除されます。
namespace_stringは、XPath_stringのネームスペース情報を提供します。このパラメータは、VARCHAR2型である必要があります。次の例では、「APPENDCHILDXML」の例で変更したウェアハウスの1つのwarehouse_specから/Ownerノードを削除します。
UPDATE warehouses SET warehouse_spec = DELETEXML(warehouse_spec, '/Warehouse/Building/Owner') WHERE warehouse_id = 2; SELECT warehouse_id, warehouse_spec FROM warehouses WHERE warehouse_id in (2,3); ID WAREHOUSE_SPEC ---------- ----------------------------------- 2 <?xml version="1.0"?> <Warehouse> <Building>Rented</Building> <Area>50000</Area> <Docks>1</Docks> <DockType>Side load</DockType> <WaterAccess>Y</WaterAccess> <RailAccess>N</RailAccess> <Parking>Lot</Parking> <VClearance>12 ft</VClearance> </Warehouse> 3 <?xml version="1.0"?> <Warehouse> <Building>Rented <Owner>Grandco</Owner> <Owner>ThirdOwner</Owner> <Owner>LesserCo</Owner> </Building> <Area>85700</Area> <DockType/> <WaterAccess>N</WaterAccess> <RailAccess>N</RailAccess> <Parking>Street</Parking> <VClearance>11.5 ft</VClearance> </Warehouse>
DENSE_RANKは、順序付けされた行のグループ内の行のランクを計算し、そのランクをNUMBERとして戻します。ランクは1から始まる連続した整数です。ランクの最大値は、問合せが戻す一意の数値です。ランクの値は、連続した整数です。ランク付け基準と同じ値を持つ行は、同じランクになります。このファンクションは、上位N番および下位N番のレポートに有効です。
このファンクションは、引数に任意の数値データ型を受け入れ、NUMBERを戻します。
DENSE_RANKは、ファンクションの引数によって識別される不確定な行の稠密ランクを、与えられたソート指定で計算します。ファンクションの引数は、各集計グループの単一行を識別するため、すべての引数は各集計グループ内で定数式に評価される必要があります。定数引数式および集計のorder_by_clauseの式の位置は、一致します。このため、引数の数は同じであり、型は互換性がある必要があります。
DENSE_RANKは、他の行について、問合せで戻される各行のランクを計算します。この計算は、order_by_clauseにあるvalue_exprsの値に基づいて行われます。
次の例では、サンプル表oe.employeesから、給与が15,500ドルであり、歩合が5%の仮想の従業員のランクを計算します。
SELECT DENSE_RANK(15500, .05) WITHIN GROUP (ORDER BY salary DESC, commission_pct) "Dense Rank" FROM employees; Dense Rank ------------------- 3
次の文は、人事部門または購買部門で働くすべての従業員の部門名、従業員名および給与を選択します。その後、この2つの部門それぞれについて、一意の各給与に対するランクを計算します。同じ給与は同じランクになります。この例と、「RANK」の例を比較してください。
SELECT d.department_name, e.last_name, e.salary, DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary) AS drank FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id IN ('30', '40'); DEPARTMENT_NAME LAST_NAME SALARY DRANK ----------------------- ------------------ ---------- ---------- Purchasing Colmenares 2500 1 Purchasing Himuro 2600 2 Purchasing Tobias 2800 3 Purchasing Baida 2900 4 Purchasing Khoo 3100 5 Purchasing Raphaely 11000 6 Human Resources Marvis 6500
DEPTHは、UNDER_PATHおよびEQUALS_PATH条件でのみ使用される補助ファンクションです。このファンクションは、同じ相関変数を持つUNDER_PATH条件によって指定されるパスのレベル数を戻します。
correlation_integerは任意のNUMBER整数です。文に複数の一次条件が含まれている場合に、この補助ファンクションをその一次条件と関連付けるために使用します。1未満の値は1として扱われます。
EQUALS_PATHおよびUNDER_PATH条件は、2つの補助ファンクションDEPTHおよびPATHを取ることができます。次に、その2つの補助ファンクションの使用方法を示します。この例では、XML Schemaであるwarehouses.xsd（「SQL文でのXMLの使用方法」で作成）が存在することを前提としています。
SELECT PATH(1), DEPTH(2) FROM RESOURCE_VIEW WHERE UNDER_PATH(res, '/sys/schemas/OE', 1)=1 AND UNDER_PATH(res, '/sys/schemas/OE', 2)=1; PATH(1) DEPTH(2) -------------------------------- -------- /www.oracle.com 1 /www.oracle.com/xwarehouses.xsd 2
DEREFは、引数exprのオブジェクト参照を戻します。この場合、exprはオブジェクトにREFを戻す必要があります。問合せでこのファンクションを使用しない場合、次の例で示すとおり、かわりにREFのオブジェクトIDを戻します。
サンプル・スキーマoeには、cust_address_typというオブジェクト型が含まれます。「REF制約の例」では、類似する型cust_address_typ_new、およびその型へのREFである1つの列を含む表を作成します。次の例では、その列に挿入を行う方法、およびDEREFを使用して列から情報を抽出する方法を示します。
INSERT INTO address_table VALUES ('1 First', 'G45 EU8', 'Paris', 'CA', 'US'); INSERT INTO customer_addresses SELECT 999, REF(a) FROM address_table a; SELECT address FROM customer_addresses; ADDRESS -------------------------------------------------------------------------------- 000022020876B2245DBE325C5FE03400400B40DCB176B2245DBE305C5FE03400400B40DCB1 SELECT DEREF(address) FROM customer_addresses; DEREF(ADDRESS)(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) -------------------------------------------------------------------------------- CUST_ADDRESS_TYP('1 First', 'G45 EU8', 'Paris', 'CA', 'US')
DUMPは、exprのデータ型コード、長さ（バイト単位）および内部表現を含むVARCHAR2値を戻します。戻される結果は、常にデータベース・キャラクタ・セットの文字です。各コードに対応するデータ型については、表2-2「精度および位取りの格納」を参照してください。
引数return_fmtには戻り値の書式として、次の値のいずれかを指定します。
デフォルトでは、戻り値にキャラクタ・セット情報が含まれません。exprのキャラクタ・セット名を取り出すには、前述の書式のいずれかの値に1000を加えます。たとえば、return_fmtに1008を指定すると、8進表記で結果が戻り、さらにexprのキャラクタ・セット名が得られます。
引数start_positionとlengthを組み合せて、内部表現の戻す部分を指定します。デフォルトでは、10進表記で全体の内部表現が戻されます。
exprがNULLの場合はNULLを戻します。
このファンクションは、CLOBデータを直接的にサポートしていません。ただし、暗黙的なデータ変換を使用してCLOBを引数として渡すことはできます。
次の例では、文字列式および列からダンプ情報を抽出する方法を示します。
SELECT DUMP('abc', 1016) FROM DUAL; DUMP('ABC',1016) ------------------------------------------ Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63 SELECT DUMP(last_name, 8, 3, 2) "OCTAL" FROM employees WHERE last_name = 'Hunold'; OCTAL ------------------------------------------------------------------- Typ=1 Len=6: 156,157 SELECT DUMP(last_name, 10, 3, 2) "ASCII" FROM employees WHERE last_name = 'Hunold'; ASCII -------------------------------------------------------------------- Typ=1 Len=6: 110,111
EMPTY_BLOBおよびEMPTY_CLOBは、LOB変数を初期化したり、INSERTまたはUPDATE文でLOB列または属性をEMPTYに初期化できる空のLOBロケータを戻します。EMPTYとは、LOBは初期化されていても、データが移入されていない状態をいいます。
このファンクションから戻されるロケータは、DBMS_LOBパッケージまたはOCIへのパラメータとして使用することはできません。
次の例では、サンプル表pm.print_mediaのad_photo列をEMPTYに初期化します。
UPDATE print_media SET ad_photo = EMPTY_BLOB();
EXISTSNODEは、指定されたパスを使用してXML文書をトラバースした後にノードが存在するかを判断します。XML文書、およびパスを指定するXPath文字列VARCHAR2を含むXMLTypeインスタンスを引数として指定します。オプションのnamespace_stringは、接頭辞のデフォルト・マッピングまたはネームスペース・マッピング（OracleデータベースがXPath式を評価する場合に使用）を指定するVARCHAR2値に解決される必要があります。
namespace_string引数のデフォルトは、ルート要素の名前空間になります。Xpath_stringのサブ要素を参照する場合は、namespace_stringを指定する必要があります。また、これらの引数の両方にwho接頭辞を指定する必要があります。
戻り値は、NUMBERです。
次の例では、サンプル表oe.warehousesのwarehouse_spec列のXMLパスに/Warehouse/Dockノードが存在するかを判断します。
SELECT warehouse_id, warehouse_name FROM warehouses WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Docks') = 1; WAREHOUSE_ID WAREHOUSE_NAME ------------ ----------------------------------- 1 Southlake, Texas 2 San Francisco 4 Seattle, Washington
EXPは、eをn乗した値（e = 2.71828183 ...）を戻します。このファンクションは、引数と同じ型の値を戻します。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。引数がBINARY_FLOATの場合、このファンクションはBINARY_DOUBLEを戻します。それ以外の場合、引数と同じ数値データ型を戻します。
次の例では、eを4乗した値を戻します。
SELECT EXP(4) "e to the 4th power" FROM DUAL; e to the 4th power ------------------ 54.59815
EXTRACTは、日時または期間値の式から、指定された日時フィールドの値を抽出し、戻します。TIMEZONE_REGIONを抽出する場合、戻り値は、適切なタイムゾーン名を含む文字列です。また、TIMEZONE_ABBR（略称）を抽出する場合、戻り値は、適切な略称を含む文字列です。その他の値を抽出する場合、戻り値はグレゴリオ暦です。タイムゾーン値を持つ日時から抽出する場合、戻り値はUTCです。有効なタイムゾーン名およびそれに対する略称を表示するには、V$TIMEZONE_NAMES動的パフォーマンス・ビューに問合せを実行してください。
このファンクションは、次に示す最初の例のように、非常に大規模な表の日時フィールド値を操作する場合に特に有効です。
日時フィールドと日時または期間値の式を組み合せると、あいまいな結果になる場合があります。この場合、Oracleデータベースは、UNKNOWNを戻します（詳細は、次の例を参照してください）。
抽出を実行するフィールドは、datetime_value_exprまたはinterval_value_exprフィールドである必要があります。たとえば、DATE値からは、YEAR、MONTHおよびDAYのみを抽出できます。同様に、TIMESTAMP WITH TIME ZONEデータ型からは、TIMEZONE_HOURおよびTIMEZONE_MINUTEのみを抽出できます。
|
参照:
|
次の例では、oe.orders表から、各月の注文数を戻します。
SELECT EXTRACT(month FROM order_date) "Month", COUNT(order_date) "No. of Orders" FROM orders GROUP BY EXTRACT(month FROM order_date) ORDER BY "No. of Orders" DESC; Month No. of Orders ---------- ------------- 11 15 7 14 6 14 3 11 5 10 9 9 2 9 8 7 10 6 1 5 12 4 4 1 12 rows selected.
次の例では、1998年を戻します。
SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL; EXTRACT(YEARFROMDATE'1998-03-07') --------------------------------- 1998
次の例では、サンプル表hr.employeesから、1998以降に雇用されたすべての従業員を選択します。
SELECT last_name, employee_id, hire_date FROM employees WHERE EXTRACT(YEAR FROM TO_DATE(hire_date, 'DD-MON-RR')) > 1998 ORDER BY hire_date; LAST_NAME EMPLOYEE_ID HIRE_DATE ------------------------- ----------- --------- Landry 127 14-JAN-99 Lorentz 107 07-FEB-99 Cabrio 187 07-FEB-99 . . .
次の例では、結果があいまいになるため、OracleはUNKNOWNを戻します。
SELECT EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '1999-01-01 10:00:00 -08:00') FROM DUAL; EXTRACT(TIMEZONE_REGIONFROMTIMESTAMP'1999-01-0110:00:00-08:00') ---------------------------------------------------------------- UNKNOWN
タイムゾーン数値オフセットが式に指定され、その数値オフセットが複数のタイムゾーン地域をマップする場合、結果があいまいになります。
EXTRACT（XML）は、EXISTSNODEファンクションに似ています。VARCHAR2のXPath文字列を適用し、XMLのフラグメントを含むXMLTypeインスタンスを戻します。先頭にスラッシュを付けて絶対XPath_stringを指定したり、先頭のスラッシュを省略して相対XPath_stringを指定できます。先頭のスラッシュを省略した場合、相対パスのコンテキストは、デフォルトでルート・ノードに設定されます。オプションのnamespace_stringは、接頭辞のデフォルト・マッピングまたはネームスペース・マッピング（OracleデータベースがXPath式を評価する場合に使用）を指定するVARCHAR2値に解決される必要があります。
次の例では、サンプル表oe.warehousesのwarehouse_spec列のXMLパスの/Warehouse/Dockノードの値を抽出します。
SELECT warehouse_name, EXTRACT(warehouse_spec, '/Warehouse/Docks') "Number of Docks" FROM warehouses WHERE warehouse_spec IS NOT NULL; WAREHOUSE_NAME Number of Docks -------------------- -------------------- Southlake, Texas <Docks>2</Docks> San Francisco <Docks>1</Docks> New Jersey <Docks/> Seattle, Washington <Docks>3</Docks>
この例と、XMLのフラグメントのスカラー値を戻す、「EXTRACTVALUE」の例を比較してみてください。
EXTRACTVALUEは、引数としてXMLTypeインスタンスとXPath式を取り、結果として得られるノードのスカラー値を戻します。結果はシングルノードであり、テキスト・ノード、属性または要素のいずれかである必要があります。結果が要素である場合、その要素はシングル・テキスト・ノードを子ノードとして持つ必要があり、このファンクションが戻す値は、その子ノードの値になります。先頭にスラッシュを付けて絶対XPath_stringを指定したり、先頭のスラッシュを省略して相対XPath_stringを指定できます。先頭のスラッシュを省略した場合、相対パスのコンテキストは、デフォルトでルート・ノードに設定されます。
指定されたXPathが複数の子ノードを持つノードを指す場合、または指されたノードが非テキスト・ノードの子を持つ場合は、エラーが戻されます。オプションのnamespace_stringは、接頭辞のデフォルト・マッピングまたはネームスペース・マッピング（OracleがXPath式を評価する場合に使用）を指定するVARCHAR2値に解決される必要があります。
XML Schemaに基づくドキュメントで戻り値の型が推測できる場合は、適切な型のスカラー値が戻ります。その他の場合は、VARCHAR2型の結果が戻ります。XML Schemaに基づかないドキュメントの場合、戻り値は常にVARCHAR2です。
次の例では、入力として、「EXTRACT（XML）」の例と同じ引数を取ります。このファンクションは、EXTRACTファンクションとは異なり、XMLのフラグメントを戻すのではなく、XMLのフラグメントのスカラー値を戻します。
SELECT warehouse_name, EXTRACTVALUE(e.warehouse_spec, '/Warehouse/Docks') "Docks" FROM warehouses e WHERE warehouse_spec IS NOT NULL; WAREHOUSE_NAME Docks -------------------- ------------ Southlake, Texas 2 San Francisco 1 New Jersey Seattle, Washington 3
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIを使用して作成した特徴抽出モデルで使用するためのものです。このファンクションは、最も高い係数値を持つ特徴の識別子をOracleのNUMBERで戻します。
mining_attribute_clauseは、PREDICTIONファンクションと同様に動作します。詳細は、「mining_attribute_clause」を参照してください。
次の例では、データセット内の特徴と、それに対応する顧客の件数を示します。
この例と前提条件のデータ・マイニング操作（nmf_sh_sampleモデルおよびnmf_sh_sample_apply_preparedビューの作成など）は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmnmdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
SELECT FEATURE_ID(nmf_sh_sample USING *) AS feat, COUNT(*) AS cnt FROM nmf_sh_sample_apply_prepared GROUP BY FEATURE_ID(nmf_sh_sample USING *) ORDER BY cnt DESC; FEAT CNT ---------- ---------- 7 1443 2 49 3 6 1 1 6 1
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIを使用して作成した特徴抽出モデルで使用するためのものです。このファンクションは、有効な特徴を含むオブジェクトのVARRAYを戻します。VARRAYの各オブジェクトは、特徴IDと特徴値を含むスカラー値の組です。オブジェクト・フィールドには、FEATURE_IDとVALUEの名前が付き、両方ともOracleのNUMBERになります。
オプションのtopN引数は、特徴セットを上位Nの値のいずれかを持つ特徴セットに制限する正の整数です。N番目の値に同順位がある場合でも、N個の値のみが戻されます。この引数を指定しない場合、このファンクションはすべての特徴を戻します。
オプションのcutoff引数は、戻される特徴を、指定したカットオフ以上の特徴値を持つ特徴のみに制限します。cutoffのみをフィルタ処理するには、NULLをtopNに指定し、必要なカットオフ値をcutoffに指定します。
mining_attribute_clauseは、PREDICTIONファンクションと同様に動作します。詳細は、「mining_attribute_clause」を参照してください。
次の例では、指定した顧客レコードに対応する上位の特徴を示し（一致する品質を基準）、各特徴に上位の属性を決定します（0.25を超える係数を基準）。
この例と前提条件のデータ・マイニング操作（モデル、ビューおよび型の作成など）は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmkmdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
WITH feat_tab AS ( SELECT F.feature_id fid, A.attribute_name attr, TO_CHAR(A.attribute_value) val, A.coefficient coeff FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_NMF('nmf_sh_sample')) F, TABLE(F.attribute_set) A WHERE A.coefficient > 0.25 ), feat AS ( SELECT fid, CAST(COLLECT(Featattr(attr, val, coeff)) AS Featattrs) f_attrs FROM feat_tab GROUP BY fid ), cust_10_features AS ( SELECT T.cust_id, S.feature_id, S.value FROM (SELECT cust_id, FEATURE_SET(nmf_sh_sample, 10 USING *) pset FROM nmf_sh_sample_apply_prepared WHERE cust_id = 100002) T, TABLE(T.pset) S ) SELECT A.value, A.feature_id fid, B.attr, B.val, B.coeff FROM cust_10_features A, (SELECT T.fid, F.* FROM feat T, TABLE(T.f_attrs) F) B WHERE A.feature_id = B.fid ORDER BY A.value DESC, A.feature_id ASC, coeff DESC, attr ASC, val ASC; VALUE FID ATTR VAL COEFF -------- ---- ------------------------- -------------------- ------- 6.8409 7 YRS_RESIDENCE 1.3879 6.8409 7 BOOKKEEPING_APPLICATION .4388 6.8409 7 CUST_GENDER M .2956 6.8409 7 COUNTRY_NAME United States of Ame .2848 rica 6.4975 3 YRS_RESIDENCE 1.2668 6.4975 3 BOOKKEEPING_APPLICATION .3465 6.4975 3 COUNTRY_NAME United States of Ame .2927 rica 6.4886 2 YRS_RESIDENCE 1.3285 6.4886 2 CUST_GENDER M .2819 6.4886 2 PRINTER_SUPPLIES .2704 6.3953 4 YRS_RESIDENCE 1.2931 5.9640 6 YRS_RESIDENCE 1.1585 5.9640 6 HOME_THEATER_PACKAGE .2576 5.2424 5 YRS_RESIDENCE 1.0067 2.4714 8 YRS_RESIDENCE .3297 2.3559 1 YRS_RESIDENCE .2768 2.3559 1 FLAT_PANEL_MONITOR .2593 17 rows selected.
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIを使用して作成した特徴抽出モデルで使用するためのものです。このファンクションは、指定した特徴の値を戻します。feature_id引数を指定しない場合、このファンクションは最も高い特徴値を戻します。この形式とFEATURE_IDファンクションを組み合せて使用すると、特徴と値の最大の組合せを取得できます。
mining_attribute_clauseは、PREDICTIONファンクションと同様に動作します。詳細は、「mining_attribute_clause」を参照してください。
次の例では、特徴3に対応する顧客を、一致する品質の順序で示します。
この例と前提条件のデータ・マイニング操作（モデルおよびビューの作成など）は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmkmdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
SELECT * FROM (SELECT cust_id, FEATURE_VALUE(nmf_sh_sample, 3 USING *) match_quality FROM nmf_sh_sample_apply_prepared ORDER BY match_quality DESC) WHERE ROWNUM < 11; CUST_ID MATCH_QUALITY ---------- ------------- 100210 19.4101627 100962 15.2482251 101151 14.5685197 101499 14.4186292 100363 14.4037396 100372 14.3335148 100982 14.1716545 101039 14.1079914 100759 14.0913761 100953 14.0799737 10 rows selected.
FIRSTファンクションとLASTファンクションは類似しています。両方のファンクションとも、与えられたソート指定に対して、FIRSTまたはLASTとしてランク付けされた一連の行の一連の値を操作する集計ファンクションおよび分析ファンクションです。1つの行のみがFIRSTまたはLASTとしてランク付けされている場合、集計は、1つの要素のみを持つセットを操作します。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。また、引数の数値データ型と同じデータ型を戻します。
ソートされたグループの最初または最後の行の値が必要であり、その値がソート・キーでない場合、FIRSTおよびLASTファンクションは、自己結合またはビューの必要性を排除し、パフォーマンスを向上させます。
aggregate_functionは、MIN、MAX、SUM、AVG、COUNT、VARIANCEまたはSTDDEVファンクションのいずれかです。FIRSTまたはLASTのいずれかにランク付けされた行の値を操作します。1つの行のみがFIRSTまたはLASTとしてランク付けされている場合、集計は、単一（集計ではない）のセットを操作します。
KEEPキーワードはセマンティクスを明確にするためのものです。これはaggregate_functionを修飾し、戻されるaggregate_functionの値がFIRSTまたはLASTのみであることを示します。
DENSE_RANK FIRSTまたはDENSE_RANK LASTは、最小（FIRST）または最大（LAST）稠密ランク（「オリンピック・ランク」）を持つ行のみを集計することを示します。
OVER句を指定すると、FIRSTおよびLASTファンクションを分析ファンクションとして使用できます。OVER句のうち、query_partitioning_clauseの部分のみがこれらのファンクションで有効です。
次の例では、サンプル表hr.employeesの各部門で、歩合が最低である従業員の中での最低給与、および歩合が最高の従業員の中での最高給与を戻します。
SELECT department_id, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst", MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best" FROM employees GROUP BY department_id; DEPARTMENT_ID Worst Best ------------- ---------- ---------- 10 4400 4400 20 6000 13000 30 2500 11000 40 6500 6500 50 2100 8200 60 4200 9000 70 10000 10000 80 6100 14000 90 17000 24000 100 6900 12000 110 8300 12000 7000 7000
次の例では、前述の例と同じ計算をしますが、当該部門の各従業員の結果を戻します。
SELECT last_name, department_id, salary, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Worst", MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Best" FROM employees ORDER BY department_id, salary; LAST_NAME DEPARTMENT_ID SALARY Worst Best ------------------- ------------- ---------- ---------- ---------- Whalen 10 4400 4400 4400 Fay 20 6000 6000 13000 Hartstein 20 13000 6000 13000 . . . Gietz 110 8300 8300 12000 Higgins 110 12000 8300 12000 Grant 7000 7000 7000
FIRST_VALUEは分析ファンクションです。これは、順序付けられた値の集合にある最初の値を戻します。集合内の最初の値がNULLの場合、IGNORE NULLSを指定していないかぎり、ファンクションはNULLを戻します。この設定は、データの稠密化に役立ちます。IGNORE NULLSを指定すると、FIRST_VALUEは集合内の最初のNULLではない値を戻します。すべての値がNULLの場合はNULLを戻します。データの稠密化の例は、「パーティション化された外部結合の使用例:」を参照してください。
exprには、FIRST_VALUEまたは他の分析ファンクションを使用できません。分析ファンクションはネストできませんが、他の組込みファンクション式はexprで使用できます。exprの書式の詳細は、「SQL式」を参照してください。
次の例では、部門90の各従業員について、その部門で給与が一番少ない従業員の名前を選択します。
SELECT department_id, last_name, salary, FIRST_VALUE(last_name) OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS lowest_sal FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY employee_id); DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL ------------- ------------- ---------- ------------------------- 90 Kochhar 17000 Kochhar 90 De Haan 17000 Kochhar 90 King 24000 Kochhar
例では、FIRST_VALUEファンクションの非決定的な性質が示されています。KochharとDe Haanの給与は同じであるため、Kochharの次の行にDe Haanがあります。Kochharが最初に表示されているのは、副問合せが戻す行がemployee_idで順序付けられているためです。ただし、副問合せが戻す行がemployee_idで降順に順序付けられている場合は、次の例に示すとおり、ファンクションは異なる値を戻します。
SELECT department_id, last_name, salary, FIRST_VALUE(last_name) OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) as fv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER by employee_id DESC); DEPARTMENT_ID LAST_NAME SALARY FV ------------- ------------- ---------- ------------------------- 90 De Haan 17000 De Haan 90 Kochhar 17000 De Haan 90 King 24000 De Haan
次の例では、一意キーで順序付けることによって、FIRST_VALUEファンクションを決定的にする方法を示します。
SELECT department_id, last_name, salary, hire_date, FIRST_VALUE(last_name) OVER (ORDER BY salary ASC, hire_date ROWS UNBOUNDED PRECEDING) AS fv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY employee_id DESC); DEPARTMENT_ID LAST_NAME SALARY HIRE_DATE FV ------------- ------------- ---------- --------- --------------- 90 Kochhar 17000 21-SEP-89 Kochhar 90 De Haan 17000 13-JAN-93 Kochhar 90 King 24000 17-JUN-87 Kochhar
FLOORはn以下の最大整数を戻します。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。また、引数の数値データ型と同じデータ型を戻します。
次の例では、15.7以下である最大の整数を戻します。
SELECT FLOOR(15.7) "Floor" FROM DUAL; Floor ---------- 15
FROM_TZファンクションは、タイムスタンプ値およびタイムゾーンをTIMESTAMP WITH TIME ZONE値に変換します。time_zone_valueは、'TZH:TZM'書式の文字列、またはオプションのTZD書式を持つTZR書式で文字列を戻す文字式です。
次の例では、タイムスタンプ値をTIMESTAMP WITH TIME ZONEに戻します。
SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00') FROM DUAL; FROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00') --------------------------------------------------------------- 28-MAR-00 08.00.00 AM +03:00
GREATESTは、1つ以上の式のリストの最大値を戻します。Oracleデータベースは、最初のexprを使用して戻り型を判断します。最初のexprが数値である場合、Oracleは、数値の優先順位が最も高い引数を判断し、比較の前に残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。最初のexprが数値ではない場合、比較の前に、2番目以降の各exprが最初のexprのデータ型に暗黙的に変換されます。
Oracleデータベースは、非空白埋め比較セマンティクスを使用して各exprを比較します。比較では、デフォルトの場合はバイナリが使用され、NLS_COMPパラメータがLINGUISTICに設定されている場合は言語が使用されます。文字の比較は、データベース・キャラクタ・セットの文字の数値コードに基づいて行われます。また、文字ごとではなく、一連のバイトとして扱われる文字列全体で行われます。このファンクションによって戻される値が文字データの場合、そのデータ型は常にVARCHAR2になります。
|
参照:
|
次の文では、最大値を持つ文字列を検索します。
SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD') "Greatest" FROM DUAL; Greatest -------- HARRY
GROUP_IDは、GROUP BY指定の結果から、重複するグループを識別します。このファンクションは、問合せ結果から重複グループを除外する場合に有効です。このファンクションは、重複グループを一意に識別するために、OracleのNUMBER値を戻します。このファンクションは、1つのGROUP BY句を含むSELECT文のみに適用できます。
特定のグループ化でn個の重複が存在する場合、GROUP_IDは0〜n-1の範囲の数値を戻します。
次の例では、サンプル表sh.countriesおよびsh.salesの問合せの結果、重複するco.country_regionグループ化に値1を割り当てます。
SELECT co.country_region, co.country_subregion, SUM(s.amount_sold) "Revenue", GROUP_ID() g FROM sales s, customers c, countries co WHERE s.cust_id = c.cust_id AND c.country_id = co.country_id AND s.time_id = '1-JAN-00' AND co.country_region IN ('Americas', 'Europe') GROUP BY co.country_region, ROLLUP (co.country_region, co.country_subregion); COUNTRY_REGION COUNTRY_SUBREGION Revenue G -------------------- -------------------- ---------- ---------- Americas Northern America 220844 0 Americas Southern America 10872 0 Europe Eastern Europe 12751 0 Europe Western Europe 558686 0 Americas 231716 0 Europe 571437 0 Americas 231716 1 Europe 571437 1
GROUP_IDが1より小さい行のみを戻すには、文の最後に次のHAVING句を追加します。
HAVING GROUP_ID() < 1
GROUPINGは、通常のグループ化された行と超集合行を区別します。ROLLUPやCUBEなどのGROUP BYの拡張機能は、すべての値の集合がNULLで表される超集合行を生成します。GROUPINGファンクションを使用すると、通常の行に含まれるNULLと超集合行ですべての値の集合を表すNULLを区別できます。
GROUPINGファンクションのexprは、GROUP BY句の式のいずれかと一致する必要があります。行のexprの値がすべての値の集合を表すNULLの場合、このファンクションは1の値を戻します。それ以外の場合は、0（ゼロ）を戻します。GROUPINGファンクションは、OracleのNUMBER値を戻します。これらの用語の詳細は、「SELECT」の「group_by_clause」を参照してください。
次の例では、サンプル表hr.departmentsおよびhr.employeesで、GROUPINGファンクションが1（表の通常の行ではなく超集合行）を戻す場合、それ以外の場合に表示されるNULLのかわりに、文字列「All Jobs」が「JOB」列に表示されます。
SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department, DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job, COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal" FROM employees e, departments d WHERE d.department_id = e.department_id GROUP BY ROLLUP (department_name, job_id); DEPARTMENT JOB Total Empl Average Sal ------------------------------ ---------- ---------- ----------- Accounting AC_ACCOUNT 1 99600 Accounting AC_MGR 1 144000 Accounting All Jobs 2 121800 Administration AD_ASST 1 52800 Administration All Jobs 1 52800 Executive AD_PRES 1 288000 Executive AD_VP 2 204000 Executive All Jobs 3 232000 Finance FI_ACCOUNT 5 95040 Finance FI_MGR 1 144000 Finance All Jobs 6 103200 . . .
GROUPING_IDは、行に関連するGROUPINGビット・ベクトルに対応する数値を戻します。GROUPING_IDは、ROLLUPやCUBEなど、GROUP BYの拡張機能およびGROUPINGファンクションを含むSELECT文にのみ適用できます。多くのGROUP BY式を含む問合せでは、多くのGROUPINGファンクションを必要とする特定の行のGROUP BYレベルを指定するため、非常に複雑なSQLになります。GROUPING_IDは、このような場合に有効です。
GROUPING_IDは、複数のGROUPINGファンクションの結果をビット・ベクトル（1と0を組み合せた文字列）に連結したものと同じです。GROUPING_IDを使用すると、複数のGROUPINGファンクションを使用する必要がなくなり、行のフィルタ条件の表記が簡単になります。GROUPING_IDを使用すると、要求する行が単一の条件（GROUPING_ID = n）によって識別されるため、行のフィルタ処理が簡単になります。このファンクションは、1つの表に複数のレベルの集計を格納する場合に、特に有効です。
次の例では、サンプル表sh.salesの問合せからグループ化IDを抽出する方法を示します。
SELECT channel_id, promo_id, sum(amount_sold) s_sales, GROUPING(channel_id) gc, GROUPING(promo_id) gp, GROUPING_ID(channel_id, promo_id) gcp, GROUPING_ID(promo_id, channel_id) gpc FROM sales WHERE promo_id > 496 GROUP BY CUBE(channel_id, promo_id); C PROMO_ID S_SALES GC GP GCP GPC - ---------- ---------- ---------- ---------- ---------- ---------- C 497 26094.35 0 0 0 0 C 498 22272.4 0 0 0 0 C 499 19616.8 0 0 0 0 C 9999 87781668 0 0 0 0 C 87849651.6 0 1 1 2 I 497 50325.8 0 0 0 0 I 498 52215.4 0 0 0 0 I 499 58445.85 0 0 0 0 I 9999 169497409 0 0 0 0 I 169658396 0 1 1 2 P 497 31141.75 0 0 0 0 P 498 46942.8 0 0 0 0 P 499 24156 0 0 0 0 P 9999 70890248 0 0 0 0 P 70992488.6 0 1 1 2 S 497 110629.75 0 0 0 0 S 498 82937.25 0 0 0 0 S 499 80999.15 0 0 0 0 S 9999 267205791 0 0 0 0 S 267480357 0 1 1 2 T 497 8319.6 0 0 0 0 T 498 5347.65 0 0 0 0 T 499 19781 0 0 0 0 T 9999 28095689 0 0 0 0 T 28129137.3 0 1 1 2 497 226511.25 1 0 2 1 498 209715.5 1 0 2 1 499 202998.8 1 0 2 1 9999 623470805 1 0 2 1 624110031 1 1 3 3
HEXTORAWは、CHAR、VARCHAR2、NCHARまたはNVARCHAR2キャラクタ・セットで16進数を含むcharをRAW値に変換します。
このファンクションは、CLOBデータを直接的にサポートしていません。ただし、暗黙的なデータ変換を使用してCLOBを引数として渡すことはできます。
次の例では、RAW列を含む簡単な表を作成し、RAWに変換された16進数値を挿入します。
CREATE TABLE test (raw_col RAW(10)); INSERT INTO test VALUES (HEXTORAW('7D'));
INITCAPは、各単語の最初の文字を大文字、残りの文字を小文字にしてcharを戻します。単語は空白または英数字以外の文字で区切ります。
charは、CHAR、VARCHAR2、NCHARまたはNVARCHAR2データ型です。戻り値は、charと同じデータ型です。データベースは、基礎となるキャラクタ・セットに対して定義したバイナリ・マッピングに基づいて先頭文字の形式を設定します。大文字と小文字の区別については、「NLS_INITCAP」を参照してください。
このファンクションは、CLOBデータを直接的にサポートしていません。ただし、暗黙的なデータ変換を使用してCLOBを引数として渡すことはできます。
次の例では、文字列にある各単語を大文字で始めます。
SELECT INITCAP('the soap') "Capitals" FROM DUAL; Capitals --------- The Soap
INSERTCHILDXMLは、ユーザー指定の値を、XPath式で指定したノードのターゲットXMLに挿入します。このファンクションと「INSERTXMLBEFORE」を比較してください。
XMLType_instanceは、XMLTypeのインスタンスです。
XPath_stringは、1つ以上の子ノードが挿入されるノードを1つ以上示すXPath式です。先頭にスラッシュを付けて絶対XPath_stringを指定したり、先頭のスラッシュを省略して相対XPath_stringを指定できます。先頭のスラッシュを省略した場合、相対パスのコンテキストは、デフォルトでルート・ノードに設定されます。
child_exprは、挿入する要素または属性のノードを1つ以上指定します。
value_exprは、挿入されるノードを1つ以上指定するXMLTypeのフラグメントです。これは文字列に変換する必要があります。
namespace_stringは、XPath_stringのネームスペース情報を提供します。このパラメータは、VARCHAR2型である必要があります。
次の例では、2番目の/Ownerノードを、「APPENDCHILDXML」の例で更新したウェアハウスの1つのwarehouse_specに追加します。
UPDATE warehouses SET warehouse_spec = INSERTCHILDXML(warehouse_spec, '/Warehouse/Building', 'Owner', XMLType('<Owner>LesserCo</Owner>')) WHERE warehouse_id = 3; SELECT warehouse_spec FROM warehouses WHERE warehouse_id = 3; WAREHOUSE_SPEC ---------------------------------------------------------------------------- <?xml version="1.0"?> <Warehouse> <Building>Rented <Owner>Grandco</Owner> <Owner>LesserCo</Owner> </Building> <Area>85700</Area> <DockType/> <WaterAccess>N</WaterAccess> <RailAccess>N</RailAccess> <Parking>Street</Parking> <VClearance>11.5 ft</VClearance> </Warehouse>
INSERTXMLBEFOREは、ユーザー指定の値を、XPath式で指定したノードの前のターゲットXMLに挿入します。 このファンクションと「INSERTCHILDXML」を比較してください。
XMLType_instanceは、XMLTypeのインスタンスです。
XPath_stringは、1つ以上の子ノードが挿入されるノードを1つ以上示すXPath式です。先頭にスラッシュを付けて絶対XPath_stringを指定したり、先頭のスラッシュを省略して相対XPath_stringを指定できます。先頭のスラッシュを省略した場合、相対パスのコンテキストは、デフォルトでルート・ノードに設定されます。
value_exprは、挿入される1つ以上のノードと親ノード内での位置を定義するXMLTypeのフラグメントです。これは文字列に変換する必要があります。
namespace_stringは、XPath_stringのネームスペース情報を提供します。このパラメータは、VARCHAR2型である必要があります。次の例は、「INSERTCHILDXML」の例と似ていますが、この例では、「INSERTCHILDXML」の例で追加した/Ownerノードの前に、3番目の/Ownerノードを追加します。問合せの出力は読み取りやすいように整えられています。
UPDATE warehouses SET warehouse_spec = INSERTXMLBEFORE(warehouse_spec, '/Warehouse/Building/Owner[2]', XMLType('<Owner>ThirdOwner</Owner>')) WHERE warehouse_id = 3; SELECT warehouse_name, EXTRACT(warehouse_spec, '/Warehouse/Building/Owner') "Owners" FROM warehouses WHERE warehouse_id = 3; Name Owners ------------ -------------------------------------------------------------------- New Jersey <Owner>Grandco</Owner> <Owner>ThirdOwner</Owner> <Owner>LesserCo</Owner>
INSTRファンクションは、stringのsubstringを検索します。このファンクションは、最初に現れた文字stringの位置を示す整数を戻します。INSTRは、入力キャラクタ・セットによって定義された文字を使用して、文字列を算出します。INSTRBは、文字のかわりにバイトを使用します。INSTRCは、完全なUnicodeキャラクタを使用します。INSTR2は、UCS2コードポイントを使用します。INSTR4は、UCS4コードポイントを使用します。
positionは、Oracleデータベースが検索を開始する文字stringの位置を示す0（ゼロ）以外の整数です。positionが負の場合、Oracleはstringの終わりから逆方向にカウントし、結果位置から逆方向に検索します。
occurrenceは、検索するstringが現れたことを示す整数です。occurrenceの値は正である必要があります。
stringおよびsubstringは、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。戻り値のデータ型はNUMBERです。
positionおよびoccurrenceは、NUMBERデータ型か、または暗黙的にNUMBERに変換可能な任意のデータ型で、整数に変換されるものである必要があります。positionおよびoccurrenceのデフォルト値は1です。この場合、Oracleはstringの最初の文字から検索を開始します。検索対象はsubstringが最初に現れる位置です。戻り値は、positionの値にかかわらず、stringの先頭に関係し、文字で表されます。検索が失敗した（stringのposition番目の文字の後にsubstringがoccurrence回現れない）場合、戻り値は0となります。
次の例では、文字列CORPORATE FLOORで文字列ORの検索を3番目の文字から開始します。文字列CORPORATE FLOORで2回目に現れる「OR」の開始位置を戻します。
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL; Instring ---------- 14
次の例では、最後の文字から、最後から3番目の文字まで、つまりFLOORの最初のOまで逆方向にカウントします。次に、2回目に現れる「OR」を逆方向に検索し、2回目に現れるこの文字列が、検索文字列の2番目の文字で始まることを認識します。
SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring" FROM DUAL; Reversed Instring ----------------- 2
次の例では、データベース・キャラクタ・セットがダブルバイトの場合を想定しています。
SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL; Instring in bytes ----------------- 27
ITERATION_NUMBERファンクションは、SELECT文のmodel_clauseにのみ指定でき、model_rules_clauseにITERATE(number)が指定されている場合にのみ使用できます。このファンクションは、モデル・ルールに従って完了した反復を示す整数を戻します。最初の反復では0（ゼロ）を戻します。2回目以降の各反復では、iteration_numberに1を足した整数を戻します。
次の例では、1998年および1999年のマウス・パッドの売上を、それぞれ2001年および2002年のマウス・パッドの売上に割り当てます。
SELECT country, prod, year, s FROM sales_view_ref MODEL PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale s) IGNORE NAV UNIQUE DIMENSION RULES UPSERT SEQUENTIAL ORDER ITERATE(2) ( s['Mouse Pad', 2001 + ITERATION_NUMBER] = s['Mouse Pad', 1998 + ITERATION_NUMBER] ) ORDER BY country, prod, year; COUNTRY PROD YEAR S ---------- ----------------------------------- -------- --------- France Mouse Pad 1998 2509.42 France Mouse Pad 1999 3678.69 France Mouse Pad 2000 3000.72 France Mouse Pad 2001 2509.42 France Mouse Pad 2002 3678.69 France Standard Mouse 1998 2390.83 France Standard Mouse 1999 2280.45 France Standard Mouse 2000 1274.31 France Standard Mouse 2001 2164.54 Germany Mouse Pad 1998 5827.87 Germany Mouse Pad 1999 8346.44 Germany Mouse Pad 2000 7375.46 Germany Mouse Pad 2001 5827.87 Germany Mouse Pad 2002 8346.44 Germany Standard Mouse 1998 7116.11 Germany Standard Mouse 1999 6263.14 Germany Standard Mouse 2000 2637.31 Germany Standard Mouse 2001 6456.13 18 rows selected.
この例では、ビューsales_view_refが必要です。このビューの作成方法については、「MODEL句の例:」を参照してください。
LAGは分析ファンクションです。これは、自己結合せずに、表の1つ以上の行へ同時アクセスを行います。問合せから戻される一連の行およびカーソル位置を指定すると、LAGは、その位置より前にある指定された物理オフセットにある行へアクセスします。
offsetを指定しない場合、デフォルト値は1です。オフセットがウィンドウの有効範囲を超えた場合、オプションのdefault値が戻されます。defaultを指定しない場合、デフォルトはNULLです。
value_exprには、LAGまたは他の分析ファンクションを使用できません。分析ファンクションはネストできませんが、他の組込みファンクション式はvalue_exprで使用できます。
次の例では、employees表の各販売員について、その販売員の直前に雇用された従業員の給与を示します。
SELECT last_name, hire_date, salary, LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal FROM employees WHERE job_id = 'PU_CLERK'; LAST_NAME HIRE_DATE SALARY PREV_SAL ------------------------- --------- ---------- ---------- Khoo 18-MAY-95 3100 0 Tobias 24-JUL-97 2800 3100 Baida 24-DEC-97 2900 2800 Himuro 15-NOV-98 2600 2900 Colmenares 10-AUG-99 2500 2600
FIRSTファンクションとLASTファンクションは類似しています。両方のファンクションとも、与えられたソート指定に対して、FIRSTまたはLASTとしてランク付けされた一連の行の一連の値を操作する集計ファンクションおよび分析ファンクションです。1つの行のみがFIRSTまたはLASTとしてランク付けされている場合、集計は、1つの要素のみを持つセットを操作します。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。また、引数の数値データ型と同じデータ型を戻します。
このファンクションの詳細および使用例は、「FIRST」を参照してください。
LAST_DAYは、dateを含む月の最終日の日付を戻します。戻り型は、dateのデータ型に関係なく常にDATEです。
次の文は、現在の月の残りの日数を確認します。
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL; SYSDATE Last Days Left --------- --------- ---------- 30-MAY-01 31-MAY-01 1
次の例では、各従業員の雇用開始日に5か月を加えて、評価日付を戻します。
SELECT last_name, hire_date, TO_CHAR( ADD_MONTHS(LAST_DAY(hire_date), 5)) "Eval Date" FROM employees; LAST_NAME HIRE_DATE Eval Date ------------------------- --------- --------- King 17-JUN-87 30-NOV-87 Kochhar 21-SEP-89 28-FEB-90 De Haan 13-JAN-93 30-JUN-93 Hunold 03-JAN-90 30-JUN-90 Ernst 21-MAY-91 31-OCT-91 Austin 25-JUN-97 30-NOV-97 Pataballa 05-FEB-98 31-JUL-98 Lorentz 07-FEB-99 31-JUL-99 . . .
LAST_VALUEは分析ファンクションです。これは、順序付けられた値の集合にある最後の値を戻します。集合内の最後の値がNULLの場合、IGNORE NULLSを指定していないかぎり、ファンクションはNULLを戻します。この設定は、データの稠密化に役立ちます。IGNORE NULLSを指定すると、LAST_VALUEは集合内の最初のNULLではない値を戻します。すべての値がNULLの場合はNULLを戻します。データの稠密化の例は、「パーティション化された外部結合の使用例:」を参照してください。
exprには、LAST_VALUEまたは他の分析ファンクションを使用できません。分析ファンクションはネストできませんが、他の組込みファンクション式はexprで使用できます。exprの書式の詳細は、「SQL式」を参照してください。
次の例では、給与が一番高い従業員の雇用開始日を各行に戻します。
SELECT last_name, salary, hire_date, LAST_VALUE(hire_date) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date); LAST_NAME SALARY HIRE_DATE LV ------------------------- ---------- --------- --------- Kochhar 17000 21-SEP-89 17-JUN-87 De Haan 17000 13-JAN-93 17-JUN-87 King 24000 17-JUN-87 17-JUN-87
この例では、LAST_VALUEファンクションの非決定的な性質を示しています。KochharとDe Haanの給与は同じであるため、Kochharの次の行にDe Haanがあります。Kochharが最初に表示されているのは、副問合せの行がhire_dateで順序付けられているためです。ただし、行がhire_dateで降順に順序付けられている場合は、次の例に示すとおり、ファンクションは異なる値を戻します。
SELECT last_name, salary, hire_date, LAST_VALUE(hire_date) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date DESC); LAST_NAME SALARY HIRE_DATE LV ------------------------- ---------- --------- --------- De Haan 17000 13-JAN-93 17-JUN-87 Kochhar 17000 21-SEP-89 17-JUN-87 King 24000 17-JUN-87 17-JUN-87
次の2つの例では、一意キーで順序付けることによって、LAST_VALUEファンクションを決定的にする方法を示しています。salaryおよびhire_dateでファンクション内を順序付けると、副問合せの順序付けにかかわらず、同じ結果が戻されます。
SELECT last_name, salary, hire_date, LAST_VALUE(hire_date) OVER (ORDER BY salary, hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date); LAST_NAME SALARY HIRE_DATE LV ------------------------- ---------- --------- --------- Kochhar 17000 21-SEP-89 17-JUN-87 De Haan 17000 13-JAN-93 17-JUN-87 King 24000 17-JUN-87 17-JUN-87 SELECT last_name, salary, hire_date, LAST_VALUE(hire_date) OVER (ORDER BY salary, hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date DESC); LAST_NAME SALARY HIRE_DATE LV ------------------------- ---------- --------- --------- Kochhar 17000 21-SEP-89 17-JUN-87 De Haan 17000 13-JAN-93 17-JUN-87 King 24000 17-JUN-87 17-JUN-87
LEADは分析ファンクションです。これは、自己結合せずに、表の1つ以上の行へ同時アクセスを行います。問合せから戻される一連の行およびカーソル位置を指定すると、LEADは、その位置より後にある指定された物理オフセットの行へアクセスします。
offsetを指定しない場合、デフォルト値は1です。オフセットが表の有効範囲を超えた場合、オプションのdefault値が戻されます。defaultを指定しない場合、デフォルト値はNULLです。
value_exprには、LEADまたは他の分析ファンクションを使用できません。分析ファンクションはネストできませんが、他の組込みファンクション式はvalue_exprで使用できます。
次の例では、employees表の各従業員について、その従業員の直後に雇用された従業員の雇用開始日を示します。
SELECT last_name, hire_date, LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired" FROM employees WHERE department_id = 30; LAST_NAME HIRE_DATE NextHired ------------------------- --------- --------- Raphaely 07-DEC-94 18-MAY-95 Khoo 18-MAY-95 24-JUL-97 Tobias 24-JUL-97 24-DEC-97 Baida 24-DEC-97 15-NOV-98 Himuro 15-NOV-98 10-AUG-99 Colmenares 10-AUG-99
LEASTは、リストされたexpr内の最小値を戻します。比較の前に、2番目以降のすべてのexprは最初のexprのデータ型に暗黙的に変換されます。Oracleデータベースは、非空白埋め比較セマンティクスを使用してexprを比較します。このファンクションによって戻される値が文字データの場合、そのデータ型は常にVARCHAR2になります。
|
参照:
暗黙的な変換の詳細は、表2-10「暗黙的な型変換のマトリックス」を参照してください。2進浮動小数点の比較セマンティクスの詳細は、「浮動小数点数」を参照してください。「データ型の比較規則」も参照してください。 |
次の文では、最小値を持つ文字列を検索します。
SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST" FROM DUAL; LEAST ------ HAROLD
LENGTHの各ファンクションは、charの長さを戻します。LENGTHは、入力キャラクタ・セットによって定義された文字を使用して、長さを算出します。LENGTHBは、文字のかわりにバイトを使用します。LENGTHCは、完全なUnicodeキャラクタを使用します。LENGTH2は、UCS2コードポイントを使用します。LENGTH4は、UCS4コードポイントを使用します。
charは、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。戻り値のデータ型はNUMBERです。charのデータ型がCHARの場合、その長さにはすべての後続空白が含まれます。charがNULLの場合、このファンクションはNULLを戻します。
LENGTHBファンクションは、シングルバイトのLOBでのみサポートされます。このファンクションは、マルチバイトのキャラクタ・セットのCLOBとNCLOBでは使用できません。
次の例では、シングルバイトおよびマルチバイトのデータベース・キャラクタ・セットを使用するLENGTHファンクションを使用します。
SELECT LENGTH('CANDIDE') "Length in characters" FROM DUAL; Length in characters -------------------- 7
次の例では、データベース・キャラクタ・セットがダブルバイトの場合を想定しています。
SELECT LENGTHB ('CANDIDE') "Length in bytes" FROM DUAL; Length in bytes --------------- 14
LNは、nの自然対数を戻します（nは正の数）。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。引数がBINARY_FLOATの場合、このファンクションはBINARY_DOUBLEを戻します。それ以外の場合、引数と同じ数値データ型を戻します。
次の例では、95の自然対数を戻します。
SELECT LN(95) "Natural log of 95" FROM DUAL; Natural log of 95 ----------------- 4.55387689
LNNVLは、条件のオペランドの1つまたは両方がNULLの可能性がある場合にその条件を簡単に評価する方法を提供します。このファンクションは、問合せのWHERE句でのみ使用できます。このファンクションは、引数として条件を取り、その条件がFALSEまたはUNKNOWNの場合はTRUEを戻し、TRUEの場合はFALSEを戻します。LNNVLは、スカラー式を指定できる場所であればどこでも指定でき、有効ではないが、発生する可能性があるNULLを評価するためにIS [NOT] NULL、ANDまたはOR条件が必要であるようなコンテキストでも指定できます。Oracleデータベースは、LNNVLファンクションをこの方法で内部的に使用して、NOT IN条件をNOT EXISTS条件として書き換える場合があります。この場合、EXPLAIN PLANからの出力によって、この操作がPLAN TABLEに出力されます。conditionでは、どのようなスカラー値でも評価できますが、AND、ORまたはBETWEENを含む複合条件は指定できません。
a = 2およびb=NULLの場合のLNNVLからの戻り値を次の表に示します。
| 条件 | 条件の真偽 | LNNVLの戻り値 |
|---|---|---|
|
a = 1 |
|
|
|
a = 2 |
|
|
|
a |
|
|
|
b = 1 |
|
|
|
b |
|
|
|
a = b |
|
|
歩合を受け取らない従業員を含めて、歩合率が20%未満の従業員数を調べるとします。次の問合せは、20%未満の歩合を実際に受け取る従業員のみを戻します。
SELECT COUNT(*) FROM employees WHERE commission_pct < .2; COUNT(*) ---------- 11
歩合を受け取らない72人の従業員も含めるには、LNNVLファンクションを次のように使用して、この問合せを書き換えます。
SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2); COUNT(*) ---------- 83
LOCALTIMESTAMPは、セッションのタイムゾーンの現在の日付および時刻をTIMESTAMPデータ型の値で戻します。これに対して、CURRENT_TIMESTAMPは、TIMESTAMP WITH TIME ZONE値を戻します。
オプションの引数timestamp_precisionには、戻される時刻値の秒の小数部の精度を指定します。
次の例では、LOCALTIMESTAMPとCURRENT_TIMESTAMPの相違を示します。
ALTER SESSION SET TIME_ZONE = '-5:00'; SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; CURRENT_TIMESTAMP LOCALTIMESTAMP ------------------------------------------------------------------- 04-APR-00 01.27.18.999220 PM -05:00 04-APR-00 01.27.19 PM ALTER SESSION SET TIME_ZONE = '-8:00'; SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; CURRENT_TIMESTAMP LOCALTIMESTAMP ----------------------------------- ------------------------------ 04-APR-00 10.27.45.132474 AM -08:00 04-APR-00 10.27.451 AM
LOCALTIMESTAMPで書式マスクを使用する場合は、ファンクションが戻す値と書式マスクを一致させてください。たとえば、次の表の場合を考えます。
CREATE TABLE local_test (col1 TIMESTAMP WITH LOCAL TIME ZONE);
ファンクションが戻す型のTIME ZONEの部分がマスクに含まれていないため、次の文は正常に実行されません。
INSERT INTO local_test VALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));
次の文では、LOCALTIMESTAMPの戻り値の型と一致する正しい書式マスクが使用されています。
INSERT INTO local_test VALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
LOGは、n2を底とするn1の対数を戻します。底n1は0（ゼロ）または1以外の任意の正の値で、n2は任意の正の値です。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。いずれかの引数がBINARY_FLOATまたはBINARY_DOUBLEの場合、このファンクションはBINARY_DOUBLEを戻します。それ以外の場合、NUMBERを戻します。
次の例では、100の対数を戻します。
SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL; Log base 10 of 100 ------------------ 2
LOWERは、すべての文字を小文字にしてcharを戻します。charは、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。戻り値は、charと同じデータ型です。データベースは、基礎となるキャラクタ・セットに対して定義したバイナリ・マッピングに基づいて文字の形式を設定します。小文字の区別については、「NLS_LOWER」を参照してください。
次の例では、文字列を小文字にして戻します。
SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase" FROM DUAL; Lowercase -------------------- mr. scott mcmillan
LPADは、expr1の左側にexpr2に指定した文字を連続的に埋め込んでn桁にして戻します。このファンクションは、問合せの出力の書式設定に役立ちます。
expr1およびexpr2は、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。戻される文字列は、expr1が文字データ型の場合はVARCHAR2データ型になり、expr1がLOBデータ型の場合はLOBになります。expr1と同じキャラクタ・セットの文字列が戻されます。引数nは、NUMBER型の整数か、またはNUMBER型の整数に暗黙的に変換可能な値である必要があります。
expr2を指定しない場合、デフォルトで空白1個が指定されます。expr1がnより長い場合、このファンクションはnに収まるexpr1の一部を戻します。
引数nは、戻り値が画面に表示される場合の全体の長さです。多くのキャラクタ・セットでは、これは戻り値の文字数でもあります。ただし、マルチバイトのキャラクタ・セットでは、表示される文字列の長さが文字列の文字数と異なる場合もあります。
次の例では、文字列の左側にアスタリスクとピリオド（*.）を埋め込みます。
SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL; LPAD example --------------- *.*.*.*.*Page 1
LTRIMは、charの左端から、setに指定されたすべての文字を削除します。setを指定しない場合、デフォルトで空白1個が指定されます。charが文字リテラルの場合、一重引用符で囲む必要があります。Oracleデータベースはcharの先頭文字からスキャンし始め、setに指定された文字をすべて削除します。setに指定された文字以外の文字が見つかった時点で結果を戻します。
charおよびsetは、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。戻される文字列は、charが文字データ型の場合はVARCHAR2データ型になり、charがLOBデータ型の場合はLOBになります。
次の例では、oe.products表内の製品名のグループから、冗長な最初の語を削除します。
SELECT product_name, LTRIM(product_name, 'Monitor ') "Short Name" FROM products WHERE product_name LIKE 'Monitor%'; PRODUCT_NAME Short Name -------------------- --------------- Monitor 17/HR 17/HR Monitor 17/HR/F 17/HR/F Monitor 17/SD 17/SD Monitor 19/SD 19/SD Monitor 19/SD/M 19/SD/M Monitor 21/D 21/D Monitor 21/HR 21/HR Monitor 21/HR/M 21/HR/M Monitor 21/SD 21/SD Monitor Hinge - HD Hinge - HD Monitor Hinge - STD Hinge - STD
MAKE_REFは、オブジェクト識別子が主キーに基づいている、オブジェクト・ビューの行またはオブジェクト表の行に対するREFを作成します。このファンクションは、オブジェクト・ビューを作成する場合などに有効です。
サンプル・スキーマoeには、inventory_typに基づくオブジェクト・ビューoc_inventoriesが格納されています。オブジェクト識別子はproduct_idです。次の例では、3003というproduct_idを含むオブジェクト・ビューoc_inventoriesにある行へのREFを作成します。
SELECT MAKE_REF (oc_inventories, 3003) FROM DUAL; MAKE_REF(OC_INVENTORIES,3003) ------------------------------------------------------------------ 00004A038A0046857C14617141109EE03408002082543600000014260100010001 00290090606002A00078401FE0000000B03C21F040000000000000000000000000 0000000000
MAXはexprの最大値を戻します。これは、集計ファンクションまたは分析ファンクションとして使用できます。
次の例では、hr.employees表の最高給与を検索します。
SELECT MAX(salary) "Maximum" FROM employees; Maximum ---------- 24000
次の例では、各従業員について、その従業員と所属が同じ従業員のうち、一番高い給与を計算します。
SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max FROM employees; MANAGER_ID LAST_NAME SALARY MGR_MAX ---------- ------------------------- ---------- ---------- 100 Kochhar 17000 17000 100 De Haan 17000 17000 100 Raphaely 11000 17000 100 Kaufling 7900 17000 100 Fripp 8200 17000 100 Weiss 8000 17000 . . .
この問合せを述語のある親問合せで囲むと、各部門で給与の一番高い従業員がわかります。
SELECT manager_id, last_name, salary FROM (SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal FROM employees) WHERE salary = rmax_sal; MANAGER_ID LAST_NAME SALARY ---------- ------------------------- ---------- 100 Kochhar 17000 100 De Haan 17000 101 Greenberg 12000 101 Higgens 12000 102 Hunold 9000 103 Ernst 6000 108 Faviet 9000 114 Khoo 3100 120 Nayer 3200 120 Taylor 3200 121 Sarchand 4200 122 Chung 3800 123 Bell 4000 124 Rajs 3500 145 Tucker 10000 146 King 10000 147 Vishney 10500 148 Ozer 11500 149 Abel 11000 201 Goyal 6000 205 Gietz 8300 King 24000
MEDIANは、連続分散モデルを想定する逆分散関数です。このファンクションは、数値または日時値を取り、その中央値、または値のソート後に中央値となる補間された値を戻します。計算では、NULLは無視されます。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。exprのみを指定した場合、このファンクションは引数の数値データ型と同じデータ型を戻します。OVER句を指定した場合、Oracleデータベースは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。
MEDIANの結果の計算では、まず行が順序付けされます。グループ内の行数としてNが使用され、対象の行番号（RN）がRN = (1 + (0.5×(N-1))という式で計算されます。集計ファンクションの最終結果は、行番号がCRN = CEILING(RN)およびFRN = FLOOR(RN)の行の値間の直線補間によって計算されます。
最終結果は次のとおりです。
if (CRN = FRN = RN) then (value of expression from row at RN) else (CRN - RN) * (value of expression for row at FRN) + (RN - FRN) * (value of expression for row at CRN)
MEDIANは、分析ファンクションとして使用できます。その場合、OVER句には、query_partition_clauseのみを指定できます。各行に対して、各パーティション内の一連の値の中央値に該当する値が戻されます。
このファンクションと次のファンクションを比較してください。
MEDIANは、パーセンタイル値がデフォルトで0.5に指定される特別なPERCENTILE_CONTです。
次の問合せは、hr.employees表内の各従業員の給与の中央値を戻します。
SELECT department_id, MEDIAN(salary) FROM employees GROUP BY department_id; DEPARTMENT_ID MEDIAN(SALARY) ------------- -------------- 10 4400 20 9500 30 2850 40 6500 50 3100 60 4800 70 10000 80 8900 90 17000 100 8000 110 10150 7000
次の問合せは、hr.employees表内の部門のサブセットのマネージャごとに給与の中央値を戻します。
SELECT manager_id, employee_id, salary, MEDIAN(salary) OVER (PARTITION BY manager_id) "Median by Mgr" FROM employees WHERE department_id > 60; MANAGER_ID EMPLOYEE_ID SALARY Median by Mgr ---------- ----------- ---------- ------------- 100 149 10500 13500 100 148 11000 13500 100 147 12000 13500 100 146 13500 13500 100 145 14000 13500 100 101 17000 13500 100 102 17000 13500 101 204 10000 12000 101 108 12000 12000 101 205 12000 12000 108 113 6900 7800 108 111 7700 7800 108 112 7800 7800 108 110 8200 7800 108 109 9000 7800 145 155 7000 8500 145 154 7500 8500 . . .
MINは、exprの最小値を戻します。これは、集計ファンクションまたは分析ファンクションとして使用できます。
次の例では、hr.employees表の最初の雇用開始日を戻します。
SELECT MIN(hire_date) "Earliest" FROM employees; Earliest --------- 17-JUN-87
次の例では、各従業員について、その従業員が雇用された日以前に雇用された従業員を検索します。その従業員と所属が同じ従業員のサブセットを決定し、そのサブセット内で一番低い給与を戻します。
SELECT manager_id, last_name, hire_date, salary, MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date RANGE UNBOUNDED PRECEDING) AS p_cmin FROM employees; MANAGER_ID LAST_NAME HIRE_DATE SALARY P_CMIN ---------- ------------------------- --------- ---------- ---------- 100 Kochhar 21-SEP-89 17000 17000 100 De Haan 13-JAN-93 17000 17000 100 Raphaely 07-DEC-94 11000 11000 100 Kaufling 01-MAY-95 7900 7900 100 Hartstein 17-FEB-96 13000 7900 100 Weiss 18-JUL-96 8000 7900 100 Russell 01-OCT-96 14000 7900 100 Partners 05-JAN-97 13500 7900 100 Errazuriz 10-MAR-97 12000 7900 . . .
MODはn2をn1で割った余りを戻します。n1が0の場合は、n2を戻します。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。
次の例では、11を4で割ったときの余りを戻します。
SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3
このファンクションは、mが負の場合には古典数学のモジュール・ファンクションとは異なる動作をします。古典数学のモジュール・ファンクションは、次の公式を用いたMODファンクションで表すことができます。
m - n * FLOOR(m/n)
次の表に、MODファンクションと古典数学のモジュール・ファンクションの相違を示します。
| m | n | MOD(m,n) | 古典数学のモジュール・ファンクション |
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
MONTHS_BETWEENは、日付date1とdate2の間の月数を戻します。date1がdate2より後の日付の場合、結果は正の値になります。date1がdate2より前の日付の場合、結果は負の値になります。date1およびdate2が、月の同じ日または月の最終日の場合、結果は常に整数になります。それ以外の場合、Oracleデータベースは結果の小数部を1か月31日として計算し、date1とdate2の差を割り出します。
次の例では、2つの日付間の月数を計算します。
SELECT MONTHS_BETWEEN (TO_DATE('02-02-1995','MM-DD-YYYY'), TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM DUAL; Months ---------- 1.03225806
NANVLファンクションは、BINARY_FLOAT型またはBINARY_DOUBLE型の浮動小数点数のみに有効です。このファンクションは、入力値n2がNaN（非数値）の場合は代替値n1を戻すようにOracleデータベースに指示します。n2がNaNでない場合、Oracleはn2を戻します。このファンクションは、NaN値をNULLにマップする場合に有効です。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。
|
参照:
暗黙的な変換の詳細は、表2-10「暗黙的な型変換のマトリックス」を参照してください。2進浮動小数点の比較セマンティクスの詳細は、「浮動小数点数」を参照してください。数値の優先順位の詳細は、「数値の優先順位」を参照してください。 |
「TO_BINARY_DOUBLE」で作成したfloat_point_demoを使用して、表に2つ目のエントリを挿入します。
Insert INTO float_point_demo VALUES (0,'NaN','NaN'); SELECT * FROM float_point_demo; DEC_NUM BIN_DOUBLE BIN_FLOAT ---------- ---------- ---------- 1234.56 1.235E+003 1.235E+003 0 Nan Nan
次の例では、数値の場合はbin_floatを戻します。数値以外の場合は、0（ゼロ）を戻します。
SELECT bin_float, NANVL(bin_float,0) FROM float_point_demo; BIN_FLOAT NANVL(BIN_FLOAT,0) ---------- ------------------ 1.235E+003 1.235E+003 Nan 0
NCHRは、各国語キャラクタ・セットのnumberと同等のバイナリを持つ文字を戻します。戻り値は常にNVARCHAR2です。このファンクションは、CHRファンクションにUSING NCHAR_CS句を指定して使用した場合と同じ結果を戻します。
このファンクションは、引数として、NUMBER値、または暗黙的にNUMBER型に変換可能な任意の値を取り、文字を戻します。
次の例では、NCHAR文字187を戻します。
SELECT NCHR(187) FROM DUAL; NC -- > SELECT CHR(187 USING NCHAR_CS) FROM DUAL; CH -- >
NEW_TIMEは、タイムゾーンtimezone1の日時がdateの時点のタイムゾーンtimezone2の日時を戻します。このファンクションを使用する前に、24時間で表示されるように、NLS_DATE_FORMATパラメータを設定する必要があります。戻り型は、dateのデータ型に関係なく常にDATEです。
引数timezone1およびtimezone2には、次のテキスト文字列のいずれかを指定できます。
次の例では、指定された太平洋標準時と同等の大西洋標準時を戻します。
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT NEW_TIME(TO_DATE( '11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time" FROM DUAL; New Date and Time -------------------- 09-NOV-1999 21:23:45
NEXT_DAYは、charで指定した曜日で、日付dateより後の最初の日付を戻します。戻り型は、dateのデータ型に関係なく常にDATEです。引数charは、セッションの日付言語での曜日である必要があります（フルネームでも省略形でも可）。必要最小限の文字数は、省略形の文字数です。有効な省略形の後に続けて文字が入力されていても、それらの文字は無視されます。戻り値は、引数dateと同じ時、分および秒のコンポーネントを持っています。
次の例では、2001年2月2日以降の最初の火曜日の日付を戻します。
SELECT NEXT_DAY('02-FEB-2001','TUESDAY') "NEXT DAY" FROM DUAL; NEXT DAY ----------- 06-FEB-2001
NLS_CHARSET_DECL_LENは、NCHAR列の宣言の長さを（文字数で）戻します。byte_count引数は、列の幅です。char_set_id引数は、列のキャラクタ・セットIDです。
次の例では、マルチバイト・キャラクタ・セットを使用している場合に、列の幅が200バイトである文字の数を戻します。
SELECT NLS_CHARSET_DECL_LEN (200, nls_charset_id('ja16eucfixed')) FROM DUAL; NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED')) -------------------------------------------------------- 100
NLS_CHARSET_IDは、キャラクタ・セット名stringに対応するキャラクタ・セットのID番号を戻します。引数stringは、実行時のVARCHAR2値です。string値'CHAR_CS'は、サーバーのデータベース・キャラクタ・セットのID番号を戻します。string値'NCHAR_CS'は、サーバーの各国語キャラクタ・セットのID番号を戻します。
無効なキャラクタ・セット名を指定すると、NULLが戻されます。
次の例では、キャラクタ・セットのIDを戻します。
SELECT NLS_CHARSET_ID('ja16euc') FROM DUAL; NLS_CHARSET_ID('JA16EUC') ------------------------- 830
NLS_CHARSET_NAMEは、ID番号numberに対応するキャラクタ・セット名を戻します。キャラクタ・セット名は、データベース・キャラクタ・セットのVARCHAR2値として戻されます。
numberが有効なキャラクタ・セットIDとして認識されない場合は、このファンクションはNULLを戻します。
次の例では、キャラクタ・セットのID番号2に対応するキャラクタ・セットを戻します。
SELECT NLS_CHARSET_NAME(2) FROM DUAL; NLS_CH ------ WE8DEC
NLS_INITCAPは、各単語の最初の文字を大文字、残りの文字を小文字にしてcharを戻します。単語は空白または英数字以外の文字で区切ります。
charおよび'nlsparam'は、CHAR、VARCHAR2、NCHARまたはNVARCHAR2データ型です。戻される文字列は、VARCHAR2データ型であり、charと同じキャラクタ・セットです。
'nlsparam'の値は次の書式で指定します。
'NLS_SORT = sort'
sortは、言語ソート基準またはBINARYのいずれかです。言語ソート基準は、大文字と小文字の変換のために特別な言語要件を処理します。これらの要件によって、charと異なる長さの値が戻される場合があります。'nlsparam'を省略すると、このファンクションはセッションに対してデフォルトのソート基準を使用します。
このファンクションは、CLOBデータを直接的にサポートしていません。ただし、暗黙的なデータ変換を使用してCLOBを引数として渡すことはできます。
次に、ファンクションによって言語ソート基準がどのように異なる値を戻すかを示します。
SELECT NLS_INITCAP ('ijsland') "InitCap" FROM DUAL; InitCap ------- Ijsland SELECT NLS_INITCAP ('ijsland', 'NLS_SORT = XDutch') "InitCap" FROM DUAL; InitCap ------- IJsland
NLS_LOWERは、すべての文字を小文字にしてcharを戻します。
charおよび'nlsparam'は、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。戻される文字列は、charが文字データ型の場合はVARCHAR2データ型になり、charがLOBデータ型の場合はLOBになります。charと同じキャラクタ・セットの文字列が戻されます。
'nlsparam'の書式および用途は、NLS_INITCAPファンクションと同じです。
次の例では、XGerman言語ソート順序を使用する文字列'citta''を戻します。
SELECT NLS_LOWER ('CITTA''', 'NLS_SORT = XGerman') "Lowercase" FROM DUAL; Lowerc ------ citta'
NLSSORTは、charのソートに使用される文字列のバイトを戻します。
charおよび'nlsparam'は、CHAR、VARCHAR2、NCHARまたはNVARCHAR2データ型です。戻される文字列はRAWデータ型です。
'nlsparam'の値は次の書式で指定します。
'NLS_SORT = sort'
sortは、言語ソート基準またはBINARYのいずれかです。'nlsparam'を省略すると、このファンクションはセッションに対してデフォルトのソート基準を使用します。BINARYを指定すると、このファンクションはcharを戻します。
'nlsparam'を指定した場合、言語ソート名に接尾辞_aiを追加してアクセント記号の有無を区別しないソートを行うか、または_ciを追加して大/小文字を区別しないソートを行うことができます。アクセント記号の有無および大/小文字を区別しないソートの詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。
このファンクションは、CLOBデータを直接的にサポートしていません。ただし、暗黙的なデータ変換を使用してCLOBを引数として渡すことはできます。
このファンクションを使用すると、文字列の2進値を基にしたソートおよび比較ではなく、言語ソート基準を基にしたソートおよび比較を指定できます。次の例では、2つの値を含むテスト表を作成し、戻される値がNLSSORTファンクションによってどのように順序付けられるかを示します。
CREATE TABLE test (name VARCHAR2(15)); INSERT INTO test VALUES ('Gaardiner'); INSERT INTO test VALUES ('Gaberd'); INSERT INTO test VALUES ('Gaasten'); SELECT * FROM test ORDER BY name; NAME --------------- Gaardiner Gaasten Gaberd SELECT * FROM test ORDER BY NLSSORT(name, 'NLS_SORT = XDanish'); NAME --------------- Gaberd Gaardiner Gaasten
次の例では、比較操作でのNLSSORTファンクションの使用方法を示します。
SELECT * FROM test WHERE name > 'Gaberd'; no rows selected SELECT * FROM test WHERE NLSSORT(name, 'NLS_SORT = XDanish') > NLSSORT('Gaberd', 'NLS_SORT = XDanish'); NAME --------------- Gaardiner Gaasten
同一の言語ソート基準を使用する比較操作で頻繁にNLSSORTを使用する場合、NLS_COMPパラメータ（データベース用か現行のセッション用のいずれか）にLINGUISTICを設定し、セッションのNLS_SORTパラメータに必要なソート基準を設定するとより効率的です。これによって、現行のセッション中のすべてのソート操作および比較操作において、デフォルトでそのソート基準が使用されるようになります。
ALTER SESSION SET NLS_COMP = 'LINGUISTIC'; ALTER SESSION SET NLS_SORT = 'XDanish'; SELECT * FROM test WHERE name > 'Gaberd'; NAME --------------- Gaardiner Gaasten
NLS_UPPERは、すべての文字を大文字にしてcharを戻します。
charおよび'nlsparam'は、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。戻される文字列は、charが文字データ型の場合はVARCHAR2データ型になり、charがLOBデータ型の場合はLOBになります。charと同じキャラクタ・セットの文字列が戻されます。
'nlsparam'の書式および用途は、NLS_INITCAPファンクションと同じです。
次の例では、すべての文字を大文字に変換して文字列を戻します。
SELECT NLS_UPPER ('groe') "Uppercase" FROM DUAL; Upper ----- GROE SELECT NLS_UPPER ('groe', 'NLS_SORT = XGerman') "Uppercase" FROM DUAL; Upperc ------ GROSSE
NTILEは分析ファンクションです。これは、順序付けられたデータセットをexprに指定した数のバケットに分割し、適切なバケット番号を各行に割り当てます。バケットには1〜exprの番号が付けられます。expr値は、パーティションごとに、正の定数に変換される必要があります。Oracleデータベースではexprは整数とみなされるため、この値が整数ではない定数の場合は整数に切り捨てられます。戻り値は、NUMBERです。
バケット内の行数は、最大で1異なります。残りの値（バケットで割った行数の余り）は、バケット1から順に、1行ずつ分割されます。
exprが行数より大きい場合、行数と等しい数のバケットに行が入れられ、余りのバケットは空になります。
exprには、NTILEまたは他の分析ファンクションを使用できません。分析ファンクションはネストできませんが、他の組込みファンクション式はexprで使用できます。
次の例では、部門100のoe.employees表のsalary列の値を4つのバケットに分割します。この部門のsalary列には6つの値が存在するため、2つの余分な値（6を4で割った余り）は、バケット1および2に割り当てられます。そのため、バケット1および2は、バケット3または4より値が1つ多くなります。
SELECT last_name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees WHERE department_id = 100; LAST_NAME SALARY QUARTILE ------------------------- ---------- ---------- Greenberg 12000 1 Faviet 9000 1 Chen 8200 2 Urman 7800 2 Sciarra 7700 3 Popp 6900 4
NULLIFは、expr1とexpr2を比較します。同じである場合は、NULLを戻します。異なる場合は、expr1を戻します。expr1には、リテラルNULLを指定できません。
両方の引数が数値データ型である場合、Oracleデータベースは、数値の優先順位が高い方の引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。2つの引数が数値ではない場合、それらのデータ型が同じである必要があります。データ型が異なる場合、Oracleはエラーを戻します。
NULLIFファンクションは、次のCASE式と論理的に同じです。
CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
次の例では、サンプル・スキーマhrから、雇用後に職種が変更した従業員を検索します。これは、employees表の現行のjob_idがjob_history表のjob_idと異なるかどうかによって識別されます。
SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID" FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY last_name; LAST_NAME Old Job ID ------------------------- ---------- De Haan AD_VP Hartstein MK_MAN Kaufling ST_MAN Kochhar AD_VP Kochhar AD_VP Raphaely PU_MAN Taylor SA_REP Taylor Whalen AD_ASST Whalen
NUMTODSINTERVALは、nをINTERVAL DAY TO SECONDリテラルに変換します。引数nには、任意のNUMBER値か、またはNUMBER値に暗黙的に変換可能な式を指定できます。引数interval_unitのデータ型は、CHAR、VARCHAR2、NCHARまたはNVARCHAR2です。interval_unitの値にはnの単位を指定します。この値は次の文字列値のいずれかである必要があります。
interval_unitでは、大/小文字は区別されません。カッコ内の先行値および後続値は無視されます。デフォルトでは、戻り値の精度は9です。
次の例では、COUNT分析ファンクションにNUMTODSINTERVALを使用し、各従業員について、雇用開始日から過去100日以内に同じマネージャの部下として配属された従業員の人数を計算します。分析ファンクションの構文の詳細は、「分析ファンクション」を参照してください。
SELECT manager_id, last_name, hire_date, COUNT(*) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count FROM employees; MANAGER_ID LAST_NAME HIRE_DATE T_COUNT ---------- ------------------------- --------- ---------- 100 Kochhar 21-SEP-89 1 100 De Haan 13-JAN-93 1 100 Raphaely 07-DEC-94 1 100 Kaufling 01-MAY-95 1 100 Hartstein 17-FEB-96 1 . . . 149 Grant 24-MAY-99 1 149 Johnson 04-JAN-00 1 201 Goyal 17-AUG-97 1 205 Gietz 07-JUN-94 1 King 17-JUN-87 1
NUMTOYMINTERVALは、数値nをINTERVAL YEAR TO MONTHリテラルに変換します。引数nには、任意のNUMBER値か、またはNUMBER値に暗黙的に変換可能な式を指定できます。引数interval_unitのデータ型は、CHAR、VARCHAR2、NCHARまたはNVARCHAR2です。interval_unitの値にはnの単位を指定します。この値は次の文字列値のいずれかである必要があります。
interval_unitでは、大/小文字は区別されません。カッコ内の先行値および後続値は無視されます。デフォルトでは、戻り値の精度は9です。
次の例では、SUM分析ファンクションにNUMTOYMINTERVALを使用し、各従業員について、その従業員の雇用日から過去1年の間に雇用された従業員の給与の合計を計算します。分析ファンクションの構文の詳細は、「分析ファンクション」を参照してください。
SELECT last_name, hire_date, salary, SUM(salary) OVER (ORDER BY hire_date RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal FROM employees; LAST_NAME HIRE_DATE SALARY T_SAL ------------------------- --------- ---------- ---------- King 17-JUN-87 24000 24000 Whalen 17-SEP-87 4400 28400 Kochhar 21-SEP-89 17000 17000 . . . Markle 08-MAR-00 2200 112400 Ande 24-MAR-00 6400 106500 Banda 21-APR-00 6200 109400 Kumar 21-APR-00 6100 109400
NVLを使用すると、NULL（空白として戻される）を文字列に置換して問合せの結果に含めることができます。expr1がNULLの場合、NVLはexpr2を戻します。expr1がNULLでない場合、NVLはexpr1を戻します。
引数expr1およびexpr2は、任意のデータ型を持つことができます。2つの引数のデータ型が異なる場合、一方のデータ型が他方のデータ型に暗黙的に変換されます。暗黙的に変換できない場合、データベースはエラーを戻します。暗黙的な変換は、次のように実行されます。
expr1が文字データの場合、Oracleデータベースは2つの引数を比較する前にexpr2をexpr1のデータ型に変換し、expr1のキャラクタ・セットでVARCHAR2を戻します。
expr1が数値である場合、Oracleは数値の優先順位が最も高い引数を判断し、その引数のデータ型に他方の引数を暗黙的に変換して、そのデータ型を戻します。次の例では、従業員が歩合を受け取らない場合、従業員名および従業員の歩合「Not Applicable」を表示します。
SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable') "COMMISSION" FROM employees WHERE last_name LIKE 'B%' ORDER BY last_name; LAST_NAME COMMISSION ------------------------- ---------------------------------------- Baer Not Applicable Baida Not Applicable Banda .1 Bates .15 Bell Not Applicable Bernstein .25 Bissot Not Applicable Bloom .2 Bull Not Applicable
NVL2を使用すると、指定された式がNULLかどうかに基づく問合せによって戻される値を判断できます。expr1がNULLでない場合、NVL2はexpr2を戻します。expr1がNULLの場合、NVL2はexpr3を戻します。
引数expr1は、任意のデータ型を持つことができます。引数expr2およびexpr3は、LONG以外の任意のデータ型を持つことができます。
expr2とexpr3のデータ型が異なる場合、次のようになります。
expr2が文字データの場合、Oracleデータベースは、expr3がNULLの定数ではないかぎり、2つの引数を比較する前にexpr3をexpr2のデータ型に変換します。expr3がNULL定数の場合は、データ型の変換は行われません。Oracleは、expr2のキャラクタ・セットでVARCHAR2を戻します。
expr2が数値である場合、Oracleは、数値の優先順位が最も高い引数を判断し、その引数のデータ型に他方の引数を暗黙的に変換して、そのデータ型を戻します。次の例では、employeesのcommission_pct列がNULLかどうかによって、従業員の収入が給与と歩合か、または給与のみかを示します。
SELECT last_name, salary, NVL2(commission_pct, salary + (salary * commission_pct), salary) income FROM employees WHERE last_name like 'B%' ORDER BY last_name; LAST_NAME SALARY INCOME ------------------------- ---------- ---------- Baer 10000 10000 Baida 2900 2900 Banda 6200 6882 Bates 7300 8468 Bell 4000 4000 Bernstein 9500 11970 Bissot 3300 3300 Bloom 10000 12100 Bull 4100 4100
ORA_HASHファンクションは、指定された式のハッシュ値を計算します。このファンクションは、データのサブセットの分析や、ランダムな標本の生成などの操作に有効です。
expr引数には、Oracleデータベースでハッシュ値を計算するデータを指定します。exprに指定できるデータの型や長さの制限はありません。このデータは通常、列名です。
max_bucket引数には、ハッシュ・ファンクションから戻される最大バケット値を指定します。0（ゼロ）〜4294967295の任意の値を指定できます。デフォルトは4294967295です。
seed_value引数を指定すると、同じデータ・セットに対して様々な結果を生成できます。Oracleは、ハッシュ・ファンクションをexprとseed_valueの組合せに適用します。0（ゼロ）〜4294967295の任意の値を指定できます。デフォルトは0（ゼロ）です。
戻り値はNUMBERです。
次の例では、sh.sales表内の顧客IDと製品IDの各組合せに対してハッシュ値を作成し、そのハッシュ値を最大100個のバケットに分割して、最初のバケット（バケット0（ゼロ））でamount_sold値の合計を戻します。3つ目の引数（5）には、ハッシュ・ファンクションのシード値を指定しています。このシード値を変更すると、同じ問合せで異なるハッシュ結果を得ることができます。
SELECT SUM(amount_sold) FROM sales WHERE ORA_HASH(CONCAT(cust_id, prod_id), 99, 5) = 0; SUM(AMOUNT_SOLD) ---------------- 989431.14
PATHは、UNDER_PATHおよびEQUALS_PATH条件でのみ使用される補助ファンクションです。一次条件で指定されたリソースへの相対パスを戻します。
correlation_integerはNUMBER型の任意の整数で、この補助ファンクションをその一次条件と関連付けるために使用します。1未満の値は1として扱われます。
EQUALS_PATH条件およびUNDER_PATH条件でこの補助ファンクションを使用する例は、関連ファンクション「DEPTH」を参照してください。
PERCENT_RANKは、CUME_DIST（累積分布）ファンクションと似ています。PERCENT_RANKが戻す値の範囲は、0〜1（0および1を含む）です。すべての集合の最初の行のPERCENT_RANKは0（ゼロ）になります。戻り値はNUMBERです。
PERCENT_RANKは、ファンクションの引数および対応するソート指定によって識別される不確定な行rを計算し、行rのランクから1を引いて、集計グループ内の行の数で割ります。不確定な行rをOracleデータベースが集計する行のグループに挿入するように計算します。このファンクションの引数は、各集計グループ内の1つの不確定行を識別します。このため、すべての引数は、集計グループ内で定数式と評価される必要があります。定数引数式および集計のORDER BY句の式の位置は、一致します。このため、引数の数は同じであり、その型は互換性がある必要があります。
PERCENT_RANKは、行rに対して、rのランクから1引いた数を評価される行数（問合せ結果セット全体またはパーティション）より1少ない数で割ります。
次の例では、サンプル表hr.employeesから、給与が15,500ドルであり、歩合が5%である不確定な従業員のパーセント・ランクを計算します。
SELECT PERCENT_RANK(15000, .05) WITHIN GROUP (ORDER BY salary, commission_pct) "Percent-Rank" FROM employees; Percent-Rank ------------ .971962617
次の例では、従業員ごとに、部門内での給与のパーセント・ランクを計算します。
SELECT department_id, last_name, salary, PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr FROM employees ORDER BY pr, salary; DEPARTMENT_ID LAST_NAME SALARY PR ------------- ------------------------- ---------- ---------- 10 Whalen 4400 0 40 Marvis 6500 0 . . . 80 Vishney 10500 .176470588 50 Everett 3900 .181818182 30 Khoo 3100 .2 . . . 80 Johnson 6200 .941176471 50 Markle 2200 .954545455 50 Philtanker 2200 .954545455 50 Olson 2100 1 . . .
PERCENTILE_CONTは、連続分散モデルを想定する逆分散関数です。このファンクションは、パーセンタイル値およびソート指定を用い、そのソート指定に従ってそのパーセンタイル値に該当する補間された値を戻します。計算では、NULLは無視されます。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。また、引数の数値データ型と同じデータ型を戻します。
最初のexprは、パーセンタイル値であるため、0〜1の数値で評価します。このexprは、各集計グループ内の定数である必要があります。ORDER BY句には、Oracleが補間を実行できる型である数値または日時値の単一式を指定します。
PERCENTILE_CONTの結果は、順序付けされた後の値間の直線補間によって計算されます。集計グループで、パーセンタイル値（P）および行数（N）を使用すると、ソート指定に従って行を順序付けた後の行数を計算します。この行数（RN）は、計算式RN = (1+ (P*(N-1))に従って計算されます。集計ファンクションの最終結果は、行番号がCRN = CEILING(RN)およびFRN = FLOOR(RN)の行の値間の直線補間によって計算されます。
最終結果は次のとおりです。
If (CRN = FRN = RN) then the result is (value of expression from row at RN) Otherwise the result is (CRN - RN) * (value of expression for row at FRN) + (RN - FRN) * (value of expression for row at CRN)
PERCENTILE_CONTファンクションは、分析ファンクションとしても使用できます。その場合、OVER句には、query_partitioning_clauseのみを指定できます。各行に対して、各パーティション内の一連の値から、指定されたパーセンタイルに該当する値を戻します。
MEDIANファンクションは、パーセンタイル値がデフォルトで0.5に指定される特別なPERCENTILE_CONTです。詳細は、「MEDIAN」を参照してください。
次の例では、各部門の給与の中央値を計算します。
SELECT department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) "Median cont", PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) "Median disc" FROM employees GROUP BY department_id; DEPARTMENT_ID Median-cont Median-disc ------------- ----------- ----------- 10 4400 4400 20 9500 13000 30 2850 2900 40 6500 6500 50 3100 3100 60 4800 4800 70 10000 10000 80 8800 8800 90 17000 17000 100 8000 8200 110 10150 12000
PERCENTILE_CONTおよびPERCENTILE_DISCは、異なる結果を戻す場合があります。PERCENTILE_CONTは、直線補間後の結果を計算します。PERCENTILE_DISCは、集計された一連の値から値のみを戻します。この例に示すように、パーセンタイル値が0.5の場合、PERCENTILE_CONTは、偶数の要素を持つグループの中間の2つの値の平均を戻します。それに対して、PERCENTILE_DISCは、中間の2つの値の最初の値を戻します。奇数の要素を持つ集計グループでは、どちらのファンクションも中間の要素の値を戻します。
次の例では、0.5のパーセンタイル（Percent_Rank）に対応する部門60の中央値は4800です。部門30の給与にパーセンタイル0.5がないため、2900（パーセンタイル0.4）〜2800（パーセンタイル0.6）の範囲で2850に評価される中央値が補間される必要があります。
SELECT last_name, salary, department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) OVER (PARTITION BY department_id) "Percentile_Cont", PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) "Percent_Rank" FROM employees WHERE department_id IN (30, 60); LAST_NAME SALARY DEPARTMENT_ID Percentile_Cont Percent_Rank ------------- ---------- ------------- --------------- ------------ Raphaely 11000 30 2850 0 Khoo 3100 30 2850 .2 Baida 2900 30 2850 .4 Tobias 2800 30 2850 .6 Himuro 2600 30 2850 .8 Colmenares 2500 30 2850 1 Hunold 9000 60 4800 0 Ernst 6000 60 4800 .25 Austin 4800 60 4800 .5 Pataballa 4800 60 4800 .5 Lorentz 4200 60 4800 1
PERCENTILE_DISCは、不連続分散モデルを想定する逆分散関数です。このファンクションでは、パーセンタイル値およびソート指定を指定し、そのセットから要素を戻します。計算では、NULLは無視されます。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。また、引数の数値データ型と同じデータ型を戻します。
最初のexprは、パーセンタイル値であるため、0〜1の数値で評価します。このexprは、各集計グループ内の定数である必要があります。ORDER BY句には、ソート可能な型の単一式を指定します。
指定されたパーセンタイル値Pに対して、PERCENTILE_DISCは、ORDER BY句の式の値をソートし、P以上である（同じソート指定に従う）最小CUME_DIST値を持つ値を戻します。
「PERCENTILE_CONT」の例を参照してください。
次の例では、サンプル表hr.employeesの各従業員の給与の中央値不連続パーセンタイルを計算します。
SELECT last_name, salary, department_id, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) OVER (PARTITION BY department_id) "Percentile_Disc", CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary DESC) "Cume_Dist" FROM employees where department_id in (30, 60); LAST_NAME SALARY DEPARTMENT_ID Percentile_Disc Cume_Dist ------------- ---------- ------------- --------------- ---------- Raphaely 11000 30 2900 .166666667 Khoo 3100 30 2900 .333333333 Baida 2900 30 2900 .5 Tobias 2800 30 2900 .666666667 Himuro 2600 30 2900 .833333333 Colmenares 2500 30 2900 1 Hunold 9000 60 4800 .2 Ernst 6000 60 4800 .4 Austin 4800 60 4800 .8 Pataballa 4800 60 4800 .8 Lorentz 4200 60 4800 1
部門30の中央値の値は2900です。この値の対応するパーセンタイル（Cume_Dist）は、0.5以上の最小値です。部門60の中央値の値は4800です。この値の対応するパーセンタイルは、0.5以上の最小値です。
POWERは、n2をn1乗した値を戻します。底n2および指数n1は任意の数です。ただし、n2が負の場合、n1は整数である必要があります。
このファンクションは、引数として、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を取ります。いずれかの引数がBINARY_FLOATまたはBINARY_DOUBLEの場合、このファンクションはBINARY_DOUBLEを戻します。それ以外の場合、NUMBERを戻します。
次の例では、3の2乗を戻します。
SELECT POWER(3,2) "Raised" FROM DUAL; Raised ---------- 9
POWERMULTISETは、入力としてネストした表を取り、入力されたネストした表のすべての空でないサブセットを含む、ネストした表のネストした表（サブ多重集合という）を戻します。
exprには、ネストした表に評価される任意の式を指定できます。
exprがNULLである場合、OracleデータベースはNULLを戻します。
exprがネストした表である場合、Oracleはエラーを戻します。
まず、cust_address_tab_typeデータ型のネストした表であるデータ型を作成します。
CREATE TYPE cust_address_tab_tab_typ AS TABLE OF cust_address_tab_typ;
次に、POWERMULTISETファンクションを使用して、customers_demo表から、ネストした表の列cust_address_ntabを選択します。
SELECT CAST(POWERMULTISET(cust_address_ntab) AS cust_address_tab_tab_typ) FROM customers_demo; CAST(POWERMULTISET(CUST_ADDRESS_NTAB) AS CUST_ADDRESS_TAB_TAB_TYP) (STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) ------------------------------------------------------------------ CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP ('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'))) CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP ('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US'))) CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP ('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'))) CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP ('6445 Bay Harbor Ln', '46254', 'Indianapolis', 'IN', 'US'))) . . .
前述の例では、customers_demo表、およびデータを含むネストした表の列が必要です。この表およびネストした表の列を作成する方法については、「MULTISET演算子」を参照してください。
POWERMULTISET_BY_CARDINALITYは、入力としてネストした表およびカーディナリティを取り、指定されたカーディナリティを持つネストした表のすべての空でないサブセットを含む、ネストした表のネストした表（サブ多重集合という）を戻します。
exprには、ネストした表に評価される任意の式を指定できます。
cardinalityには、任意の整数を指定できます。
exprがNULLである場合、OracleデータベースはNULLを戻します。
exprがネストした表である場合、Oracleはエラーを戻します。
まず、ネストした表のすべての行内の要素を複製し、ネストした表の行のカーディナリティを2に増やします。
UPDATE customers_demo SET cust_address_ntab = cust_address_ntab MULTISET UNION cust_address_ntab;
次に、POWERMULTISET_BY_CARDINALITYファンクションを使用して、customers_demo表から、ネストした表の列cust_address_ntabを選択します。
SELECT CAST(POWERMULTISET_BY_CARDINALITY(cust_address_ntab, 2) AS cust_address_tab_tab_typ) FROM customers_demo;
CAST(POWERMULTISET_BY_CARDINALITY(CUST_ADDRESS_NTAB,2) AS CUST_ADDRESS_TAB_TAB_TYP)
(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
----------------------------------------------------------------------------------------
CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP
(CUST_ADDRESS_TYP('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'),
CUST_ADDRESS_TYP('514 W Superior St', '46901', 'Kokomo', 'IN', 'US')))
CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP
(CUST_ADDRESS_TYP('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US'),
CUST_ADDRESS_TYP('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US')))
CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP
(CUST_ADDRESS_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'),
CUST_ADDRESS_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US')))
. . .
前述の例では、customers_demo表、およびデータを含むネストした表の列が必要です。この表およびネストした表の列を作成する方法については、「MULTISET演算子」を参照してください。
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIで作成したモデルで使用するためのものです。このファンクションは、モデルの最適な予測を戻します。戻されるデータ型は、モデルの作成中に使用するターゲット値の型によって異なります。回帰モデルの場合、このファンクションは期待値を戻します。
作成時にモデルに関連付けられたコスト・マトリックスを考慮して、スコアリングを実行する必要があることを示すには、COST MODELを指定します。このようなコスト・マトリックスが存在しない場合は、エラーが戻されます。COST MODEL句は、決定ツリーの分類モデルのみに関連します。
COST MODEL句を指定しない場合、最適な予測は最も高い確率を持つターゲット・クラスになります。2つ以上のクラスが最も高い確率にある場合、その内の1つのクラスが選択されます。
これは、モデルの作成時に提供された予測子をマップします。USING *を指定すると、基礎となる入力（表、ビューなど）から取り出すことができる列と式にすべての予測子がマップされます。
mining_attribute_clauseで指定すると、余分な式が自動的に無視されます。
次の例では、提携カードを使用している可能性のある顧客の平均年齢を性別ごとに戻します。PREDICTIONファンクションでは、cust_marital_status、educationおよびhousehold_sizeの予測子のみを考慮します。
この例と前提条件のデータ・マイニング操作（ビューの作成など）は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmdtdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
SELECT cust_gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age FROM mining_data_apply_v WHERE PREDICTION(DT_SH_Clas_sample COST MODEL USING cust_marital_status, education, household_size) = 1 GROUP BY cust_gender ORDER BY cust_gender; C CNT AVG_AGE - ---------- ---------- F 170 38 M 685 42
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIで作成した決定ツリーの分類モデルで使用するためのものです。このファンクションは、他の型のモデルに対しては無効です。また、指定した予測のコストのメジャーをOracleのNUMBERとして戻します。
オプションのclassパラメータを指定すると、このファンクションは指定したクラスのコストを戻します。classパラメータを指定しない場合、最適な予測に関連付けられたコストが戻されます。この形式とPREDICTIONファンクションを組み合せて使用すると、予測値とコストの最適な組合せを取得できます。
COST MODELは、作成時にモデルに関連付けられたコスト・マトリックスを考慮して、スコアリングを実行する必要があることを示します。このようなコスト・マトリックスが存在しない場合は、エラーが戻されます。
mining_attribute_clauseは、PREDICTIONファンクションと同様に動作します。詳細は、「mining_attribute_clause」を参照してください。
次の例では、イタリア在住で、提携カードの使用を薦める上で最もコストが低い顧客を10人検索します。
この例と前提条件のデータ・マイニング操作は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmdtdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
WITH cust_italy AS ( SELECT cust_id FROM mining_data_apply_v WHERE country_name = 'Italy' ORDER BY PREDICTION_COST(DT_SH_Clas_sample, 1 COST MODEL USING *) ASC, 1 ) SELECT cust_id FROM cust_italy WHERE rownum < 11; CUST_ID ---------- 100081 100179 100185 100324 100344 100554 100662 100733 101250 101306 10 rows selected.
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIで作成した決定ツリー・モデルと単一機能のAdaptive Bayes Network（ABN）モデルで使用するためのものです。このファンクションは、入力行のスコアリングに関連するモデル固有の情報を含むXML文字列を戻します。今回のリリースでは、戻り値は次の書式になります。
<Node id= "integer"/>
ここで、integerはデータ・マイニングのツリー・ノードの識別子です。出力の形式は、変更される可能性があります。今後のリリースでは、追加の予測情報を提供するように拡張される可能性があります。
mining_attribute_clauseは、PREDICTIONファンクションと同様に動作します。詳細は、「mining_attribute_clause」を参照してください。
次の例では、DT_SH_Clas_sample決定ツリー・モデルの関連する予測子であるmining_data_apply_vビューから、すべての属性を使用します。テクニカル・サポートで働く25才未満の顧客の場合、DT_SH_Clas_sampleモデルを持つレコードのスコアリングの結果からツリー・ノードが戻されます。
この例と前提条件のデータ・マイニング操作（ビューの作成など）は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmdtdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
SELECT cust_id, education, PREDICTION_DETAILS(DT_SH_Clas_sample using *) treenode FROM mining_data_apply_v WHERE occupation = 'TechSup' AND age < 25 ORDER BY cust_id; CUST_ID EDUCATION TREENODE ---------- --------------------- ------------------------- 100234 < Bach. <Node id="21"/> 100320 < Bach. <Node id="21"/> 100349 < Bach. <Node id="21"/> 100419 < Bach. <Node id="21"/> 100583 < Bach. <Node id="13"/> 100657 HS-grad <Node id="21"/> 101171 < Bach. <Node id="21"/> 101225 < Bach. <Node id="21"/> 101338 < Bach. <Node id="21"/> 9 rows selected.
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIで作成した分類モデルで使用するためのものです。このファンクションは、他の型のモデルに対しては無効です。また、指定した予測の確率をOracleのNUMBERとして戻します。
オプションのclassパラメータを指定すると、このファンクションは指定したクラスの確率を戻します。これは、指定したターゲット・クラス値の選択に関連付けられた確率と同じです。
classパラメータを指定しない場合、最適な予測に関連付けられた確率が戻されます。この形式とPREDICTIONファンクションを組み合せて使用すると、予測値と確率の最適な組合せを取得できます。
mining_attribute_clauseは、PREDICTIONファンクションと同様に動作します。詳細は、「mining_attribute_clause」を参照してください。
次の例では、提携カードを使用している可能性が最も高いイタリア在住の顧客を10人戻します。
この例と前提条件のデータ・マイニング操作（ビューの作成など）は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmdtdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
SELECT cust_id FROM ( SELECT cust_id FROM mining_data_apply_v WHERE country_name = 'Italy' ORDER BY PREDICTION_PROBABILITY(DT_SH_Clas_sample, 1 USING *) DESC, cust_id) WHERE rownum < 11; CUST_ID ---------- 100081 100179 100185 100324 100344 100554 100662 100733 101250 101306 10 rows selected.
このファンクションは、DBMS_DATA_MININGパッケージまたはOracle Data Mining Java APIを使用して作成した分類モデルで使用するためのものです。このファンクションは、他の型のモデルに対しては無効です。このファンクションは、複数クラスの分類の使用例で、すべてのクラスを含むオブジェクトのVARRAYを戻します。オブジェクト・フィールドには、PREDICTION、PROBABILITYおよびCOSTの名前が付きます。PREDICTIONフィールドのデータ型は、モデルの作成中に使用するターゲット値の型によって異なります。他の2つのフィールドは、両方ともOracleのNUMBERになります。要素は最適な予測から最低の予測の順序で戻されます。
bestNの場合、戻されたターゲット・クラスを最も高い確率を持つNに制限するには、正の整数を指定します。複数のクラスがN番目の値にあっても、N個の値のみが戻されます。cutoffのみでフィルタ処理するには、このパラメータにNULLを指定します。
cutoffには、NUMBER値を指定し、戻されるターゲット・クラスを、指定したコスト値以下のコストを持つターゲット・クラスに制限します。bestNにNULLを指定すると、cutoffのみでフィルタ処理できます。bestNとcutoffの両方に値を指定すると、戻される予測は、bestNであり、確率（またはCOST MODEL指定時のコスト）がしきい値を超えるもののみに制限されます。
COST MODELを指定します。このようなコスト・マトリックスが存在しない場合は、エラーが戻されます。COST MODELを指定すると、bestNとcutoffの両方は、予測の確率ではなく予測コストに関して処理されます。つまり、bestNは、結果をNの最適（最低）コストを持つターゲット・クラスに制限し、cutoffは、ターゲット・クラスを、指定したカットオフ以下のコストを持つターゲット・クラスに制限します。
この句を指定すると、コレクションの各オブジェクトは、予測値を含むスカラー値（データ型はモデルの作成時に使用されるターゲット値の型によって異なる）、予測確率および予測コスト（両方ともOracleのNUMBER）になります。
COST MODELを指定しない場合、VARRAYの各オブジェクトは、予測値と予測確率を含むスカラーの組になります。戻されるデータ型は、前述の説明と同様です。
mining_attribute_clauseは、PREDICTIONファンクションと同様に動作します。詳細は、「mining_attribute_clause」を参照してください。
次の例では、提携カードの使用および不使用の可能性とコストを、10人の顧客それぞれで示します。この例はバイナリ・ターゲットを持ちますが、このような問合せは「Low」、「Med」、「High」などの複数クラスの分類でも有効です。
この例と前提条件のデータ・マイニング操作は、デモ・ファイル$ORACLE_HOME/rdbms/demo/dmdtdemo.sqlで確認できます。データ・マイニングのデモ・ファイルの一般情報は、『Oracle Data Mining管理者ガイド』を参照してください。次に、このファンクションの構文の使用例を示します。
SELECT T.cust_id, S.prediction, S.probability, S.cost FROM (SELECT cust_id, PREDICTION_SET(dt_sh_clas_sample COST MODEL USING *) pset FROM mining_data_apply_v WHERE cust_id < 100011) T, TABLE(T.pset) S ORDER BY cust_id, S.prediction; CUST_ID PREDICTION PROBABILITY COST ---------- ---------- ----------- ----- 100001 0 .96682 .27 100001 1 .03318 .97 100002 0 .74038 2.08 100002 1 .25962 .74 100003 0 .90909 .73 100003 1 .09091 .91 100004 0 .90909 .73 100004 1 .09091 .91 100005 0 .27236 5.82 100005 1 .72764 .27 100006 0 1.00000 .00 100006 1 .00000 1.00 100007 0 .90909 .73 100007 1 .09091 .91 100008 0 .90909 .73 100008 1 .09091 .91 100009 0 .27236 5.82 100009 1 .72764 .27 100010 0 .80808 1.54 100010 1 .19192 .81 20 rows selected.
PRESENTNNVファンクションは、SELECT文のmodel_clauseでのみ、およびモデル・ルールの右側でのみ使用できます。このファンクションは、cell_referenceが存在しNULLではない場合、model_clauseを実行する前にexpr1を戻します。それ以外の場合はexpr2を戻します。
次の例では、2002年のマウス・パッドの売上を含む行が存在し、その売上値がNULLではない場合、その売上値を変更しません。その行が存在し、売上値がNULLの場合、その売上値を10に設定します。その行が存在しない場合、売上値を10に設定して行を作成します。
SELECT country, prod, year, s FROM sales_view_ref MODEL PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale s) IGNORE NAV UNIQUE DIMENSION RULES UPSERT SEQUENTIAL ORDER ( s['Mouse Pad', 2002] = PRESENTNNV(s['Mouse Pad', 2002], s['Mouse Pad', 2002], 10) ) ORDER BY country, prod, year; COUNTRY PROD YEAR S ---------- ----------------------------------- -------- --------- France Mouse Pad 1998 2509.42 France Mouse Pad 1999 3678.69 France Mouse Pad 2000 3000.72 France Mouse Pad 2001 3269.09 France Mouse Pad 2002 10 France Standard Mouse 1998 2390.83 France Standard Mouse 1999 2280.45 France Standard Mouse 2000 1274.31 France Standard Mouse 2001 2164.54 Germany Mouse Pad 1998 5827.87 Germany Mouse Pad 1999 8346.44 Germany Mouse Pad 2000 7375.46 Germany Mouse Pad 2001 9535.08 Germany Mouse Pad 2002 10 Germany Standard Mouse 1998 7116.11 Germany Standard Mouse 1999 6263.14 Germany Standard Mouse 2000 2637.31 Germany Standard Mouse 2001 6456.13 18 rows selected.
この例では、ビューsales_view_refが必要です。このビューの作成方法については、「例」を参照してください。
PRESENTVファンクションは、SELECT文のmodel_clauseでのみ、およびモデル・ルールの右側でのみ使用できます。このファンクションは、cell_referenceが存在する場合、model_clauseを実行する前にexpr1を戻します。それ以外の場合はexpr2を戻します。
次の例では、2000年のマウス・パッドの売上を含む行が存在する場合、2001年のマウス・パッドの売上を2000年のマウス・パッドの売上値に設定します。その行