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

BI 商業(yè)智能這個(gè)概念已經(jīng)提出好幾十年了，這個(gè)概念本身比較寬泛，不同人也有不同的理解和定義，但落實(shí)到技術(shù)環(huán)節(jié)，特別是面向業(yè)務(wù)用戶的環(huán)節(jié)，所稱的 BI，基本就是指的多維分析或者自助報(bào)表

不管是叫自助報(bào)表還是多維分析，也都是一回事，都是讓用戶自己去通過拖拽的方式查詢數(shù)據(jù)或制作報(bào)表

用戶想通過 BI，實(shí)現(xiàn)查詢和報(bào)表自由，也就是可以靈活地分析自己想要的數(shù)據(jù)，挖掘出更大的價(jià)值

廠商想通過 BI，給用戶賦能，盤活用戶數(shù)據(jù)價(jià)值的同時(shí)，也能體現(xiàn)出 BI 產(chǎn)品本身的價(jià)值

那實(shí)際的情況如何呢，BI 有沒有發(fā)揮出它預(yù)期的作用呢，我們就來探究一下

做技術(shù)的都清楚，要查詢分析數(shù)據(jù)，其實(shí)就是編寫 SQL 語句去查詢（我們假設(shè)要分析的數(shù)據(jù)都在關(guān)系數(shù)據(jù)庫中，這是絕大多數(shù) BI 的實(shí)際場景），那給業(yè)務(wù)人員使用的BI 多維分析的技術(shù)本質(zhì)，其實(shí)就是通過頁面拖拽出這個(gè) SQL

對于單表的查詢，并不是很難理解和實(shí)施，選出字段再配上過濾條件及排序，和用 Excel 差不太多，分組匯總會稍復(fù)雜些，但也不是多難懂

但是，有業(yè)務(wù)意義的查詢經(jīng)常涉及多表關(guān)聯(lián)，比如查詢存儲余額 10 萬元以上的儲戶中本地人的比例，看看某月回款額與發(fā)票額的對比。這些都需要多表關(guān)聯(lián)，也就是要用到 SQL 的 JOIN

業(yè)務(wù)人員很難理解 SQL 的 JOIN，多個(gè)表及其關(guān)系是個(gè)網(wǎng)狀形式，要指定關(guān)聯(lián)字段，還會涉及自關(guān)聯(lián)、遞歸關(guān)聯(lián)還有子查詢再關(guān)聯(lián)的復(fù)雜情況。三五個(gè)關(guān)聯(lián)表之間的數(shù)據(jù)關(guān)系連技術(shù)人員都可能會暈，就更別說業(yè)務(wù)人員了，這時(shí)候，界面再炫麗、操作再流暢都沒有什么意義了

多表的 JOIN 拖拽把用戶難住了，BI 廠商就只能繞路解決，總不能和用戶說我們的分析只能基于單表進(jìn)行吧（畢竟相當(dāng)多有業(yè)務(wù)意義的分析都是多表的，世界是普遍關(guān)聯(lián)的嘛），目前采用的變通手段就是建模，當(dāng)前市場上的產(chǎn)品，基本都是這么做的

所謂建模，就是把表間關(guān)聯(lián)運(yùn)算做成邏輯視圖或物理寬表，這樣業(yè)務(wù)人員在查詢時(shí)相當(dāng)于面對的還是邏輯上的單表，這就又變的簡單了，又可以拖拽了

問題完美解決？不，并沒有，寬表并不是一個(gè)好的解決方案

寬表的局限性很明顯，數(shù)據(jù)冗余，維護(hù)麻煩這些就不說了

單單是：分析也只能基于寬表現(xiàn)有的關(guān)聯(lián)去做這一條，就讓用戶和廠商都無法忍受了

用戶分析需求超出范圍，或者有變化，就得技術(shù)人員修改或者重新再做一次寬表，用戶不自由，啥也得廠商幫忙，今天想做的分析，可能得一周以后才能做；廠商更不樂意，每一次修改和重做，都是人工成本，可是自己產(chǎn)品提供的自助關(guān)聯(lián)又不好用，也只能任用戶擺布了

當(dāng)然有的 BI 廠商的建模，不叫寬表，事實(shí)上他們也確實(shí)比寬表做了更多的準(zhǔn)備和優(yōu)化，但歸根結(jié)底，不管是 CUBE, 還是立方體，還是其他名字，本質(zhì)都還是一個(gè)寬表，邏輯上并沒有脫離寬表的范疇，分析需求變動時(shí)，還是得技術(shù)人員去改

在一個(gè)數(shù)據(jù)系統(tǒng)中,BI 的作用本來就有限，然后還被死死的限制在了需要技術(shù)人員介入的寬表上，所謂的自由靈活就更得打折扣了

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

造成這些難題的根本原因是，SQL 本身對于 JOIN 的定義過于簡單了，用來描述復(fù)雜的關(guān)聯(lián)場景時(shí)，就會很難理解，容易犯暈，就像用加法來描述乘法一樣

我們通過兩個(gè)例子來看下

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

涉及通話記錄表和電話帳戶表以及地區(qū)表的多次關(guān)聯(lián)

查詢：中國經(jīng)理的美國員工

人事系統(tǒng)里員工表，還有部門表。員工表中有所屬部門的字段與部門表關(guān)聯(lián)，部門會有經(jīng)理，而經(jīng)理也是個(gè)員工，部門表中的經(jīng)理字段會再和員工表關(guān)聯(lián)。這就發(fā)生互相關(guān)聯(lián)的情況，轉(zhuǎn)圈了

這倆例子是很正常，很普遍的查詢，但是即使是技術(shù)人員來寫這個(gè) SQL，也得費(fèi)點(diǎn)勁兒，這是 SQL 本身的局限性造成的

BI 廠商們也沒有在數(shù)據(jù)模型層面針對這個(gè)難題進(jìn)行優(yōu)化封裝，只是簡單的把表對業(yè)務(wù)人員做了可視，把技術(shù)人員都覺得難的問題丟給了沒有技術(shù)能力的業(yè)務(wù)人員，那當(dāng)然沒人能用的起來了

更多的關(guān)于 BI 廠商做不好 JOIN 的分析，可以參考：為什么 BI 軟件都搞不定關(guān)聯(lián)分析

要解決這個(gè)難題，就需分析研究 SQL 的 JOIN 運(yùn)算，突破 SQL 的局限才可以

我們發(fā)現(xiàn)，BI 多維分析中需要的 JOIN，都屬于這么3+1種情況：

1. 外鍵關(guān)聯(lián)，多對 1 的 JOIN 和 LEFT JOIN

2. 同維表關(guān)聯(lián)，1 對 1 的 LEFT JOIN 或 FULL JOIN

3. 主子表關(guān)聯(lián)，1 對多的 JOIN 和 LEFT JOIN

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

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

這里說的是 BI 中的 JOIN，并不是 SQL 中全部的 JOIN，有些關(guān)聯(lián)計(jì)算仍然需要原始的 JOIN 定義來描述，比如做矩陣乘法，但在 BI 中碰不到

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

我們來分別看一下這幾種情況下的 SQL 的復(fù)雜度以及 DQL 是怎么解決的

外鍵屬性化

我們用前面提到的那個(gè)查詢中國經(jīng)理的美國員工的例子來看一下 SQL 要怎么寫，員工表里有個(gè)部門外鍵字段指向部門表的主鍵，部門表里又有經(jīng)理外鍵字段指回員工表，這是很常見的數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)

SQL 寫出來是這樣的：

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

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

使用 DQL 會寫成這樣：

SELECT *FROM 員工表WHERE 國籍='美國' AND 部門.經(jīng)理.國籍='中國'

這個(gè)句子中，美國員工好理解，中國經(jīng)理的條件稍復(fù)雜一點(diǎn)，字段有了子屬性，子屬性又有子屬性，但并不難理解，也就是部門的經(jīng)理的國籍是中國

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

同維表等同化

這是兩個(gè)一比一的表，主鍵相同，在數(shù)據(jù)庫設(shè)計(jì)中經(jīng)常有這種情況，字段的業(yè)務(wù)分類不同，不適合都放在一個(gè)表里，太寬的表在各字段豐滿度相差較大時(shí)還會造成空間冗余浪費(fèi)，訪問性能也下降，因此常常會分到多個(gè)主鍵相同的表中

現(xiàn)在我們要查詢計(jì)算所有員工的收入

SQL 中需要做 JOIN：

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

DQL 則可以把這兩個(gè)表看成一個(gè)表訪問：

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

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

子表集合化

訂單及訂單明細(xì)是典型的主子表，前者的主鍵是后者的一部分

現(xiàn)在我們想計(jì)算每張訂單的總金額

用 SQL 寫出來會是這樣：

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

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

如果我們把子表中與主表相關(guān)的記錄看成主表的一個(gè)字段，那么這個(gè)問題也可以不再使用 JOIN 以及 GROUP BY：

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

與普通字段不同，訂單明細(xì)被看成訂單表的字段時(shí)，其取值將是一個(gè)集合，因?yàn)閮蓚€(gè)表是一對多的關(guān)系。所以要在這里使用聚合運(yùn)算把集合值計(jì)算成單值。這種簡化方式稱為子表集合化

這樣看待主子表關(guān)聯(lián)，不僅理解書寫更為簡單，而且不容易出錯

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

按維對齊

這里有三個(gè)表：合同表、回款表和庫存表

我們希望按日期統(tǒng)計(jì)合同額、回款額和庫存金額

用 SQL 寫出來是這樣的：

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

用子查詢把每個(gè)表分組匯總后再 JOIN 起來，如果偷懶不用子查詢先 JOIN 后 GROUP，那結(jié)果是錯誤的，統(tǒng)計(jì)值會變多。這個(gè)問題必須使用子查詢

這里涉及的三個(gè)子查詢都要連接上，SQL 的 JOIN 關(guān)系要寫成若干個(gè)兩表關(guān)聯(lián)，在表比較多時(shí)，增刪關(guān)聯(lián)表有可能把某個(gè)表漏掉而沒有連接條件，出現(xiàn)完全叉乘

用 DQL 寫出來是這樣的：

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

在 DQL 中，只要把這幾個(gè)表分別按日期對齊分別匯總就行了，而不必關(guān)心這些表之間的關(guān)系，在增刪表時(shí)也不容易發(fā)生遺漏

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

我們希望按地區(qū)統(tǒng)計(jì)銷售員人數(shù)和合同額

用 SQL 寫出來是這樣：

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

這個(gè)子查詢很復(fù)雜

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

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

這里又出現(xiàn)了子屬性，但整個(gè)句子仍然很簡單，DQL 允許每個(gè)表獨(dú)立設(shè)定統(tǒng)計(jì)維度，無須關(guān)心表間關(guān)聯(lián)，還可以與屬性化的外鍵配合使用

對這些 JOIN 更深入的探討，可以參考 連接運(yùn)算 1-SQL 中的 JOIN
http://c.raqsoft.com.cn/article/1619562709132

解決關(guān)聯(lián)

前面講的這幾個(gè) JOIN 的例子，都是在實(shí)際應(yīng)用中常見的，具有業(yè)務(wù)意義的查詢需求，

這些例子都是可以用來檢驗(yàn) BI 產(chǎn)品的“自助”靈活程度的，能否不需要技術(shù)人員更新模型就由完成這些查詢。結(jié)果會發(fā)現(xiàn)，業(yè)內(nèi)的大部分 BI 產(chǎn)品，無論界面多炫麗、操作多流暢，都經(jīng)不起這個(gè)檢驗(yàn)

原因就在于，低層模型上，并沒有解決好 JOIN 問題

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

從前面的 DQL 例子中可以明顯的看出，前 3 個(gè)查詢用 SQL 的 JOIN 都沒有了，多表變成單表了，只是字段變成有子屬性了，而這并不難理解，業(yè)務(wù)人員可以輕車熟路地搞定。最后一個(gè)按維對齊的情況雖然還有 JOIN，但也很簡單，用戶無需關(guān)心這些表之間的關(guān)聯(lián)關(guān)系，只要向統(tǒng)一的目標(biāo)維度對齊就行了

重新定義 JOIN 后，就徹底把不易于理解和拼寫的 JOIN 變的簡單易懂了，再做一個(gè)拖拽的前端界面，能讓業(yè)務(wù)人員做 JOIN 的 BI 就做成了

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

DQL 和寬表大有不同!!!

DQL 當(dāng)然也需要技術(shù)人員提前定義好元數(shù)據(jù)，但是用到技術(shù)人員的地方也僅此一次

元數(shù)據(jù)中預(yù)先定義好了各種關(guān)聯(lián)關(guān)系，但并沒有做實(shí)際關(guān)聯(lián)，當(dāng)用戶在前端拖拽分析的時(shí)候，才實(shí)時(shí)生成關(guān)聯(lián)查詢，不需要像寬表一樣預(yù)先關(guān)聯(lián)，占用數(shù)據(jù)庫資源

它的關(guān)聯(lián)關(guān)系只要數(shù)據(jù)表本身結(jié)構(gòu)不變，就不用修改元數(shù)據(jù)，不需要像寬表一樣總得重新生成，相當(dāng)于一次定義可以適應(yīng)無數(shù)次不同的分析需求，它擁有寬表的優(yōu)勢但從根本上解決了寬表的各種弊端

這就是所謂的非按需建模，建模只要考慮數(shù)據(jù)結(jié)構(gòu)本身，而與用戶需求無關(guān)。寬表（無論邏輯還是物理的）則是按需建模，需求一變就要再建模

用 DQL 語法還能降低出錯率

很多程序員習(xí)慣用 WHERE 來寫 JOIN 運(yùn)算的過濾條件，表少的時(shí)候沒有問題，表多的時(shí)候漏寫了 JOIN 條件意味著將發(fā)生多對多的完全叉乘，而這個(gè) SQL 卻還可以正常執(zhí)行，一方面計(jì)算結(jié)果會出錯，另一方面，如果漏寫條件的表很大，笛卡爾積的規(guī)模將是平方級的，這極有可能把數(shù)據(jù)庫直接“跑死”！

采用 DQL 的 JOIN 語法，就不可能發(fā)生漏寫 JOIN 條件的情況了。因?yàn)閷?JOIN 的理解不再是以笛卡爾積為基礎(chǔ)，而且設(shè)計(jì)這些語法時(shí)已經(jīng)假定了多對多關(guān)聯(lián)沒有業(yè)務(wù)意義，這個(gè)規(guī)則下寫不出完全叉乘的運(yùn)算

對于多個(gè)子表分組后與主表對齊的運(yùn)算，在 SQL 中要寫成多個(gè)子查詢的形式。但如果只有一個(gè)子表時(shí)，可以先 JOIN 再 GROUP，這時(shí)不需要子查詢。有些程序員沒有仔細(xì)分析，會把這種寫法推廣到多個(gè)子表的情況，也先 JOIN 再 GROUP，可以避免使用子查詢，但計(jì)算結(jié)果是錯誤的

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

DQL 還能讓數(shù)據(jù)結(jié)構(gòu)顯得更為清晰

這是我們平時(shí)看到的 E-R 圖，它是個(gè)網(wǎng)狀結(jié)構(gòu)的，表與表之間可能都有關(guān)聯(lián)，表多了就會顯得很零亂，增刪表的時(shí)間很容易遺漏或重復(fù)表間的關(guān)聯(lián)。

而在 DQL 體系下看到的表間關(guān)聯(lián)是總線式的：

表與表之間沒有直接的關(guān)聯(lián)，都只處在中間地位的維度關(guān)聯(lián)，增刪表的時(shí)候不會影響到其它表，數(shù)據(jù)結(jié)構(gòu)耦合度低

不過，要說明的是，無論是 E-R 圖還是后面的總線圖，其中連線的數(shù)量都是相當(dāng)?shù)?，這是數(shù)據(jù)關(guān)系本身決定的，不會因?yàn)楦淖兞丝创椒ǘ兩?，只是總線式看著更清晰些

DQL 讓 BI 告別了寬表，實(shí)現(xiàn)了更大程度的自由自助；也拓寬了 BI 分析的邊界，讓分析可以應(yīng)對更多的數(shù)據(jù)場景，讓 BI 成了更自由更好用的新一代的 BI

DQL 從低層模型上解決了 JOIN 的問題后，前端的界面要怎么來做其實(shí)也就變的簡單了，不需要再費(fèi)心去想怎么樣設(shè)計(jì)才能讓用戶更好的理解數(shù)據(jù)了，因?yàn)椴还茉趺醋?，都能輕松理解拖拽了

下面是潤乾基于 DQL 實(shí)現(xiàn)的一套界面，我們還是按前面的例子，挨個(gè)看看每個(gè) JOIN 是怎么呈現(xiàn)給業(yè)務(wù)人員，怎么拖拽的

外鍵關(guān)聯(lián) --- 中國經(jīng)理的美國員工

經(jīng)過 DQL 解析后，數(shù)據(jù)就都變成業(yè)務(wù)人員可以理解的清晰的樹狀結(jié)構(gòu)了

原先的兩個(gè)表變到一個(gè)表里了，業(yè)務(wù)人員已經(jīng)完全不用去管后臺是幾個(gè)表，怎么關(guān)聯(lián)了，直接拖拽員工姓名，再拖拽部門經(jīng)理姓名，然后再設(shè)置一下兩個(gè)的國籍，就可以了

同維表關(guān)聯(lián)

同樣的，多表變一表，主鍵相同的表，像員工表，經(jīng)理表；客戶表，VIP 客戶表，直接同化到一個(gè)表中了

主子表關(guān)聯(lián) --- 每個(gè)訂單的總金額

主子表，被視為一個(gè)表了，拖出訂單，再選擇求和方式拖出明細(xì)金額就可以了，不操心怎么關(guān)聯(lián)的

按維對齊匯總 --- 按日期統(tǒng)計(jì) 3 個(gè)不同表的匯總金額

這個(gè)雖然還是三個(gè)表，但業(yè)務(wù)人員也不用管各個(gè)表之間有什么關(guān)聯(lián)關(guān)系，找到對應(yīng)的金額指標(biāo)，選擇求和，然后直接拖拽就可以，再選一個(gè)“日”當(dāng)做共同的統(tǒng)計(jì)條件，那就是按日期匯總了

而且查詢控件還會自動把和已選擇數(shù)據(jù)不匹配的數(shù)據(jù)項(xiàng)過濾隱藏掉，有匯總的還會自動建立匯總項(xiàng)與統(tǒng)計(jì)維度之間的匹配關(guān)系，使用起來就更加智能了，不僅避免了出錯，保證了拖拽分析的業(yè)務(wù)正確性，也使得查詢分析更加流暢了

潤乾基于 DQL 引擎的全新一代 BI，突破寬表的限制，真正做到自由靈活分析，讓業(yè)務(wù)人員能能輕松應(yīng)對各種數(shù)據(jù) JOIN 場景的 BI

DQL 引擎會把 DQL 語句翻譯成 SQL 執(zhí)行，所以可以基于任何關(guān)系數(shù)據(jù)庫工作。這款 DQL 引擎目前是免費(fèi)提供的哦，前端界面部分還開源，你可以輕松把這些強(qiáng)大的功能集成到自己的 BI 應(yīng)用中

BI 的定位是自由的分析，它可以隱忍一時(shí)的因?yàn)榧夹g(shù)限制而帶來的不自由，但它絕不會永遠(yuǎn)這樣逆來順受，技術(shù)是需要革新的，也會有人去革新，當(dāng)新的技術(shù)突破瓶頸，捅破限制它的天花板以后，新一代的 BI 就到來了