精讀《什麼是 LOD 表示式》

黃子毅發表於2021-11-02

LOD 表示式在資料分析領域很常用，其全稱為 Level Of Detail，即詳細級別。

精讀

什麼是詳細級別，為什麼需要 LOD？你一定會有這個問題，我們來一步步解答。

什麼是詳細級別

可以嘗試這麼發問：你這個資料有多詳細？

得到的回答可能是：

  1. 資料是彙總的，抱歉看不到細節，不過如果您正好要看總銷量的話，這兒都給您彙總好了。。
  2. 詳細？這直接就是原始表資料，30 億條，這夠詳細了吧？如果覺得還不夠詳細，那隻好把業務過程再拆分一下重新埋點了。

詳細程度越高，資料量越大，詳細程度越低，資料就越少，就越是彙總的資料。

人很難在詳細程度很高的 30 億條記錄裡看到有價值的資訊，所以資料分析的過程也可以看作是 對資料彙總計算的過程，這背後資料詳細程度在逐漸降低。

BI 工具的詳細級別

如果沒有 LOD 表示式，一個 BI 查詢的詳細程度是完全固定的：

  • 如果表格拖入度量，沒有維度，那就是最高詳細級別，因為最終只會彙總出一條記錄。
  • 如果折線圖拖入維度，那結果就是根據這個維度內分別聚合度量，資料更詳細了，詳細粒度為當前維度，比如日期。

如果我們要更詳細的資料，就需要在維度上拖入更多欄位，直到達到最詳細的明細表級別的粒度。然而同一個查詢不可能包含不同詳細粒度，因為詳細粒度由維度組合決定，不可改變，比如下面表格的例子：

行：國家 省 城市
列：GDP

這個例子中，詳細級別限定在了城市這一級彙總，城市下更細粒度的資料就看不到了，每一條資料都是城市粒度的，我們不可能讓查詢結果裡出現按照國家彙總的 GDP，或者看到更詳細粒度的每月 GDP 資訊，更不可能讓城市粒度的 GDP 與國家粒度 GDP 在一起做計算，算出城市 GDP 在國家中佔比。

但是，類似上面例子的需求是很多的，而且很常見，BI 工具必須想出一種解法，因此誕生了 LOD：LOD 就是一種表示式，允許我們在一個查詢中描述不同的詳細粒度。

從表示式計算來看詳細級別

表示式計算必須限定在同樣的詳細粒度，這是鐵律，為什麼呢？

試想一下下面兩張不同詳細粒度的表：

總銷售額：

10000

各城市銷售額：

北京 3000
上海 7000

如果我們想在各城市銷售額中，計算貢獻佔比，那麼就要寫出 [各城市銷售額] / [總銷售額] 的計算公式，但顯然這是不可能的，因為前者有兩條資料，後者只有一條資料，根本無法計算。

我們能做的一定是資料行數相同，那麼無論是 IF ELSE、CASE WHEN，還是加減乘除都可以按照行粒度進行了。

LOD 給了我們跨詳細粒度計算的能力，其本質還是將資料詳細粒度統一，但我們可以讓某列資料來自於一個完全不同詳細級別的計算：

城市 銷售額 總銷售額
北京 3000  10000
上海 7000  10000

如圖表，LOD 可以把資料加工成這樣，即雖然總銷售額與城市詳細粒度不同，但還是新增到了每一行的末尾，這樣就可以進行計算了。

因此 LOD 可以按照任意詳細級別進行計算，將最終產出 “貼合” 到當前查詢的詳細級別中。

LOD 表示式分為三種能力，分別是 FIXED、INCLUDE、EXCLUDE。

FIXED

{ fixed [省份] : sum([GDP]) }

按照城市這個固定詳細粒度，計算每個省份的 DGP，最後合併到當前詳細粒度裡。

假如現在的查詢粒度是省份、城市，那麼 LOD 欄位的新增邏輯如下圖所示：

可見，本質是兩個不同 sql 查詢後 join 的結果，內部的 sum 表示在 FIXED 表示式內的聚合方式，外部的 sum 表示，如果 FIXED 詳細級別比當前檢視詳細級別低，應該如何聚合。在這個例子中，FIXED 詳細級別較高，所以 sum 不起作用，換成 avg 效果也相同，因為合併詳細級別是，是一對多關係，只有合併時多對一關係才需要聚合。

最外層聚合方式一般在 INCLUDE 表示式中發揮作用。

EXCLUDE

{ exclude [城市] : sum([GDP]) }

在當前查詢粒度中，排除城市這個粒度後計算 GDP，最後合併到當前詳細粒度中。

假如現在的查詢粒度是省份、城市、季節，那麼 LOD 欄位的新增邏輯如下圖所示：

如圖所示，EXCLUDE 在當前檢視詳細級別的基礎上，排除一些維度，所得到的詳細級別一定會更高。

INCLUDE

{ include [城鄉] : avg([GDP]) }

在當前查詢粒度中，額外加上城鄉這個粒度後計算 GDP，最後合併到當前詳細粒度中。

這類的例子比較難理解，且在 sum 情況下一般無實際意義，因為計算結果不會有差異，必須在類似 avg 場景下才有意義，我們還是結合下圖來看：

這就是 avg 算不準的問題，即不同詳細級別計算的平均值是不同的，但 sum、count 等不會隨著詳細級別變化而影響計算結果，所以當涉及到 avg 計算時，可以通過 INCLUDE 表示式指定計算的詳細級別，以保證資料口徑準確性。

LOD 欄位怎麼用

除了上面的例子中，直接查出來展示給使用者外，LOD 欄位更常用的是作為中間計算過程，比如計算省份 GDP 佔在國內佔比。因為 LOD 已經將不同詳細粒度計算結果合併到了當前的詳細粒度裡，所以如下的計算表示式：

sum([GDP]) / sum({ fixed [國家] : sum([GDP]) })

看似是跨詳細粒度計算，其實沒有，實際計算時還是一行一行來算的，後面的 LOD 表示式只是在邏輯上按照指定的詳細粒度計算，但最終會保持與當前檢視詳細粒度一致，因此可以參與計算。

我們後面會繼續解讀 tableau 整理的 Top 15 LOD 表示式業務場景，更深入的理解 LOD 表示式。

總結

LOD 表示式讓你輕鬆建立 “脫離” 當前檢視詳細級別的計算欄位。

或許你會疑惑，為什麼不主動改變當前檢視詳細級別來實現同樣的效果？比如新增或減少一個維度。

原因是，LOD 往往用於跨詳細級別的計算，比如算部分相對總體的佔比，計算當條記錄是否為使用者首單等等，更多的場景會在下次精讀中解讀。

討論地址是：精讀《什麼是 LOD 表示式》· Issue #365 · dt-fe/weekly

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

關注 前端精讀微信公眾號

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

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

相關文章