【備忘録】MySQLでGROUP BYとサブクエリを使った話

## 概要
仕事上で一見難しくなさそうな条件でSQLを書くのに手こずったので、備忘録がわりに記事を書きました。自分がこれを使えば想定通りに取得できるかな?と考えた順で書いています。

## やりたいこと

以下のテーブルから、`type`毎に`id`が一番大きいレコード(id, type, urlの全項目)だけを取得したい。
別個のレコードには、同じ`url`が含まれない想定。

**sample_table**
| id | type | url |
|:-----------:|:------------:|:------------:|
| 1       | 1        | http://example.com/11         |
| 2       | 1        | http://example.com/12     |
| 3       | 2        | http://example.com/21         |
| 4       | 2        | http://example.com/22         |
| 5       | 2        | http://example.com/23         |
| 6       | 3        | http://example.com/31        |

　　　　　　　　↓

| id | type | url |
|:-----------:|:------------:|:------------:|
| 2       | 1        | http://example.com/12     |
| 5       | 2        | http://example.com/23         |
| 6       | 3        | http://example.com/31        |

## 試した・考えたこと

### ❌ DISTINCTを使う
重複を除いてレコードを取得するので、使えるのではと考えた。
→今回の場合は`url`も含めて取得したいが、urlがユニークで全てのレコードが重複してしまうためNG


実行SQL:
```SQL
select distinct * from sample_table st 
```

実行結果:

| id | type | url |
|:-----------:|:------------:|:------------:|
| 1       | 1        | http://example.com/11         |
| 2       | 1        | http://example.com/12     |
| 3       | 2        | http://example.com/21         |
| 4       | 2        | http://example.com/22         |
| 5       | 2        | http://example.com/23         |
| 6       | 3        | http://example.com/31        |



以下のように`type`のみなら重複なしで3レコードのみ取得できる。

実行SQL:
```SQL
select distinct * from sample_table st 
```

実行結果:

| type |
|:------------:|
| 1        |
| 2        |
| 3        |


### ❌ GROUP BYを使う
`type`毎にレコードを取得するので、使えないかと考えた。
→`GROUP BY`で対象とする列以外は、集計関数を利用しないと取得できないためNG。

実行SQL:
```SQL
select MAX(st.id) as max_id, st.type from sample_table st 
group by st.type
```

実行結果:
以下のように、`type`毎に`id`が最も大きいレコードの`id`、`type`を取得することはできる。
ただし、`id`、`type`と同じ行の`url`が取得できない。

| id | type |
|:-----------:|:------------:|
| 2       | 1        |
| 5       | 2        |
| 6       | 3        |


実行SQL:
```SQL
select MAX(st.id) as max_id, st.type, url from sample_table st 
group by st.type
```

実行結果:
`url`を取得する対象に含めると、以下のようなエラーが発生する。
要約すると、`GROUP BY`の対象になっていない列は`sql_mode=only_full_group_by`ではそのまま取得できないらしい。

[この記事](https://blog.capilano-fw.com/?p=7001) の説明がわかりやすかった

```
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.st.url' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
```

### ⭕️ GROUP BYを利用したサブクエリと自己結合する

GROUP BYを使って取得したいレコードの`id`は取得できているので、このSQLをサブクエリにして`sample_table`と結合すれば`url`も含めた必要なレコードが取得できるのではと考えた
→`left join`の結合対象が存在しない場合、`null`が入ることだけ気をつければOK


実行SQL:
```SQL
select * from sample_table st 
left join (
  select MAX(st.id) as max_id, st.type
  from sample_table st 
  group by st.type
) as subquery
on st.id = subquery.max_id
```

実行結果:
`sample_table`に[GROUP BYを使う](#group-byを使う)で実行したSQLの結果を結合したテーブルを取得する。
以下のように取得したいレコードのみ、結合した`max_id`、`type`に値が入っている。

| id | type | url | max_id | type |
|:-----------:|:------------:|:------------:|:------------:|:------------:|
| 1       | 1        | http://example.com/11|null|null|
| 2       | 1        | http://example.com/12|2|1|
| 3       | 2        | http://example.com/21|null|null|
| 4       | 2        | http://example.com/22|null|null|
| 5       | 2        | http://example.com/23|5|2|
| 6       | 3        | http://example.com/31|6|3|



実行SQL:
```SQL
select st.* from sample_table st 
left join (
  select MAX(st.id) as max_id, st.type
  from sample_table st 
  group by st.type
) as subquery
on st.id = subquery.max_id
where not ISNULL(subquery.max_id)
```

実行結果:
1つ前のSQLから、以下の2点を修正することで想定していたレコードを取得可能
- 取得対象を`sample_table`の列のみ(結合した列は含まない)にする
- `max_id`が`null`でない条件を追加する

| id | type | url |
|:-----------:|:------------:|:------------:|
| 2       | 1        | http://example.com/12     |
| 5       | 2        | http://example.com/23         |
| 6       | 3        | http://example.com/31        |



## 補足

### ⭕️ ウィンドウ関数を利用する

先輩とChatGPTからウィンドウ関数なるものでも同じことができると聞いたので、試してみた。

実行SQL:
```SQL
select *, max(id) over (partition by type) as max_id from sample_table st 
```

実行結果:

| id | type | url | max_id |
|:-----------:|:------------:|:------------:|:------------:|
| 1       | 1        | http://example.com/11|2|
| 2       | 1        | http://example.com/12|2|
| 3       | 2        | http://example.com/21|5|
| 4       | 2        | http://example.com/22|5|
| 5       | 2        | http://example.com/23|5|
| 6       | 3        | http://example.com/31|6|


`新規列の値 over (partition by 列名)`の形で、指定列の値ごとに新規列の値を計算するらしい。今回の場合は、`type`毎に`id`の最大値を計算している。

この辺りの説明は、[この記事](https://qiita.com/ngyuki/items/c35dd314815939ffbeb6)がわかりやすかった。

あとは、上記の表から`id`と`max_id`が同じ元レコード部分だけ表示すればOK

実行SQL:
```SQL
select id, type, url from (
  select *, max(id) over (partition by type) as max_id 
  from sample_table st 
) as subquery
where id = subquery.max_id
```

実行結果:

| id | type | url |
|:-----------:|:------------:|:------------:|
| 2       | 1        | http://example.com/12     |
| 5       | 2        | http://example.com/23         |
| 6       | 3        | http://example.com/31        |