今回は、データマート開発等で使用される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でのデータの編集の幅が広がるので、機会がありましたら使用してみてください。