列資料庫與行資料庫存---SQL與DAX

有些人喜歡用Excel電子表格的思維方式來思考Power Pivot的核心語言DAX,以致於問的問題都帶有很強烈的Excel表函數思維的味道.這真是一個悲傷的故事,這會導致學習效率很慢.這也沒辦法,做為一般的辦公用戶,除了Excel就沒有其他的.

在我眼裡,它就是一門資料庫語言,和SQL語言很類似.如果你習慣於SQL語言，那麼已經使用了許多表，並創建了列之間的連接，以便設置關係。從這個角度來看，你已經習慣用表關係,欄位篩選等思維去觀察數據,那恭喜你了,你將在DAX世界中感覺回到家庭.(很多不同的BI工具的計算方式都會倍感親切,覺得的語言思維都很類似)

一般的SQL語言的資料庫與BI其實本質都是資料庫.一般的資料庫是行資料庫,數據是一行一行的存儲的,而BI都是列資料庫(Power bi,Tableau等等),所有的數據都是一列一列的存儲,數據以列存儲的方式的好處就是計算速度要比行資料庫要快很多.打個比方這裡有一份數據清單,

假如要對銷售數量求和.做為行資料庫,他的計算方式是掃描第一行,找到銷售數量這行的數據取出來,再接著掃描第二行,同樣找到銷售數量這行的數據取出來,以此循環,得掃描完整個表再加總.這樣的處理方式是很慢的,只為了計算銷售數量這個欄位,居然要掃完整個表.而列資料庫就要快N多倍,由於數據是以列的形式存儲的,它只要找到這個欄位,挑出來,直接加總,就不需要掃描完整個表,這節省了很多時間.因此所有的BI都是列資料庫,計算速度更快.

其次SQL語言的查詢與DAX查詢是很類似的,使用的思維邏輯一樣,只是語言不太一樣,如果你能用SQL語言做一個複雜查詢,那同樣的,你可以用剛才的SQL查詢思路,用DAX語言再寫一遍.就像打麻將在不同的地方有不同的規則,但思考的思維習慣是一樣的.

還是接上一張圖,假如要把銷售數量大於平均銷售數量的業務員找出來,用SQL應該怎麼寫呢?

SELECT * FROM [銷售記錄$] nWHERE 銷售數量>(SELECT AVG(銷售數量) FROM [銷售記錄$]) nORDER BY 業務員n

EVALUATEnCALCULATETABLE ( 表3, nFILTER ( 表3, 表3[銷售數量] > AVERAGE ( 表3[銷售數量] ) ) )nORDER BY 表3[業務員]n

結果一模一樣,只是排列順序不太一樣.在SQL里,是先查詢出平均值的結果表,然後再將這個結果表與外層的的Select的where條件作對比,把最後的結果篩選出來.

而使用DAX編寫查詢的思路完全是同樣如此.同樣是先用FILTER把大於平均銷量的結果表查詢出來,再將這個結果表做為外層表的篩選條件.

接下來按上面的思路,只是把難度加大點.有這樣一張銷售表,要把叫王雙的所在部門,查詢出來一份銷售清單

用SQL寫:

SELECT * FROM [銷售表$] nWHERE 銷售部門=(SELECT 銷售部門 FROM [銷售表$] WHERE 姓名="王雙") nORDER BY 姓名n

用DAX寫:

EVALUATEnCALCULATETABLE (n 表4,n 表4[銷售部門]n IN ( CALCULATETABLE ( VALUES ( 表4[銷售部門] ), 表4[姓名] = "王雙" ) )n)nORDER BY 表4[姓名] ASCn

思路都是類似的,他們都是資料庫,都要按資料庫的規則來思考,不像電子表格那麼靈活(太靈活了,也導致數據不幹凈...),只是寫法稍有區別.

來做一道比較複雜的題目.必修課表裡的課程是所有同學必須學的,以這張表為基準,在選課情況表裡找出沒有學習必修課表裡的課程的同學學號找出來,還包括沒有學哪些課程.

SQL:

SELECT 學號,課程名稱 FROM n(SELECT DISTINCT 學號 FROM [選課情況$]),[必修課$] nWHERE 學號&課程性質&課程名稱 NOT IN (SELECT 學號&課程性質&課程名稱 FROM [選課情況$])n

結果如下:

DAX:

EVALUATEnEXCEPT (nCROSSJOIN ( VALUES ( 選課情況[學號] ), ALLNOBLANKROW ( 必修課[課程名稱] ) ),nSELECTCOLUMNS ( 選課情況, "學號", 選課情況[學號], "課程", 選課情況[課程名稱] )n)n

無論是DAX還是SQL思路一模一樣,都是用必修課程表與學生學號相乘製造迪卡爾積,

然後與選課情況表進行比對.

還有很多挺複雜的資料庫查詢類的思考題就不一一舉例了.如果你過去非常習慣資料庫語言SQL,那你會覺得DAX查詢如魚得水,至少學習起來要比其他人快很多.又或許是其它BI工具如Tableau里的函數,比較容易搞懂,學任何BI工具都有一種覺得很容易上手的感覺.

有人認為我拿資料庫SQL對比DAX有點裝逼的感覺.但其實這不是我首先提出來的,而是我的確在用這種思維規劃思考問題,而不是按excel工作表函數的角度.我就把DAX權威指南這本國外的書翻譯一下,這本書首先提出這樣的觀點.

有人問我,如何腦補編寫度量公式時腦補中間的過程.我覺得如果不熟練時,可以放棄編寫度量公式,而是用插件編寫DAX查詢表,這個並不難,是一個熟能生巧的實踐過程.可以多嘗試用插件編寫DAX查詢表,這樣省得在寫度量公式時,總要不停的腦補中間過程.在用插件編寫查詢表時每一步的中間過程都是可以觀察到的.寫多了,以後在寫度量公式時,自然而然的就會腦補出中間過程.

這是以前閱讀國外書籍時,我自己編寫的大量的DAX查詢表時的公式代碼.寫多了,其實我也沒太注意思考上下文的問題.很多複雜的度量公式在編寫時,自己自動會腦補出一些中間過程.

EasyCharts團隊出品

帥的人都關注了EasyCharts團隊^..^~

QQ交流群：553270834

微信公眾號：EasyCharts