MySQL

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

サンプルデータ

SQL Fiddle でブラウザで試せます 

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');

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

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

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

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

Screen_Shot_2018-03-18_at_1_27_44.jpg

そもそも GROUP BY で指定されておらず、集約もされていない datetime を SELECT することは標準の SQL では不正ですが MySQL では許容しているそうです。
MySQL での GROUP BY の処理

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

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

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 つあった場合も両方取れています。

Screen Shot 2018-03-18 at 1.46.05.png

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

Screen Shot 2018-03-18 at 2.12.31.png

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

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

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
;

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

Screen Shot 2018-03-18 at 2.21.47.png

Screen Shot 2018-03-18 at 2.14.01.png

参考