スパゲッティ・クエリ【SQLアンチパターンまとめ】 -

全ての結果を一つのクエリで出そうとしてはいけない

電話で幹部と話している上司が、いきなりあなたの机に来てこう言いました。

「SQLを使って次の4つの値を調べて欲しい。開発者一人当たりの平均バグ修正数、修正したバグの中で顧客から報告された数、バグを修正した開発者の数、うちの課が取り扱っているバグの数」

あなたは怒りを抑えて、全ての答えを一気に取り出したいので、次のような複雑なクエリを書くことにしました。(これが間違い)

SELECT COUNT(bp.product_id) AS how_many_products,
COUNT(dev.account_id) AS how_many_developers,
COUNT(b.bug_id)/COUNT(dev.account_id) AS avg_bugs_per_developer,
COUNT(cust.account_id) AS how_many_customers
FROM Bugs b JOIN BugsProducts bp ON (b.bug_id = bp.bug_id)
JOIN Accounts dev ON (b.assigned_to = dev.account_id)
JOIN Accounts cust ON (b.reported_by = cust.account_id)
WHERE cust.email NOT LIKE '%@example.com'
GROUP BY bp.product_id;

できたデータを確認してみると、なんだか間違っているような気がします。

結局間違いを確認できずに、あなたはデータを提出することができませんでした。

参考記事

この記事は以下の書籍を参考にしています。 以下のリンクから購入してお小遣いを恵んでください！！！

複雑な問題を一つのクエリで解決してはいけない

確かにSQLは強力な言語で一つのクエリで全てのタスクを処理することもできます。ですがだからと言って、SQLは一つのクエリで全てを解決することを強制しません。時にはそれがよくないアイデアであることがあります。

デカルト積が生み出されてしまうことがある

一つのクエリで全てを処理しようとする試みは、しばしばデカルト積を生み出す原因になります。

デカルト積とは一言で言うと、総当たりです。

二つのテーブル、AとBがあった場合、それらのテーブルから総当たりとなる集合をデカルト積と呼びます。

各テーブルに10行ずつ存在しいた場合でも、デカルト積として統計データを算出する場合は100行に膨れ上がります。

デカルト積は二つのテーブルの関係を定義しない場合、つまりWHERE句やJOIN句での結合の失敗時に発生しやすいです。特に、一つのクエリで全てを処理しようとする試みは、複数のテーブルの関係が曖昧になり、意図しない結合に発展しやすいのです。

クエリが読めなくなる

プログラミングのコードは書くだけでは終わりではありません。むしろ、一度書いたコードを他の人が何回も読みます。

この「読む」と言うプロセスに問題がないかどうかを確認するためにコードレビューという作業が存在し、読めないコードやクエリはレビュアーという第三者によって弾かれます。でなければ保守不可能なコードが蓄積し、更なるスパゲッティコードや障害の温床になるのです。

SQLも同じです。

データベースアプリケーションでは頻繁に機能追加が求められ卯rことを忘れてはいけません。上司から、詳細なレポート生成やユーザーインターフェースのフィールド追加を求められることもあります。

https://www.slideshare.net/makopi/spaghetti-query

スパゲッティのように絡まったコードを解読するのは困難です。

SQLで統計データを算出する方法

1.クエリを複数に分割する

冒頭のエピソードのような、統計データを出して欲しいという緊急のリクエストに答えるにはどうすればいいのでしょうか？

最善の策は一つの統計データに対して一つのクエリを出すことです

製品の数

-- 製品の数
SELECT 
    COUNT(*) AS how_many_products
FROM 
    Products;

バグを解決した開発者の数

-- バグを解決した開発者の数
SELECT 
    COUNT(DISTINCT assigned_to) AS how_many_developers
FROM 
    Bugs
WHERE 
    status = 'FIXED';

開発者一人当たりの平均バグ修正数

-- 開発者一人当たりの平均バグ修正数
SELECT 
    AVG(bugs_per_developer) AS average_bugs_per_developer
FROM 
    (
    SELECT 
        dev.account_id, COUNT(*) AS bugs_per_developer
    FROM 
        Bugs b JOIN Accounts dev ON (b.assigned_to = dev.account_id)
    WHERE 
        b.status = 'FIXED'
    GROUP BY 
        dev.account_id
    ) t;

修正したバグの中で顧客から報告されたバグの数

-- 修正したバグの中で顧客から報告されたバグの数
SELECT 
    COUNT(*) AS how_many_customer_bugs
FROM 
    Bugs b 
JOIN 
    Accounts cust ON (b.reported_by = cust.account_id)
WHERE 1=1
    AND b.status = 'FIXED' 
    AND cust.email NOT LIKE '%@example.com';

2.SQLを用いたSQLの生成

コード生成は、新しいコードを手で書くには労力がかかるという場合に非常に有効です。この技法を使いこなすことによって、プログラマーは繰り返し同じようなコードを書かなくてよくなります。

例.複数のUPDATEステートメント生成

別のIT部門が電話があり、「在庫マネジメントシステムが朝からオフラインだ」と連絡を受けました。

「原因はデータベースにあり、特定の条件の行をアップデートすれば解決するのだが、良いコードが書けない」

彼はSQLの初心者ではありませんでしたが、彼の失敗は良いコードを一回で書こうとしたことでした。

私は複雑な一つのSQL ステートメントを書く代わりに、シンプルなSQLを複数生成するスクリプトを書きました。 その結果、求めていたUPDATEステートメントを獲得できたのです。

SELECT CONCAT('UPDATE Inventory ',
    ' SET last_used = ''', MAX(u.usage_date), '''',
    ' WHERE inventory_id = ', u.inventory_id, ';') AS update_statement
FROM 
    ComputerUsage u
GROUP BY 
    u.inventory_id;

このSQLの実行結果は次の通りです。

UPDATE Inventory SET last_used = ’2002-04-19’ WHERE inventory_id = 1234;
UPDATE Inventory SET last_used = ’2002-03-12’ WHERE inventory_id = 2345;
UPDATE Inventory SET last_used = ’2002-04-30’ WHERE inventory_id = 3456;
UPDATE Inventory SET last_used = ’2002-04-04’ WHERE inventory_id = 4567;

このテクニックによって、その担当者が何時間も頭を悩ませていた問題を、数分のうちに解決できたのです。

3.UNIONでつなげる

それでもあなたの上司が「どうしても一つのクエリがいいんだ〜〜〜」と喚いたとしましょう。

その場合にはUNIONを使って一つのクエリとして結合することをお勧めします。

(
    SELECT p.product_id, f.status, COUNT(f.bug_id) AS bug_count
    FROM 
        BugsProducts p
    LEFT OUTER JOIN 
        Bugs f ON (p.bug_id = f.bug_id AND f.status = 'FIXED')
    WHERE 
        p.product_id = 1
    GROUP BY 
        p.product_id, f.status
)

UNION ALL

(
    SELECT 
        p.product_id, o.status, COUNT(o.bug_id) AS bug_count
    FROM 
        BugsProducts p
    LEFT OUTER JOIN 
        Bugs o ON (p.bug_id = o.bug_id AND o.status = 'OPEN')
    WHERE 
        p.product_id = 1
    GROUP BY 
        p.product_id, o.status
)

ORDER BY bug_count;

このクエリの結果は、各サブクエリの結果を合わせたものになります。全体として合計2行が結果として出力されることになるのです。

4.全部SELECT句に納める:オリジナル

あなたの上司が「縦に並ぶのやだ〜横二列がいい〜」と喚いたとします。

その場合は~~怒りを鎮めて~~dualと副問い合わせを使用して、select句に全て納めましょう。

select
    (
        SELECT 
            COUNT(f.bug_id) AS bug_count
        FROM 
            BugsProducts p
        LEFT OUTER JOIN 
            Bugs f ON (p.bug_id = f.bug_id AND f.status = 'FIXED')
        WHERE 
            p.product_id = 1
        GROUP BY 
            p.product_id, f.status
    ) AS "修正済みバグ件数",
    (
        SELECT 
            COUNT(o.bug_id) AS bug_count
        FROM 
            BugsProducts p
        LEFT OUTER JOIN 
            Bugs o ON (p.bug_id = o.bug_id AND o.status = 'OPEN')
        WHERE 
            p.product_id = 1
        GROUP BY 
            p.product_id, o.status
    ) AS "対応中バグ件数"
from
    dual

5.CASE文とSUM関数を組み合わせる

上司が「ヤダヤダ〜〜新しいステータスがきても対応できるように拡張してくれないとやだ〜」と喚いたとします。

そんな場合は~~怒りを抑えて~~条件ごとの集約を一つのクエリでシンプルに行うために、CASE式とSUM関数を組み合わせる方法もあります。

SELECT
    p.product_id,
    SUM(CASE b.status WHEN 'FIXED' THEN 1 ELSE 0 END) AS count_fixed,
    SUM(CASE b.status WHEN 'OPEN'  THEN 1 ELSE 0 END) AS count_opend,
FROM
    BugsProduct p
INNER JOIN
    Bugs b USING (bugid)
WHERE
    p.product_id = 1
GROUP BY
    p.product_id;