SQLで「greatest-n-per-group」問題（グループごとに最大値の行を求める）

[スタックオーバーフローで「Greatest-N-Per-Group」](https://stackoverflow.com/questions/tagged/greatest-n-per-group)として知られている問題があります。割とよくある問題で名前までついてるとのことなので、理解するついでにまとめてみます。

どういう問題かというと…

グループごとに、最大の値を持つ行を求める
---------

例えば、次のような得点データ`points`テーブルがあります。

id | name | point
---+-----+------
 1 | 太郎 | 50
 2 | 花子 | 30
 3 | 太郎 | 40
 4 | 花子 | 60

それぞれの名前の最大得点のある行を求める、という話です。

### それはMAX()とGROUP BYでは？

よくあるシンプルなSQLですね。こんなの。

```sql
SELECT MAX(point) AS max_point, name 
FROM points 
GROUP BY name
```

これで各自の最大値は分かります。

|max_point|name|
|---:|---|
| 50 | 太郎 |
| 60 | 花子 |

でも、

### 最大値を持つ「`行`」の情報が欲しい！

今回知りたいのは、「`最大値の行`」です。
例えば、こんな結果です。

|max_point|id|name|
|---:|---:|---|
| 50 |1| 太郎 |
| 60 |4| 花子 |

この例だと「最大値とその`id`が知りたい」という場合です。これを、SQLで求める方法になります。StackOverflowには、大量のSQLが紹介されているので、その中から気になったSQLを紹介します。

サブクエリを使う方法
---------

最大値を持つテーブルをサブクエリで作って結合する方法です。

```sql
SELECT MAX(x.point) AS max_point, x.id, x.name
FROM points x 
  JOIN ( 
    SELECT p.name, MAX(point) AS max_point 
    FROM points p 
    GROUP BY p.name ) y -- 最大値を持つテーブル
  ON (y.name=x.name AND y.max_point=x.point) 
GROUP BY x.id, x.name
```

テーブル`y`が最大値を持ってます。これに対して、最大値と名前の条件（`y.name=x.name AND y.max_point=x.point`）で結合することで、欲しい行の情報を取得してます。

ある意味、一番素直にSQLを書いた感じでしょうか。

[参考](https://stackoverflow.com/a/7745635)

自分自身とJOINする
---------

最初は？でしたが、意外と分かりやすいかも、と思った方法です。

```sql
SELECT x.id, x.name, x.point 
FROM points x 
  LEFT OUTER JOIN points y -- 自分自身と結合
    ON(y.name = x.name AND y.point > x.point) 
WHERE y.id IS NULL
```

自分自身とJOINします（最初が`x`、JOINするテーブルが`y`です）。

JOINする条件に、名前が一致することと、最大値`y.point > x.point`の条件があります。これがあるので、最大値の行だけジョインする相手がいないので、`y.id IS NULL`で引っ張ってこれる、というわけです。

[参考](https://stackoverflow.com/a/7745635)


Where In句を使う方法
---------

こんな方法があるのか！？

```sql
SELECT * 
FROM points 
WHERE (name, point) IN (
  SELECT name, MAX(point)
  FROM points
  GROUP BY name
)
```

StackOverflowでも、知らなかったとの声が。MySql、Oracle、PostgreSQL、DB2で動いたとのコメントあり。SQLiteは動かなかったと。自分はMySQLとPostgreSQLで動くのを確認できました。

[参考](https://stackoverflow.com/a/7745679)

PostgreSQLのDISTINCT ONを使う方法
---------

こちらも、こんな方法があるのか！？と思った方法です。ただしPostgreSQL限定のようですね。

```sql
SELECT DISTINCT ON(name)
    id, name, point
FROM points
ORDER BY name, point DESC, id
```

`DISTINCT ON`を使って名前をユニーク、つまり一行のみ取得するようにします。その上で、名前・得点の高い順に並べると、一番得点の高い行が取得できる仕組みですね。これだと同じ得点の場合は、一行だけ取得することになるのでしょう。

[参考](https://stackoverflow.com/a/7630564)

With句とRow_Number()を使う方法
---------

With句とは[SQL99で導入された構文](http://www.atmarkit.co.jp/fnetwork/tokusyuu/01sql99/sql99_1b.html)で、サブクエリなどの[ネストが読みやすくなる](http://kazzna.hatenablog.com/entry/2016/05/28/211429)とのこと。PostgreSQLではWith句に対応済み、MySQLも8.0から使えるという話なので学んでおきたいところ…

```sql
WITH ranking AS (
    SELECT p.id, p.point, p.name,
    ROW_NUMBER() OVER(
        PARTITION BY p.name ORDER BY p.point DESC) AS point_rank
    FROM points p
)
SELECT r.*
FROM ranking r
WHERE r.point_rank = 1
```

まず、With句で`ranking`を定義してます。ここで、`ROW_NUMBER() ... AS point_rank`としています。各行ごとに番号を振ってるんですね。

行番号については、`OVER(PARTITION BY ... ORDER BY ...)`を使うことで、名前ごとに1から振っていて、さらにポイントで並べてます。これで名前ごとで一番高いポイントの行が`point_rank=1`となるので、簡単に絞りこめると。SQLすごい。


[参考](https://stackoverflow.com/a/3800572)

最後に
=====

よくあることですが、書き終わってからググったら[日本語のgreatest-n-per-groupの情報がありました](https://code.i-harness.com/ja/tagged/greatest-n-per-group)。さらに[「グループごとに最大値の行を取得」でググる](https://www.google.co.jp/search?q=%E3%82%B0%E3%83%AB%E3%83%BC%E3%83%97%E3%81%94%E3%81%A8%E3%81%AB%E6%9C%80%E5%A4%A7%E5%80%A4%E3%81%AE%E8%A1%8C%E3%82%92%E5%8F%96%E5%BE%97)と結構ヒットするようです。

今回は最大値を求めるSQLでしたが、最小値の場合でも使えます。同じ得点がある場合、全部取得にするか一行に絞り込むかなど、色んなバリエーションがあります。

### パフォーマンス

本来は各SQLのパフォーマンス比較が欲しいですが、今回はやってません（[このページに測定した結果があります](https://stackoverflow.com/a/34715134)）。

データ数やDBによって結果は異なりそうなので、毎回試すべきと思われます。パフォーマンスが絶対に必要なら別に結果テーブルを作成（マテリアルビューとか？）するのもありなんじゃないでしょうか。

### With句について

これを書くことで、With句とPARTITION OVERが少しわかった気がします。書くといい事がある。