SQL 解説書

OLAP 関数

 
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 です。 結果がヌル値になることはありません。

PARTITION BY (partitioning-expression,...)
関数を適用するときの区分を定義します。 partitioning-expression は、結果セットの区分化を定義するときに使う式です。 partitioning-expression で参照されている各 column-name は、 OLAP 関数副選択ステートメントの結果セット列をはっきり参照するものでなければなりません (SQLSTATE 42702 または 42703)。 各 partitioning-expression の長さは、255 バイトを超えてはなりません (SQLSTATE 42907)。 partitioning-expression には、scalar-fullselect (SQLSTATE 42822) や、 可変の関数または外部処理を伴う関数 (SQLSTATE 42845) を含めることはできません。

ORDER BY (sort-key-expression,...)
OLAP 関数の値、 または window-aggregation-group-clause の ROW 値の意味を決める、 区分内の行の順序を定義します (照会結果セットの順序を定義するものではありません)。 sort-key-expression は、枠の区分に含まれる行の順序を定義するときに使う式です。 sort-key-expression で参照されている各 column-name は、 OLAP 関数を含む副選択の結果セットの列をはっきり参照するものでなければなりません (SQLSTATE 42702 または 42703)。 各 sort-key-expression の長さは、255 バイトを超えてはなりません (SQLSTATE 42907)。 sort-key-expression には、scalar-fullselect (SQLSTATE 42822) や、 可変の関数または外部処理を伴う関数 (SQLSTATE 42845) を含めることはできません。 この文節は、RANK および DENSE_RANK 関数 (SQLSTATE 42601) で必要になります。

ASC
sort-key-expression の値を昇順に使用します。 ヌル値は順序の最後になります。

DESC
sort-key-expression の値を降順に使用します。 ヌル値は順序の最初になります。

window-aggregation-group-clause
行 R の集約グループは一連の行であり、 R の区分の行の順序で R に対して定義されます。 この文節は、集約グループを指定します。

ROWS
カウント行ごとに集約グループが定義されることを示します。

RANGE
分類キーからの相対位置によって集約グループが定義されることを示します。

group-start
集約グループの開始点を指定します。 集約グループが終了するのは、現在行です。 group-start 文節を指定することは、 "BETWEEN group-start AND CURRENT ROW" の形式で group-between 文節を指定することと同じです。

group-between
ROWS か RANGE に基づいて集約グループの開始点と終了点を指定します。

UNBOUNDED PRECEDING
現在行より前の区分全体を組み込みます。 これは、ROWS または RANGE に基づいて指定できます。 また、window-order-clause 内で複数の sort-key-expression を使って、指定することも可能です。

UNBOUNDED FOLLOWING
現在行より後の区分全体を組み込みます。 これは、ROWS または RANGE に基づいて指定できます。 また、window-order-clause 内で複数の sort-key-expression を使って、指定することも可能です。

CURRENT ROW
集約グループの開始点または終了点を現在行として指定します。 group-bound1value FOLLOWING を指定している場合には、 group-bound2 にこの文節を指定することはできません。

value PRECEDING
現在行より前の行の範囲か行数を指定します。 ROWS が指定されている場合、value は行数を示す正の整数になります。 RANGE が指定されている場合、value のデータ・タイプは、 window-order-clause の sort-key-expression のタイプと比較できるものでなければなりません。 sort-key-expression は 1 つだけ指定でき、 sort-key-expression のデータ・タイプは減算できるものでなければなりません。 group-bound1 が CURRENT ROW または value FOLLOWING の場合には、 group-bound2 にこの文節を指定することはできません。

value FOLLOWING
現在行より後の行の範囲か行数を指定します。 ROWS が指定されている場合、value は行数を示す正の整数になります。 RANGE が指定されている場合、value のデータ・タイプは、 window-order-clause の sort-key-expression のタイプと比較できるものでなければなりません。 sort-key-expression は 1 つだけ指定でき、 sort-key-expression のデータ・タイプは加算できるものでなければなりません。

例:


脚注:

36
DENSE_RANK と DENSERANK は同義です。

37
ROW_NUMBER と ROWNUMBER は同義です。


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]