【SQL】NOT EXISTS で最新（MAX）の値を取得する

こんにちは。ryohei515です。

例えば履歴を蓄積するようなテーブルがあるとき、顧客毎の履歴の最新値を取りたいことがあると思います。
私はありました。その時、最新値を NOT EXISTS で取得するようにしたことで、パフォーマンスを改善できたので、残しておこうと思います。

環境準備

こちらの記事に記載した環境のテーブルを使用しています。
（ちゃんと整理して書いていた過去の自分に感謝）

例えば rental と customer を使い、「各 customer が最後にレンタルした日時」を取得したいケースを考えるとします。
レンタルした日時なので、rental_date を使いたいところなのですが、一意ではない（同時に複数レンタルできる）ので、代わりに rental_id を使います。

最新の値の取得

一番単純に考えるなら、

SELECT
    c.customer_id
  , MAX(r.rental_id) AS max_rental_id
FROM
  customer c
LEFT JOIN
  rental r
ON c.customer_id = r.customer_id
GROUP BY
  c.customer_id
ORDER BY  
  MAX(r.rental_id) DESC
;

 customer_id | max_rental_id
-------------+---------------
         393 |         16049
         103 |         16048
         114 |         16047
          74 |         16046
          14 |         16045
.
.
.

で取れます。

ただ例えば、「customer の first_name を取得したい」となると、SELECT句に max(first_name)を追加するか、SELECT句と GROUP BY 両方に first_name 追加するような対応が必要となります。
また、結合するテーブルを増やしたいときも同様の問題が発生し、SELECT句に単純に項目を追加するということができないので、拡張性に難ありです。

MAX のサブクエリ

なので、ここは rental を「 customer_id 毎に最新（MAX）の rental_date を保持するサブクエリ」として集約を先に済ませておくことで問題を回避できそうです。

SELECT
    c.customer_id
  , q.max_rental_id
FROM
  customer c
LEFT JOIN
  (
    SELECT
        r.customer_id
      , MAX(rental_id) AS max_rental_id
    FROM
      rental r
    GROUP BY
      r.customer_id
  ) q
ON  c.customer_id = q.customer_id
ORDER BY
    q.max_rental_id DESC
;

これなら、「customer の first_name を取得したい」となっても、単に SELECT 句に first_name と追加すればいいだけなので、拡張性が高まります。
ただ一つ問題があり、GROUP BY による集約でコストが高くなるケースがあります。

NOT EXISTS のサブクエリ

COUNT で件数を出したい、SUM で合計値を出したいというケースであればこの書き方しかない*1のですが、MAX や MIN を出したいケースであれば、NOT EXIST を使って書くことも可能です。

SELECT
    c.customer_id
  , q.max_rental_id
FROM
  customer c
LEFT JOIN
  (
    SELECT
        r1.customer_id
      , r1.rental_id as max_rental_id
    FROM
      rental r1
    WHERE
      NOT EXISTS (
        SELECT
          1
        FROM
          rental r2
        WHERE
            r1.customer_id = r2.customer_id
        AND r1.rental_id < r2.rental_id
      )
  ) q
ON  c.customer_id = q.customer_id
ORDER BY
    q.max_rental_id desc
;

LEFT JOIN 内のクエリで何をやっているかというと、customer_id が同じrental_id の中で、大きいものがある場合を除外（最大値ではないものを除外）しています。
rental が例えば以下のようなデータだったとしたなら、

customer_id	rental_id
1	1
1	2
1	3

欲しいデータは rental_id = 3 のデータになります。

そのため、rental 同士（ r1 と r2 ）を customer_id で結合させることで以下のようなデータの状態となり、

customer_id	r1.rental_id	r2.rental_id
1	1	1
1	1	2
1	1	3
2	2	1
2	2	2
2	2	3
3	3	1
3	3	2
3	3	3

r1.rental_id < r2.rental_id に合致するデータがない（NOT EXISTS）、r1.rental_id = 3 のデータだけが取得できるといったロジックです。
※ r1.rental_id = 1 のデータ（1,2,3行目）は2,3行目で合致してしまっているので対象外。
※r1.rental_id = 2 のデータ（4,5,6行目）は6行目で合致してしまっているので対象外。

各クエリのコストについて

各クエリのコストを測るため、実行計画を出力してみました。
ただ、今回使った rental と custome のテーブルの場合だと、MAX したサブクエリを結合させた方がコストが低かったです。。（ちょっと予想外）

rental と customer - MAX のサブクエリ

                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Sort  (cost=390.61..392.11 rows=599 width=12)
   Sort Key: q.latest_rental_date DESC, c.customer_id
   ->  Hash Left Join  (cost=346.41..362.98 rows=599 width=12)
         Hash Cond: (c.customer_id = q.customer_id)
         ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=4)
         ->  Hash  (cost=338.92..338.92 rows=599 width=10)
               ->  Subquery Scan on q  (cost=326.94..338.92 rows=599 width=10)
                     ->  HashAggregate  (cost=326.94..332.93 rows=599 width=10)
                           Group Key: r.customer_id
                           ->  Seq Scan on rental p  (cost=0.00..253.96 rows=14596 width=10)
(10 rows)

rental と customer - NOT EXISTS のサブクエリ

                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Limit  (cost=1245.04..1245.06 rows=5 width=12)
   ->  Sort  (cost=1245.04..1271.78 rows=10696 width=12)
         Sort Key: r1.rental_date DESC, c.customer_id
         ->  Hash Right Join  (cost=533.47..1067.39 rows=10696 width=12)
               Hash Cond: (r1.customer_id = c.customer_id)
               ->  Hash Anti Join  (cost=510.99..1016.63 rows=10696 width=10)
                     Hash Cond: (r1.customer_id = r2.customer_id)
                     Join Filter: (r1.rental_date < r2.rental_date)
                     ->  Seq Scan on rental r1  (cost=0.00..310.44 rows=16044 width=10)
                     ->  Hash  (cost=310.44..310.44 rows=16044 width=10)
                           ->  Seq Scan on rental r2  (cost=0.00..310.44 rows=16044 width=10)
               ->  Hash  (cost=14.99..14.99 rows=599 width=4)
                     ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=4)
(13 rows)

業務で対応した箇所だと以下のように、NOT EXISTS にすることでコストが半分になりました。
データ量か、インデックスが貼られているか等、ケースバイケースなのかもしれません。

業務での対応 - MAX のサブクエリ

                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.42..26219.80 rows=1064 width=995)
.
.
.

業務での対応 - NOT EXISTS のサブクエリ

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Nested Loop  (cost=1291.76..12931.58 rows=1419 width=995)
.
.
.