OLAP-function |--+-| ranking-function |-----+---------------------------------| +-| numbering-function |---+ '-| aggregation-function |-' ranking-function |---+-RANK ()-------+--OVER-------------------------------------> '-DENSE_RANK ()-' >----(--+------------------------------+------------------------> '-| window-partition-clause |--' >----| window-order-clause |--)---------------------------------| numbering-function |---ROW_NUMBER ()--OVER---(--+------------------------------+---> '-| window-partition-clause |--' >----+--------------------------+---)---------------------------| '-| window-order-clause |--' aggregation-function |---column-function--OVER---(--+------------------------------+-> '-| window-partition-clause |--' >----+--------------------------+-------------------------------> '-| window-order-clause |--' .-RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING--. >----+------------------------------------------------------------+---)-> '-| window-aggregation-group-clause |------------------------' >---------------------------------------------------------------| window-partition-clause .-,--------------------------. V | |---PARTITION BY-----partitioning-expression---+----------------| window-order-clause .-,-------------------------------. V .-ASC--. | |---ORDER BY-----sort-key-expression--+------+--+---------------| '-DESC-' window-aggregation-group-clause |---+-ROWS--+---+-| group-start |---+---------------------------| '-RANGE-' '-| group-between |-' group-start |---+-UNBOUNDED PRECEDING-----------+---------------------------| +-unsigned-constant--PRECEDING--+ '-CURRENT ROW-------------------' group-between |---BETWEEN--| group-bound1 |--AND--| group-bound2 |------------| group-bound1 |---+-UNBOUNDED PRECEDING-----------+---------------------------| +-unsigned-constant--PRECEDING--+ +-unsigned-constant--FOLLOWING--+ '-CURRENT ROW-------------------' group-bound2 |---+-UNBOUNDED FOLLOWING-----------+---------------------------| +-unsigned-constant--PRECEDING--+ +-unsigned-constant--FOLLOWING--+ '-CURRENT ROW-------------------'
OLAP (On-Line Analytical Processing) 関数には、照会の結果の中で、 ランキング、行番号、および既存の列関数情報をスカラー値で戻す機能があります。 OLAP 関数は、select-list の式、 または select-statement の ORDER BY 文節に組み込むことができます (SQLSTATE 42903)。 OLAP 関数を列関数の引き数として使うことはできません (SQLSTATE 42607)。 OLAP 関数を適用したときの照会の結果は、その OLAP 関数が含まれる、 最も内側の副選択の結果表です。
OLAP 関数を指定するときには、関数を適用する行を定義したり、 その順序を定義する枠が指定されます。 列関数とともに使用すると、該当する行をさらに洗練して、 現在行との相対関係で、その前後の行範囲または行数として扱うことができます。 たとえば、月単位の区分では、直前の四半期の平均を計算することができます。
ランキング関数は、枠内の行の序数ランクを計算します。 それぞれの枠内での順序がはっきりしていない行は、同位に割り当てられます。 ランキングの結果については、重複する値の結果の数値にギャップがあってもなくても定義できます。
RANK を指定すると、該当行に先行する行数に 1 を足した数で、行のランクが定義されます。 したがって、順序がはっきりしていない行が 2 行以上あると、 通しランク番号には、1 つ以上のギャップができます。
DENSE_RANK 36 を指定する場合、 順序のはっきりしている先行する行数に 1 を足した数で、行のランクが定義されます。 したがって、通しランク番号にはギャップはありません。
ROW_NUMBER 37 関数は、 最初の行を 1 行目としてみなす順序付けに基づいて定義される、枠内の行の通し行番号を計算します。 枠内で ORDER BY 文節を指定していない場合、 (SELECT ステートメントの ORDER BY 文節に基づくのではなく) 副選択で戻されたとおりに、 任意の順番で行に行番号が割り当てられます。
RANK、DENSE_RANK、または ROW_NUMBER の結果のデータ・タイプは BIGINT です。 結果がヌル値になることはありません。
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_SALARYまたは
ORDER 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
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結果を最初に計算する際、ランクを WHERE 文節で使う前に、そのランクが含まれた、 ネストされた表の式が使われていることに注意してください。 共通表式も使われています。