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

語言: CN / TW / HK

theme: awesome-green

持續創作,加速成長!這是我參與「掘金日新計劃 · 10 月更文挑戰」的第9天,點擊查看活動詳情

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

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

不管是叫自助報表還是多維分析,也都是一回事,都是讓用户自己去通過拖拽的方式查詢數據或製作報表

image

用户想通過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 的定義過於簡單了,用來描述複雜的關聯場景時,就會很難理解,容易犯暈,就像用加法來描述乘法一樣

我們通過兩個例子來看下

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

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

imagepng

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

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

imagepng

這倆例子是很正常,很普遍的查詢,但是即使是技術人員來寫這個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是怎麼解決的

外鍵屬性化

imagepng

我們用前面提到的那個查詢中國經理的美國員工的例子來看一下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的語法體系中,外鍵被看成了屬性,外鍵指向表的字段可直接用子屬性的方式引用,也允許多層和遞歸引用

同維表等同化

imagepng

這是兩個一比一的表,主鍵相同,在數據庫設計中經常有這種情況,字段的業務分類不同,不適合都放在一個表裏,太寬的表在各字段豐滿度相差較大時還會造成空間宂餘浪費,訪問性能也下降,因此常常會分到多個主鍵相同的表中

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

SQL中需要做JOIN:

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

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

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

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

子表集合化

imagepng

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

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

用 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後直接再加字段就可以了

按維對齊

imagepng

這裏有三個表:合同表、回款表和庫存表

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

用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中,只要把這幾個表分別按日期對齊分別彙總就行了,而不必關心這些表之間的關係,在增刪表時也不容易發生遺漏

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

imagepng

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

用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還能讓數據結構顯得更為清晰

imagepng

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

而在DQL體系下看到的表間關聯是總線式的:

imagepng

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

不過,要説明的是,無論是E-R圖還是後面的總線圖,其中連線的數量都是相當的,這是數據關係本身決定的,不會因為改變了看待方法而變少,只是總線式看着更清晰些

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

告別寬表的新一代BI

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

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

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

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

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

imagepng

imagepng

同維表關聯

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

imagepng

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

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

imagepng

imagepng

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

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

imagepng

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

imagepng

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

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

總結

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

在這裏插入圖片描述