お題
下記のテーブルで、顧客 (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 でブラウザで試せます
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 しました。
SELECT
customer_id, MAX(amount), datetime
FROM
purchase_header
GROUP BY customer_id
;
amount の最大値は取れていますが、意図した datetime を取れていません。
そもそも 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 つあった場合も両方取れています。
ただし相関サブクエリとなり、内側のサブクエリ × 外側のクエリの評価が行われてコストが高くなりがちです。
EXPLAIN すると sub_ph が DEPENDENT SUBQUERY となります。
パターン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
;
うーん、これを知った時は目からウロコでした。
パフォーマンスも良いです。
パターン3 WHERE NOT EXISTS
@nora1962jp さんより頂きました。ありがとうございます。
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で意味が理解しやすく速度もまずまずの別案
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 )