MySQLで指定範囲内のデータをCOUNTやSUMしたい

MySQLでCOUNT/SUMなど値の集計をする場合、LIMIT句で範囲を制限しても全体の集計がされてしまいます。

本記事では、LIMIT句で集計範囲を絞り、その中でCOUNT/SUMを行う方法を説明します。

事例紹介

例えば下記のようなデータの入ったテーブルで、User1の直近３件のスコアを合計したいとします。

テーブル名：test_table

上手く行かない例

test_tableから、User1の直近の3件のデータを得る場合は次のSQL文を書きます。

SELECT *
FROM test_table
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 3;

結果
日付の新しいものから3件が取得できます。

一方、同じ書き方でCOUNTやSUMを行うと上手くいきません。

SELECT COUNT(*), SUM(score)
FROM test_table
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 3;

結果

（希望する値は「COUNT = 3、SUM = 12」）

「LIMIT」は出力される結果の行数を絞るためのもので、集計範囲の指定には利用できないためです。

ですので、今回の目的の結果を返すためには集計する前に行数を絞っておく必要があります。

解決方法

指定範囲に対してCOUNTやSUMを適用するには、FROM句に絞り込み済みのデータを指定してやります。

SELECT COUNT(*), SUM(score)
FROM (
    SELECT *
    FROM test_table
    WHERE user_id = 1
    ORDER BY created_at DESC
    LIMIT 3
) as t;

結果

おまけ

LaravelのEloquentを使うとこんな感じで書けます。

$subQuery = TestTableModel::query()
    ->where('user_id', '=', 1)
    ->orderByDesc('created_at')
    ->limit(5);

$results = TestTableModel::query()
    ->select(
        DB::raw('COUNT(*) as count'),
        DB::raw('SUM(score) as sum'),
    )
    ->fromSub($subQuery, 'tb')
    ->get();

終わりに

サブクエリを用いると、絞り込んだデータに対して処理を行なうことが出来ます。

このテクニックは大規模なデータのテーブルを操作する際にも有効で、処理速度改善に活用できます。