OLAP 指定
OLAP (On-Line Analytical Processing) 関数には、照会の結果の中で、 ランキング、行番号、および既存の集約関数情報をスカラー値で戻す機能があります。
- 1 ARRAY_AGG, CUME_DIST, and PERCENT_RANK are not supported as an aggregate function in aggregation-specification (SQLSTATE 42887).
OLAP 関数は、select-list の式、 または select-statement の ORDER BY 節に組み込むことができます (SQLSTATE 42903)。 OLAP 関数は、XMLQUERY または XMLEXISTS 式への引数内で使用することはできません (SQLSTATE 42903)。 OLAP 関数を集約関数の引数として使うことはできません (SQLSTATE 42607)。 OLAP 関数を適用したときの照会の結果は、その OLAP 関数の入った最も内側の副選択の結果表です。
OLAP 関数を指定するときには、関数を適用する行を定義したり、その順序を定義する枠が指定されます。 該当する行を集約関数とともに使用すると、その行をさらに詳細化して、 現在行との相対関係で、その前後の行範囲または行数として扱うことができます。 例えば、月単位のパーティションでは、直前の四半期の平均を計算することができます。
- OLAP ウィンドウ内の現在行より前の行数 (現在行を含む) を、OLAP ウィンドウ内の行数で除算する。
結果のデータ・タイプは DECFLOAT(34) です。 結果が NULL 値になることはありません。
- OLAP ウィンドウの現在行の RANK から 1 を引いたものを、OLAP ウィンドウ内の行数から 1 を引いたもので除算する。
結果のデータ・タイプは DECFLOAT(34) です。 結果が NULL 値になることはありません。
ランキング関数は、枠内の行の序数ランクを計算します。 それぞれの枠内での順序がはっきりしていない行は、同位に割り当てられます。 ランキングの結果については、重複する値の結果の数値にギャップがあってもなくても定義できます。
RANK を指定すると、該当行に先行する行数に 1 を足した数で、行のランクが定義されます。 したがって、順序がはっきりしていない行が 2 行以上あると、 通しランク番号には、1 つ以上のギャップができます。
DENSE_RANK (または DENSERANK) を指定すると、順序の明確な先行行数に 1 を足して行のランクが定義されます。 したがって、通しランク番号にはギャップはありません。
ROW_NUMBER (または ROWNUMBER) 関数は、最初の行を 1 行目とする順序付けで定義された枠内の行の通し番号を計算します。 枠内で ORDER BY 節を指定していない場合、 (SELECT ステートメントの ORDER BY 節に基づくのではなく) 副選択で戻されたとおりに、 任意の順番で行に行番号が割り当てられます。
fetch-clause が ROW_NUMBER 関数と共に使用される場合、行番号が順序どおりに表示されないことがあります。 fetch-clause は、結果セット (ROW_NUMBER 割り当てを含む) が生成された後に適用されます。そのため、行番号の順序が結果セットの順序と同じでない場合、割り当てられた番号の一部がシーケンスから欠落することがあります。
RANK、DENSE_RANK、または ROW_NUMBER の結果のデータ・タイプは BIGINT です。 結果が NULL 値になることはありません。
- 数値タイル
- 分位の数を指定する式。 この式は、数値、CHAR、または VARCHAR の組み込みデータ・タイプの値を戻す必要があります。 Unicode データベースでは、 式は GRAPHIC または VARGRAPHIC のデータ・タイプにすることもできます。 CHAR、VARCHAR、GRAPHIC、および VARGRAPHIC は、暗黙的キャストを介してサポートされます。 SMALLINT、INTEGER、または BIGINT のいずれでもない式は、関数の評価の前に BIGINT にキャストされます。 値は 0 より大きくなければなりません (SQLSTATE 22014)。 この式は、定数、変数、または定数か変数のキャストでなければなりません (SQLSTATE 42601)。
暗黙的キャストが行われた後、NTILE の結果のデータ・タイプは、num-tiles のデータ・タイプと同じデータ・タイプになります。 引数が NULL になる可能性がある場合、結果も NULL になる可能性があります。 引数が NULL の場合、結果は NULL 値になります。
NTILE 関数は、OLAP ウィンドウ内の並べられた行を分位数 num-tiles 個に分割し、特定の行の分位ランクを計算して 1 から MIN(n, num-tiles) までの値を戻します。ここで、n は OLAP ウィンドウ内の行数です。 n が num-tiles で割り切れる場合、OLAP ウィンドウ内の行は、(n / num-tiles) 行ずつ含む、分位数 num-tiles 個の分位に分けられます。 割り切れない場合、1 から MOD(n, num-tiles) までの分位には、それぞれ (n / num-tiles + 1) 行が割り当てられ、(MOD(n, num-tiles) + 1) から num-tiles までの分位には、それぞれ (n / num-tiles) 行が割り当てられます。 結果は、現在行が相当する分位ランクです。
行を分位数に分割する際に、同じソート・キーは考慮されません。 同じソート・キーを持つ行は、それらのソート・キーの確定的でない順序に基づいて、別々の分位に割り当てられる可能性があります。 したがって、NTILE は決定論的な関数ではありません。
LAG 関数は、現在の行から offset 行前にある行の式の値を戻します。 offset は、正の整数定数でなければなりません (SQLSTATE 42815)。 0 の offset 値は、現在行を意味します。 window-partition-clause が指定されている場合、 offset とは現在のパーティションに含まれる、 現在の行から offset 行前のことです。 offset が指定されていない場合、値 1 が使用されます。 default-value (これは式であることが可能) が指定されている場合、 オフセットが現在のパーティションの有効範囲を超える場合にその値が戻されます。 指定されていない場合は、NULL 値が戻されます。 'IGNORE NULLS' が指定されている場合、行の式値が NULL 値であるすべての行は計算に算入されません。 'IGNORE NULLS' が指定されていて、すべての行が NULL の場合、 default-value (または default-value が指定されていない場合には NULL 値) が戻されます。
LEAD 関数は、現在の行から offset 行後にある行の式の値を戻します。 offset は、正の整数定数でなければなりません (SQLSTATE 42815)。 0 の offset 値は、現在行を意味します。 window-partition-clause が指定されている場合、 offset とは現在のパーティションに含まれる、 現在の行から offset 行後のことです。 offset が指定されていない場合、値 1 が使用されます。 default-value (これは式であることが可能) が指定されている場合、 オフセットが現在のパーティションの有効範囲を超える場合にその値が戻されます。 指定されていない場合は、NULL 値が戻されます。 'IGNORE NULLS' が指定されている場合、行の式値が NULL 値であるすべての行は計算に算入されません。 'IGNORE NULLS' が指定されていて、すべての行が NULL の場合、 default-value (または default-value が指定されていない場合には NULL 値) が戻されます。
FIRST_VALUE 関数は、OLAP ウィンドウ内の最初の行の式値を戻します。 'IGNORE NULLS' が指定されている場合、行の式値が NULL 値であるすべての行は計算に算入されません。 'IGNORE NULLS' が指定されていて、OLAP ウィンドウ内のすべての値が NULL の場合、 FIRST_VALUE は NULL 値を戻します。
LAST_VALUE 関数は、OLAP ウィンドウ内の最後の行の式の値を戻します。 'IGNORE NULLS' が指定されている場合、行の式値が NULL 値であるすべての行は計算に算入されません。 'IGNORE NULLS' が指定されていて、OLAP ウィンドウ内のすべての値が NULL の場合、 LAST_VALUE は NULL 値を戻します。
FIRST_VALUE、LAG、LAST_VALUE、および LEAD の結果のデータ・タイプは、 式のデータ・タイプとなります。 結果は NULL 値の場合もあります。
- expression
- OLAP ウィンドウ内の現在行を指定する式。 式は組み込みデータ・タイプの値を戻す必要があります。(SQLSTATE 42884)。 n 行目
- 戻す OLAP ウィンドウの行を指定する式。 式は数値、CHAR、または VARCHAR の組み込みデータ・タイプの値を戻す必要があります。 Unicode データベースでは、 式は GRAPHIC または VARGRAPHIC のデータ・タイプにすることもできます。 CHAR、VARCHAR、GRAPHIC、および VARGRAPHIC は、暗黙的キャストを介してサポートされます。 SMALLINT、INTEGER、または BIGINT のいずれでもない式は、関数の評価の前に BIGINT にキャストされます。 値は 0 より大きくなければなりません (SQLSTATE 22016)。 この式は、定数、変数、または定数か変数のキャストでなければなりません (SQLSTATE 428I9)。
- FROM FIRST または FROM LAST
- nth-row の適用方法を指定します。 FROM FIRST を指定する場合、nth-row は、OLAP ウィンドウ内の最初の行から順方向にカウントするものとして扱われます。 FROM LAST を指定する場合、nth-row は、OLAP ウィンドウ内の最後の行から逆方向にカウントするものとして扱われます。
- RESPECT NULLS または IGNORE NULLS
- OLAP ウィンドウ内の NULL 値を処理する方法を指定します。 RESPECT NULLS を指定した場合、行の式値が NULL 値であるすべての行が計算に含められます。 IGNORE NULLS が指定されている場合、行の式値が NULL 値であるすべての行は計算に算入されません。
NTH_VALUE の結果のデータ・タイプは、expression のデータ・タイプと同じです。
結果は NULL 値の場合もあります。 nth-row が NULL の場合、結果は NULL 値です。 OLAP ウィンドウ内の行の数 (RESPECT NULLS を指定した場合は NULL 値を含み、IGNORE NULLS を指定した場合は NULL 値を除く) が nth-row の値未満の場合、結果は NULL 値です。
NTH_VALUE 関数は、非決定論的な関数です。window-order-clause が不要であり、window-order-clause を指定しても、同じソート・キーを持つ行の順序が確定的でないからです。
RATIO_TO_REPORT(expression) OVER (...)
CAST(expression AS DECFLOAT(34)) / SUM(expression) OVER(...) - PARTITION BY (パーティショニング式, ...)
- 関数を適用するときのパーティションを定義します。 partitioning-expression は、結果セットのパーティションを定義するときに使う式です。 partitioning-expression で参照されている各 column-name は、OLAP 仕様を含む副選択の結果表の列をはっきり参照するものでなければなりません (SQLSTATE 42702 または 42703)。 partitioning-expression には、スカラー全選択、XMLQUERY または XMLEXISTS 式 (SQLSTATE 42822)、決定論的でないか外部アクションを持つ関数または照会 (SQLSTATE 42845) を含めることはできません。
- window-order-clause
- ORDER BY (sort-key-expression, ...)
- OLAP 関数の値、 または window-aggregation-group-clause の ROW 値の意味を決める、 パーティション内の行の順序を定義します (照会結果セットの順序を定義するものではありません)。 ソート・キー式
- 枠のパーティション内の行の順序を定義するのに使う式。 sort-key-expression で参照されている各 column-name は、OLAP 関数を含む副選択の結果セットの列をはっきり参照するものでなければなりません (SQLSTATE 42702 または 42703)。 sort-key-expression には、スカラー全選択、XMLQUERY または XMLEXISTS 式 (SQLSTATE 42822)、決定論的でないか外部アクションを持つ関数または照会 (SQLSTATE 42845) を含めることはできません。 この節は、RANK および DENSE_RANK 関数 (SQLSTATE 42601) で必要になります。
- ASC
- sort-key-expression の値を昇順に使用します。
- DESC
- sort-key-expression の値を降順に使用します。
- NULLS FIRST
- ウィンドウ配列において、ソート順序は、すべての非 NULL 値の前に NULL 値が置かれます。
- NULLS LAST
- ウィンドウ配列において、ソート順序は、すべての非 NULL 値の後に NULL 値が置かれます。
- ORDER OF 表指定子
- 表指定子 で使用されているのと同じ順序付けを、副選択の結果表にも適用することを指定します。 この節を指定する副選択の FROM 節内には、表指定子 に一致する表参照がなければなりません (SQLSTATE 42703)。 適用される順序は、ネストされた副選択 (または全選択) 内の ORDER BY 節の列が外部副選択 (または全選択) に入っていた場合、およびそれらの列が ORDER OF 節の代わりに指定された場合と同じです。
- window-aggregation-group-clause
- 行 R の集約グループは、(R のパーティションの行の順序付け内の) R に関連して定義されている行のセットです。 その節は集約グループを指定します。 この節が指定されない場合で、window-order-clause も指定されなければ、
集約グループはウィンドウ・パーティションのすべての行から構成されます。 このデフォルトは、RANGE (示したように) または ROWS を明示的に使用して指定できます。
window-order-clause が指定された場合、デフォルトの動作は window-aggregation-group-clause が指定されていない場合は異なります。 ウィンドウ集約グループは、window-order-clause によって定義されたウィンドウ・パーティションのウィンドウ順序付けで、R に先行する R のパーティションおよび R のピアである R のパーティションのすべての行で構成されます。
- 行
- 集約グループがカウント行によって定義されることを示します。
- RANGE
- 集約グループがソート・キーからのオフセットによって定義されることを示します。
- group-start
- 集約グループの開始点を指定します。 集約グループの終了は current row です。 group-start 節の仕様は、 "BETWEEN group-start AND CURRENT ROW" 形式の group-between 節と同じです。
- group-between
- ROWS または RANGE に基づいて、集約グループの開始および終了を指定します。
- group-end
- 集約グループの終了点を指定します。 集約グループの開始は current row です。 group-end 節の仕様は、 "BETWEEN CURRENT ROW AND group-end" 形式の group-between 節と同じです。
- UNBOUNDED PRECEDING
- current row の前のパーティション全体を組み込みます。 これは、ROWS または RANGE のいずれかと一緒に指定できます。 window-order-clause 内の複数の sort-key-expressions と一緒に指定することもできます。
- UNBOUNDED FOLLOWING
- current row に続くパーティション全体を組み込みます。 これは、ROWS または RANGE のいずれかと一緒に指定できます。 window-order-clause 内の複数の sort-key-expressions と一緒に指定することもできます。
- CURRENT ROW
- current row に基づいて、集約グループの開始および終了を指定します。 ROWS が指定された場合、current row が集約グループ境界です。 RANGE が指定された場合、集約グループ境界には、 current row と同じ値を sort-key-expressions として持つ行のセットが組み込まれます。 group-bound1 で value FOLLOWING が指定されている場合、 この節を group-bound2 で指定することはできません。
- unsigned-constant 先行
- current row の前の行の範囲または行数のいずれかを指定します。 ROWS が指定された場合、unsigned-constant は行数を示す、ゼロまたは正の整数でなければなりません。 RANGE が指定された場合、unsigned-constant のデータ・タイプは、 window-order-clause の sort-key-expression のタイプと互換性がなければなりません。 sort-key-expression は 1 つのみで、 sort-key-expression のデータ・タイプは減算を許可しなければなりません。 group-bound1 が CURRENT ROW または unsigned-constant FOLLOWING の場合、 この節を group-bound2 で指定することはできません。
- 符号なし定数 FOLLOWING
- current row の後の行の範囲または行数のいずれかを指定します。 ROWS が指定された場合、unsigned-constant は行数を示す、ゼロまたは正の整数でなければなりません。 RANGE が指定された場合、unsigned-constant のデータ・タイプは、 window-order-clause の sort-key-expression のタイプと互換性がなければなりません。 sort-key-expression は 1 つのみで、 sort-key-expression のデータ・タイプは加算を許可しなければなりません。
例
- 給与合計 (給与 + ボーナス) が $30,000 を超えている従業員のランキングを、
それぞれの給与合計に基づいて、姓の順に表示します。
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE WHERE SALARY+BONUS > 30000 ORDER BY LASTNAME結果をランキング順に並べる場合、ORDER BY LASTNAME を以下のように置き換えます。
またはORDER BY RANK_SALARYORDER BY RANK() OVER (ORDER BY SALARY+BONUS DESC) - それぞれの給与合計の平均に基づいて部門をランク付けします。
SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY, RANK() OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL FROM EMPLOYEE GROUP BY WORKDEPT ORDER BY RANK_AVG_SAL - それぞれの学歴に基づいて部門内で従業員をランク付けします。 部門内で同じランクの従業員が複数いた場合は、次のランキング値を増やさないようにします。
SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL, DENSE_RANK() OVER (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL FROM EMPLOYEE ORDER BY WORKDEPT, LASTNAME - 照会の結果に行番号を示します。
SELECT ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER, LASTNAME, SALARY FROM EMPLOYEE ORDER BY WORKDEPT, LASTNAME - 収入の多い上位 5 人をリストします。
ランクを WHERE 節で使うために、事前にそのランキングも含めた結果をまず計算するのに、ネストされた表の式が使われていることに注意してください。 共通表式も使われています。SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY FROM (SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE) AS RANKED_EMPLOYEE WHERE RANK_SALARY < 6 ORDER BY RANK_SALARY - 部門ごとに、従業員の給与をリストして、
各人の給与がその部門で次に給与の高い従業員と比較してどれほど少ないかを示します。
SELECT EMPNO, WORKDEPT, LASTNAME, FIRSTNME, JOB, SALARY, LEAD(SALARY, 1) OVER (PARTITION BY WORKDEPT ORDER BY SALARY) - SALARY AS DELTA_SALARY FROM EMPLOYEE ORDER BY WORKDEPT, SALARY - 従業員の給与を、同じタイプの仕事のために最初に雇用された従業員との相対関係で計算します。
SELECT JOB, HIREDATE, EMPNO, LASTNAME, FIRSTNME, SALARY, FIRST_VALUE(SALARY) OVER (PARTITION BY JOB ORDER BY HIREDATE) AS FIRST_SALARY, SALARY - FIRST_VALUE(SALARY) OVER (PARTITION BY JOB ORDER BY HIREDATE) AS DELTA_SALARY FROM EMPLOYEE ORDER BY JOB, HIREDATE - 2006 年 1 月中の株式 XYZ の平均終値を計算します。 指定の日に株式が取り引きされなかった場合、DAILYSTOCKDATA 表でのその終値は NULL 値になります。 株式が取り引きされなかった日に対して NULL 値を戻す代わりに、
COALESCE 関数および LAG 関数を使用して、株式が取り引きされた直近の日付での終値を戻すようにします。 直前の非 NULL の終値を検索することを、2006 年 1 月から 1 カ月前に制限します。
WITH V1(SYMBOL, TRADINGDATE, CLOSEPRICE) AS ( SELECT SYMBOL, TRADINGDATE, COALESCE(CLOSEPRICE, LAG(CLOSEPRICE, 1, CAST(NULL AS DECIMAL(8,2)), 'IGNORE NULLS') OVER (PARTITION BY SYMBOL ORDER BY TRADINGDATE) ) FROM DAILYSTOCKDATA WHERE SYMBOL = 'XYZ' AND TRADINGDATE BETWEEN '2005-12-01' AND '2006-01-31' ) SELECT SYMBOL, AVG(CLOSEPRICE) AS AVG FROM V1 WHERE TRADINGDATE BETWEEN '2006-01-01' AND '2006-01-31' GROUP BY SYMBOL - 2005 年中の株式 ABC および XYZ の 30 日移動平均を計算します。
WITH V1(SYMBOL, TRADINGDATE, MOVINGAVG30DAY) AS ( SELECT SYMBOL, TRADINGDATE, AVG(CLOSEPRICE) OVER (PARTITION BY SYMBOL ORDER BY TRADINGDATE ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) FROM DAILYSTOCKDATA WHERE SYMBOL IN ('ABC', 'XYZ') AND TRADINGDATE BETWEEN DATE('2005-01-01') - 2 MONTHS AND '2005-12-31' ) SELECT SYMBOL, TRADINGDATE, MOVINGAVG30DAY FROM V1 WHERE TRADINGDATE BETWEEN '2005-01-01' AND '2005-12-31' ORDER BY SYMBOL, TRADINGDATE - カーソル位置を定義する式を使用して、その位置よりも 50 行前のスライディング・ウィンドウを照会します。
SELECT DATE, FIRST_VALUE(CLOSEPRICE + 100) OVER (PARTITION BY SYMBOL ORDER BY DATE ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING) AS FV FROM DAILYSTOCKDATA ORDER BY DATE - それぞれの従業員について、特定の従業員より教育レベルが 1 低い、また 1 高い、同じ部署内の従業員を含む従業員のセットの平均給与を計算します。
SELECT WORKDEPT, EDLEVEL, SALARY, AVG(SALARY) OVER (PARTITION BY WORKDEPT ORDER BY EDLEVEL RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM EMPLOYEE ORDER BY WORKDEPT, EDLEVEL - 各従業員の給与の四分位数を計算します。
結果セットは、次のとおりです。SELECT EMPNO, SALARY, NTILE(4) OVER (ORDER BY SALARY) AS QUARTILE FROM EMPLOYEE ORDER BY SALARYEMPNO SALARY QUARTILE ------ ----------- ----------- 200340 31840.00 1 000290 35340.00 1 200330 35370.00 1 000310 35900.00 1 200310 35900.00 1 000280 36250.00 1 000270 37380.00 1 000300 37750.00 1 200240 37760.00 1 200120 39250.00 1 000320 39950.00 1 000230 42180.00 2 000340 43840.00 2 000170 44680.00 2 000330 45370.00 2 200280 46250.00 2 200010 46500.00 2 000260 47250.00 2 000240 48760.00 2 000250 49180.00 2 000120 49250.00 2 000220 49840.00 2 000190 50450.00 3 000180 51340.00 3 000150 55280.00 3 000200 57740.00 3 000160 62250.00 3 200170 64680.00 3 000110 66500.00 3 000210 68270.00 3 000140 68420.00 3 200140 68420.00 3 200220 69840.00 4 000060 72250.00 4 000130 73800.00 4 000050 80175.00 4 000100 86150.00 4 000090 89750.00 4 000020 94250.00 4 000070 96170.00 4 000030 98250.00 4 000010 152750.00 4 42 record(s) selected. - 次の例の照会は、最高給与を基準にして行を 3 つのバケットに分けています。 各バケットに入る値を示すために、最高給与を含めています。
照会の出力部分を次の表に示します。SELECT NTILE(3) OVER (ORDER BY MAX_SALARY) AS Bucket, MAX_SALARY FROM GOSALESDW.EMP_POSITION_DIM;表 1. 出力例 BUCKET MAX_SALARY 1 0.00 ... ... 1 35000.00 2 5000.00 ... ... 2 12000.00 3 13000.00 ... ... 3 301500.00 - 部門内の各従業員の給与の累積分布と相対パーセンタイル・ランクを求めます。
結果セットは、次のとおりです。SELECT EMPNO, WORKDEPT, SALARY, CAST(CUME_DIST() OVER (PARTITION BY WORKDEPT ORDER BY SALARY) AS DECIMAL(4,3)) AS CUME_DIST, CAST(PERCENT_RANK() OVER (PARTITION BY WORKDEPT ORDER BY SALARY) AS DECIMAL(4,3)) AS PERCENT_RANK FROM EMP ORDER BY WORKDEPT, SALARYEMPNO WORKDEPT SALARY CUME_DIST PERCENT_RANK ------ -------- ----------- --------- ------------ 200120 A00 39250.00 0.200 0.000 200010 A00 46500.00 0.400 0.250 000120 A00 49250.00 0.600 0.500 000110 A00 66500.00 0.800 0.750 000010 A00 152750.00 1.000 1.000 000020 B01 94250.00 1.000 0.000 000140 C01 68420.00 0.500 0.000 200140 C01 68420.00 0.500 0.000 000130 C01 73800.00 0.750 0.666 000030 C01 98250.00 1.000 1.000 000170 D11 44680.00 0.090 0.000 000220 D11 49840.00 0.181 0.100 000190 D11 50450.00 0.272 0.200 000180 D11 51340.00 0.363 0.300 000150 D11 55280.00 0.454 0.400 000200 D11 57740.00 0.545 0.500 000160 D11 62250.00 0.636 0.600 200170 D11 64680.00 0.727 0.700 000210 D11 68270.00 0.818 0.800 200220 D11 69840.00 0.909 0.900 000060 D11 72250.00 1.000 1.000 000270 D21 37380.00 0.142 0.000 200240 D21 37760.00 0.285 0.166 000230 D21 42180.00 0.428 0.333 000260 D21 47250.00 0.571 0.500 000240 D21 48760.00 0.714 0.666 000250 D21 49180.00 0.857 0.833 000070 D21 96170.00 1.000 1.000 000050 E01 80175.00 1.000 0.000 000290 E11 35340.00 0.142 0.000 000310 E11 35900.00 0.428 0.166 200310 E11 35900.00 0.428 0.166 000280 E11 36250.00 0.571 0.500 000300 E11 37750.00 0.714 0.666 200280 E11 46250.00 0.857 0.833 000090 E11 89750.00 1.000 1.000 200340 E21 31840.00 0.166 0.000 200330 E21 35370.00 0.333 0.200 000320 E21 39950.00 0.500 0.400 000340 E21 43840.00 0.666 0.600 000330 E21 45370.00 0.833 0.800 000100 E21 86150.00 1.000 1.000 42 record(s) selected. - 各従業員の給与を、部門内の最高給与および 2 番目に高い給与と比較します。
結果セットは、次のとおりです。SELECT WORKDEPT, SALARY, FIRST_VALUE(SALARY) OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS FIRST, NTH_VALUE(SALARY, 2) OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS SECOND FROM EMP ORDER BY WORKDEPT, SALARYWORKDEPT SALARY FIRST SECOND -------- ----------- ----------- -------- A00 39250.00 152750.00 66500.00 A00 46500.00 152750.00 66500.00 A00 49250.00 152750.00 66500.00 A00 66500.00 152750.00 66500.00 A00 152750.00 152750.00 66500.00 B01 94250.00 94250.00 - C01 68420.00 98250.00 73800.00 C01 68420.00 98250.00 73800.00 C01 73800.00 98250.00 73800.00 C01 98250.00 98250.00 73800.00 D11 44680.00 72250.00 69840.00 D11 49840.00 72250.00 69840.00 D11 50450.00 72250.00 69840.00 D11 51340.00 72250.00 69840.00 D11 55280.00 72250.00 69840.00 D11 57740.00 72250.00 69840.00 D11 62250.00 72250.00 69840.00 D11 64680.00 72250.00 69840.00 D11 68270.00 72250.00 69840.00 D11 69840.00 72250.00 69840.00 D11 72250.00 72250.00 69840.00 D21 37380.00 96170.00 49180.00 D21 37760.00 96170.00 49180.00 D21 42180.00 96170.00 49180.00 D21 47250.00 96170.00 49180.00 D21 48760.00 96170.00 49180.00 D21 49180.00 96170.00 49180.00 D21 96170.00 96170.00 49180.00 E01 80175.00 80175.00 - E11 35340.00 89750.00 46250.00 E11 35900.00 89750.00 46250.00 E11 35900.00 89750.00 46250.00 E11 36250.00 89750.00 46250.00 E11 37750.00 89750.00 46250.00 E11 46250.00 89750.00 46250.00 E11 89750.00 89750.00 46250.00 E21 31840.00 86150.00 45370.00 E21 35370.00 86150.00 45370.00 E21 39950.00 86150.00 45370.00 E21 43840.00 86150.00 45370.00 E21 45370.00 86150.00 45370.00 E21 86150.00 86150.00 45370.00 42 record(s) selected.