SQL Antipatterns ch15 Ambiguous Groups

pragprog.com

---

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
  • (エラー等を)出す