Postgresqlで最大値を持つ行を取得する最速の方法

2018年2月16日

はじめに

こんにちは、よっしーです。

SQLを扱っていると、よく何かの値の最大値を持つ行を取得することってありますよね。そして、その方法はプログラマさんによってマチマチだったりします。
意外と分かりやすくて高速な挙動をするSQLを考えるのは難しいものだと思います。

今回は、最大値を持つ行を取得する方法について、分かりやすくて高速な方法を考えます。

テスト環境

こんな環境でテストしました。

  • Postgresql : Version10.2
  • CPU : 2.2 GHz Intel Core i7

テストケース1

イメージとしてはこんなテーブルを考えます。

ID 点数 名前
ID SCORE name

こんなテーブルで、SCOREの高い人を取得するSQLを考えてみたいと思います。

テストデータの準備

まずは、テストデータを準備しようかと思います。

CREATE TABLE test_data(
id serial NOT NULL
, score INTEGER NOT NULL
, name VARCHAR (100)
, PRIMARY KEY (id)
);

INSERT
INTO test_data(score, name)
SELECT
(random() * 100000) ::INTEGER % 100
, format('テスト%s', i)
FROM
generate_series(1, 100000) AS i;

これで、ランダムで、0-99点の人を100000件作成しました。100000件のデータを検索します。

最大値を持つ行の検索のSQL

いろんな検索方法を考えてみます。

WHERE句でNOT EXISTを使う方法

SELECT
*
FROM
test_data AS m
WHERE
NOT EXISTS (
SELECT
1
FROM
test_data AS s
WHERE
m.score < s.score
);

自分より、大きなScoreをもつ行が無いかどうか検索する方法です。時々、使っている人もいます。でも、パッと見た感じ、最大の値を取ってくるように見えないので、僕は苦手です。

WHERE句でサブクエリを使う方法

SELECT
*
FROM
test_data AS m
WHERE
m.score = (SELECT max(score) AS score FROM test_data);

先程と違って、サブクエリにmaxが入っているので、何がしたいかすぐわかります。

INNER JOINを使う方法

SELECT
m.*
FROM
test_data AS m
INNER JOIN (SELECT max(score) AS score FROM test_data) t
ON m.score = t.score;

WHERE句と似ていますが、サブクエリをINNER JOINして、最大値の行を取得しています。サブクエリにmaxがはいいているので、わかりやすいかと思います。僕はいつもこの方法を使っています。

NATURAL JOINを使う方法

SELECT
m.*
FROM
test_data AS m
NATURAL JOIN (SELECT max(score) AS score FROM test_data) t;

INNER JOINを使う方法と基本的に同じですが、ONを書く必要がないので、短くなります。でもわかりづらくなるかも・・・。

Window関数を使う方法

SELECT
m.id
, m.score
, m.name
FROM
(
SELECT
*
, max(score) OVER () AS max_score
FROM
test_data
) m
WHERE
m.score = m.max_score

最大値の行を取得するのにわざわざWindow関数を使用するのは大げさですが、一応やってみました。まあまあ、わかりやすいですが、SQLが長いです。

実行速度

SQL 実行速度 可読性 SQLの長さ
WHERE句でNOT EXISTを使う方法 11.006s  △
WHERE句でサブクエリを使う方法 0.027s  
INNER JOINを使う方法 0.029s
NATURAL JOINを使う方法 0.038s  
Window関数を使う方法 0.067s  △

テストケース2

先程は、単にSCOREの高い人を取得しました。でも、単純に高い値を取得することはあんまりありません。どちらかというと、何かのグループのなかで最大値を取得することのほうが多そうです。
それで、こんどはグループの中で最大値を持つ行をピックアップします。こんなテーブルで試してみます。

ID 点数 名前 グループ番号
ID SCORE name group_num

同じグループ番号の中で、一番scoreの高い人をピックアップします。

テストデータの作成

CREATE TABLE test_data2(
id serial NOT NULL
, score INTEGER NOT NULL
, name VARCHAR (100)
, group_num INTEGER
, PRIMARY KEY (id)
);

INSERT
INTO test_data2(score, name, group_num)
SELECT
random() * 100000 ::INTEGER
, format('テスト%s', i)
, random() * 100 ::INTEGER
FROM
generate_series(1, 100000) AS i;

グループの中で最大値の行を取得するSQL

WHERE句でNOT EXISTを使う方法

SELECT
*
FROM
test_data2 AS m
WHERE
NOT EXISTS (
SELECT
1
FROM
test_data2 AS s
WHERE
m.score < s.score
AND m.group_num = s.group_num
);

単純にサブクエリにgroup_numの=を追加しただけです。拡張するのは簡単です。

WHERE句でサブクエリを使う方法

今回は、サブクエリで scoregroup_numの2つを返す必要があるので、簡単に書くことはできませんでした。

INNER JOINを使う方法

SELECT
m.*
FROM
test_data2 AS m
INNER JOIN (
SELECT
max(score) AS score
, group_num
FROM
test_data2
group by
group_num
) s
ON m.score = s.score
AND m.group_num = s.group_num;

サブクエリ内で、group by を使って、JOIN時のONの条件も増えました。結構記述量が増えた気がします。

Window関数を使う方法

最後にWindow関数を使用する方法

SELECT
m.id
, m.score
, m.name
, m.group_num
FROM
(
SELECT
*
, max(score) OVER (PARTITION BY group_num) AS max_score
FROM
test_data2
) m
WHERE
m.score = m.max_score;

OVERの引数にPARTITION BYを追加して、group_numごとの最大値を取得することができます。慣れてくると読みやすいかもしれません。

実行速度

テストケース2の実行速度と可読性をまとめてみました。

SQL 実行速度 可読性 SQLの長さ
WHERE句でNOT EXISTを使う方法 0.160s  △
WHERE句でサブクエリを使う方法  -
INNER JOINを使う方法 0.051s
Window関数を使う方法 0.112s  △

NOT EXISTを使用する方法がなぜか急に早くなりました。とはいえ、読みにくいと僕は思っています。

まとめ

最大値の行を取得するSQLはいろいろありますが、INNER JOINが可読性も悪くないし、性能的には一番良さそうかなと思います。Postgresql以外はあまり使わないので、他のDBでどんな結果になるかわわからないですけど、多分似たような結果になる気がします。