GRPUP BY で集計→ max のレコードを抽出（MySQL/MariaDB）のための備忘録

見よう見まねでSQL。

このような「食べ放題」テーブルで、

+----+--------------+------------+--------------+
| ID | 学生番号     | 日付       | おにぎり     |
+----+--------------+------------+--------------+
|  1 | 100          | 2024-03-27 |            5 |
|  2 | 100          | 2024-03-22 |            4 |
|  3 | 101          | 2024-03-10 |            7 |
|  4 | 102          | 2024-03-21 |           10 |
|  5 | 101          | 2024-03-15 |            3 |
+----+--------------+------------+--------------+

最後にチャレンジしたスコアを学生ごとに出したい。

+----+--------------+------------+--------------+
| ID | 学生番号     | 日付       | おにぎり     |
+----+--------------+------------+--------------+
|  1 | 100          | 2024-03-27 |            5 |
|  4 | 102          | 2024-03-21 |           10 |
|  5 | 101          | 2024-03-15 |            3 |
+----+--------------+------------+--------------+

上記のようなものを出すには、

SELECT ID,  学生番号,max(日付),おにぎり  FROM 食べ放題
            GROUP BY 学生番号

でいいかな・・・

+----+--------------+-------------+--------------+
| ID | 学生番号     | max(日付)   | おにぎり     |
+----+--------------+-------------+--------------+
|  1 | 100          | 2024-03-27  |            5 |
|  3 | 101          | 2024-03-15  |            7 |
|  4 | 102          | 2024-03-21  |           10 |
+----+--------------+-------------+--------------+

うーん？

環境

• mysql Ver 15.1 Distrib 10.6.16-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
• 母艦 Ubuntu22.04 LTS

解決方法

調べてみたらいろいろ方法があるらしい

INNTER JOIN を使う方法

 SELECT ID,学生番号 ,日付 ,おにぎり
  FROM 食べ放題 AS 食べ放題A
  INNER JOIN 
   (
    SELECT max(日付) AS 日付B ,学生番号 AS 学生番号B FROM 食べ放題
     GROUP BY 学生番号B
   )
   AS 食べ放題B
  ON 食べ放題A.学生番号=食べ放題B.学生番号B
  AND 食べ放題A.日付=食べ放題B.日付B;

+----+--------------+------------+--------------+
| ID | 学生番号     | 日付       | おにぎり     |
+----+--------------+------------+--------------+
|  1 | 100          | 2024-03-27 |            5 |
|  4 | 102          | 2024-03-21 |           10 |
|  5 | 101          | 2024-03-15 |            3 |
+----+--------------+------------+--------------+

PARTITON BY を使う方法

GROUP BY を使わない方法。
Rank フィールドを持ったデータを作って、 PARTITON BY で Rank = 1 を抽出する。
こっちがわかりやすいかな。

SELECT 食べ放題Member.ID,食べ放題Member.おにぎり,食べ放題Member.学生番号,食べ放題Member.日付
FROM 
  ( SELECT ROW_NUMBER() OVER (
    PARTITION BY 学生番号
    ORDER BY 日付 DESC
  ) AS 日付Rank,
   ID,
   おにぎり,
   学生番号,
   日付
   FROM  食べ放題
   ) AS 食べ放題Member
   WHERE 食べ放題Member.日付Rank = 1

+------+--------------+--------------+------------+
| ID   | おにぎり     | 学生番号     | 日付       |
+------+--------------+--------------+------------+
|    1 |            5 | 100          | 2024-03-27 |
|    5 |            3 | 101          | 2024-03-15 |
|    4 |           10 | 102          | 2024-03-21 |
+------+--------------+--------------+------------+

うまくいきました。

抽出元を絞り込む

SELECT 食べ放題Member.ID,食べ放題Member.おにぎり,食べ放題Member.学生番号,食べ放題Member.日付
FROM 
  ( SELECT ROW_NUMBER() OVER (
    PARTITION BY 学生番号
    ORDER BY 日付 DESC
  ) AS 日付Rank,
   ID,
   おにぎり,
   学生番号,
   日付
   FROM  食べ放題 WHERE 日付<'2024/03/25'
   ) AS 食べ放題Member
   WHERE 食べ放題Member.日付Rank = 1

+------+--------------+--------------+------------+
| ID   | おにぎり     | 学生番号     | 日付       |
+------+--------------+--------------+------------+
|    2 |            4 | 100          | 2024-03-22 |
|    5 |            3 | 101          | 2024-03-15 |
|    4 |           10 | 102          | 2024-03-21 |
+------+--------------+--------------+------------+