各ユーザーの履歴データを件数を限定して、一括取得する方法の案

概要

実装していく中で、複数ユーザーの履歴をそれぞれ最新3件だけ表示するという必要があり、その方法を私なりに考えた結果をまとめておきたいと思います。

ここで使用するのは、SQLのウィンドウ関数とサブクエリです。

説明するためのテストデータ紹介

例えば、以下のようなテーブルがあったとします。

ユーザーテーブル(user)

カラム名	説明
id	ユーザーID
user_name	ユーザー名

操作履歴テーブル(history)

カラム名	説明
id	操作履歴id
user_id	ユーザーID
history_content	操作内容

考え方

操作履歴をすべて取得する場合は簡単だと思いますが、
ここに件数という条件が付与されると少し工夫が必要になります。

さて、ユーザーごとの操作履歴を3件ずつ取得する方法を考えてみます。

まず、私がすぐに思いついたのはユーザーidごとに3件取得するという方法です。
しかし、この方法は取得したいユーザー数が増えるほどクエリ数が増えるため、おすすめできません。

そこで、以下の方法を考えました。

1. ユーザーごとに最新の履歴から順番を付与
2. 順番が3以下のデータを取得

上記の方法を実現するためにはウィンドウ関数とサブクエリを使用します。
(WITH句でも良いとは思いますが、ここではサブクエリを使用する方針とします。)

実際の方法

1.ユーザーごとに最新の履歴から順番を付与
下記のSQLでindexという名称で順番を付与することができます。

SELECT
    id,
    user_id,
    history_content,
    - 以下でウィンドウ関数を利用し、履歴の最新順に順番を付与
    ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY id DESC ) AS index
FROM
    history
WHERE
    user_id IN (1,2,3)
;

2.順番が3以下のデータを取得
単純に、1で付与した順番に対して、3以下のものを取得するようにwhere句で条件を付与します。

SELECT
    *
FROM
    (1のサブクエリ)
WHERE
    index <= 3
;

1と2を合わせた結果

SELECT
    *
FROM
    (
        SELECT
            id,
            user_id,
            history_content,
            - 以下でウィンドウ関数を利用し、履歴の最新順に順番を付与
            ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY id DESC ) AS index
        FROM
            history
        WHERE
            user_id IN (1,2,3)
    ) AS history_index
WHERE
    history_index.index <= 3
;

注意点
ユーザーを絞り込むwhere句については、サブクエリの段階で絞り込みをしないと、すべてのユーザー履歴を読み込んで順番を付与することになるので、DB負荷があがってしまいます。

まとめ

上記のような形で、取得することでユーザー数に関係なく、クエリの発行回数は固定で効率よく取得できるので、おすすめです。

ユースケースとして、特定のユーザーでのグループがあり、そのグループページにて、それぞれの最新の投稿履歴やゲームであれば戦闘履歴などを表示する場合には有効かと思います。