【SQL】グループごとの最大値を持つレコードを抽出する。

2023年11月1日
2023年11月1日
DB
DB, SQL

今回はSQLでグループごとの最大値を抽出してみます。

グループごとの最大値を抽出する方法はいくつかあるので、それぞれみていきます。

1 使用するデータ
2 SQL
3 まとめ

使用するデータ

以下のような表で、各食べ物についてそれぞれ最も最近食べた日のレコードを抽出してみます。

id	食事	食べた日
1	ラーメン	2023/4/10
2	ラーメン	2023/6/20
3	タコス	2023/6/27
4	ピザ	2023/7/2
5	ピザ	2023/7/19
6	寿司	2023/8/8
7	寿司	2023/9/12
8	寿司	2023/10/10
9	カレー	2023/10/19

テーブルを作成するDDLは以下の通りです。

-- テーブルを作成
CREATE TABLE meal_history (
    id INTEGER PRIMARY KEY,
    meal_name VARCHAR(255),
    eaten_date DATE
);

-- データを挿入
INSERT INTO meal_history (id, meal_name, eaten_date) VALUES
    (1, 'ラーメン', '2023-04-10'),
    (2, 'ラーメン', '2023-06-20'),
    (3, 'タコス', '2023-06-27'),
    (4, 'ピザ', '2023-07-02'),
    (5, 'ピザ', '2023-07-19'),
    (6, '寿司', '2023-08-08'),
    (7, '寿司', '2023-09-12'),
    (8, '寿司', '2023-10-10'),
    (9, 'カレー', '2023-10-19');

SQL

サブクエリを使って抽出

まずはサブクエリを用いた方法です。サブクエリとは副問合せとも呼ばれ、SQLの中にSQLを記述する入れ子構造のことを指します。

下記は、サブクエリを使って各食べ物についてそれぞれ最も最近食べた日のレコードを抽出する一例です。

サブクエリは、メインクエリ（m1）の各行に対して実施されます。サブクエリ内では、m1各行のmeal_name（食事）に一致する行について、eaten_date（食べた日）の最大値を取得します。

その日付にヒットするm1の行について、id, meal_name, eaten_dateを取得します（食べ物ごと）

SELECT
    m1.id
    , m1.meal_name
    , m1.eaten_date 
FROM
    meal_history m1 
WHERE
    m1.eaten_date = ( 
        SELECT
            MAX(m2.eaten_date) 
        FROM
            meal_history m2 
        WHERE
            m2.meal_name = m1.meal_name
    );

id	食事	食べた日
2	ラーメン	2023/6/20
3	タコス	2023/6/27
5	ピザ	2023/7/19
8	寿司	2023/10/10
9	カレー	2023/10/19

ただ、個人的になんか直感的に理解しづらいSQLだと思います。

サブクエリを使って抽出②

上記よりもう少し直感的に理解やすいサブクエリを用いたSQLを作ってみます。以下では、サブクエリで取得した結果をINNER JOINしています。

サブクエリの中は、GROUP BYで食事名ごとに日付の最大値を取得した結果です。その結果を元のテーブルとINNER JOINすることで、各食事の日付が最大（最新）のレコードを抽出しています。

SELECT
    m1.id
    , m1.meal_name
    , m1.eaten_date 
FROM
    meal_history m1 
    INNER JOIN ( 
        SELECT
            meal_name
            , MAX(eaten_date) recent_date 
        FROM
            meal_history 
        GROUP BY
            meal_name
    ) m2 
        ON m1.meal_name = m2.meal_name 
        AND m1.eaten_date = m2.recent_date 
ORDER BY
    m1.id;

個人的には、こっちのSQLの方が直感的にどんな処理をしているか理解しやすいです。

WITH句を使って抽出

WITH句を使った方法でも実現可能です。

WITH句はサブクエリに名前を付けて一時的に管理できるものです。SQLの可読性は上がる一方、サブクエリを別テーブルとしてメモリ上に保持するため、メモリを消費してしまうというデメリットもあります。

DBの種類によってはWITH句を使用できない場合もあるので、ご使用のDBでWITH句が利用可能かは事前にご確認ください。

下記では、WITH句で作成したRankedMealsで、食事ごと食べた日ごとに並べ替え、食事ごとに1からの連番を付与しています（下図）。

rn=1のレコードが各食事での最新日付になるので、その行を抽出することで実現しています。

WITH RankedMeals AS ( 
    SELECT
        id
        , meal_name
        , eaten_date
        , ROW_NUMBER() OVER (PARTITION BY meal_name ORDER BY eaten_date DESC) AS rn 
    FROM
        meal_history
) 

SELECT
    id
    , meal_name
    , eaten_date 
FROM
    RankedMeals 
WHERE
    rn = 1;

WITH句内SQLの実行結果は以下の通りです。