第18章 意大利面条式查询

Spaghetti-Query/intro/report.sql
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 ow_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;

Spaghetti-Query/anti/cartesian.sql
SELECT p.product_id, COUNT(f.bug_id) AS count_fixed, COUNT(o.bug_id) AS count_open
 FROM BugsProducts p LEFT OUTER JOIN Bugs f ON (p.bug_id = f.bug_id AND f.status = 'FIXED')
                     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;

Spaghetti-Query/anti/cartesian-no-group.sql
SELECT p.product_id, f.bug_id AS fixed, o.bug_id AS open FROM BugsProducts p
 JOIN Bugs f ON (p.bug_id = f.bug_id AND f.status = 'FIXED')
 JOIN Bugs o ON (p.bug_id = o.bug_id AND o.status = 'OPEN')
WHERE p.product_id = 1;

• “为什么我的求和、计数返回的结果异常地大？”一个意料之外的两个联结查询的数据集生成的笛卡儿积。
• “我一整天都在和这个变态的查询语句做斗争！”SQL并不是那么难的，真的！如果你和单条SQL查询纠结了很长时间，应该重新考虑你的实现方式。
• “我们不能在数据库报表中再加入任何新东西了，因为对查询语句的修改要花很长时间。”写这个查询语句的人要永远为他所写的代码负责，即使他们已经加入到别的项目中去了。那个写这段代码的人可能就是你，因此别把查询写得如此复杂以至于别人无法维护！
• “试试看再加一个DISTINCT进去？”要修正由于笛卡儿积所带来的数据集暴涨，程序员通常使用DISTINCT这个关键字作为一个查询修正或者一个聚合函数来减少重复。这个方法能够隐藏掉那个难看的查询的踪迹，但导致RDBMS做了更多的工作来生成排序、去重的临时表。

1 William of Ockham，十四世纪英国著名思想家，逻辑学家。著有“奥卡姆剃刀”理论。——编者注

Spaghetti-Query/soln/split-query.sql
SELECT p.product_id, COUNT(f.bug_id) AS count_fixed 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;  SELECT p.product_id, COUNT(o.bug_id) AS count_open 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;

• 这个查询不会产生早先例子中出现的那种意外的笛卡儿积，因此很简单地就能确认这个查询给出的结果是精确的。
• 当有新的需求增加到报表中时，添加另一个简单的查询，比将更多的计算整合到一个已经很复杂的查询中去要简单很多。
• SQL引擎能更容易和可靠地对简单的查询进行优化和执行。即使整个工作看上去像是被分割出来的查询弄得有点重复，但可能执行得更快。
• 在代码审查或者团队间的培训交流时，解释几个易懂的查询要比解释一个庞大复杂的查询容易得多。

Spaghetti-Query/soln/union.sql
(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;

• 多少产品：

  Spaghetti-Query/soln/count-products.sql
  SELECT COUNT(*) AS how_many_products FROM Products;
  

• 多少开发人员在参与修补Bug：

  Spaghetti-Query/soln/count-developers.sql
  SELECT COUNT(DISTINCT assigned_to) AS how_many_developers FROM Bugs WHERE status = 'FIXED';
  

• 平均每个程序员修复了多少Bug：

  Spaghetti-Query/soln/bugs-per-developer.sql
  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;
  

• 多少修复了的Bug是由客户报告的：

  Spaghetti-Query/soln/bugs-by-customers.sql
  SELECT COUNT(*) AS how_many_customer_bugs FROM Bugs b JOIN Accounts cust ON (b.reported_by = cust.account_id) WHERE b.status = 'FIXED' AND cust.email NOT LIKE '%@example.com';
  

Spaghetti-Query/soln/generate-update.sql
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;

Update_statement
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;