7.24.6　ウィンドウ関数の使用例

〈この項の構成〉

(1)　ウィンドウ枠句にROWSまたはRANGEを指定する例
(2)　RANKの指定例
(3)　DENSE_RANKの指定例
(4)　CUME_DISTの指定例
(5)　ROW_NUMBERの指定例
(6)　LAGの指定例
(7)　LEADの指定例
(8)　PERCENTILE_CONTの指定例

(1)　ウィンドウ枠句にROWSまたはRANGEを指定する例

ウィンドウ枠句のROWSとRANGEの指定の違いについて，次の移動累計を求めるSQL文を例にして説明します。

(a)　ROWSを指定した例

SELECT "C1_SORTKEY", "C2_NUM", 
        SUM("C2_NUM") OVER(ORDER BY "C1_SORTKEY"
                      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS "ROWS_SUM"
    FROM "T1"
    ORDER BY "C1_SORTKEY", "C2_NUM"

実行結果の例

[図データ]

［説明］

上記の例の場合，C1_SORTKEYの値が昇順に並んだ状態でウィンドウ枠が設定されます。設定されるウィンドウ枠は，現在行の1行前から現在行までをウィンドウ関数の集計範囲としています。
ROWS_SUM列には，集計範囲の行のC2_NUM列の値を合計した値が格納されます。

(b)　RANGEを指定した例

SELECT "C1_SORTKEY","C2_NUM",
        SUM("C2_NUM") OVER(ORDER BY "C1_SORTKEY"
                        RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS "RANGE_SUM"
    FROM "T1"
    ORDER BY "C1_SORTKEY","C2_NUM"

実行結果の例

[図データ]

［説明］

上記の例の場合，C1_SORTKEYの値が昇順に並んだ状態でウィンドウ枠が設定されます。設定されるウィンドウ枠は，C1_SORTKEYの値が現在行の値より1小さい行から，現在行と同じ値を持つ行までをウィンドウ関数の集計範囲としています。
RANGE_SUM列には，集計範囲の行のC2_NUM列の値を合計した値が格納されます。

ページの先頭へ

(2)　RANKの指定例

給与表（SALARYLIST）から，社員の職級（POSITION）ごとに，社員の給料（SALARY）のランクを求めます。

SELECT "EMPID","POSITION","SALARY",
        RANK() OVER(PARTITION BY "POSITION" ORDER BY "SALARY" DESC) AS "RANK"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY" DESC,"EMPID"

実行結果の例

[図データ]

ページの先頭へ

(3)　DENSE_RANKの指定例

給与表（SALARYLIST）から，社員の職級（POSITION）ごとに，社員の給料（SALARY）のランクを求めます。

SELECT "EMPID","POSITION","SALARY",
        DENSE_RANK() OVER(PARTITION BY "POSITION" ORDER BY "SALARY" DESC) AS "DENSE_RANK"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY" DESC,"EMPID"

実行結果の例

[図データ]

ページの先頭へ

(4)　CUME_DISTの指定例

給与表（SALARYLIST）から，社員の職級（POSITION）ごとに，社員の給料（SALARY）の相対位置を求めます。

SELECT "EMPID","POSITION","SALARY",
        CUME_DIST() OVER(PARTITION BY "POSITION" ORDER BY "SALARY" DESC) AS "CUME_DIST"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY" DESC,"EMPID"

実行結果の例

[図データ]

ページの先頭へ

(5)　ROW_NUMBERの指定例

給与表（SALARYLIST）から，社員の職級（POSITION）ごとに，社員の給料（SALARY）に対する降順の行番号を求めます。

SELECT "EMPID","POSITION","SALARY",
        ROW_NUMBER() OVER(PARTITION BY "POSITION" ORDER BY "SALARY" DESC) AS "ROW_NUMBER"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY" DESC,"EMPID"

実行結果の例

[図データ]

ページの先頭へ

(6)　LAGの指定例

販売履歴表（SALESLIST）から，顧客ID（USERID）ごとに，1つ前の行（前日）の購入額（PRE-PRICE）を求めます。

■RESPECT NULLSを指定した例

SELECT "USERID", "PUR-DATE", "PUR-PRICE",
        LAG("PUR-PRICE", 1, 0) RESPECT NULLS
            OVER(PARTITION BY "USERID" ORDER BY "PUR-DATE") AS "PRE-PRICE"
    FROM "SALESLIST"
    ORDER BY "USERID", "PUR-DATE"

実行結果の例

[図データ]

［説明］

PRE-PRICE列には，顧客IDごとに，1つ前の行のPUR-PRICE列の値が格納されます。1つ前の行のPUR-PRICE列の値がナル値でも，ナル値が格納されます。
顧客IDごとの最初の行の場合，1つ前の行は順序付けされた行の集合外になります。そのため，PRE-PRICE列には，既定値に指定した0が格納されます。

■IGNORE NULLSを指定した例

SELECT "USERID", "PUR-DATE", "PUR-PRICE",
        LAG("PUR-PRICE", 1, 0) IGNORE NULLS
            OVER(PARTITION BY "USERID" ORDER BY "PUR-DATE") AS "PRE-PRICE"
    FROM "SALESLIST"
    ORDER BY "USERID", "PUR-DATE"

実行結果の例

[図データ]

［説明］

PRE-PRICE列には，顧客IDごとに，1つ前の行のPUR-PRICE列の値が格納されます。ナル値は取り除かれるため，PUR-PRICE列の値がナル値の行を除いた1つ前の行のPUR-PRICE列の値が格納されます。

なお，PUR-PRICE列の値がナル値の行を除いた1つ前の行が順序付けされた行の集合外になった場合，PRE-PRICE列には，既定値に指定した0が格納されます。
顧客IDごとの最初の行の場合，1つ前の行は順序付けされた行の集合外になります。そのため，PRE-PRICE列には，既定値に指定した0が格納されます。

ページの先頭へ

(7)　LEADの指定例

販売履歴表（SALESLIST）から，顧客ID（USERID）ごとに，1つ後ろの行（翌日）の購入額（POST-PRICE）を求めます。

■RESPECT NULLSを指定した例

SELECT "USERID", "PUR-DATE", "PUR-PRICE",
        LEAD("PUR-PRICE", 1, 0) RESPECT NULLS
            OVER(PARTITION BY "USERID" ORDER BY "PUR-DATE") AS "POST-PRICE"
    FROM "SALESLIST"
    ORDER BY "USERID", "PUR-DATE"

実行結果の例

[図データ]

［説明］

POST-PRICE列には，顧客IDごとに，1つ後ろの行のPUR-PRICE列の値が格納されます。1つ後ろの行のPUR-PRICE列の値がナル値でも，ナル値が格納されます。
顧客IDごとの最後の行の場合，1つ後ろの行は順序付けされた行の集合外になります。そのため，POST-PRICE列には，既定値に指定した0が格納されます。

■IGNORE NULLSを指定した例

SELECT "USERID", "PUR-DATE", "PUR-PRICE",
        LEAD("PUR-PRICE", 1, 0) IGNORE NULLS
            OVER(PARTITION BY "USERID" ORDER BY "PUR-DATE") AS "POST-PRICE"
    FROM "SALESLIST"
    ORDER BY "USERID", "PUR-DATE"

実行結果の例

[図データ]

［説明］

POST-PRICE列には，顧客IDごとに，1つ後ろの行のPUR-PRICE列の値が格納されます。ナル値は取り除かれるため，PUR-PRICE列の値がナル値の行を除いた1つ後ろの行のPUR-PRICE列の値が格納されます。

なお，PUR-PRICE列の値がナル値の行を除いた1つ後ろの行が順序付けされた行の集合外になった場合，POST-PRICE列には，既定値に指定した0が格納されます。
顧客IDごとの最後の行の場合，1つ後ろの行は順序付けされた行の集合外になります。そのため，POST-PRICE列には，既定値に指定した0が格納されます。

ページの先頭へ

(8)　PERCENTILE_CONTの指定例

給与表（SALARYLIST）から，社員の職級（POSITION）ごとに，社員の給料（SALARY）の中央値（50パーセンタイル）を求めます。

SELECT "EMPID","POSITION","SALARY",
        PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY "SALARY")
            OVER(PARTITION BY "POSITION") AS "PERCENTILE_CONT"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY","EMPID"

実行結果の例

[図データ]

ページの先頭へ