カタカタブログ

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

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とサブクエリで書くよりも、分析関数を利用したほうがパフォーマンス的によさそう。

以上!