概要

テーブル構成

Bugs

bug_id	bug_name
1	Yabaiバグ
2	Kituiバグ
3	Iyanaバグ
4	Kanashiiバグ
5	Rakunaバグ

BugProducts

product_id	date_reported	bug_id
101	2022-12-12 12:00	1
101	2022-12-09 10:00	2
102	2021-02-03 07:00	3
102	2020-03-04 05:00	4
103	1992-01-15 01:00	5

Products

product_id	product_name
101	AAAサービス
102	BBBサービス
103	CCCサービス

クエリ

「サービスごとに、直近発生した、バグ」を取得したかったので以下クエリを実行した

SELECT product_id, MAX(date_reported) AS latest, bug_id
FROM Bugs
INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;

想定する結果

product_id	latest	bug_id
101	2022-12-12 12:00	1
102	2021-02-03 07:00	3
103	1992-01-15 01:00	5

実際の結果

product_id	latest	bug_id
101	2022-12-12 12:00	2(！？)
102	2021-02-03 07:00	3
103	1992-01-15 01:00	5

問題：bug_idがMAX(date_reported)の値を取得していない（！？の箇所）

→必ずしもSQLでいい感じにMAX(date_reported)であるbug_idを取得するわけではない

原因

GROUP BYで指定したカラムの値ごとにそれぞれ1つの行グループが存在するため（単一値の原則）

＝GROUP BY使用時のSELECT文で指定したカラムはすべて一意の値となることが原則

今回、GROUP BYで指定したproduct_idはbug_idを２つ持つため、単一値の原則に反するため想定外の挙動になった

対策

1, 単一ではないカラムを使わない

SELECT product_id, MAX(date_reported) AS latest  // bug_idを指定しない
INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;

2, 他の列に対しても集約関数を使用する

SELECT product_id, MAX(date_reported) AS latest, MAX(bug_id) as latest_bug_id // bug_idに対してもMAX()を使う
FROM Bugs
INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;

3, グループごとにすべての値を連結する（MySQL、SQLiteのみ）

SELECT product_id, MAX(date_reported) AS latest, GROUP_CONCAT(bug_id) as bug_id_list // MAX(date_reported)のbug_idを全て取得
FROM Bugs
INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;

SET型がサポートされているので使用可能。↓の感じになる

product_id	latest	bug_id
101	2022-12-12 12:00	1, 2
102	2021-02-03 07:00	3, 4
103	1992-01-15 01:00	5

１レコードの値に複数の値が取得できる

所感

「アンビギュアスグループ」が発生するクエリを実行すると、基本エラーとなるが、MySQL, SQLiteについてはエラーが吐かれず、間違った値が取得できてしまうのでバグの温床になりえて怖い。。。
対策としてはいくつかあるが現実的なのは欲しい値が取得できる「2, 他の列に対しても集約関数を使用する」なのかなと思う
- 他にも相関サブクエリを使用したり導出テーブルを使用したりする方法もあったが、取得したい結果のわりに複雑なクエリを準備する必要があるのは現実的じゃないかなあ

[SQL]アンビギュアスグループ

概要