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