還用寬表？體驗一下DQL成就新一代的BI吧

---

theme: awesome-green

持續創作，加速成長！這是我參與「掘金日新計劃 · 10 月更文挑戰」的第9天，點選檢視活動詳情

📢📢📢📣📣📣 哈嘍！大家好，我是【IT邦德】，江湖人稱jeames007，10年DBA工作經驗 一位上進心十足的【大資料領域博主】！😜😜😜 中國DBA聯盟(ACDU)成員，目前從事DBA及程式程式設計 擅長主流資料Oracle、MySQL、PG 運維開發，備份恢復，安裝遷移，效能優化、故障應急處理等。 ✨ 如果有對【資料庫】感興趣的【小可愛】，歡迎關注【IT邦德】💞💞💞 ❤️❤️❤️感謝各位大可愛小可愛！❤️❤️❤️

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

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

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

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

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

BI多維分析的本質

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

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

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

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

分析被禁錮在寬表內

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

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

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

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

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

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

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

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

BI廠商為什麼做不好JOIN

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

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

我們通過兩個例子來看下

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

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

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

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

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

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

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

重新定義JOIN的DQL

要解決這個難題，就需分析研究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寫出來是這樣的：

SELECT A.* FROM 員工表 A JOIN 部門表 ON A.部門 = 部門表.編號 JOIN 員工表 C ON 部門表.經理 = C.編號 WHERE A.國籍 = '美國' AND C.國籍 = '中國'

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

使用DQL會寫成這樣：

SELECT * FROM 員工表 WHERE 國籍='美國' AND 部門.經理.國籍='中國'

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

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

同維表等同化

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

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

SQL中需要做JOIN：

SELECT 員工表.姓名, 員工表.工資 + 經理表.津貼 FROM 員工表 LEFT JOIN 經理表 ON 員工表.編碼 = 經理表.編號

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

SELECT 姓名,工資+津貼 FROM 員工表

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

子表集合化

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

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

用 SQL 寫出來會是這樣：

SELECT T1.訂單編號,T1.客戶,SUM(T2.價格) FROM 訂單表 T1 JOIN 訂單明細表 T2 ON T1.訂單編號=T2.訂單編號 GROUP BY T1.訂單編號,T1.客戶

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

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

SELECT 訂單編號,客戶,訂單明細表.SUM(價格) FROM 訂單表

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

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

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

按維對齊

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

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

用SQL寫出來是這樣的：

SELECT T1.日期,T1.金額,T2.金額 FROM (SELECT 日期, SUM(金額) 金額 FROM 合同表 GROUP BY 日期）T1 LEFT JOIN (SELECT 日期, SUM(金額) 金額 FROM 回款表 GROUP BY 日期）T2 ON T1.日期 = T2.日期 LEFT JOIN (SELECT 日期, SUM(金額) 金額 FROM 庫存表 GROUP BY 日期 ) T3 ON T2.日期=T3.日期

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

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

用DQL寫出來是這樣的：

SELECT 合同表.SUM(金額),回款表.SUM(金額),庫存表.SUM(金額) ON 日期 FROM 合同表 BY 日期 LEFT JOIN 回款表 BY 日期 LEFT JOIN 庫存表 BY 日期

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

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

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

用SQL寫出來是這樣：

SELECT T1.地區,T1.數量,T2.金額 FROM (SELECT 地區,COUNT(1) 數量 FROM 銷售員 GROUP BY 地區）T1 LEFT JOIN (SELECT 客戶表.地區 地區,SUM(合同.金額) 金額 FROM 客戶表,合同表 WHERE 客戶表.編號=合同表.客戶 GROUP BY 客戶表.地區 ) T2 ON T1.地區 = T2.地區

這個子查詢很複雜

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

SELECT 銷售員.count(1),合同表.sum(金額) ON 地區 FROM 銷售員 BY 地區 JOIN 合同表 BY 客戶表.地區

這裡又出現了子屬性，但整個句子仍然很簡單，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

告別寬表的新一代BI

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

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

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

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

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

同維表關聯

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

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

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

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

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

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

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

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

總結

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