精讀《15 大 LOD 表示式 - 上》

黃子毅發表於2021-11-08

通過上一篇 精讀《什麼是 LOD 表示式》 的學習，你已經理解了什麼是 LOD 表示式。為了鞏固理解，結合場景複習是最有效的手段，所以這次我們結合 Top 15 LOD Expressions 這篇文章學習 LOD 表示式的 15 大應用場景，因篇幅限制，本文介紹 1～8 場景。

1. 客戶下單頻次

各下單次數的顧客數量是多少？

柱狀圖的 Y 軸顯然是 count([customerID])，因為要統計 當前維度下的客戶總數。

這裡插一句，對於柱狀圖的 Y 軸，在 sql 裡就是對 X 軸 group by 後的聚合，因此 Y 軸就是對 X 軸各項的彙總。

柱狀圖的 X 軸要表達的是以何種粒度拆解，比如我們是看各城市資料，還是看各省資料。在這個場景下也不例外，我們要看 各下單次數下的資料，那麼如何把下單次數轉化為維度呢？

我們需要用 FIX 表示式製作一個維度欄位，表示各顧客下單次數。很顯然資料庫是沒有這個維度的，而且這個維度需要按照客戶 ID group by 後，按照訂單 ID count 聚合才能得到，因此可以利用 FIX 表示式：{ fixed [customerID] : count([orderId]) } 描述。

2. 陣列分析

當我們看年客戶銷售量時，即便是逐年增長的，我們也會有一個疑問：每年銷量中，首單在各年份的顧客分別貢獻了多少？

因為關係到老客忠誠度和新客擴充速度，新客與老客差距過大都不好，那我們如何讓 2021 年的柱狀圖按照 2019、2020、2021 年首單的顧客分層呢？這就是陣列分析。

我們要畫一個柱狀圖，X、Y 軸分別是 [Year]、sum([Sales])。

為了讓柱狀圖分層，我們需要一個表示顏色圖例的維度欄位，比如我們拖入已有的性別維度，每根柱子就會被劃分為男、女兩塊。但問題是，我們製作並不存在的 “首單年份維度”？

答案是利用 FIX 表示式：{ fixed [customerID] : min([orderDate]) }。

3. 日利潤指標

分析 每年各月份的盈利、虧損天數分佈。如下圖：

列是年到月的下鑽，比較好實現，只要拖入欄位 [year] 並下鑽到月粒度，移除季度粒度即可。

行是 “高收益”、“正收益”、“虧損” 的透檢視，值是在當前月份中天數。

那麼如何計算高收益、虧損狀態呢？因為最終粒度是天，所以我們要按天計，首先就要得到每天的利潤總和，這些中間過程可以利用 LOD 的欄位來完成，即建立一個 日利潤欄位（profitPerDay）：{ fixed [orderDate] : sum([profit]) }。

由於我們對利潤總量不敏感，只希望拆分為三個階段，所以利用 IF THEN 生成一個新欄位 日利潤指標（dailyProfitKPI）：IF [profitPerDay] > 2000 THEN "Highly Profitable" ELSEIF [profitPerDay] <= 0 THEN "unprofitable" ELSE "profitable" END。

所以建立的 [dailyProfitKPI] 指標是個維度，即如果當前行所在的天利潤彙總如果大於 2000，值就是 "Highly Profitable"。所以在行上拖入 count(distinct [orderDate])，把 [dailyProfitKPI] 拖入行的顏色透視即可。

4. 佔總體百分比

LOD 表示式的一大特色就是計算跨詳細級別的佔比，比如我們要看 歐洲各國的銷量在全世界佔比：

顯然這個圖裡所有國家之和不是 100%，因為歐洲加起來也才不到百分之二十，然而在當前詳細級別下，是拿不到全球總銷售量的，所以我們可以利用 FIX 表示式來實現：sum([sales]) / max({ sum([sales]) })。

這裡解釋兩點：

  1. 之所以用 max 是因為 LOD 表示式只是一個欄位，並沒有聚合方式，運算必須在相同詳細級別下進行，由於總銷量只有一條資料，所以我們用 max 或者 min 甚至 sum 都行，結果都是一樣的。
  2. 如果不加維度限制，就可以省略 “fix” 申明，所以 { sum([sales]) } 實際上就是 FIX 表示式，它表示 { fixed : sum([sales]) }。

5. 新客增長趨勢

看著年客戶增長趨勢圖，你有沒有想過，這個趨勢圖肯定永遠是向上的？也就是說，看著趨勢圖朝上走，不一定說明業務做得好。

如果公司每年都比去年發展的好，每年的新增新客數應該要比去年多，所以 每年新客增長趨勢圖 才比較有意義，如果你看到這個趨勢圖的趨勢朝上，說明每年的新客都比去年多，說明公司擺脫了慣性，每年都獲得了新的增長。

所以我們要加一個篩選條件。新增一個維度欄位，當這一單客戶是今年新客時為 true，否則為 false，這樣我們篩選時，只看這個欄位為 true 的結果就行了。

那麼這個欄位怎麼來呢？思路是，獲取客戶首單年份，如果首單年份與當前下單年份相同，值為 true，否則為 false。

我們利用 LOD 建立首單年份欄位 [firstOrderDate]：{ fixed [customerId] : min([orderDate]) }，然後建立篩選欄位 [newOrExist]: IFF([firstOrderDate] = [orderDate], 'true', 'false')。

6. 銷量對比分析

入下圖條形圖所示，右側是每項根據選擇的分類的對比資料：

對比值計算方式是，用 當前的銷量減去當前選中分類的銷量。相信你可以猜到，但前分類的銷量與當前檢視詳細級別無關，只與使用者選擇的 Category 有關。

如果我們已經有一個度量欄位 - 選中分類銷量 selectedSales，應該再排除當前 category 維度的干擾，所以可用 EXCLUDE 表示式描述 selectedCategorySales: { exclude [category] : sum([selectedSales]) }。

接下來是建立 selectedSales 欄位。背景知識是 [parameters].[category] 可以獲得當前選中的維度值，那我們可以寫個 IF 表示式，在維度等於選中維度時聚合銷量，不就是選中銷量嗎？所以公式是：IF [category] = [parameters].[category] THEN sales ELSE 0 END。

最後對比差異，只要建立一個 [diff] 欄位，表示式為 sum(sales) - sum(selectedCategorySales) 即可。

7. 平均最高交易額

如下圖所示，當前的詳細級別是國家，但我們卻要展示每個國家平均最高交易額：

顯然，要求平均最高交易額，首先要計算每個銷售代表的最高交易額，由於這個詳細級別比國家低，我們可以利用 INCLUDE 表示式計算銷售代表最高交易額 largestSalesByRep: { include [salesRep] : max([sales]) }，並對這個度量欄位求平均即可。

從這個例子可以看出，如果我們在一個較高的詳細級別，比如國家，此時的 sum([sales]) 是根據國家詳細級別彙總的，而忽略了銷售代表這個詳細級別。但如果要展示每個國家的平均最高交易額，就必須在銷售代表這個詳細級別求 max([sales])，由於是各國家的，所以我們不用 { fixed [salesRep] }，而是 { include [salesRep] }，這樣最終計算的詳細級別是：[country]，[salesRep]，這樣才能算出銷售在每個國家的最高交易額（因為也許某些銷售同時在不同國家銷售）。

8. 實際與目標

在第六個例子 - 銷量對比分析中，我們可以看到銷量絕對值的對比，這次，我們需要計算實際銷售額與目標的差距百分比：

如上圖所示，左上角展示了實際與目標的差值；右上角展示了每個地區產品目標完成率；下半部分展示了每個產品實際銷量柱狀圖，並用黑色橫線標記出目標值。

左上角非常簡單，[diffActualTraget]: [profit] - [targetProfit]，只要將當前利潤與目標利潤相減即可。

右上角需要分為幾步拆解。我們的最終目標是計算每個地區產品目標完成率，顯然公式是 當前完成產品數/總產品數。總產品數比較簡單，在已有地區維度拆解下，計算下產品總數就行了，即 count(distinct [product])；難點是當前完成產品數，這裡我們又要用到 INCLUDE，為什麼呢？因為地區粒度比產品粒度高，我們看地區彙總的時候，就不知道各產品的完成情況了，所以必須 INCLUDE product 維度計算利潤目標差，公式是 [diffProductActualTraget] ：{ include [product] : sum(diffActualTraget) }，然後當這個值大於 0 就認為完成了目標，我們可以再建立一個欄位，即完成目標數，如果達成目標就是 1，否則是 0，這樣便於求 “當前完成產品數”：aboveTargetProductCount: IFF([diffProductActualTraget] > 0, 1, 0)，那麼當前完成產品數就是 sum([diffProductActualTraget])，所以產品目標完成率就是 sum([diffProductActualTraget]) / count(distinct [product])，將這個欄位拖入指標，按照百分比格式化，就得到結果了。

總結

通過上面的例子，我們可以總結出實際業務場景中幾條使用心法：

  1. 首先對計算公式進行拆解，判斷拆解後的欄位是否資料集裡都有，如果都有的話就結束了，說明是個簡單需求。
  2. 如果資料集裡沒有，而且發現資料詳細級別與當前不符（比如要得到每個國家銷量，但當前維度是城市），就要用 FIXED 表示式固定詳細級別。
  3. 如果不是明確的按照某個詳細級別計算，就不要使用 FIXED，因為不太靈活。
  4. 當計算時要跳過某個指定詳細級別，但又要保留檢視裡的詳細級別時，使用 EXCLUDE 表示式。
  5. 如果計算涉及到比檢視低的詳細級別，比如計算平均或者最大最小時，使用 INCLUDE 表示式。
  6. 使用 FIXED 表示式建立的欄位也可以進行二次計算，合理拆解多個計算欄位並組合，會讓邏輯更加清晰，易於理解。
討論地址是：精讀《15 大 LOD 表示式 - 上》· Issue #369 · dt-fe/weekly

如果你想參與討論，請 點選這裡，每週都有新的主題，週末或週一釋出。前端精讀 - 幫你篩選靠譜的內容。

關注 前端精讀微信公眾號

<img width=200 src="https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg">

版權宣告：自由轉載-非商用-非衍生-保持署名（創意共享 3.0 許可證）

相關文章