みなさん、こんにちは！
今回は、データマート開発等で使用されるSQLの分析関数（ウィンドウ関数）について紹介します。
※多くのDBで実装されている関数ですが、本記事ではOracle Databaseでの使用を想定しています。
分析関数とは？
- 分析関数はグループ内の指定された範囲で集計を行い、結果を集計元の各行に戻すことができる関数。
- 分析関数を使うには、分析関数にOVER句を指定する。
OVER句に以下を指定することで集計範囲を指定できる。
PARTITION BY ・・・ グループ分け
ORDER BY ・・・ 順序指定
今回紹介する関数
- LAG関数
- LEAD関数
- FIRST_VALUE関数
- LAST_VALUE関数
- ROW_NUMBER関数
- SUM関数
LAG関数
使用用途：グループ内の指定された範囲で、前の行の値が欲しい時に使用する
SQLサンプル：
SELECT
ID,
KEY,
VALUE,
LAG(VALUE) over(PARTITION BY ID ORDER BY KEY) AS LAG
FROM
TEST01;
実行結果
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え、前の行の「VALUE」列の値が「LAG」列に入っていることがわかります。
LEAD関数
使用用途：グループ内の指定された範囲で、後の行の値が欲しい時に使用する
SQLサンプル：
SELECT
ID,
KEY,
VALUE,
LEAD(VALUE) over(PARTITION BY ID ORDER BY KEY) AS LEAD
FROM
TEST01;
実行結果
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え、後の行の「VALUE」列の値が「LEAD」列に入っていることがわかります。
FIRST_VALUE関数
使用用途：グループ内の指定された範囲で、最初の行の値が欲しい時に使用する
SQLサンプル：
SELECT
ID,
KEY,
VALUE,
FIRST_VALUE(VALUE) over(PARTITION BY ID ORDER BY KEY) AS FIRST_VALUE
FROM
TEST01;
実行結果
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え、最初の行の「VALUE」列の値が「FIRST_VALUE」列に入っていることがわかります。
LAST_VALUE関数
使用用途：グループ内の指定された範囲で、最後の行の値が欲しい時に使用する
SQLサンプル：
SELECT
ID,
KEY,
VALUE,
LAST_VALUE(VALUE) over(PARTITION BY ID ORDER BY KEY ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_VALUE
FROM
TEST01;
実行結果
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え、最後の行の「VALUE」列の値が「LAST_VALUE」列に入っていることがわかります。
LAST_VALUE関数使用時の注意点
LAST_VALUE関数では上記のとおり、「ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING」を指定しないと正しい結果が得られません。
OVER内のクエリは、デフォルトで「RANGE UNBOUNDED PRECEDING」となり、これは「RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」と同義で、指定しない場合はPARTITIONで区切ったうちの「1つ目」から「自分」までしか調べないという意味です。
※FIRST_VALUEが成功したのは、FIRST＝「1つ目」さえ取れていれば正しい値が取得出来るためです。
指定なし
指定した範囲のうち、「自分までの範囲」で最後の値を取得しています。
指定あり
指定した範囲のうち、「全ての範囲」で最後の値を取得しています。
NULLでない値を取得する「IGNORE NULLS」指定
オプションの指定がない場合は、FIRST_VALUEでは最初の行の値、LAST_VALUEでは最後の行の値を取得しますが、取得した値がNULLの場合、NULLを返します。
業務の中で最初のNULLでない値や最後のNULLでない値を取得する要件がある場合、「IGNORE NULLS」を指定する必要があります。
※「IGNORE NULLS」は、LAST_VALUE関数、LAG関数、LEAD関数でも使用可能です。
SQLサンプル：
SELECT
ID,
KEY,
VALUE,
FIRST_VALUE(VALUE) over(PARTITION BY ID ORDER BY KEY) AS FIRST_VALUE,
FIRST_VALUE(VALUE IGNORE NULLS) over(PARTITION BY ID ORDER BY KEY) AS FIRST_VALUE_IGNORE
FROM
TEST01;
実行結果
NULLを除いた最初の値が「FIRST_VALUE_IGNORE」列に入っていることがわかります。
ROW_NUMBER関数
使用用途：グループ内の指定された範囲で、1から始まる順番を付ける
SQLサンプル：
SELECT
ID,
KEY,
VALUE,
ROW_NUMBER() over(PARTITION BY ID ORDER BY KEY DESC) AS ROW_NUMBER
FROM
TEST01;
実行結果
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え(今回は降順で並び替え)、1から始まる値が「ROW_NUMBER」列に入っていることがわかります。
SUM関数
使用用途：グループ内の指定された範囲で、累計を求めたい時に使用する
SQLサンプル：
SELECT
ID,
KEY,
VALUE,
SUM(VALUE) over(PARTITION BY ID ORDER BY KEY) AS SUM
FROM
TEST01;
実行結果
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え、自分までの範囲の集計値が「SUM」列に入っていることがわかります。
分析関数を使用した編集例
- 履歴テーブルより集約を行いカレントテーブルを作成。
- ID単位で受付番号が最大のレコードを出力し、末尾に初回契約機種としてID単位で受付番号が最小の値を設定。
SQLサンプル：
SELECT
"顧客番号",
"受付番号",
"契約機種",
"初回契約機種"
FROM
(
SELECT
"顧客番号",
"受付番号",
"契約機種",
FIRST_VALUE("契約機種") over(PARTITION BY "顧客番号" ORDER BY "受付番号") AS "初回契約機種",
ROW_NUMBER() over(PARTITION BY "顧客番号" ORDER BY "受付番号" DESC) AS ROW_NUMBER
FROM
TEST02
)
WHERE
ROW_NUMBER = 1
;
まとめ
今回ご紹介した分析関数は単体で使用することは少なく、複数の分析関数を組み合わせて使用することが多いです。
分析関数は他にも様々なものがあり、使用するとSQLでのデータの編集の幅が広がるので、機会がありましたら使用してみてください。