14.0 きっかけ
上司：報告された直近のバグを製品ごとにまとめたレポートがほしい
部下：同じproduct_idを共有するバグのグループごとに、data_reported列の最大値を計算するクエリを書こう
表14-1 直近のバグを製品ごとにまとめたレポートはこう書きたい
| product_name | latest | bug_id |
|---|---|---|
| やすり | 2010-06-01 | 1234 |
| ドライバー | 2010-02-16 | 3456 |
| ペンチ | 2010-01-01 | 5678 |
上司：レポートに列挙されているそれぞれのバグを調べよう
表14-2 結果の不一致
| product_name | date_reported | bug_id |
|---|---|---|
| やすり | 2009-12-19 | 1234 |
| やすり | 2010-06-01 | 2248 |
| ドライバー | 2010-02-16 | 3456 |
| ドライバー | 2010-02-10 | 4077 |
| ドライバー | 2010-02-16 | 5150 |
| ペンチ | 2010-01-01 | 5678 |
| ペンチ | 2009-11-09 | 8063 |
上司：やすりのバグ、日付とid一致してないよ。直近のバグのbug_idは2248だけど。
14.1 目的：グループ内で最大値を持つ行を取得する
GROUP BY使って、Bugsテーブルから、製品ごとの最新のバグ報告日付を取得するクエリを発行
SELECT product_id, MAX(date_reported) AS latest
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;
クエリ拡張して、製品ごとに最新の報告日付を持つバグのIDを取得できる
SELECT product_id, MAX(date_reported) AS latest, bug_id
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;
グループ内の最大値だけでなく、 その最大値が見つかった行の他の属性も取得するクエリ を実行したい。
14.2 アンチパターン：非グループ化列を参照する
14.2.1 単一値の原則(Single-Value Rule)
SELECT product_id, MAX(date_reported) AS latest
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;
単一値の原則 => このクエリのSELECT句の選択リストに列挙されるすべての列は、行グループごとに単一の値でなければいけない。
つまり、GROUP BYで列挙された列(今回ならproduct_id)は、グループ化される行数に関わらず、グループ毎に単一であることが保証される(MAX関数も)。
しかし、選択リストの中でGROUP BY句に列挙されていない列では事情が異なる
これらの列でグループ内のすべての行に同じ値があることを常に保証できない。
今回の例にあてはめると、
SELECT product_id, MAX(date_reported) AS latest, bug_id
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;
あるproduct_idに対応したbug_idには複数の値が存在する
表14-3.1 1つのproductに対する複数のbug_id
| product_name | date_reported | bug_id |
|---|---|---|
| やすり | 2009-12-19 | 1234 |
| やすり | 2010-06-01 | 2248 |
GROUP BYで指定されていない列では、グループ毎の単一値保証はされないので、それらの列を単一値の原則に反すると見なし、ほとんどのDBではこれをエラーにするが、MySQLとSQLite は違う！
14.2.2 SQLがクエリの意図を汲んでくれるとは限らない
アンチパターン思考：date_reportedの最大値を取得するときに、ついでに選択リストに指定された他の列の値も、その最大値の存在する同じ行からとってきてくれるよね
そうはいかない
* date_reported列で2つのバグが同じ報告日付を持っている、かつ、その日付が行グループの最大値である場合、どっちのbug_id返せばいいの？
* クエリで2つの異なる集約関数を実行したとき(例えばMAXとMIN)、集約関数の値はおそらくグループ内の2つの異なる行から取得される。どっちのbug_id返せばいいの？
SELECT product_id, MAX(date_reported) AS latest,
MIN(date_reported) AS earliest, bug_id
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;
- 集約関数で返された値と一致する行がテーブルにない場合、bug_idの値はどうすればよい？AVGとかCOUNTとかSUMとか
SELECT product_id, SUM(hours) AS total_project_estimate, bug_id
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;
これらのクエリが必ず曖昧な結果を返すとは限らないが、多くの場合は曖昧
14.3 アンチパターンの見つけ方
ほとんどのDB製品では、単一値の原則に反するクエリを発行すると、ただちにエラーが返される。
SQLiteとMySQLでは、曖昧な列には予期しない値や信頼性の低い値が返される可能性がある。
MySQLではグループの最初(物理的な格納場所)の行から値が返されてしまう
SQLiteではグループの最終の行から値が返される
14.4 アンチパターンを用いてもよい場合
SELECT b.reported_by, a.account_name
FROM Bugs b INNER JOIN Accounts a ON b.reported_by = a.account_id
GROUP BY b.reported_by;
account_nameは集約関数で指定されていないから単一値の原則に違反している
しかし、Bugs.reported_byはAccountsテーブルの外部キーなので、各グループはAccountsテーブルの行と1対1
つまり、reported_byの値を知っていれば、account_nameの値を一意に特定できる
accountsテーブルの主キーでクエリを実行する場合と同じ
関数従属性
Bugs.reported_byはAccountsテーブルの主キーを参照するので、Accountsテーブルの主キーに従属する属性に対して、主キーと同様の関係性を持つ
クエリがreported_by列でグループ化されるとき、reported_byが外部キーなので、Accountsテーブルの各属性が関数従属となり、曖昧さが発生しない
でもほとんどのデータベースではエラー
MySQLやSQliteを使用している場合は、関数従属性を持つ列のみにクエリを実行するように
プログラマーが注意 すれば、この種のクエリのグループ化を使用できる
14.5 解決策：曖昧でない列を使用する
14.5.1 関数従属性のある列のみにクエリを実行する
クエリから曖昧な列を排除する
SELECT product_id, MAX(date_reported) AS latest
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;
製品ごとのバグの最新の日付を返す
これで十分な場合もある
14.5.2 相関サブクエリを使用する
SELECT bp1.product_id,b1.date_reported AS latest, b1.bug_id
FROM Bugs b1 INNER JOIN BugProducts bp1 USING (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);
サブクエリが結果を1つも検出しない場合、外部クエリのバグが最新
相関サブクエリが外部クエリの各行に対してそれぞれ実行されるので、最善のパフォーマンスは得られない
14.5.3 導出テーブルを使用する
サブクエリを導出テーブルとして使用
product_idと各製品に対応する最新のバグレポートの日付のみを含む中間結果を取得できる
その結果を用いてテーブルにJOINさせ、クエリの最終結果に製品ごとの最新日付を持つバグのみが含まれるようにする
表14-3 サブクエリで返された日付が複数行と一致する場合の結果
| product_id | latest | bug_id |
|---|---|---|
| 1 | 2010-06-01 | 2248 |
| 2 | 2010-02-16 | 3456 |
| 2 | 2010-02-16 | 5150 |
| 3 | 2010-01-01 | 5678 |
サブクエリで返されたlatestの日付が複数行と一致する場合、1つの製品に複数行が取得される可能性がある
product_idごとに1行のみ出したい場合は、外部クエリでもう1回グループ化関数を使う
この手法だと中間結果をテンポラリーテーブルに格納するため最善のパフォーマンスをもたらすわけではない
14.5.4 JOINを使用する
大量のデータに対するパフォーマンスが重要な場合に使用
仕組みの理解や保守が難しくなるが、サブクエリに比べればパフォーマンスは良好
SELECT bp1.product_id,b1.date_reported AS latest, b1.bug_id
FROM Bugs b1
INNER JOIN BugProducts bp1
ON b1.bug_id = bp1.bug_id
LEFT OUTER JOIN (
Bugs AS b2 INNER JOIN BugsProducts AS bp2
ON b2.bug_id = bp2.bug_id
)
ON (bp1.product_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;
14.5.5 他の列に対しても集約関数を使用する
これは最大のbug_idが最新の日付を持つことをあてにできる場合のみに使用
つまりバグ報告が時系列順に行われることが保証されている場合のみ有効
14.5.6 グループごとにすべての値を連結する
GROUP_CONCAT関数を使う
表14-6 GROUP_CONCAT関数を使い、グループ内のすべての値を1つに連結
| product_id | latest | bug_id_list |
|---|---|---|
| 1 | 2010-06-01 | 1234,2248 |
| 2 | 2010-02-16 | 3456,4077,5150 |
| 3 | 2010-01-01 | 5678,8063 |
最新日付に対応するbug_idは特定できない + 各グループのbug_idがすべて含まれる
加えて、SQL標準に準拠していない
他のDB製品ではこの関数をサポートしていない
カスタム関数とカスタム集約関数をサポートしているDBもある
カスタム関数をサポートしていない場合は、非グループ化クエリの結果をループし、値を連結させるためのストアドプロシージャを記述することが必要
この手法は、GROUP BYで指定されていないような、単一値原則に違反する可能性も残っている場合に使う
まとめ
曖昧なクエリ結果を避けるために、単一値の原則に従うこと