MySQLで同一ユーザの複数レコードから最新のデータのみ抽出する

概要

MySQLでテーブル内に同一ユーザ（ユーザじゃなくてもいいですが）のレコードが複数入っている状態で、ユーザごとの最新もしくは一番古いレコードのみを抽出する方法を紹介します。

例えば、user_purchase という、ユーザごとに何をいつ購入しているかの情報を保有しているテーブルがあるとします。
今回のケースだと、赤文字で示されている最新データだけを引っ張ってくる方法になります。

数字のみの場合

抽出するデータが数字のみの場合は以下のように簡単に抽出が可能です。今回のケースだと revenue のみを抽出するパターンです。

SELECT
  user_id
, MAX(created_at*10000+revenue)%10000 AS last_revenue
FROM user_purchase
GROUP BY user_id
;

user_id で GROUP BY し、MAX() 関数を使用し一番最新の created_at のレコードを引っ張ってきます、その際datetime型である created_at に10000を掛け（桁数は抽出するデータに合わせて適当に）対象のカラムのデータを足します。最後にそれを%10000で余りを出してあげれば最新レコードの数字のみが残るという理屈です。

順を追って実行してみるとこういった実行結果になります。

SELECT created_at ...
> "2018-01-01 01:00:00"

SELECT craeted_at * 10000 ...
> 201801010100000000

SELECT created_at * 10000 + 10 ...
> 201801010100000010

SELECT (created_at * 10000 + 10)%10000 ...
> 10

文字列を抽出する場合その①

文字列を抽出する場合は少しだけ複雑になりますが、以下のように実現可能です。

SELECT
  us1.user_id AS user_id
, us2.name AS last_name
FROM
  (SELECT
    user_id
  , MAX(created_at) AS last_created_at
  FROM user_purchase
  GROUP BY user_id
  ) AS us1
JOIN user_purchase AS us2
  ON us1.user_id = us2.user_id
  AND us1.last_created_at = us2.created_at
;

ネスト内でユーザごとの最新レコードの created_at を抽出し、そこにもう一度同じテーブルをJOINし user_id と created_at でデータの紐づけをしてあげれば最新のレコードだけ取り出すことが可能です。

文字列を抽出する場合その②

ネストしてJOINを行うとクエリが複雑になるので、数字のみを抽出した場合と同じような書き方をしてあげることも可能です。

SELECT
  user_id
, SUBSTR(MAX(CONCAT(created_at, name)), 20) AS last_name
FROM user_purchase
GROUP BY user_id
;

createt_at と name を CONCAT 関数で文字列としてくっつけてしまい MAX() 関数で一番大きい（最新）のデータのみにしたあと SUBSTR() 関数で created_at の部分のみ（20文字目以前のみ）除外します。

順を追って実行するとこうなります。

SELECT user_id, CONCAT(created_at, name) ...
> 1234　"2018/05/03 12:00:00リンゴ"
> 1234　"2018/05/02 12:00:00メロン"
> 1234　"2018/05/01 12:00:00バナナ"

SELECT user_id, MAX(CONCAT(created_at, name)) ...
> 1234　"2018/05/03 12:00:00リンゴ"

SELECT user_id, SUBSTR(MAX(CONCAT(created_at, name)), 20) ...
> 1234　"リンゴ"