SQL グループごとの最大値を持つ行を取得するときは分析関数MAXを使う

SQLを書いていて、あるグループごとにある項目を集計して最大値を持つ行だけを検索したいときがある。
これまではあまり気にせずにgroup byと集計関数MAXを使ったサブクエリを検索条件に使うことで求めていたが、
パフォーマンスがよくないことがあり、今回改めて検証してみた。

結果、自分の中では分析関数MAXを使って検索条件とする方法がパフォーマンス的にもっともよいという結論に達した。

以下、Oracle DB 12cの環境で行った検証についてまとめてみる。
なおこの検証は自分の中では一般性を持つ結果だと考えているが、
データの特性やハードウェアスペックによって異なる結果となる可能性はあるため、必ずその環境で検証を行う必要がある。

問題設定

Oracle DB標準のサンプルスキーマであるSCOTTスキーマの従業員表(emp), 部署表(dept)を用いる。
部署ごとに、それぞれ給与が最大の従業員を求めるクエリを書き、パフォーマンスの観点で比較してみる。
なお、実行速度はデータ数が少なくほとんど差がないため、実行計画を見て比較する。

データの構成を示す。
・emp表

select
 e.EMPNO
, e.ENAME
, e.SAL
, e.DEPTNO
from SCOTT.EMP e

EMPNO	ENAME	SAL	DEPTNO
7369	SMITH	800	20
7499	ALLEN	1600	30
7521	WARD	1250	30
7566	JONES	2975	20
7654	MARTIN	1250	30
7698	BLAKE	2850	30
7782	CLARK	2450	10
7788	SCOTT	3000	20
7839	KING	5000	10
7844	TURNER	1500	30
7876	ADAMS	1100	20
7900	JAMES	950	30
7902	FORD	3000	20
7934	MILLER	1300	10

・dept表

select
  d.DEPTNO
, d.DNAME
from SCOTT.DEPT d

DEPTNO	DNAME
10	ACCOUNTING
20	RESEARCH
30	SALES
40	OPERATIONS

・emp表とdept表をDEPTNOで結合

select e.EMPNO, e.ENAME, e.SAL, d.DEPTNO, d.DNAME
from SCOTT.EMP e, SCOTT.DEPT d
where e.DEPTNO = d.DEPTNO
order by d.DEPTNO, e.SAL desc

EMPNO	ENAME	SAL	DEPTNO	DNAME
7839	KING	5000	10	ACCOUNTING
7782	CLARK	2450	10	ACCOUNTING
7934	MILLER	1300	10	ACCOUNTING
7788	SCOTT	3000	20	RESEARCH
7902	FORD	3000	20	RESEARCH
7566	JONES	2975	20	RESEARCH
7876	ADAMS	1100	20	RESEARCH
7369	SMITH	800	20	RESEARCH
7698	BLAKE	2850	30	SALES
7499	ALLEN	1600	30	SALES
7844	TURNER	1500	30	SALES
7654	MARTIN	1250	30	SALES
7521	WARD	1250	30	SALES
7900	JAMES	950	30	SALES

改めてクエリの条件を書くと、「部署ごとに、それぞれ給与が最大の従業員を求める」SQLを書きたい。
つまり以下のような結果が得られるはずである。

EMPNO	ENAME	SAL	DEPTNO	DNAME
7839	KING	5000	10	ACCOUNTING
7788	SCOTT	3000	20	RESEARCH
7902	FORD	3000	20	RESEARCH
7698	BLAKE	2850	30	SALES

このような結果を取得するためのSQLについて考えてみる。

パターン1: GROUP BY + MAX集計関数を使う方法

これまで私が何も考えずに書いていたパターン。
サブクエリで部署番号(dept.DEPTNO)ごとに最大の給与(emp.SAL)を持つ従業員番号(emp.EMPNO)を求め、
その結果の部署番号と給与で従業員表を検索する。

select
 e1.EMPNO
, e1.ENAME
, e1.SAL
, d.DEPTNO
, d.DNAME
from
 SCOTT.EMP e1
, (select
     ee.DEPTNO
   , MAX(ee.SAL) MAX_SAL
  from SCOTT.EMP ee
  group by ee.DEPTNO) e2
, SCOTT.DEPT d
where e1.DEPTNO = d.DEPTNO
  and e1.DEPTNO = e2.DEPTNO
  and e1.SAL = e2.MAX_SAL
order by d.DEPTNO

結果は以下の通り。意図通りの検索結果になっている。

EMPNO	ENAME	SAL	DEPTNO	DNAME
7839	KING	5000	10	ACCOUNTING
7788	SCOTT	3000	20	RESEARCH
7902	FORD	3000	20	RESEARCH
7698	BLAKE	2850	30	SALES

ここで実行計画を見ておく。

Plan hash value: 2079720973

------------------------------------------------------------------------------
| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |   |  65 | 7670 |  8 (25)| 00:00:01 |
|* 1 | FILTER       |   |   |   |      |     |
| 2 | SORT GROUP BY   |   |  65 | 7670 |  8 (25)| 00:00:01 |
|* 3 |  HASH JOIN     |   |  65 | 7670 |  7 (15)| 00:00:01 |
|* 4 |  HASH JOIN    |   |  14 | 1288 |  5 (20)| 00:00:01 |
| 5 |   TABLE ACCESS FULL| DEPT |  4 | 136 |  2 (0)| 00:00:01 |
| 6 |   TABLE ACCESS FULL| EMP |  14 | 812 |  2 (0)| 00:00:01 |
| 7 |  TABLE ACCESS FULL | EMP |  14 | 364 |  2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("E1"."SAL"=MAX("EE"."SAL"))
 3 - access("E1"."DEPTNO"="EE"."DEPTNO")
 4 - access("E1"."DEPTNO"="D"."DEPTNO")

6,7行目よりEMP表のフルスキャンが2回走っていることが分かる。
これはサブクエリと本体でそれぞれEMP表を検索し、結合しているためである。

パターン2: MAX分析関数を使う方法

上のパターンはEMP表のフルスキャンが2回走っていたが、
なんとかこれを1回で済ませられないだろうか、という観点で考えた結果、
分析関数MAXで部署番号ごとの最大給与を算出した上で直接where句でその行の給与列と最大給与を比較して絞ってしまえばよい、という発想に至ったので、
さっそくSQLクエリを書いてみた。

select
 e1.EMPNO
, e1.ENAME
, e1.SAL
, d.DEPTNO
, d.DNAME
from
(select
  ee.EMPNO
 , ee.ENAME
 , ee.SAL
 , MAX(ee.SAL) OVER(partition by ee.DEPTNO) MAX_SAL
 , ee.DEPTNO
 from SCOTT.EMP ee) e1
, SCOTT.DEPT d
where e1.DEPTNO = d.DEPTNO
 and e1.SAL = e1.MAX_SAL
order by d.DEPTNO

MAX(ee.SAL) OVER(partition by ee.DEPTNO) MAX_SALのところがいわゆる分析関数で
GROUP BYと同じようにee.DEPTNOの値ごとにee.SALの値を集計し、最大値を求める。
これはOracle DB特有の書き方だが、他のMySQLなどのRDBMSでも似たようなことはできるらしい。

このSQLでも(FORTとSCOTTの順番が逆になったが）同じ結果が得られる。

EMPNO	ENAME	SAL	DEPTNO	DNAME
7839	KING	5000	10	ACCOUNTING
7902	FORD	3000	20	RESEARCH
7788	SCOTT	3000	20	RESEARCH
7698	BLAKE	2850	30	SALES

さらに実行計画を見てみる。

Plan hash value: 2845603103

-----------------------------------------------------------------------------
| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time  |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |   |  14 | 1134 |  6 (34)| 00:00:01 |
| 1 | MERGE JOIN     |   |  14 | 1134 |  6 (34)| 00:00:01 |
|* 2 | VIEW       |   |  14 | 826 |  3 (34)| 00:00:01 |
| 3 |  WINDOW SORT   |   |  14 | 644 |  3 (34)| 00:00:01 |
| 4 |  TABLE ACCESS FULL| EMP |  14 | 644 |  2 (0)| 00:00:01 |
|* 5 | SORT JOIN     |   |  4 |  88 |  3 (34)| 00:00:01 |
| 6 |  TABLE ACCESS FULL | DEPT |  4 |  88 |  2 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - filter("E1"."SAL"="E1"."MAX_SAL")
 5 - access("E1"."DEPTNO"="D"."DEPTNO")
   filter("E1"."DEPTNO"="D"."DEPTNO")

EMP表のフルスキャンが1回(4行目)になり、COSTも8から6に減少している。
EMP表は従業員表なのでフルスキャンの回数が増えると劇的にパフォーマンスが悪くなるはずであり、
分析関数を用いた方法はフルスキャンの回数が減らせるので、明らかにパフォーマンスがよくなるはず。

まとめ

「グループごとに最大値を含む行のみを取得する」クエリを書く場合は、
GROUP BYとサブクエリで書くよりも、分析関数を利用したほうがパフォーマンス的によさそう。

以上！

カタカタブログ

SIerで働くITエンジニアがカタカタした記録を残す技術ブログ。Java, Oracle Database, Linuxが中心です。たまに数学やデータ分析なども。

SQL グループごとの最大値を持つ行を取得するときは分析関数MAXを使う

問題設定

パターン1: GROUP BY + MAX集計関数を使う方法

パターン2: MAX分析関数を使う方法

まとめ