背景と状況

データを履歴で持っているテーブルで最新レコードだけが欲しい場合があります。例えば、体重を管理しているテーブルなら最後に入力した体重を表示したい。
※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パズル