背景と状況
データを履歴で持っているテーブルで最新レコードだけが欲しい場合があります。例えば、体重を管理しているテーブルなら最後に入力した体重を表示したい。
※IBM Db2 V11.5 Windowsで確認していますが他のDBMSでもできるはずです。
やりたいこと
ユーザーごとに最新のレコードだけを取得したい。SQLでグループごとに最新レコードを出力します。例では最新の体重一覧をユーザー単位で出力します。
体重管理テーブル wt_tbl
<-------------主キー----------->
USR_ID WT_DATE WT_TIME WT_KG
----------- ---------- -------- -------
1 2020-09-12 08:23:14 53.3
1 2020-09-13 08:11:19 53.6
1 2020-09-14 08:03:11 53.5 <-- 最新レコード
2 2020-09-13 08:45:01 73.3
2 2020-09-14 08:42:31 73.6
2 2020-09-14 15:55:21 73.9 <-- 最新レコード
3 2020-09-07 09:32:19 63.7
3 2020-09-08 08:37:39 63.3
3 2020-09-10 08:12:28 64.1 <-- 最新レコード
制約条件
- テーブル設計は変更できない
- レコードが多いため、パフォーマンスを考慮したい
副照会を使う方法
副照会のなかでgroup byを使ってユーザーごとに最新レコードをMax関数で特定、主キーでジョインします。
latestSubQuery.sql
SELECT w.*
FROM wt_tbl w
INNER JOIN(SELECT usr_id,
Max(TIMESTAMP(wt_date, wt_time)) AS max_timestamp
FROM wt_tbl
GROUP BY usr_id) m
ON w.usr_id = m.usr_id
AND w.wt_date = DATE(m.max_timestamp)
AND w.wt_time = TIME(m.max_timestamp)
ORDER BY usr_id;
ユーザーごとに最新レコードのみが出力されます。
実行結果
F:\>db2 -tvf latestSubQuery.sql
SELECT w.* FROM wt_tbl w INNER JOIN(SELECT usr_id, Max(TIMESTAMP(wt_date, wt_time)) AS max_timestamp FROM wt_tbl GROUP BY usr_id) m ON w.usr_id = m.usr_id AND w.wt_date = DATE(m.max_timestamp) AND w.wt_time = TIME(m.max_timestamp) ORDER BY usr_id
USR_ID WT_DATE WT_TIME WT_KG
----------- ---------- -------- -------
1 2020-09-14 08:03:11 53.5
2 2020-09-14 15:55:21 73.9
3 2020-09-10 08:12:28 64.1
3 レコードが選択されました。
Rank関数を使用する方法
ユーザー単位に日時でランキングをつけて、Where句で1位だけをとります。
latestRank.sql
SELECT DISTINCT usr_id, wt_date, wt_time, wt_kg
FROM (SELECT *,
RANK()
OVER(
PARTITION BY usr_id
ORDER BY wt_date desc, wt_time desc) AS RANK
FROM wt_tbl)
WHERE RANK = 1
ORDER BY usr_id;
副照会と同じ結果になります。
実行結果
F:\>db2 -tvf latestRank.sql
SELECT DISTINCT usr_id, wt_date, wt_time, wt_kg FROM (SELECT *, RANK() OVER( PARTITION BY usr_id ORDER BY wt_date desc, wt_time desc) AS RANK FROM wt_tbl) WHERE RANK = 1 ORDER BY usr_id
USR_ID WT_DATE WT_TIME WT_KG
----------- ---------- -------- -------
1 2020-09-14 08:03:11 53.5
2 2020-09-14 15:55:21 73.9
3 2020-09-10 08:12:28 64.1
3 レコードが選択されました。
First_value関数を使用する方法
ユーザー単位に日時順で最初の値をとります。
latestFirstValue.sql
SELECT DISTINCT
usr_id,
FIRST_VALUE(wt_date) OVER(PARTITION BY usr_id ORDER BY wt_date desc, wt_time desc) AS wt_date,
FIRST_VALUE(wt_time) OVER(PARTITION BY usr_id ORDER BY wt_date desc, wt_time desc) AS wt_time,
FIRST_VALUE(wt_kg) OVER(PARTITION BY usr_id ORDER BY wt_date desc, wt_time desc) AS wt_kg
FROM wt_tbl
ORDER BY usr_id;
副照会と同じ結果になります。
実行結果
F:\>db2 -tf latestFirstValue.sql
USR_ID WT_DATE WT_TIME WT_KG
----------- ---------- -------- -------
1 2020-09-14 08:03:11 53.5
2 2020-09-14 15:55:21 73.9
3 2020-09-10 08:12:28 64.1
3 レコードが選択されました。
結果は同じですがパフォーマンスはどうでしょうか。
注意：NULLがorder byの列にある場合
- NULLを最小値とみなすか最大値とみなすかはDBMSによって異なる
- Db2はNULLを最大値とみなす
- Db2でNULLを最小値として扱いたい場合、ORDER BY wt_date DESC NULLS LAST と記述
アクセスプラン
テーブルにダミーレコードを1万件ほどインサートして、SQLのアクセスプラン(実行計画)を比較しましょう。アクセスプランはDb2のエクスプレインで出力しています。
副照会のアクセスプラン latestSubQuery.sql
Access Plan:
-----------
Total Cost: 118.921
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1120
TBSCAN
( 2)
118.921
122
|
1120
SORT
( 3)
118.899
122
|
1120
^HSJOIN
( 4)
118.733
122
/---+----\
10009 10000
TBSCAN GRPBY
( 5) ( 6)
57.6302 59.823
61 61
| |
10009 10009
TABLE: DB2ADMIN TBSCAN
WT_TBL ( 7)
Q5 59.6181
61
|
10009
SORT
( 8)
59.421
61
|
10009
TBSCAN
( 9)
57.6302
61
|
10009
TABLE: DB2ADMIN
WT_TBL
Q1
副照会のコストは約118です。
Rank関数のアクセスプラン latestRank.sql
Access Plan:
-----------
Total Cost: 61.7188
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
400.36
FILTER
( 2)
61.7188
61
|
10009
TBSCAN
( 3)
60.9085
61
|
10009
SORT
( 4)
60.7114
61
|
10009
TBSCAN
( 5)
57.6302
61
|
10009
TABLE: DB2ADMIN
WT_TBL
Q1
Rank関数のコストは約61です。
Fitst_value関数のアクセスプラン latestFirstValue.sql
Access Plan:
-----------
Total Cost: 66.3212
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
10009
TBSCAN
( 2)
66.3212
61
|
10009
SORT
( 3)
66.124
61
|
10009
TBSCAN
( 4)
60.9424
61
|
10009
SORT
( 5)
60.7452
61
|
10009
TBSCAN
( 6)
57.6302
61
|
10009
TABLE: DB2ADMIN
WT_TBL
Q1
Fitst_value関数のコストは約66です。
パフォーマンス比較
| SQL | コスト |
|---|---|
| 副照会 | 118 |
| Rank関数 | 61 |
| First_value関数 | 66 |
- Rank関数とFirst_value関数のコストは副照会に比べ約1/2
- 副照会はジョインが発生する分、パフォーマンスが悪化
まとめ
- グループごとに最新レコードを取る方法：副照会、Rank関数、Fitst_value関数
- Rank関数とFitst_value関数がパフォーマンス的に優位
- Fitst_value関数の記述がシンプル
参考
以下の書籍を参考にしています。
翔泳社 SQLパズル