告別寬表，用DQL成就新一代BI

BI 商業智慧這個概念已經提出好幾十年了，這個概念本身比較寬泛，不同人也有不同的理解和定義，但落實到技術環節，特別是面向業務使用者的環節，所稱的 BI，基本就是指的多維分析或者自助報表。

不管是叫自助報表還是多維分析，也都是一回事，都是讓使用者自己去透過拖拽的方式查詢資料或製作報表。

使用者想透過 BI，實現查詢和報表自由，也就是可以靈活地分析自己想要的資料，挖掘出更大的價值。

廠商想透過 BI，給使用者賦能，盤活使用者資料價值的同時，也能體現出 BI 產品本身的價值。

那實際的情況如何呢，BI 有沒有發揮出它預期的作用呢，我們就來探究一下。

做技術的都清楚，要查詢分析資料，其實就是編寫 SQL 語句去查詢（我們假設要分析的資料都在關聯式資料庫中，這是絕大多數 BI 的實際場景），那給業務人員使用的 BI 多維分析的技術本質，其實就是透過頁面拖拽出這個 SQL。

對於單表的查詢，並不是很難理解和實施，選出欄位再配上過濾條件及排序，和用 Excel 差不太多，分組彙總會稍複雜些，但也不是多難懂。

但是，有業務意義的查詢經常涉及多表關聯，比如查詢儲存餘額 10 萬元以上的儲戶中本地人的比例，看看某月回款額與發票額的對比。這些都需要多表關聯，也就是要用到 SQL 的 JOIN。

業務人員很難理解 SQL 的 JOIN，多個表及其關係是個網狀形式，要指定關聯欄位，還會涉及自關聯、遞迴關聯還有子查詢再關聯的複雜情況。三五個關聯表之間的資料關係連技術人員都可能會暈，就更別說業務人員了，這時候，介面再炫麗、操作再流暢都沒有什麼意義了。

多表的 JOIN 拖拽把使用者難住了，BI 廠商就只能繞路解決，總不能和使用者說我們的分析只能基於單表進行吧（畢竟相當多有業務意義的分析都是多表的，世界是普遍關聯的嘛），目前採用的變通手段就是建模，當前市場上的產品，基本都是這麼做的。

所謂建模，就是把表間關聯運算做成邏輯檢視或物理寬表，這樣業務人員在查詢時相當於面對的還是邏輯上的單表，這就又變的簡單了，又可以拖拽了。

問題完美解決？不，並沒有，寬表並不是一個好的解決方案。

寬表的侷限性很明顯，資料冗餘，維護麻煩這些就不說了。

單單是：分析也只能基於寬表現有的關聯去做這一條，就讓使用者和廠商都無法忍受了。

使用者分析需求超出範圍，或者有變化，就得技術人員修改或者重新再做一次寬表，使用者不自由，啥也得廠商幫忙，今天想做的分析，可能得一週以後才能做；廠商更不樂意，每一次修改和重做，都是人工成本，可是自己產品提供的自助關聯又不好用，也只能任使用者擺佈了。

當然有的 BI 廠商的建模，不叫寬表，事實上他們也確實比寬表做了更多的準備和最佳化，但歸根結底，不管是 CUBE, 還是立方體，還是其他名字，本質都還是一個寬表，邏輯上並沒有脫離寬表的範疇，分析需求變動時，還是得技術人員去改。

在一個資料系統中,BI 的作用本來就有限，然後還被死死的限制在了需要技術人員介入的寬表上，所謂的自由靈活就更得打折扣了。

那為什麼這麼多廠商都做不好多表的 JOIN，提供的 JOIN 功能，使用者根本不會用，只能被迫用寬表呢？

造成這些難題的 根本原因是，SQL 本身對於 JOIN 的定義過於簡單了，用來描述複雜的關聯場景時，就會很難理解，容易犯暈，就像用加法來描述乘法一樣。

我們透過兩個例子來看下。

查詢：北京號碼打給上海號碼的通話記錄

涉及通話記錄表和電話帳戶表以及地區表的多次關聯。

查詢：中國經理的美國員工

人事系統裡員工表，還有部門表。員工表中有所屬部門的欄位與部門表關聯，部門會有經理，而經理也是個員工，部門表中的經理欄位會再和員工表關聯。這就發生互相關聯的情況，轉圈了。

這倆例子是很正常，很普遍的查詢，但是即使是技術人員來寫這個 SQL，也得費點勁兒，這是 SQL 本身的侷限性造成的。

BI 廠商們也沒有在資料模型層面針對這個難題進行最佳化封裝，只是簡單的把表對業務人員做了可視，把技術人員都覺得難的問題丟給了沒有技術能力的業務人員，那當然沒人能用的起來了。

更多的關於 BI 廠商做不好 JOIN 的分析，可以參考： 為什麼 BI 軟體都搞不定關聯分析。

要解決這個難題，就需分析研究 SQL 的 JOIN 運算，突破 SQL 的侷限才可以。

我們發現，BI 多維分析中需要的 JOIN，都屬於這麼 3+1種情況：

1. 外來鍵關聯，多對 1 的 JOIN 和 LEFT JOIN；

2. 同維表關聯，1 對 1 的 LEFT JOIN 或 FULL JOIN；

3. 主子表關聯，1 對多的 JOIN 和 LEFT JOIN；

4. 按維對齊，1 對 1 的 FULL JOIN 或 JOIN，LEFT JOIN 較少見。

第四種維度對齊，稍有特殊，但也並沒有超出前三種情況的範圍，所以我們說成 3+1。

這裡說的是 BI 中的 JOIN，並不是 SQL 中全部的 JOIN，有些關聯計算仍然需要原始的 JOIN 定義來描述，比如做矩陣乘法，但在 BI 中碰不到

我們針對這 3+1種情況，重新定義 JOIN 運算，改造 SQL 語法形成另一種類似的查詢語言，也就是這裡所說的 DQL， 它是潤乾開發出的新一代 BI 多維分析引擎，D 是即 Dimensional 維度的意思。

我們來分別看一下這幾種情況下的 SQL 的複雜度以及 DQL 是怎麼解決的。

外來鍵屬性化

我們用前面提到的那個 查詢中國經理的美國員工 的例子來看一下 SQL 要怎麼寫，員工表裡有個部門外來鍵欄位指向部門表的主鍵，部門表裡又有經理外來鍵欄位指回員工表，這是很常見的資料結構設計。

SQL 寫出來是這樣的：

員工表和部門表 JOIN，再 JOIN 回員工表，也就是同一個表要連線兩次，這就起個別名。在 WHERE 中寫上 JOIN 的條件和最終我們希望的條件。整個句子要看一會才能明白。

使用 DQL 會寫成這樣：

這個句子中，美國員工好理解，中國經理的條件稍複雜一點，欄位有了子屬性，子屬性又有子屬性，但並不難理解，也就是部門的經理的國籍是中國。

在 DQL 的語法體系中， 外來鍵被看成了屬性，外來鍵指向表的欄位可直接用子屬性的方式引用，也允許多層和遞迴引用。

同維表等同化

這是兩個一比一的表，主鍵相同，在資料庫設計中經常有這種情況，欄位的業務分類不同，不適合都放在一個表裡，太寬的表在各欄位豐滿度相差較大時還會造成空間冗餘浪費，訪問效能也下降，因此常常會分到多個主鍵相同的表中。

現在我們要 查詢計算所有員工的收入。

SQL 中需要做 JOIN：

DQL 則可以把這兩個表看成一個表訪問：

" 工資 + 津貼”的的部分實際上來自兩個表，DQL 把 主鍵同維的表等同化，視為一個寬表，訪問其中任何一個均可引用其它表的欄位。

子表集合化

訂單及訂單明細是典型的主子表，前者的主鍵是後者的一部分。

現在我們想 計算每張訂單的總金額。

用 SQL 寫出來會是這樣：

要完成這個運算，不僅要用到 JOIN，還需要做一次 GROUP BY，否則選出來的記錄數太多。

如果我們把子表中與主表相關的記錄看成主表的一個欄位，那麼這個問題也可以不再使用 JOIN 以及 GROUP BY：

與普通欄位不同，訂單明細被看成訂單表的欄位時，其取值將是一個集合，因為兩個表是一對多的關係。所以要在這裡使用聚合運算把集合值計算成單值。這種簡化方式稱為 子表集合化。

這樣看待主子表關聯，不僅理解書寫更為簡單，而且不容易出錯。

如果有多個子表時，SQL 需要分別先做 GROUP, 然後在一起和主表 JOIN 才行，會寫成子查詢的形式，但是 DQL 則仍然很簡單，SELECT 後直接再加欄位就可以了。

按維對齊

這裡有三個表：合同表、回款表和庫存表。

我們希望 按日期統計合同額、回款額和庫存金額。

用 SQL 寫出來是這樣的：

用子查詢把每個表分組彙總後再 JOIN 起來，如果偷懶不用子查詢先 JOIN 後 GROUP，那結果是錯誤的，統計值會變多。這個問題必須使用子查詢。

這裡涉及的三個子查詢都要連線上，SQL 的 JOIN 關係要寫成若干個兩表關聯，在表比較多時，增刪關聯表有可能把某個表漏掉而沒有連線條件，出現完全叉乘。

用 DQL 寫出來是這樣的：

在 DQL 中，只要把這幾個表分別按日期對齊分別彙總就行了，而不必關心這些表之間的關係，在增刪表時也不容易發生遺漏。

如果按維對齊再與外來鍵攪到一起，情況就會更復雜：

我們希望 按地區統計銷售員人數和合同額

用 SQL 寫出來是這樣：

這個子查詢很複雜。

而在 DQL 中，可以和外來鍵屬性化結合，這樣查詢會寫成：

這裡又出現了子屬性，但整個句子仍然很簡單，DQL 允許每個表獨立設定統計維度，無須關心表間關聯，還可以與屬性化的外來鍵配合使用

對這些 JOIN 更深入的探討，可以參考  連線運算 1-SQL 中的 JOIN。

解決關聯

前面講的這幾個 JOIN 的例子，都是在實際應用中常見的，具有業務意義的查詢需求，

這些例子都是可以用來檢驗 BI 產品的“自助”靈活程度的，能否不需要技術人員更新模型就由完成這些查詢。結果會發現，業內的大部分 BI 產品，無論介面多炫麗、操作多流暢，都經不起這個檢驗。

原因就在於，低層模型上，並沒有解決好 JOIN 問題。

有了 DQL 之後，我們就能解決 BI 中的 JOIN 問題了。

從前面的 DQL 例子中可以明顯的看出，前 3 個查詢用 SQL 的 JOIN 都沒有了，多表變成單表了，只是欄位變成有子屬性了，而這並不難理解，業務人員可以輕車熟路地搞定。最後一個按維對齊的情況雖然還有 JOIN，但也很簡單，使用者無需關心這些表之間的關聯關係，只要向統一的目標維度對齊就行了。

重新定義 JOIN 後，就徹底把不易於理解和拼寫的 JOIN 變的簡單易懂了，再做一個拖拽的前端介面，能讓業務人員做 JOIN 的 BI 就做成了。

有人可能會問，多表變一表，那不還是寬表嗎？那不也還得技術人員做嗎？

DQL 和寬表大有不同!!!

DQL 當然也需要技術人員提前定義好後設資料，但是用到技術人員的地方也 僅此一次。

後設資料中預先定義好了各種關聯關係，但並沒有做實際關聯，當使用者在前端拖拽分析的時候，才實時生成關聯查詢，不需要像寬表一樣預先關聯，佔用資料庫資源。

它的關聯關係只要資料表本身結構不變，就不用修改後設資料，不需要像寬表一樣總得重新生成，相當於一次定義可以適應無數次不同的分析需求，它擁有寬表的優勢但從根本上解決了寬表的各種弊端。

這就是所謂的 非按需建模，建模只要考慮資料結構本身，而與使用者需求無關。寬表（無論邏輯還是物理的）則是按需建模，需求一變就要再建模。

用 DQL 語法還能降低出錯率。

很多程式設計師習慣用 WHERE 來寫 JOIN 運算的過濾條件，表少的時候沒有問題，表多的時候漏寫了 JOIN 條件意味著將發生多對多的完全叉乘，而這個 SQL 卻還可以正常執行，一方面計算結果會出錯，另一方面，如果漏寫條件的表很大，笛卡爾積的規模將是平方級的，這極有可能把資料庫直接“跑死”！

採用 DQL 的 JOIN 語法，就不可能發生漏寫 JOIN 條件的情況了。因為對 JOIN 的理解不再是以笛卡爾積為基礎，而且設計這些語法時已經假定了多對多關聯沒有業務意義，這個規則下寫不出完全叉乘的運算。

對於多個子表分組後與主表對齊的運算，在 SQL 中要寫成多個子查詢的形式。但如果只有一個子表時，可以先 JOIN 再 GROUP，這時不需要子查詢。有些程式設計師沒有仔細分析，會把這種寫法推廣到多個子表的情況，也先 JOIN 再 GROUP，可以避免使用子查詢，但計算結果是錯誤的。

使用維度對齊的寫法就不容易發生這種錯誤了，無論多少個子表，都不需要子查詢，一個子表和多個子表的寫法完全相同。

DQL 還能讓資料結構顯得更為清晰。

這是我們平時看到的 E-R 圖，它是個網狀結構的，表與表之間可能都有關聯，表多了就會顯得很零亂，增刪表的時間很容易遺漏或重複表間的關聯。

而在 DQL 體系下看到的表間關聯是匯流排式的：

表與表之間沒有直接的關聯，都只處在中間地位的維度關聯，增刪表的時候不會影響到其它表，資料結構耦合度低。

不過，要說明的是，無論是 E-R 圖還是後面的匯流排圖，其中連線的數量都是相當的，這是資料關係本身決定的，不會因為改變了看待方法而變少，只是匯流排式看著更清晰些。

DQL 讓 BI 告別了寬表，實現了更大程度的自由自助；也拓寬了 BI 分析的邊界，讓分析可以應對更多的資料場景，讓 BI 成了更自由更好用的新一代的 BI。

DQL 從低層模型上解決了 JOIN 的問題後，前端的介面要怎麼來做其實也就變的簡單了，不需要再費心去想怎麼樣設計才能讓使用者更好的理解資料了，因為不管怎麼做，都能輕鬆理解拖拽了。

下面是潤乾基於 DQL 實現的一套介面，我們還是按前面的例子，挨個看看每個 JOIN 是怎麼呈現給業務人員，怎麼拖拽的。

外來鍵關聯 --- 中國經理的美國員工

經過 DQL 解析後，資料就都變成業務人員可以理解的清晰的樹狀結構了。

原先的兩個表變到一個表裡了，業務人員已經完全不用去管後臺是幾個表，怎麼關聯了，直接拖拽員工姓名，再拖拽部門經理姓名，然後再設定一下兩個的國籍，就可以了。

同維表關聯

同樣的，多表變一表，主鍵相同的表，像員工表，經理表；客戶表，VIP 客戶表，直接同化到一個表中了。

主子表關聯 --- 每個訂單的總金額

主子表，被視為一個表了，拖出訂單，再選擇求和方式拖出明細金額就可以了，不操心怎麼關聯的。

按維對齊彙總 --- 按日期統計 3 個不同表的彙總金額

這個雖然還是三個表，但業務人員也不用管各個表之間有什麼關聯關係，找到對應的金額指標，選擇求和，然後直接拖拽就可以，再選一個“日”當做共同的統計條件，那就是按日期彙總了。

而且查詢控制元件還會自動把和已選擇資料不匹配的資料項過濾隱藏掉，有彙總的還會自動建立彙總項與統計維度之間的匹配關係，使用起來就更加智慧了，不僅避免了出錯，保證了拖拽分析的業務正確性，也使得查詢分析更加流暢了。

潤乾基於 DQL 引擎的全新一代 BI，突破寬表的限制，真正做到自由靈活分析，讓業務人員能能輕鬆應對各種資料 JOIN 場景的 BI

DQL 引擎會把 DQL 語句翻譯成 SQL 執行，所以可以基於任何關聯式資料庫工作。這款 DQL 引擎目前是免費提供的哦，前端介面部分還開源，你可以輕鬆把這些強大的功能整合到自己的 BI 應用中。

BI 的定位是自由的分析，它可以隱忍一時的因為技術限制而帶來的不自由，但它絕不會永遠這樣逆來順受，技術是需要革新的，也會有人去革新，當新的技術突破瓶頸，捅破限制它的天花板以後，新一代的 BI 就到來了。