SQL:ユーザーごとに最新レコードが欲しい

# 背景と状況
データを履歴で持っているテーブルで最新レコードだけが欲しい場合があります。例えば、体重を管理しているテーブルなら最後に入力した体重を表示したい。
※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関数で特定、主キーでジョインします。

```sql: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位だけをとります。

```sql: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関数を使用する方法
ユーザー単位に日時順で最初の値をとります。

```sql: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パズル](https://www.shoeisha.co.jp/book/detail/9784798114132 "翔泳社のサイトへ")