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

〈この項の構成〉

(1)　ウィンドウ枠句にROWSまたはRANGEを指定する例
(2)　RANKの指定例
(3)　DENSE_RANKの指定例
(4)　CUME_DISTの指定例
(5)　ROW_NUMBERの指定例
(6)　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)　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"

実行結果の例

[図データ]

ページの先頭へ