【數據蔣堂】第 31 期：JOIN 簡化 – 維度對齊

我們先把上一期中雙子表對齊例子的 SQL 寫出來：

 SELECT Orders.id, Orders.customer, A.x, B.y FROM Orders  LEFT JOIN (SELECT id,SUM(price) x FROM OrderDetail GROUP BY id ) A ON Orders.id=A.id  LEFT JOIN (SELECT id,SUM(amount) y FROM OrderPayment GROUP BY id ) B ON Orders.id=B.id  WHERE A.x > B.y SELECT Orders.id, Orders.customer, A.x, B.y FROM Orders  LEFT JOIN (SELECT id,SUM(price) x FROM OrderDetail GROUP BY id ) A ON Orders.id=A.id  LEFT JOIN (SELECT id,SUM(amount) y FROM OrderPayment GROUP BY id ) B ON Orders.id=B.id  WHERE A.x > B.y

那么問題來了，這顯然是個有業務意義的 JOIN，它算是前面所說的哪一類呢？

這個 JOIN 涉及了表 Orders 和子查詢 A 與 B，仔細觀察會發現，子查詢帶有 GROUP BY id 的子句，顯然，其結果集將以 id 為主鍵。這樣，JOIN 涉及的三個表(子查詢也算作是個臨時表)的主鍵是相同的，它們是一對一的同維表，仍然在前述的范圍內。

但是，這個同維表 JOIN 卻不能用上一期說的寫法簡化，子查詢 A,B 都不能省略不寫。

可以簡化書寫的原因在于：我們假定事先知道數據結構中這些表之關聯關系。用技術術語的說法，就是知道數據庫的元數據(metadata)。而對于臨時產生的子查詢，顯然不可能事先定義在元數據中了，這時候就必須明確指定要 JOIN 的表(子查詢)。

不過，雖然 JOIN 的表不能省略，但關聯字段總是主鍵，已經在 GROUP BY 中寫過了，就沒有必要再寫一遍了；而且，子查詢的主鍵總是由 GROUP 產生，而 GROUP BY 的字段一定要被選出用于做外層 JOIN，也沒必要在 GROUP 和 SELECT 中各寫一次；并且這幾個子查詢涉及的子表是互相獨立的，它們之間不會再有關聯計算了，我們就可以把 GROUP 動作以及聚合式直接放到主句中，從而消除一層子查詢：

 SELECT Orders.id, Orders.customer, OrderDetail.SUM(price) x, OrderParyment.SUM(amount) y  FROM Orders LEFT JOIN OrderDetail GROUP BY id LEFT JOIN OrderPayment GROUP BY id  WHERE A.x > B.y SELECT Orders.id, Orders.customer, OrderDetail.SUM(price) x, OrderParyment.SUM(amount) y  FROM Orders LEFT JOIN OrderDetail GROUP BY id LEFT JOIN OrderPayment GROUP BY id  WHERE A.x > B.y

這里的 JOIN 和 SQL 定義的 JOIN 運算已經差別很大，完全沒有笛卡爾積的意思了。而且，也不同于 SQL 的 JOIN 運算將定義在任何兩個表之間，這里的 JOIN，OrderDetail 和 OrderPayment 以及 Orders 都是向共同的主鍵 id 靠攏，即所有表都向某一套基準維度對齊。而由于各表的維度(主鍵)不同，對齊時可能會有 GROUP BY，在引用該表字段時就會相應地出現聚合運算。OrderDetail 和 OrderPayment 甚至 Orders 之間都不直接發生關聯，在書寫運算時當然就不用關心它們之間的關系，甚至不必關心另一個表是否存在。而 SQL 那種笛卡爾積式的 JOIN 則總要找一個甚至多個表來定義關聯，一旦減少或修改表時就要同時考慮關聯表，增大理解難度。

我們稱這種 JOIN 稱為維度對齊，它并不超出我們前面說過的三種 JOIN 范圍，但確實在語法描述上會有不同，這里的 JOIN 不象 SQL 中是個動詞，卻更象個連詞。而且，和前面三種基本 JOIN 中不會或很少發生 FULL JOIN 的情況不同，維度對齊的場景下 FULL JOIN 并不是很罕見的情況。

雖然我們從主子表的例子抽象出維度對齊，但這種 JOIN 并不要求 JOIN 的表是主子表(事實上從上一篇的語法可知，主子表運算還不用寫這么麻煩)，任何多個表都可以這么關聯，而且關聯字段也完全不必要是主鍵或主鍵的部分。

設有合同表，回款表和發票表：

Contract 合同表

id合同編號date簽訂日期customer客戶price合同金額……

Payment 回款表

seq回款序號date回款日期source回款來源amount金額……

Invoice 發票表

code 發票編號

date 開票日期

customer 客戶

amount 開票金額

…

現在想統計每一天的合同額、回款額以及發票額，就可以寫成：

 SELECT Contract.SUM(price), Payment.SUM(amount), Invoice.SUM(amount)  FROM Contract GROUP BY date FULL JOIN Payment GROUP BY date FULL JOIN Invoice GROUP BY date SELECT Contract.SUM(price), Payment.SUM(amount), Invoice.SUM(amount)  FROM Contract GROUP BY date FULL JOIN Payment GROUP BY date FULL JOIN Invoice GROUP BY date

這幾種 JOIN 情況還可能混合出現。

延用上面的合同表，再有客戶表和銷售員表

Customer 客戶表

|—|—|

|id| 客戶編號 |

|name| 客戶名稱 |

|area| 所在地區 |

|…|…|

Sales 銷售員表

id員工編號name姓名area負責地區……

其中 Contract 表中 customer 字段是指向 Customer 表的外鍵。

現在我們想統計每個地區的銷售員數量及合同額：

 SELECT Sales.COUNT(1), Contract.SUM(price)  FROM Sales GROUP BY area FULL JOIN Contract GROUP BY customer.area SELECT Sales.COUNT(1), Contract.SUM(price)  FROM Sales GROUP BY area FULL JOIN Contract GROUP BY customer.area

維度對齊可以和外鍵屬性化的寫法配合合作。

這些例子中，最終的 JOIN 都是同維表。事實上，維度對齊還有主子表對齊的情況，不過相對罕見，我們將在后續仔細講解維度概念時再涉及，上述寫法中其實還有個小漏洞，有了明確的維度定義后才能將這個漏洞補上。