Edited at

14章 アンギュアスグループ(曖昧なグループ)

More than 3 years have passed since last update.


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テーブルから、製品ごとの最新のバグ報告日付を取得するクエリを発行


Groups/anti/groupbyproduct.sql

SELECT product_id, MAX(date_reported) AS latest

FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;

クエリ拡張して、製品ごとに最新の報告日付を持つバグのIDを取得できる


Groups/anti/groupbyproduct.sql

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)


Groups/anti/groupbyproduct.sql

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句に列挙されていない列では事情が異なる

これらの列でグループ内のすべての行に同じ値があることを常に保証できない。

今回の例にあてはめると、


Groups/anti/groupbyproduct.sql

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返せばいいの?


Groups/anti/maxandmin.sql

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とか


Groups/anti/sumbyproduct.sql

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 アンチパターンを用いてもよい場合


Groups/legit/functiona.sql

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 関数従属性のある列のみにクエリを実行する

クエリから曖昧な列を排除する


Groups/anti/groupbyproduct.sql

SELECT product_id, MAX(date_reported) AS latest

FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;

製品ごとのバグの最新の日付を返す

これで十分な場合もある


14.5.2 相関サブクエリを使用する


Groups/soln/notexists.sql

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を使用する

大量のデータに対するパフォーマンスが重要な場合に使用

仕組みの理解や保守が難しくなるが、サブクエリに比べればパフォーマンスは良好


Groups/soln/outer-join.sql

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で指定されていないような、単一値原則に違反する可能性も残っている場合に使う


まとめ

曖昧なクエリ結果を避けるために、単一値の原則に従うこと