window関数の説明

概要

機械学習の前処理やデータ分析において便利なwindow関数の使用方法を説明する。

準備

使用するテーブル
- book(購買対象物テーブル)
  - データ

book_id	book_name	price
1	1000のバグを持つ男	600
2	あなたもなれるスーパーエンジニア	1100
3	顧客をダマス極意	1200
4	バグか仕様か	1150
5	今こそ学ぶC++	900
6	スーパーエンジニアを育てる方法	1100
7	上司に好かれる77の戦術	700
8	コードを書かないエンジニア	600
9	javaとjavascriptの違いを説明するには	900
10	僕は帰宅してからコーディングする	600

テーブル作成

CREATE TABLE book
(
  book_id SERIAL PRIMARY KEY,
  book_name varchar(255) not null,
  price DECIMAL (11, 2) not null
);

データインポート

INSERT INTO book (book_name, price)VALUES
  ('1000のバグを持つ男',600),
  ('あなたもなれるスーパーエンジニア',1100),
  ('顧客をダマス極意',1200),
  ('バグか仕様か',1150),
  ('今こそ学ぶC++',900),
  ('スーパーエンジニアを育てる方法',1100),
  ('上司に好かれる77の戦術',700),
  ('コードを書かないエンジニア',600),
  ('javaとjavascriptの違いを説明するには',900),
  ('帰宅後やっとコードを書き始める',600);

purchase(購買履歴テーブル)
- データ

purchase_id	user_id	book_id
1	1	1
2	1	4
3	1	8
4	1	9
5	2	1
6	2	2
7	2	6
8	3	1
9	3	7
10	3	8
11	3	3
12	3	5
13	3	10

テーブル作成

CREATE TABLE purchase
(
  purchase_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  book_id INT NOT NULL,
  FOREIGN KEY (book_id) REFERENCES book (book_id)
);

データ挿入

INSERT INTO purchase (user_id, book_id)VALUES
(1,1),
(1,4),
(1,8),
(1,9),
(2,1),
(2,2),
(2,6),
(3,1),
(3,7),
(3,8),
(3,3),
(3,5),
(3,10);

window関数の概要

概要
- window関数は集約関数と似ているが、集約関数と異なりレコードを削除しない。
- 以下では、集約関数と比較しながらwindow関数の説明をする。
集約関数を用いた平均額の算出
- 集約関数を用いて、購入額の平均を算出する処理は以下になる。
- AVG関数の引数にpriceを指定するだけ。
- sql
```
SELECT
  AVG(price)
FROM
  purchase
INNER JOIN book USING (book_id);
```
- 結果

avg
819.2307692307692308

集約関数を用いたユーザーごとの平均額の算出
- 集約関数を用いて、ユーザー毎の購入額の平均を算出する処理は以下になる。
- group byを用いて、ユーザーごとにグループ化した上で平均を計算するだけ。
- sql
```
SELECT
  user_id,
  AVG(price)
FROM
  purchase
INNER JOIN book USING (book_id)
GROUP BY
  user_id;
```
- 結果

user_id	avg
3	766.6666666666666667
2	933.3333333333333333
1	812.5000000000000000

window関数を用いたユーザーごとの平均額の算出
- window関数を用いると以下のようになる。
- 集約関数と同様にユーザーごとにグループ化した上で平均が算出されるが、集約関数とは異なり、レコードが削除されない。
- sql
```
SELECT
  user_id,
  price,
  AVG (price) OVER (PARTITION BY user_id)
FROM
  purchase
INNER JOIN book USING (book_id);
```
- 結果

user_id	price	avg
1	600.00	812.5000000000000000
1	1150.00	812.5000000000000000
1	600.00	812.5000000000000000
1	900.00	812.5000000000000000
2	600.00	933.3333333333333333
2	1100.00	933.3333333333333333
2	1100.00	933.3333333333333333
3	600.00	766.6666666666666667
3	700.00	766.6666666666666667
3	600.00	766.6666666666666667
3	1200.00	766.6666666666666667
3	900.00	766.6666666666666667
3	600.00	766.6666666666666667

動作順序
- window関数は、JOIN, WHERE, GROUP BY と HAVING 句のあとに動作する。
- 加えて、ORDER BY句の前に動作する。

window関数の書き方

基本構文

window関数 (引数1, 引数2) OVER (PARTITION BY expression ORDER BY expression)

window関数
- 集約関数をwindow関数として使用できる。
- 加えて、以下のようなビルトインのwindow関数を使用できる。
  - row_number(), rank(), dense_rank()
PARTITION BY
- 指定したものでパーティーションの設定(グループ化)を行う。
ORDER BY
- パーティーション内での並び方を指定する。

window関数の使用例

レコードの番号
- パーティーション内でのレコードの番号を取得する処理を以下のように書ける。
- ROW_NUMBER
  - ユーザーごとに購入額の高い順にレコードに番号を振る。
  - ROW_NUMBER関数は、同一の値でも別の番号が振られる。
  - sql
    SELECT user_id, price, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY price) FROM purchase INNER JOIN book USING (book_id);
  - 結果

user_id	price	row_number
1	600.00	1
1	600.00	2
1	900.00	3
1	1150.00	4
2	600.00	1
2	1100.00	2
2	1100.00	3
3	600.00	1
3	600.00	2
3	600.00	3
3	700.00	4
3	900.00	5
3	1200.00	6

RANK
- ROW_NUMBER関数の例と同様に、ユーザーごとに購入額の高い順にレコードに番号を振る。
- ただし同じ値があるときは同じ番号を振り、次の値はその分スキップする。
- sql
```
SELECT
  user_id,
  price,
  RANK() OVER ( PARTITION BY user_id ORDER BY price)
FROM
  purchase
INNER JOIN book USING (book_id);
```
- 結果

user_id	price	rank
1	600.00	1
1	600.00	1
1	900.00	3
1	1150.00	4
2	600.00	1
2	1100.00	2
2	1100.00	2
3	600.00	1
3	600.00	1
3	600.00	1
3	700.00	4
3	900.00	5
3	1200.00	6

DENSE_RANK

RANK関数と似ているが、同じ値があるときに次の値をスキップしない。

sql

SELECT
  user_id,
  price,
  DENSE_RANK() OVER ( PARTITION BY user_id ORDER BY price)
FROM
  purchase
INNER JOIN book USING (book_id);

結果

user_id	price	dense_rank
1	600.00	1
1	600.00	1
1	900.00	2
1	1150.00	3
2	600.00	1
2	1100.00	2
2	1100.00	2
3	600.00	1
3	600.00	1
3	600.00	1
3	700.00	2
3	900.00	3
3	1200.00	4

パーティーション内での最初と最後の値

FIRST_VALUE

ユーザー毎の購入額の最小値をレコードに追加する。

sql

SELECT
  user_id,
  price,
  FIRST_VALUE (price) OVER ( PARTITION BY user_id ORDER BY price) as user_lowest_price
FROM
  purchase
INNER JOIN book USING (book_id);

結果

user_id	price	user_lowest_price
1	600.00	600.00
1	600.00	600.00
1	900.00	600.00
1	1150.00	600.00
2	600.00	600.00
2	1100.00	600.00
2	1100.00	600.00
3	600.00	600.00
3	600.00	600.00
3	600.00	600.00
3	700.00	600.00
3	900.00	600.00
3	1200.00	600.00

LAST_VALUE
- ユーザー毎の購入額の最大値をレコードに追加する。
- sql
```
SELECT
  user_id,
  price,
  LAST_VALUE (price) OVER ( PARTITION BY user_id ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as user_highest_price
FROM
  purchase
INNER JOIN book USING (book_id);
```
- PARTITION BY user_id ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  - パーティション全体に渡る集約処理を行うための設定
  - デフォルト設定は(RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)で、これはパーティションの先頭から現在の行を集約処理の対象とする。(そのため最小値を出すときは、この指定がなくても正しく動作した)
- 結果

user_id	price	user_highest_price
1	600.00	1150.00
1	600.00	1150.00
1	900.00	1150.00
1	1150.00	1150.00
2	600.00	1100.00
2	1100.00	1100.00
2	1100.00	1100.00
3	600.00	1200.00
3	600.00	1200.00
3	600.00	1200.00
3	700.00	1200.00
3	900.00	1200.00
3	1200.00	1200.00

前後のレコード
- LAG関数やLEAD関数を使用すると、レコードの前後にあるレコードの値を取得できる。
- LAG関数やLEAD関数の書き方
```
LAG  (expression [,offset] [,default])
LEAD (expression [,offset] [,default])
```
  - offset
    - 何行ずらした位置のレコードを取得するか
    - デフォルト値： 1
  - default
    - ずらした位置にレコードが存在しない場合、設定する値
    - デフォルト値： NULL
- LAG
  - ユーザーごとに、対象のレコードの価格よりも、１つ低い価格を取得する。
  - sql
    SELECT user_id, price, LAG(price, 1) OVER(PARTITION BY user_id ORDER BY price) AS prev_price FROM purchase INNER JOIN book USING (book_id) GROUP BY user_id, price;
  - 結果

user_id	price	prev_price
1	600.00
1	900.00	600.00
1	1150.00	900.00
2	600.00
2	1100.00	600.00
3	600.00
3	700.00	600.00
3	900.00	700.00
3	1200.00	900.00

LEAD

ユーザーごとに、対象のレコードの価格よりも、１つ高い価格を取得する。

sql

SELECT
  user_id,
  price,
  LEAD(price, 1) OVER(PARTITION BY user_id ORDER BY price) AS next_price
FROM
  purchase
INNER JOIN book USING (book_id)
GROUP BY user_id, price;

結果

user_id	price	next_price
1	600.00	900.00
1	900.00	1150.00
1	1150.00
2	600.00	1100.00
2	1100.00
3	600.00	700.00
3	700.00	900.00
3	900.00	1200.00
3	1200.00

参考

以下のサイトを参考にさせていただきました(内容的にはほとんど同じですorz)
- Home / PostgreSQL Window Function PostgreSQL Window Function