SQLで最新のレコードのみを取り出す方法

6565 ワード
SQL テーブルデータ データ分析 GroupBy SQL テキストリンク

概要

事例として、購買情報のテーブルを用意しました。
1ユーザは複数の購買IDを持つことが可能です。

🔽入力となるテーブル(テーブル名 : purchases)🔽

purchase_id user_id purchase_date price
1 A 03/01 ¥40,000
2 A 01/01 ¥35,000
3 B 01/01 ¥20,000
4 A 02/01 ¥5,000
5 B 04/01 ¥10,000
6 C 02/01 ¥20,000

今回、各ユーザの最新の購買レコードのみ抽出したテーブルを作成したいとします。
🔽目標となるテーブル🔽

purchase_id user_id purchase_date price
1 A 03/01 ¥40,000
5 B 04/01 ¥10,000
6 C 02/01 ¥20,000

最新レコードのみ抽出するために、「GROUP BYやを使え！」みたいな記事も多いのです。
しかし、GROUP BY対象になるカラム(user_id)や、ORDER BY対象になるカラム(purchace_date)以外のカラム情報も取得したいときに、GROUP BYだとできません。😭

「では、どうやってやるのか？」について、忘備録として残します。

方法

① グループ内で新しい/古いもの順の連番をつける

ROW_NUMBER() OVER(PARTITION BY {グループ化する列} ORDER BY {並びかえる列} ASC/DESC)
とすることで、{並びかえる列}を昇順・降順に並び替え、グループごとに連番（順位）をふることが可能です。

具体例を見てみましょう。

select 
  *
 from (
   select
    *
    row_number() over (PARTITION BY user_id ORDER BY purchase_date DESC) AS number
    FROM purchases
  )

を用いることによって、以下のテーブルになります。

purchase_id user_id purchase_date price number
1 A 03/01 ¥40,000 1
2 A 01/01 ¥35,000 3
3 B 01/01 ¥20,000 2
4 A 02/01 ¥5,000 2
5 B 04/01 ¥10,000 1
6 C 02/01 ¥20,000 1

② 値が1のものだけ取得する。
次にwhere number = 1のものを取得すれば、最新のレコードのみになります！

結論

以下のコードでOK！

first_rank as (
 select 
  *
 from (
   select
    *
    row_number() over (PARTITION BY user_id ORDER BY purchase_date DESC) AS num
    FROM purchases
  )
  where num = 1
)

もし違ういいやり方知っていれば、是非教えてください！

補足

順位づけの関数はrankとrow_numberの２つがあるが、row_numberを推奨。
理由は、作成日が同一のレコードがあった際に、rankだと同じ数字が複数現れるから。