SQL　ウィンドウ関数についてまとめる

SQLのウィンドウ関数について、その基本をまとめます。

# 環境
PostgreSQL 15.6


今回使用したテーブルのCREATEとINSERT文
```CREATE_INSERT.sal
CREATE TABLE 売上 (
    売上ID SERIAL PRIMARY KEY,
    売上日 DATE,
    商品名 VARCHAR(50),
    社員名 VARCHAR(50),
    売上金額 INT 
);

INSERT INTO 売上 (売上日, 商品名, 社員名, 売上金額) VALUES
('2024-12-01', 'ノートPC', 'John', 100000),
('2024-12-01', 'スマホ', 'Emily', 150050), 
('2024-12-02', 'ノートPC', 'John', 80075),
('2024-12-02', 'タブレット', 'Michael', 200000),     
('2024-12-03', 'スマホ', 'Emily', 120025),
('2024-12-04', 'ノートPC', 'Michael', 95000),
('2024-12-05', 'タブレット', 'John', 180050),
('2024-12-05', 'スマホ', 'Emily', 130075),   
('2024-12-06', 'ノートPC', 'Michael', 110000),
('2024-12-07', 'タブレット', 'John', 220000),        
('2024-12-08', 'スマホ', 'Emily', 160000),
('2024-12-10', 'ノートPC', 'Michael', 90050),   
('2024-12-11', 'タブレット', 'John', 210025),
('2024-12-11', 'スマホ', 'Emily', 140075),   
('2024-12-13', 'ノートPC', 'Michael', 105000),  
('2024-12-14', 'タブレット', 'John', 175000),
('2024-12-15', 'スマホ', 'Emily', 155000),     
('2024-12-16', 'ノートPC', 'Michael', 98000),
('2024-12-17', 'タブレット', 'John', 190000);      
```

# ウィンドウ関数とは
> ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。

https://www.postgresql.jp/document/15/html/tutorial-window.html

通常のクエリでは、複雑になってしまいがちな、行間比較をおこない、行ごとに集計しその結果を各行へ返すことができます。また、ウィンドウ関数を使用せず、同様のデータを取得しようとするとクエリが複雑になってしまいがちです。

ウィンドウ関数の基本的な構文は下記の通りです。

**ウィンドウ関数(式) OVER ([PARTITION BY 列リスト] [ORDER BY 列リスト [フレーム句]])**

* **ウィンドウ関数(式)**：使用したいウィンドウ関数（例：SUM,AVG,RANK,LAGなど）
* **PARTITION BY**： 行をグループ化するための列を指定。この句がない場合、全ての行を一つのグループとして扱う。
* **OVER()**：ウィンドウ関数を使用することの宣言のようなもの
* **ORDER BY**：集計や計算を行う際に、どの順序で行を処理するかを指定(下記のサンプルの場合、売上idの昇順で計算処理を行う)
* **フレーム句**：ウィンドウフレームを定義し、どの行が計算に含まれるかを指定する。
例えば、```ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW```などがあります。これは、先頭の行から（UNBOUNDED PRECEDING）から現在行（CURRENT ROW）までを集計対象とするとの指定です。

下記のクエリでは、売上データに対して移動平均を計算しています。

```売上額の移動総平均.sql
SELECT
    売上日
    , 商品名
    , 売上金額
    , avg(売上金額) OVER (ORDER BY 売上id ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW) AS 移動総平均
    FROM 売上
ORDER BY 売上日
```
![サンプル１.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3770474/31e4080e-8f8e-b121-3805-57a0509319cc.png)

ウィンドウ関数を用いない場合の例として下記のようなクエリが考えられます。

```ウィンドウ関数を使わない.sql
SELECT
        s1.売上日
        , s1.商品名
        , s1.売上金額
        , ( 
            SELECT
                    AVG(s2.売上金額) 
                FROM
                    売上 s2 
                WHERE
                    s2.売上id <= s1.売上id
        ) AS 移動総平均 
    FROM
        売上 s1 
    ORDER BY
        s1.売上日
```
**■ それぞれの比較**
* パフォーマンス: ウィンドウ関数を使用した方が一般的にはパフォーマンスが良いことが多い。
* 可読性: ウィンドウ関数の方がシンプルで可読性が高い。
* 柔軟性: サブクエリは特定の条件に基づいて集計を行う際に柔軟性がある。（半面、複雑になってしまう）

# 主なウィンドウ関数
<table>
  <captionJavaのアクセス修飾子</caption>
  <thead>
    <tr>
      <th>関数</th> <th>説明</th>
    </tr>
  </thead>
  <tr>
    <td> SUM() </td> <td>合計値を計算</td>
  </tr>
  <tr>
    <td> AVG() </td> <td>平均値を計算</td>
  </tr>
   <tr>
    <td> COUNT() </td> <td>行数をカウント</td>
  </tr>
   <tr>
    <td> MIN() </td> <td>最小値を取得</td>
  </tr>
    <tr>
    <td> MAX() </td> <td>最大値を取得</td>
  </tr>
  <tr>
    <td> ROW_NUMBER() </td> <td>行番号を付与</td>
  </tr>
   <tr>
    <td> RANK() </td> <td>同じ値に対して同じランクを付与し、次のランクにはスキップが発生</td>
  </tr>
   <tr>
    <td> DENSE_RANK() </td> <td>同じ値に対して同じランクを付与し、次のランクにはスキップが発生しない</td>
  </tr>
    <tr>
    <td> LEAD() </td> <td>次の行の値を取得</td>
  </tr>
    <tr>
    <td> LAG() </td> <td>前の行の値を取得</td>
  </tr>
    <tr>
    <td> FIRST_VALUE() </td> <td>ウィンドウ内で最初の値を取得</td>
  </tr>
    <tr>
    <td> LAST_VALUE() </td> <td>ウィンドウ内で最後の値を取得</td>
  </tr>
　<tr>
    <td>NTH_VALUE(,n) </td> <td>ウィンドウ内でn番目の値を取得</td>
  </tr>
</table>

いくつか例をまとめます。

**■ RANKの例**
売上合計高い順で社員のランク付けを行える。
```RANK.sql
SELECT
        社員名
        , sum(売上金額) AS 合計売上額
        , RANK() OVER (ORDER BY SUM(売上金額) DESC) 
    FROM
        売上 
    GROUP BY
        社員名 
    ORDER BY
        合計売上額 DESC
```

**■ LAGの例**
社員ごとの前回売上日を取得
```LAG.sql
SELECT
        社員名
        , 商品名
        , 売上日
        , LAG(売上日) OVER (PARTITION BY 社員名 ORDER BY 売上日) AS 前回売上日 
    FROM
        売上 
    ORDER BY
        社員名
```

![前回売上日.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3770474/8bc1c9ec-12e9-1d66-1d8d-415d0782315b.png)


**■ FIRST_VALUEの例**
その月の初回販売日を取得
```FIRST_VALUE.sql
SELECT
        社員名
        , 商品名
        , 売上日
        , FIRST_VALUE(売上日) OVER (PARTITION BY 社員名 ORDER BY 売上日) AS 初回売上日 
    FROM
        売上 
    ORDER BY
        社員名
```

# フレーム句について
フレーム句の指定は、ウィンドウ関数においてウィンドウ関数の計算が、どの行に対して行われるか制御するために使用される重要な機能です。
フレーム句の基本構文は下記の通りです。

**frame_mode frame_start** もしくは **frame_mode BETWEEN frame_start AND frame_end**

#### frame_startとframe_end の指定方法

<table>
  <captionJavaのアクセス修飾子</caption>
  <thead>
    <tr>
      <th>キーワード</th> <th>説明</th>
    </tr>
  </thead>
  <tr>
    <td> UNBOUNDED PRECEDING </td> <td>先頭の行（frame_endでは使えない）</td>
  </tr>
  <tr>
    <td> UNBOUNDED FOLLOWING </td> <td>末尾の行（frame_startでは使えない）</td>
  </tr>
   <tr>
    <td> CURRENT ROW </td> <td>現在行</td>
  </tr>
   <tr>
    <td> n PRECEDING </td> <td>現在行よりn行前、RANGEの場合はn値前</td>
  </tr>
  <tr>
    <td> n FOLLOWING </td> <td>現在行よりn行後、RANGEの場合はn値後</td>
  </tr>
</table>

https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5

#### フレームモード

**■ ROWS**
行数に基づいてフレームを定義します。
例えば、```ROWS BETWEEN 1 PRECEDING AND CURRENT ROW``` は、現在の行とその1行前の行を含むフレームを指定します。

**■ RANGE**
値に基づいてフレームを定義します。
下記のsample1.sqlでは、**「現在行の日付から2日前の日付のデータのみを対象」で最小の売上日**を取得します。対象データがない場合は、nullとなります。
```sample1.sql
SELECT
        社員名
        , 商品名
        , 売上日
        , MIN(売上日) OVER ( 
                ORDER BY
                    売上日 RANGE BETWEEN INTERVAL '2 day' PRECEDING AND '2 day' PRECEDING
        ) AS "2日前の売上日" 
    FROM
        売上 
    ORDER BY
        売上日
```

![２日前.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3770474/14575770-419d-61d3-b04d-2298366f4f76.png)


sample2.sqlでは、**「現在行の日付から2日前までの範囲」で最小の売上日**を取得(現在行の日付とその前の2日間（合計で3日間）の売上日の最大値)となります。
```sample2.sql
SELECT
        社員名
        , 商品名
        , 売上日
        , MIN(売上日) OVER ( 
                ORDER BY
                    売上日 RANGE BETWEEN INTERVAL '2 day' PRECEDING AND CURRENT ROW
        )
    FROM
        売上 
    ORDER BY
        売上日
```

![現在行も含む.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3770474/40cb9a49-962e-f9d0-6a94-1cda03493017.png)


#### フレーム句がない場合の動作
**■ ORDER BYが指定されている場合**　
```ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW```が適応
先頭の行から現在行までが対象
**■ ORDER BYが省略された場合**　
```ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING```が適応
全体（先頭の行から末尾の行）が対象

https://masahikosawada.github.io/2018/07/07/Window-Frame/

# 参考書籍

https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6SQL%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E7%AC%AC2%E7%89%88-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-CodeZine-BOOKS/dp/4798157821/ref=sr_1_1?adgrpid=55828662440&dib=eyJ2IjoiMSJ9.hStS2iAA73bZt-sWos6z5PNwUlZ6QgfJNTlg_bsVkcQ5nZDV5Fe7-hizl1GRl6XVp_fqFD75iImEBCRAxsKxGCTd2h4El8mPhbt2hTz-42lNfH6Jt_OJyAIQJTjmHqfCDWagn77uwQkPaqjlxTN3g9o8qFsryTJSq0no7IOHrWYGbAqoWPdipXywVz5HMm1edFX-5vvEhPyxZl1CGVvidPw0XTd_-2zS04YHYyiIHWU.wHcVlXzwo3EO__y18ZyJUF7pozh2rWmeXj_e6O_HcPY&dib_tag=se&hvadid=678984679431&hvdev=c&hvlocphy=9208422&hvnetw=g&hvqmt=e&hvrand=7932969362657837729&hvtargid=kwd-560690801493&hydadcr=27272_14738606&jp-ad-ap=0&keywords=%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6+sql+%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8&qid=1734076628&sr=8-1