Ambiguous Groups
Follow the Single-Value Rule to avoid ambiguous query results.
Objective: Get Row with Greatest Value per Group
SELECT BugsProducts.product_id
, MAX(Bugs.date_reported) AS latest
, Bugs.bug_id -- <- これ
FROM Bugs
INNER JOIN BugsProducts
ON Bugs.bug_id = BugProducts.bug_id
GROUP BY BugsProducts.product_id
-
GROUP BY句で指定していないカラムをselect-listに指定
Bugs.bug_id
Bugs.date_reported
が最大である行のBug.bug_id
が得られると思ってしまう
Antipattern: Reference Nongrouped Columns
The Single-Value Rule
-
Single-Value Rule
- select-listのカラムはすべて行ごとに単一の値でなければならない
- GROUP BY句で指定したカラムは単一の値であることが保証されている
- 集約関数の結果も単一の値
-
それ以外のカラムは単一の値とは限らない
- 関数従属性が無い場合
Do-What-I-Mean Queries
- プログラマ的には
Bugs.date_reported
が最大である行のBug.bug_id
を得たい -
SQLはこのような推論を行えない。以下、理由
Bugs.date_reported
が最大である行が2つあったらどうするの-
複数の集計関数で対応する行が異なる場合どうするの
- MAXとMIN
-
対応する行が無い場合どうするの
- COUNT,SUM, AVG
How to Recognize the Antipattern
-
動作は製品依存
- 標準、ほとんど: エラー
- MySQL: 最初の行
- SQLite: 最後の行
コラム: GROUP BY and DISTINCT
-
集約関数なし、重複をなくすためのGROUP BY
- DISTINCTと結果は同じ、実行計画も同じになるべき
Legitimate Uses of the Antipattern
-
関数従属性があり、Single Valueになることがわかっている場合
-
ただし、ほとんどのDB製品ではエラー
- SQL標準準拠
-
- MySQLまたはSQLiteを使用しており、関数従属性に気をつけられるならどうぞ
Solution: Use Columns Unambiguously
Query Only Functionally Dependent Columns
SELECT product_id
, MAX(date_reported) AS latest
FROM Bugs
INNER JOIN BugsProducts
USING (bug_id)
GROUP BY product_id
- GROUP BY句で指定したカラムと集計関数のみ
Using a Correlated Subquery
SELECT bp1.product_id,
, b1.date_reported as latest
, b1.bug_id
FROM Bugs b1
INNER JOIN BugProducts bp1
USINT (bug_id)
WHERE NOT EXISTS
(SELECT *
FROM Bugs b2
INNER JOIN BugsProducts bp2
USING (bug_id)
WHERE bp1.product_id = bp2.product_id
AND b1.date_reported < b2.date_reported);
-
シンプルで読みやすい
- 【所感】シンプルではなくない?
- 「
date_reported
がより大きな行が存在しない行」
- 【補】
date_reported
が最大の行が複数ある場合は、結果リストにすべて含まれる -
パフォーマンス面は最適ではないかも
- 相関サブクエリなので毎行サブクエリが実行される
- 【補】BugProductsのフェッチ時、
(product_id, bug_id)
主キーインデックスが使える - 【補】
Bugs.date_reported
にB+木インデックスが作成してあれば使える
Using a Derived Table
SELECT m.product_id
, m.latest,
, b1.bug_id
FROM Bugs b1
INNER JOIN BugsProducts bp1
USING (bug_id)
INNER JOIN
(SELECT product_id
, MAX(date_reported) AS latest
FROM Bugs
INNER JOIN BugsProducts
USING (bug_id)
GROUP BY product_id) m
ON bp1.product_id = m.product_id
AND b1.date_reported = m.latest
-
意味合い
product_id, MAX(date_reported)
だけの一時表をつくるBugsProducts
表と結合してproduct_id
から{bug_id}
を得るBugs
表と結合して{bug_id} -> {date_reported}
からdate_reported
を得るdate_reported = MAX(date_reported)
のものを拾う
-
date_reported
が最大の行が複数ある場合は、すべて結果リストに含まれる-
bug_id
が最大のものだけを表示したい場合は、外側のクエリで…SELECT m.product_id, latest, MAX(b1.bug_id)
GROUP BY m.product_id, latest
-
-
相関サブクエリよりもスケールしやすい
- 非相関サブクエリなので、多くの製品では1回しか実行しない
- 一時表が必要なのでまだベストではない
Using a Join
SELECT bp1.product_id
, b1.date_reported AS latest
, b1.bug_id
FROM Bugs b1
INNER JOIN BugsProducts bp1
ON b1.bug_id = bp.bug_id
LEFT OUTER JOIN
(SELECT *
FROM Bugs AS b2
INNER JOIN BugsProducts bp2
ON b2.bug_id = bp2.bug_id)
ON (bp1.prouct_id = bp2.product_id
AND (b1.date_reported < b2.date_reported
OR (b1.date_reported = b2.date_reported
AND b1.bug_id < b2.bug_id)))
WHERE b2.bug_id IS NULL;
-
NOT EXISTSの代わりにLEFT OUTER JOINを使用
- 内部表側にマッチする行がない場合、NULLとなるのを利用する
-
date_reported
が最大の行が複数ある場合は、bug_id
最大の行のみ得る-
LEFT OUTER JOINの結合条件より
date_reported
が大きいやつdate_reported
が同じなら、bug_id
が大きいやつ
- 「より大きいのがない」から最大
-
Using an Aggregate Function for Extra Columns
SELECT product_id
, MAX(date_reported) AS latest
, MAX(bug_id) AS latest_bug_id
FROM Bugs
INNER JOIN BugsProducts
USING (bug_id)
GROUP BY product_id
bug_id
が報告日順という前提付き
Concatenating All Values per Group
SELECT product_id
, MAX(date_reported) AS latest
, GROUP_CONCAT(bug_id) AS bug_id_list,
FROM Bugs
INNER JOIN BugsProducts
USING (bug_id)
GROUP BY product_id
- MySQLとSQLite固有機能
-
bug_id_list
にbug_id
全部詰め込んでSingle-Valueにする- デフォルトでカンマ区切り
- 他製品で同じことをしたければ、ストアドファンクション/プロシージャ等必要
英語
-
elicit
- (エラー等を)出す