【WINDOW関数・OVER句】PARTITION BYとORDER BYを使った集計パターン

# ランダムな作業ログ → 作業内容ごとに[１回目],[２回目]…という形に集計したい

### 作業記録テーブル(`test_log`)の概要

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/d8010ee6-f21d-2edd-9ee7-0d41ca78ad5b.png" width=60%>

**特徴**
- カラムは`id`、`user`(作業者)、`contents`(作業内容)、`time`(作業時刻)、`upload_time`(記録した時刻)
- 同じ作業内容が複数回行われるが、その際の作業者・作業時刻はランダムに決まっている
- 記録した時刻順にidが振られるため、レコードの順序は`upload_time`の時系列と一致するが、実際の作業時刻である`time`の時系列とは必ずしも一致していない

これを集計して以下のように、それぞれの作業内容(contents)について、作業回数ごとに作業時刻(time)と作業者(user)の情報を表示したい。:arrow_down:

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/c344f93a-5ec4-12ef-a9c6-427dd5cbe100.png)

# WINDOW関数：NTH_VALUE(☆) OVER(PARTITION BY ★)を使って集計する 

## 【step1】ｎ番目の値を表示するためのwindow関数

:::note info
**`FIRST_VALUE(カラム名２) OVER (PARTITION BY カラム名１)`**
レコードを［カラム名１］の値でパーティショニングして、各パーティションの最初のレコードでの［カラム名２］の値を表示する
:::

これを用いると、test_logテーブルにおける各作業内容(contents)の１回目の作業時刻は以下のコードで表示できる。
```sql
SELECT DISTINCT
    contents, FIRST_VALUE(time) OVER (PARTITION BY contents) AS 1ST_TIME
FROM
    test_log;
```

＊結果＊

<img src='https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/4d97b25b-1455-b02b-eee7-eceb9adee73e.png' width=30%>

コードも結果で得られるテーブルも、見た目がわりとすっきりシンプル。
しかし、このコードから結果のテーブルが得られるまでの過程の理解は、（初心者にとっては）ちょっとややこしかった。

<details><summary>以下に要点のみをメモ（折りたたみ）</summary>

#### ① `OVER(PARTITION BY contents)`によるレコードの並べ替え 
まずtest_logテーブルのレコードを並べ替えて、contentsの値でパーティショニングする
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/21e84f16-59ab-1159-e53e-d9993d69510f.png" width=85%>

#### ②`FIRST_VALUE(time) OVER (PARTITION BY contents)`によるカラムの追加
　次に各パーティションの**最初の作業時刻**を表すカラム［1ST_TIME］をテーブルに追加する
　（同じパーティションに属するレコードでは、値は当然すべて同じものになる）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/c5e071b4-e618-09ed-ec41-0191f3ee3604.png)

#### ③ `SELECT DISTINCT`で必要なカラムを重複なく表示する 
　最後にパーティション内で一定の値のみを示しているカラム(contents, 1ST_TIME)のみを選択して、重複を消去する
 <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/d5ed0270-6274-3dd1-1964-b64650d567b6.png" width=50%>
</details>

----
また、パーティションごとに２番目以降のデータを表示させるときは、以下の関数を用いる

:::note info
**`nTH_VALUE(カラム名２, n) OVER (PARTITION BY カラム名１)`**
レコードを［カラム名１］の値でパーティショニングして、各パーティションのn番目のレコードでの［カラム名２］の値を表示する
:::

## 【step2】window関数を使った結果と問題点の確認
これらを合わせて、各作業内容(contents)について１～４回目の作業時刻をそれぞれ集計してみる。SELECT文が長くなるので、名前付きwindow関数を使ったコードの書き方に変更。

```sql
SELECT DISTINCT
    contents, 
    FIRST_VALUE(time) OVER w AS 1ST_TIME,
    nTH_VALUE(time, 2) OVER w AS 2ND_TIME,
    nTH_VALUE(time, 3) OVER w AS 3RD_TIME,
    nTH_VALUE(time, 4) OVER w AS 4TH_TIME
FROM
    test_log
WINDOW
    w AS (PARTITION BY contents);
```
＊結果＊
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/98bc5de3-153f-fd79-4212-d454f2be15a6.png" width=70%>
一見うまく集計できてそうだが、実はwork3の時系列が2NDと3RDで逆になってしまっている。
元々test_logテーブルは記録時刻(upload_time)の順に並んでいるため、作業時刻(time)の時系列が考慮されていないことが原因。

## 【step3】`time`の時系列を反映させる方法の検討
検討した方法は２つ
- サブクエリで先にレコードを並べ替えておく
- パーティショニングの際に`time`の値で並べ替える

（これらを順番に試していくと、`ORDER BY`の扱いの難しさがほんのり実感できる気がした）

#### 1. サブクエリで先にレコードを並べ替える
`FROM`句に`ORDER BY`を含むサブクエリを入れて試すと…
（※エイリアス`AS ~`がないとエラーになるので注意）
```sql
SELECT
    contents, 
    FIRST_VALUE(time) OVER w AS 1ST_TIME,
    nTH_VALUE(time, 2) OVER w AS 2ND_TIME,
    nTH_VALUE(time, 3) OVER w AS 3RD_TIME,
    nTH_VALUE(time, 4) OVER w AS 4TH_TIME
FROM (
    SELECT * 
    FROM test_log
    ORDER BY time ) AS new_table
WINDOW
    w AS (PARTITION BY contents)
ORDER BY
    contents;
```
＊結果＊
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/70bee910-e12a-e555-9b99-7aa64a921852.png" width=60%>

`FROM`句の`ORDER BY`がクエリの最後に実行され、`PARTITION BY`によって並び替えたレコードの順序すら崩れてしまう。

そもそもSQLでは、サブクエリでは順序が指定できない、または特殊な場合しか順序が指定できない、というものが多い。
今回(MySQL)はサブクエリに書かれた`ORDER BY`句はSQLエンジンの裁量によって処理の最後に実行されているが、使用するデータベースの種類によっては異なるタイミンで実行される場合もあるし、この`ORDER BY`句をまるっと無視して処理が実行される場合もある。
このようにデータベースによって得られる結果に差が出やすいため、基本的に`ORDER BY`はクエリの最後にしか使わないようにすることが好ましいらしい。

したがって、１．の方法を手軽に実行するのは難しそう。
クエリの書き方に様々な工夫を凝らせば実現できるかもしれないが、初心者にはちょっと荷が重い。

:::note warn
**`ORDER BY`はクエリの最後でのみ使う**（その方がたぶん無難）
:::


#### 2. パーティショニングの際に`time`の値で並べ替える
次に検討した方法によるコード。
`PARTITION BY`の部分に`ORDER BY time`を追加してみる。

```sql
SELECT
    contents, 
    FIRST_VALUE(time) OVER w AS 1ST_TIME,
    nTH_VALUE(time, 2) OVER w AS 2ND_TIME,
    nTH_VALUE(time, 3) OVER w AS 3RD_TIME,
    nTH_VALUE(time, 4) OVER w AS 4TH_TIME
FROM
    test_log
WINDOW
    w AS (PARTITION BY contents ORDER BY time);
```
＊結果＊
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/609e70e6-0431-b7ba-8f78-0a619d5a6895.png" width=60%>

時系列の並びは正しく反映されている。しかし各作業内容毎に
　１回目、
　１回目～２回目、
　１回目～３回目、
という形で時刻が表示されてしまう。

 実は`OVER`句における`ORDER BY`は**範囲指定**の役割を持つため、その結果はただのレコードの並べ替えとは異なる。
 デフォルトで（特に範囲の指定なく）`ORDER BY`を用いるとき、`PARTITION BY`で作業毎に並べ替えられたレコードは、
　 ①その後作業内容ごとに作業時刻順で並べ替えられ、
 　②さらに**集計対象を１行目から現在行までに限定**して`nTH_VALUE`が実行
という過程で集計・表示される。
つまり②の集計対象を変更するため、以下のように`ORDER BY`に続けてWINDOW(frame)指定を追加する必要がある。

:::note info
`ORDER BY 指定カラム ROWS BETWEEN 開始行 AND 終了行`
　　「開始行から終了行までの、指定カラムの値」で集計
`ORDER BY 指定カラム RANGE 10 PRECEDING`
　　「指定カラムの値が、現在行の値から10以下の範囲にあるもの」で集計

:::


ORDER BY句のWINDOW指定の方法はROWSとRANGEの２種類から選択でき、集計開始行と終了行を自由に指定することができる。
今回はパーティショニング後の各作業内容について、１行目から最後の行までを集計対象として指定する。したがって`ORDER BY time`に続けて、
　`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
と書く。

```sql
SELECT
    contents, 
    FIRST_VALUE(time) OVER w AS 1ST_TIME,
    nTH_VALUE(time, 2) OVER w AS 2ND_TIME,
    nTH_VALUE(time, 3) OVER w AS 3RD_TIME,
    nTH_VALUE(time, 4) OVER w AS 4TH_TIME
FROM
    test_log
WINDOW
    w AS (PARTITION BY contents ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
```
＊結果＊
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/4d206337-d376-0467-25a6-c62e0d2eac36.png" width=60%>

集計範囲、時系列ともに正しく反映されている。（上の結果と比較するため、`SELECT`文の`DISTINCT`なしで表示。）

# 作業記録テーブルを集計するための、最終的なコード例
↓以上をふまえて、作業者カラムも追加した完成コード↓
```sql
SELECT DISTINCT
    contents, 
    FIRST_VALUE(time) OVER w AS FIRST_TIME,
    FIRST_VALUE(user) OVER w AS user,
    nTH_VALUE(time, 2) OVER w AS SECOND_TIME,
    nTH_VALUE(user, 2) OVER w AS user,
    nTH_VALUE(time, 3) OVER w AS THIRD_TIME,
    nTH_VALUE(user, 3) OVER w AS user,
    nTH_VALUE(time, 4) OVER w AS FOURTH_TIME,
    nTH_VALUE(user, 4) OVER w AS user
FROM
    test_log
WINDOW
    w AS (PARTITION BY contents ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
```
＊結果＊
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/47580ee8-a33f-89ef-db69-0debd3611ec3.png)