MySQL でグループごとの最大値を持つレコードを取得する



## お題

下記のテーブルで、顧客 (customer_id) ごとの購入金額 (amount) が最大の時の日時 (datetime) を知りたいとします。

| purchase_id | customer_id | amount | datetime |
|--:|--:|--:|:--|
| 1 | 101 | 100 | 2000-01-01T00:00:00Z |
| 2 | 102 | 180 | 2000-01-02T00:00:00Z |
| 3 | 103 | 200 | 2000-01-03T00:00:00Z |
| 4 | 101 | 70 | 2000-01-04T00:00:00Z |
| 5 | 103 | 280 | 2000-01-05T00:00:00Z |
| 6 | 102 | 310 | 2000-01-06T00:00:00Z |
| 7 | 101 | 10 | 2000-01-07T00:00:00Z |
| 8 | 103 | 280 | 2000-01-08T00:00:00Z |

## サンプルデータ

[SQL Fiddle](http://sqlfiddle.com/) でブラウザで試せます　

~~~sql
CREATE TABLE IF NOT EXISTS `purchase_header` (
  `purchase_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `amount` int(10) unsigned NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY (`purchase_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

INSERT INTO `purchase_header` (`customer_id`, `amount`, `datetime`) VALUES (101, 100, '2000-01-01 00:00:00');
INSERT INTO `purchase_header` (`customer_id`, `amount`, `datetime`) VALUES (102, 180, '2000-01-02 00:00:00');
INSERT INTO `purchase_header` (`customer_id`, `amount`, `datetime`) VALUES (103, 200, '2000-01-03 00:00:00');
INSERT INTO `purchase_header` (`customer_id`, `amount`, `datetime`) VALUES (101, 70, '2000-01-04 00:00:00');
INSERT INTO `purchase_header` (`customer_id`, `amount`, `datetime`) VALUES (103, 280, '2000-01-05 00:00:00');
INSERT INTO `purchase_header` (`customer_id`, `amount`, `datetime`) VALUES (102, 310, '2000-01-06 00:00:00');
INSERT INTO `purchase_header` (`customer_id`, `amount`, `datetime`) VALUES (101, 10, '2000-01-07 00:00:00');
INSERT INTO `purchase_header` (`customer_id`, `amount`, `datetime`) VALUES (103, 280, '2000-01-08 00:00:00');
~~~

## 駄目なパターン1 安直に SELECT で MAX(amount) と datetime を並べる

これ、MySQL を覚えたての頃にまず try しました。

~~~sql
SELECT
    customer_id, MAX(amount), datetime
FROM
    purchase_header
GROUP BY customer_id
;
~~~

amount の最大値は取れていますが、意図した datetime を取れていません。

<img width="1420" alt="Screen_Shot_2018-03-18_at_1_27_44.jpg" src="https://qiita-image-store.s3.amazonaws.com/0/106789/ffa4ac41-73f1-a0fd-82f2-1d3f8fa72c56.jpeg">

そもそも GROUP BY で指定されておらず、集約もされていない datetime を SELECT することは標準の SQL では不正ですが MySQL では許容しているそうです。
[MySQL での GROUP BY の処理](https://dev.mysql.com/doc/refman/5.6/ja/group-by-handling.html)

## パターン1 WHERE 句のサブクエリで MAX()

サブクエリで購入金額の最大値を取ってきて、外側のクエリでは、それとイコールのレコードを表示しようと言うもの。

~~~sql
SELECT
    ph.customer_id, ph.amount, ph.datetime
FROM
    purchase_header AS ph
WHERE
    ph.amount = (
        SELECT
            MAX(sub_ph.amount)
        FROM
            purchase_header AS sub_ph
        WHERE
            ph.customer_id = sub_ph.customer_id
        GROUP BY sub_ph.customer_id
    )
;
~~~

意図した結果が取れています。
最大値が 2 つあった場合も両方取れています。

<img width="1414" alt="Screen Shot 2018-03-18 at 1.46.05.png" src="https://qiita-image-store.s3.amazonaws.com/0/106789/398132da-da3a-4856-6df9-c9f6a8627164.png">

ただし相関サブクエリとなり、内側のサブクエリ × 外側のクエリの評価が行われてコストが高くなりがちです。
EXPLAIN すると sub_ph が DEPENDENT SUBQUERY となります。

<img width="1281" alt="Screen Shot 2018-03-18 at 2.12.31.png" src="https://qiita-image-store.s3.amazonaws.com/0/106789/a5050e81-0c00-59d0-2c97-f03b4a7760d9.png">


## パターン2 LEFT JOIN で自己結合

発想を転換して同じテーブルを LEFT JOIN し、ON を活用する。
ph1 のレコードより大きい ph2 が存在しない = ph1 のレコードが最大、という考え方。
ph2 が存在しないというのは IS NULL で表現。

~~~sql
SELECT
    ph1.customer_id, ph1.amount, ph1.datetime
FROM
          purchase_header AS ph1
LEFT JOIN purchase_header AS ph2 ON (ph1.customer_id = ph2.customer_id AND ph1.amount < ph2.amount)
WHERE
    ph2.amount IS NULL
;
~~~

うーん、これを知った時は目からウロコでした。
パフォーマンスも良いです。

<img width="1424" alt="Screen Shot 2018-03-18 at 2.21.47.png" src="https://qiita-image-store.s3.amazonaws.com/0/106789/52521a8b-c30f-5cc5-d2c9-50747732fee2.png">

<img width="1280" alt="Screen Shot 2018-03-18 at 2.14.01.png" src="https://qiita-image-store.s3.amazonaws.com/0/106789/2c065bbd-6239-234e-e7cc-34d05541d662.png">

## パターン3 WHERE NOT EXISTS

@nora1962jp さんより頂きました。ありがとうございます。

~~~sql
SELECT
    ph1.customer_id, ph1.amount, ph1.datetime
FROM
    purchase_header AS ph1
WHERE
    NOT EXISTS
    ( SELECT 1 FROM purchase_header AS ph2
      WHERE (ph1.customer_id = ph2.customer_id AND ph1.amount < ph2.amount) )
;
~~~

## パターン4 FROM 句で GROUP BY して最大値用のテーブルを用意

@shinx55 さんより頂きました。ありがとうございます。

SQLで意味が理解しやすく速度もまずまずの別案

~~~sql
SELECT
    ph.customer_id AS customer_id, ph.amount AS max_amount, ph.datetime AS datetime
FROM
    purchase_header AS ph, 
	(
		SELECT
			customer_id, MAX(amount) AS max_amount
		FROM
			purchase_header
		GROUP BY customer_id
	) AS mh
WHERE ( ph.customer_id = mh.customer_id AND ph.amount = mh.max_amount )
~~~

## 参考

- [MySQL 5.6 リファレンスマニュアル 特定のカラムのグループごとの最大値が格納されている行](https://dev.mysql.com/doc/refman/5.6/ja/example-maximum-column-group-row.html)