window関数の説明

# 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|

* テーブル作成

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

* データインポート

    ```sql
    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|

* テーブル作成

    ```sql
    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)
    );
    ```

* データ挿入

    ```sql
    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

      ```sql
      SELECT
        AVG(price)
      FROM
        purchase
      INNER JOIN book USING (book_id);
      ```

   * 結果

|avg|
|---|
|819.2307692307692308|

* 集約関数を用いたユーザーごとの平均額の算出

   * 集約関数を用いて、ユーザー毎の購入額の平均を算出する処理は以下になる。
   * group byを用いて、ユーザーごとにグループ化した上で平均を計算するだけ。

   * sql

      ```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

      ```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関数の書き方

* 基本構文

   ```sql
   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

         ```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

         ```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

         ```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

         ```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

         ```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関数の書き方

      ```sql
      LAG  (expression [,offset] [,default])
      LEAD (expression [,offset] [,default])
      ```

      * offset
         * 何行ずらした位置のレコードを取得するか
         * デフォルト値： 1

      * default
         * ずらした位置にレコードが存在しない場合、設定する値
         * デフォルト値： NULL

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

      * sql

         ```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

         ```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](http://www.postgresqltutorial.com/postgresql-window-function/)