データマート開発等で使用されるSQLの分析関数（ウィンドウ関数）

みなさん、こんにちは！
今回は、データマート開発等で使用されるSQLの分析関数（ウィンドウ関数）について紹介します。
※多くのDBで実装されている関数ですが、本記事ではOracle Databaseでの使用を想定しています。

## 分析関数とは？
* 分析関数はグループ内の指定された範囲で集計を行い、結果を集計元の各行に戻すことができる関数。
* 分析関数を使うには、分析関数にOVER句を指定する。
OVER句に以下を指定することで集計範囲を指定できる。
PARTITION BY ・・・ グループ分け
ORDER BY ・・・ 順序指定

## 今回紹介する関数
* LAG関数
* LEAD関数
* FIRST_VALUE関数
* LAST_VALUE関数
* ROW_NUMBER関数
* SUM関数

### LAG関数
使用用途：グループ内の指定された範囲で、前の行の値が欲しい時に使用する

SQLサンプル：
```ruby:qiita.rb
SELECT
　ID,
　KEY,
　VALUE,
LAG(VALUE) over(PARTITION BY ID ORDER BY KEY) AS LAG
　FROM
　TEST01;
```
実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/6735d084-70ec-46ee-bc7f-6525284211d4.png)
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え、前の行の「VALUE」列の値が「LAG」列に入っていることがわかります。

### LEAD関数
使用用途：グループ内の指定された範囲で、後の行の値が欲しい時に使用する

SQLサンプル：
```ruby:qiita.rb
SELECT
　ID,
　KEY,
　VALUE,
LEAD(VALUE) over(PARTITION BY ID ORDER BY KEY) AS LEAD
　FROM
　TEST01;
```
実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/30f6acc4-066e-48b3-81ac-0a6479ede855.png)
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え、後の行の「VALUE」列の値が「LEAD」列に入っていることがわかります。

### FIRST_VALUE関数
使用用途：グループ内の指定された範囲で、最初の行の値が欲しい時に使用する

SQLサンプル：
```ruby:qiita.rb
SELECT
　ID,
　KEY,
　VALUE,
FIRST_VALUE(VALUE) over(PARTITION BY ID ORDER BY KEY) AS FIRST_VALUE
　FROM
　TEST01;
```
実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/53553764-109c-4edb-a98a-b3078f2294a5.png)
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え、最初の行の「VALUE」列の値が「FIRST_VALUE」列に入っていることがわかります。

### LAST_VALUE関数
使用用途：グループ内の指定された範囲で、最後の行の値が欲しい時に使用する

SQLサンプル：
```ruby:qiita.rb
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;
```
実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/6b441bcc-aa9b-4984-8f8d-fac7b77779f2.png)
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つ目」さえ取れていれば正しい値が取得出来るためです。

指定なし
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/5c6a3bae-8352-483c-ae4a-07025e7ad836.png)
指定した範囲のうち、「自分までの範囲」で最後の値を取得しています。

指定あり
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/a3156e78-dbc1-4ae3-92d6-f1c036ce522f.png)
指定した範囲のうち、「全ての範囲」で最後の値を取得しています。

#### NULLでない値を取得する「IGNORE NULLS」指定
オプションの指定がない場合は、FIRST_VALUEでは**最初の行の値**、LAST_VALUEでは**最後の行の値**を取得しますが、取得した値がNULLの場合、NULLを返します。
業務の中で**最初のNULLでない値**や**最後のNULLでない値**を取得する要件がある場合、「IGNORE NULLS」を指定する必要があります。
※「IGNORE NULLS」は、LAST_VALUE関数、LAG関数、LEAD関数でも使用可能です。

SQLサンプル：
```ruby:qiita.rb
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;
```
実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/35267edf-7534-45cd-94f5-f5ba80d1f3d0.png)
NULLを除いた最初の値が「FIRST_VALUE_IGNORE」列に入っていることがわかります。

### ROW_NUMBER関数
使用用途：グループ内の指定された範囲で、1から始まる順番を付ける

SQLサンプル：
```ruby:qiita.rb
SELECT
　ID,
　KEY,
　VALUE,
ROW_NUMBER() over(PARTITION BY ID ORDER BY KEY DESC) AS ROW_NUMBER
　FROM
　TEST01;
```
実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/466c5473-cb92-491a-84f8-67c28e281477.png)
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え(今回は降順で並び替え)、1から始まる値が「ROW_NUMBER」列に入っていることがわかります。

### SUM関数
使用用途：グループ内の指定された範囲で、累計を求めたい時に使用する

SQLサンプル：
```ruby:qiita.rb
SELECT
　ID,
　KEY,
　VALUE,
SUM(VALUE) over(PARTITION BY ID ORDER BY KEY) AS SUM
　FROM
　TEST01;
```
実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/2878a511-94d4-4200-93de-3743de6a2297.png)
PARTITION BYで指定した「ID」ごとにグループ化され、ORDER BYで指定した「KEY」の値で並べ替え、自分までの範囲の集計値が「SUM」列に入っていることがわかります。

### 分析関数を使用した編集例
* 履歴テーブルより集約を行いカレントテーブルを作成。
* ID単位で受付番号が最大のレコードを出力し、末尾に初回契約機種としてID単位で受付番号が最小の値を設定。
SQLサンプル：
```ruby:qiita.rb
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
;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3956386/167b33b4-9c37-4532-9b82-5a4b534add51.png)

## まとめ
今回ご紹介した分析関数は単体で使用することは少なく、複数の分析関数を組み合わせて使用することが多いです。
分析関数は他にも様々なものがあり、使用するとSQLでのデータの編集の幅が広がるので、機会がありましたら使用してみてください。