OLAP 指定

On-Line Analytical Processing (OLAP) 指定を使用すれば、ランキング、行番号付け、その他の集約関数情報を照会結果の中でスカラー値として戻すことができます。


OLAP-specification

>>-+-ordered-OLAP-specification-+------------------------------><
   +-numbering-specification----+   
   '-aggregation-specification--'   

ordered-OLAP-specification

|--+-lag-function------------+---------------------------------->
   +-lead-function-----------+   
   +-RANK--(--)--------------+   
   +-DENSE_RANK--(--)--------+   
   +-NTILE--(--expression--)-+   
   '-CUME_DIST--(--)---------'   

>--OVER--(--+-------------------------+--window-order-clause--)--|
            '-window-partition-clause-'                           

lag-function

                                                               .-RESPECT NULLS-.   
|--LAG--(--expression--+---------------------------------+--)--+---------------+--|
                       '-,--offset--+------------------+-'     '-IGNORE NULLS--'   
                                    '-,--default-value-'                           

lead-function

                                                                .-RESPECT NULLS-.   
|--LEAD--(--expression--+---------------------------------+--)--+---------------+--|
                        '-,--offset--+------------------+-'     '-IGNORE NULLS--'   
                                     '-,--default-value-'                           

window-partition-clause

                 .-,-----------------------.   
                 V                         |   
|--PARTITION BY----partitioning-expression-+--------------------|


window-order-clause

|--ORDER BY----------------------------------------------------->

   .-,----------------------------------------------------.   
   |                          .-ASC-.  .-NULLS LAST-.     |   
   V                        .-+-----+--+------------+-.   |   
>----+-sort-key-expression--+-------------------------+-+-+-----|
     |                      | .-ASC-.                 | |     
     |                      +-+-----+--NULLS FIRST----+ |     
     |                      |       .-NULLS FIRST-.   | |     
     |                      +-DESC--+-------------+---+ |     
     |                      '-DESC NULLS LAST---------' |     
     '-ORDER OF--table-designator-----------------------'     

numbering-specification

|--ROW_NUMBER--(--)--OVER--(--+-------------------------+--+---------------------+--)--|
                              '-window-partition-clause-'  '-window-order-clause-'      

aggregation-specification

|--+-aggregate-function------+--OVER--(--+-------------------------+-->
   '-OLAP-aggregate-function-'           '-window-partition-clause-'   

   .-RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING------------------.      
>--+----------------------------------------------------------------------------+--)--|
   +-ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING-------------------+      
   |                      .-RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW-. |      
   '-window-order-clause--+---------------------------------------------------+-'      
                          '-window-aggregation-group-clause-------------------'        

aggregate-function

|--+-AVG function-------------+---------------------------------|
   +-CORRELATION function-----+   
   +-COUNT function-----------+   
   +-COUNT_BIG function-------+   
   +-COVARIANCE function------+   
   +-COVARIANCE_SAMP function-+   
   +-MAX function-------------+   
   +-MEDIAN function----------+   
   +-MIN function-------------+   
   +-PERCENTILE_CONT function-+   
   +-PERCENTILE_DISC function-+   
   +-Regression function------+   
   +-STDDEV function----------+   
   +-STDDEV_SAMP function-----+   
   +-SUM function-------------+   
   +-VARIANCE function--------+   
   '-VARIANCE_SAMP function---'


OLAP-aggregate-function

|--+-first-value-function-----+---------------------------------|
   +-last-value-function------+   
   +-nth-value-function-------+   
   '-ratio-to-report-function-'   

first-value-function

                                  .-RESPECT NULLS-.   
|--FIRST_VALUE--(--expression--)--+---------------+-------------|
                                  '-IGNORE NULLS--'   

last-value-function

                                 .-RESPECT NULLS-.   
|--LAST_VALUE--(--expression--)--+---------------+--------------|
                                 '-IGNORE NULLS--'   

nth-value-function

                                                 .-FROM FIRST-.  .-RESPECT NULLS-.   
|--NTH_VALUE--(--expression--,--n-expression--)--+------------+--+---------------+--|
                                                 '-FROM LAST--'  '-IGNORE NULLS--'   

ratio-to-report-function

|--RATIO_TO_REPORT--(--expression--)----------------------------|

window-aggregation-group-clause

|--+-ROWS--+--+-group-start---+---------------------------------|
   '-RANGE-'  +-group-between-+   
              '-group-end-----'   

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------------------'   

group-end

|--+-UNBOUNDED FOLLOWING----------+-----------------------------|
   '-unsigned-constant--FOLLOWING-'

OLAP の指定は、select-clause 内の式、または select-statement の ORDER BY 文節の中に含めることができます。OLAP の指定が適用される照会結果は、OLAP の指定を含む最内部の副選択の結果表です。OLAP の指定は、ウィンドウ関数 としばしば呼ばれます。

OLAP の指定は WHERE、VALUES、GROUP BY、HAVING、または SET 文節では無効であり、中間結合表の ON 文節における join-condition でも無効です。OLAP の指定は、select-clause で集約関数の引数として使用することはできません。

OLAP の指定の呼び出し時には、関数が適用される行とどの順序で適用されるかを定義するウィンドウが指定されます。

ordered-OLAP-specification

window-order-clause を必要とする OLAP 操作を指定します。

LAG または LEAD

現在行の前または後の行を使用して計算された expression 値を戻します。

offset は正の整数または正の bigint 定数でなければなりません。 offset が指定されていない場合、値 1 が使用されます。

default-value は、expression のタイプにキャスト可能な式でなければなりません。 default-value が指定されない場合、デフォルト値は NULL 値になります。

IGNORE NULLS が指定された場合は、expression 値が NULL 値である行はすべて計算時に考慮されません。

LAG: LAG 関数は、現在の行から offset 行前にある行の式の値を戻します。window-partition-clause が指定されている場合、 offset とは現在のパーティションに含まれる、現在の行から offset 行前のことです。
LEAD: LEAD 関数は、現在の行から offset 行後にある行の式の値を戻します。window-partition-clause が指定されている場合、 offset とは現在のパーティションに含まれる、現在の行から offset 行後のことです。

offset が現在のパーティションの有効範囲を超える場合は、default-value が戻されます。

結果のデータ・タイプは、expression のデータ・タイプになります。結果が、NULL になることもあります。 IGNORE NULLS が指定されていて、ウィンドウ内のすべての値が NULL の場合、結果は NULL 値になります。

RANK または DENSE_RANK

ウィンドウ内の行の順序ランクを計算することを指定します。ウィンドウ内での順序に関しては区別できない行には同位が割り当てられます。ランキングの結果については、重複する値の結果の数値間に抜けが生じる状態と生じない状態で定義できます。

結果のデータ・タイプは BIGINT です。結果が NULL になることはありません。

RANK: 行のランクが、その行の前にある厳密な行数に 1 を加算したものと定義されることを指定します。したがって、順番に関して 2 行以上の行が区別できない場合、順次のランクの番号付けには 1 つ以上の抜けが生じることになります。
DENSE_RANK: 行のランクが、その行の前にある、順序に関して区別できる行の数に 1 を加算したものと定義されることを指定します。したがって、順次のランク番号付けには抜けはありません。

NTILE

ウィンドウ内の行の分位数ランクを計算することを指定します。

引数は、BIGINT にキャスト可能でなければなりません。 expression が SMALLINT、INTEGER、および BIGINT を戻さない場合は、関数を評価する前に BIGINT にキャストされます。ゼロより大きい値でなければなりません。 expression に scalar-fullselect、列参照、およびユーザー定義関数参照を含めてはなりません。

結果は、現在行の分位数ランクになります。結果内の分位数の数は引数によって決定され、分位数の数はウィンドウ内の行数を引数の値で割ることによって決定されます。ウィンドウ内の行数が引数で割り切れない場合、各分位数は少なくとも n 行を含み、分位数 1 から m はそれぞれ、n+1 行を含みます。

r は、ウィンドウの行数です。
q は、引数の値です。
m = MOD (r , q )
n = TRUNC (r , q )

結果のデータ・タイプは、引数の値に基づいて SMALLINT、INTEGER、または BIGINT になります。引数が NULL になる可能性がある場合、結果も NULL になる可能性があります。引数が NULL の場合、結果は NULL 値になります。

CUME_DIST

各行のパーセンタイル順位 (0 から 1 までの小数として表される) を決定する累積分布関数。デフォルトの行の昇順であれば、CUME_DIST は、現在行以下にランクする行の数 (現在行も含める) をパーティション内の合計行数で割った値を計算します。 window_order_clause で降順を指定した場合、CUME_DIST は、現在行以上にランクする行の数をパーティション内の合計行数で割った値を計算します。

例えば、デフォルトの順序で、パーティション内に 10 行あり、それらの中の 6 行より下に現在行がランクする場合、CUME_DIST の結果は 0.7 (6 行 + 現在行 = 7 を 10 で割った値) になります。パーティション内の最下位行の CUME_DIST 値は、デフォルトの昇順の場合、1.0 になります。パーティション内に行が 1 つだけある場合も、CUME_DIST 値は 1.0 になります。

結果のデータ・タイプは DECFLOAT(34) です。結果が NULL 値になることはありません。

numbering-specification

行ごとに連続番号を戻す OLAP 操作を指定します。

ROW_NUMBER: 順序付けで定義されたウィンドウ内の行に関して、最初の行を 1 として始め、順番の行番号が計算されることを指定します。ORDER BY 文節がウィンドウ内で指定されていない場合、行番号は、副選択で戻される任意の順序で (select-statement 内の ORDER BY 文節によってではなく) 行に割り当てられます。
結果のデータ・タイプは BIGINT です。結果が NULL になることはありません。

window-partition-clause

その中で OLAP 演算が適用される区分を定義します。

PARTITION BY (partitioning-expression,...): その中で OLAP 演算が適用される区分を定義します。区分化式は、結果セットの区分化の定義に使用される式です。partitioning-expression で参照する各列名は、OLAP 指定を含んでいる副選択の結果表の列を明確に参照する必要があります。区分化式にはスカラー全選択あるいはどんな非 deterministic 関数や外部アクションを持つ関数も含めることはできません。

window-order-clause

OLAP の指定の値を決定するために使用されるパーティション内での行の順序を定義します。これによって結果表の順序は定義されません。

ORDER BY (sort-key-expression,...): sort-key-expression は、ウィンドウ・パーティション内の行の順序付けを定義するために使用する式です。sort-key-expression 内で参照される各列名は、OLAP の指定を含む副選択の結果表の列を明確に参照していなければなりません。ソート・キー式にはスカラー全選択あるいはどんな非 deterministic 関数や外部アクションのある関数も含めることはできません。; ソート・キー式の長さ属性の合計は 3.5 ギガバイトを超えてはなりません。
ASC: sort-key-expression の値を昇順に使用することを指定します。
DESC: sort-key-expression の値を降順に使用することを指定します。
NULLS FIRST: ウィンドウの順序付けが、ソート順序の中で NULL 値をすべての非 NULL 値より先に考慮することを指定します。
NULLS LAST: ウィンドウの順序付けが、ソート順序の中で NULL 値をすべての非 NULL 値より後に考慮することを指定します。
ORDER OF table-designator: 表指定子で使用されているのと同じ順序付けを、副選択の結果表にも適用することを指定します。表指定子に一致する表参照が FROM 文節を指定する副選択のその文節内になければならず、その表参照はネストされた表の式または共通表式を識別するものでなければなりません。指定の表指定子に対応する副選択 (または全選択) には、データに従属する ORDER BY 文節が含まれている必要があります。適用される順序付けは、ネストされた副選択 (または全選択) 内の ORDER BY 文節の列が外側の副選択 (全選択) に含まれており、それらの列が ORDER OF 文節の代わりに指定されている場合と同じです。

OLAP-aggregate-function

OLAP ウィンドウから単一値を計算する関数を指定します。

FIRST_VALUE または LAST_VALUE

OLAP ウィンドウ内の最初または最後の値を戻します。

IGNORE NULLS が指定された場合は、expression 値が NULL 値である行はすべて計算時に考慮されません。

FIRST_VALUE: 結果は、OLAP ウィンドウ内の最初の行の expression 値です。
LAST_VALUE: 結果は、OLAP ウィンドウ内の最後の行の expression 値です。

結果のデータ・タイプは、expression のデータ・タイプになります。結果が、NULL になることもあります。IGNORE NULLS が指定されていて、ウィンドウ内のすべての値が NULL の場合、結果は NULL 値になります。

NTH_VALUE

OLAP ウィンドウ内の n 番目の行の expression 値を戻します。

n-expression は、ゼロより大きい値を持つ整数定数または整数変数でなければなりません。

IGNORE NULLS が指定された場合は、expression 値が NULL 値である行はすべて計算時に考慮されません。

FROM FIRST が指定されている場合、n 番目の値は、OLAP ウィンドウの先頭から順に数えて計算されます。

FROM LAST が指定されている場合、n 番目の値は、OLAP ウィンドウの末尾から逆に数えて計算されます。

結果は、n-expression で判別された、OLAP ウィンドウ内の n 番目の値になります。

結果のデータ・タイプは、expression のデータ・タイプになります。結果が、NULL になることもあります。 n-expression が NULL の場合、結果は NULL 値になります。 IGNORE NULLS が指定されていて、ウィンドウ内のすべての値が NULL の場合、結果は NULL 値になります。

FIRST_VALUE(expression) は、NTH_VALUE(expression, 1) FROM FIRST と同等です。

LAST_VALUE(expression) は、NTH_VALUE(expression, 1) FROM LAST と同等です。

RATIO_TO_REPORT

OLAP ウィンドウにおける引数の合計に対する 1 つの引数の比率を戻します。例えば、以下の関数は同じことを意味します。

RATIO_TO_REPORT(expression) OVER (...)
CAST(expression AS DECFLOAT(34)) / 
     SUM(CAST(expression as DECFLOAT(34))) OVER(...)

引数は、DECFLOAT(34) にキャスト可能な式でなければなりません。割り算は、DECFLOAT(34) を使用して実行されます。

結果のデータ・タイプは DECFLOAT(34) です。引数が NULL になる可能性がある場合、結果も NULL になる可能性があります。引数が NULL であれば、結果は NULL 値です。

window-aggregation-group-clause

所定の行の集約グループとは、所定の行に対する関係に定義されている行のセットです (その所定の行のパーティションにおける行の順序を継承)。window-aggregation-group-clause は集約グループを指定します。この文節が指定されておらず、window-order-clause も指定されていない場合、集約グループは、ウィンドウ・パーティションの全行で構成されます。ウィンドウ・パーティションの全行による集約グループは、RANGE 文節または ROWS 文節を使用して明示的に指定できます。変更の終わり

window-order-clause が指定されている一方で window-aggregation-group-clause が指定されていない場合、ウィンドウ集約グループは、所定の行のパーティションで所定の行に先行するすべての行か、window-order-clause によって定義されるウィンドウ・パーティションのウィンドウの順序付けで所定の行と同位であるすべての行で構成されます。

ROW: 行をカウントすることによって集約グループが定義されることを指定します。
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 を指定することは、group-between を BETWEEN CURRENT ROW AND group-end として指定することと等価です。
UNBOUNDED PRECEDING: 現在行に先行するパーティション全体を集約グループに含めることを指定します。ROWS 文節または RANGE 文節のいずれかと組み合わせて指定できます。現在行に先行するパーティション全体を集約グループに含める場合は、window-order-clause で sort-key-expressions を複数使用することができます。
UNBOUNDED FOLLOWING: 現在行に続くパーティション全体を集約グループに含めることを指定します。ROWS 文節または RANGE 文節のいずれかと組み合わせて指定できます。現在行に続くパーティション全体を集約グループに含める場合は、window-order-clause で sort-key-expressions を複数使用することができます。
CURRENT ROW: 集約グループが現在行に基づいて開始または終了することを指定します。 ROWS が指定された場合、current row が集約グループ境界です。 RANGE を指定すると、sort-key-expression に指定した値を現在行とする行のセットが集約グループ境界に含まれます。 group-bound-1 で unsigned-constant FOLLOWING が指定されている場合は、この文節を group-bound-2 に指定できません。
unsigned-constant PRECEDING: 現在行に先行する範囲または行数のいずれかを指定します。ROWS が指定されている場合、unsigned-constant は、ゼロ、あるいは行数を示す正整数または正の bigint である必要があります。 RANGE が指定されている場合、unsigned-constant のデータ・タイプは window-order-clause の sort-key-expression のデータ・タイプと比較可能である必要があります。許可される sort-key-expression は 1 つのみであり、 sort-key-expression のデータ・タイプは減算できるデータ・タイプである必要があります。group-bound-1 が CURRENT ROW または unsigned-constant FOLLOWING の場合、この文節を group-bound-2 で指定することはできません。
unsigned-constant FOLLOWING: 現在行の後続の範囲または行数のいずれかを指定します。ROWS が指定されている場合、unsigned-constant は、ゼロ、あるいは行数を示す正整数または正の bigint である必要があります。 RANGE が指定されている場合、unsigned-constant のデータ・タイプは window-order-clause の sort-key-expression のデータ・タイプと比較可能である必要があります。許可される sort-key-expression は 1 つのみであり、 sort-key-expression のデータ・タイプは加算できるデータ・タイプである必要があります。

注

比較: パーティション化および順序付けは、割り当ておよび比較で説明されている比較規則にしたがって行われます。

照合順序: OLAP 式を含むステートメントの実行時に *HEX 以外の照合順序が有効な場合で、しかも区分化式 またはソート・キー式 が SBCS データ、混合データ、または Unicode データの場合、結果は重み付けされた値を使用して決定されます。この重み付けされた値は、区分化式 およびソート・キー式 に照合順序を適用して得られます。

列マスク: OLAP 指定の partitioning-expression または sort-key-expression で参照されている列が、列マスクを持つように定義されている場合、その列マスクは適用されません。

制約事項: 照会に以下の指定がある場合、OLAP の指定は許可されません。

分散表
読み取りトリガーを指定する表
複数の物理ファイル・メンバー上に構築された論理ファイル

決定論: OLAP の指定は、非決定的です。

代替構文:

DENSE_RANK の代わりに DENSERANK を指定できます。
ROW_NUMBER の代わりに ROWNUMBER を指定できます。
LAG、LEAD、FIRST_VALUE、および LAST_VALUE への string-constant の最後の引数として IGNORE NULLS または RESPECT NULLS を指定できます。

例

給与の合計が $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_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

給与の高い上位 5 人の従業員をリストします。

  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 文節で使用される前に、ランキングを含む結果の最初の計算にはネストされた表の式が使用されたことに注意してください。共通表式を使うこともできます。

NTH_VALUE を使用して、株 ABC の上位 3 件の株価を求めます。

SELECT Symbol, StockDate, Price, 
      FIRST_VALUE(Price) OVER (PARTITION BY Symbol ORDER BY StockDate) AS FIRST_PRICE,
      NTH_VALUE(Price, 2) OVER (PARTITION BY Symbol ORDER BY StockDate) AS SECOND_PRICE,
      NTH_VALUE(Price, 3) OVER (PARTITION BY Symbol ORDER BY StockDate) AS THIRD_PRICE
FROM DailyStockData
WHERE StockDate BETWEEN CURRENT DATE - 1 MONTH AND CURRENT DATE
      AND Symbol = 'ABC'

NTILE を使用して、四分位数ランクを計算します。

SELECT proc_id, total_sales,
       NTILE(4) OVER (ORDER BY total_sales DESC) AS Quartile
FROM Sales

2005 年における株「ABC」および「XYZ」の 30 日間移動平均を計算します。

WITH V1(SYMBOL, TRADINGDATE, MOVINGAVG30DAY) AS
(
	SELECT SYMBOL, TRADINGDATE,
				AVG(CLOSINGPRICE) 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

各従業員の給与と、その従業員の部門の給与の中央値との差を表示します。

SELECT EMPNO, WORKDEPT, SALARY,
       SALARY - (MEDIAN(SALARY) OVER (PARTITION BY WORKDEPT))
FROM EMPLOYEE
ORDER BY WORKDEPT

各従業員の給与と、その従業員の部門内の給与の 90 番目のパーセンタイルとの差を表示します。

SELECT EMPNO, WORKDEPT, SALARY,
       SALARY - (PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY SALARY) 
                                      OVER (PARTITION BY WORKDEPT))
FROM EMPLOYEE
ORDER BY WORKDEPT