接著上一篇 精讀《15 大 LOD 表示式 - 上》 ，這次繼續總結 Top 15 LOD Expressions 這篇文章的 9～15 場景。
9. 某時間段內最後一天的值
如何實現股票平均每日收盤價與當月最後一天收盤價的對比趨勢圖？
如圖所示，要對比的並非是某個時間段，而是當月最後一天的收盤價，因此必須要藉助 LOD 表示式。
設想原表如下：
| Date | Ticker | Adj Close |
|---|---|---|
| 29/08/2013 | SYMC | $1 |
| 28/08/2013 | SYMC | $2 |
| 27/08/2013 | SYMC | $3 |
我們按照月進行聚合作為橫軸，求 avg([Adj Close]) 作為縱軸即可。但計算對比我們需要一個 Max Date 欄位如下：
| Date | Ticker | Adj Close | Max, Date |
|---|---|---|---|
| 29/08/2013 | SYMC | $1 | 29/08/2013 |
| 28/08/2013 | SYMC | $2 | 29/08/2013 |
| 27/08/2013 | SYMC | $3 | 29/08/2013 |
如果我們使用 max(Date) 表示式，在聚合後結果是可以看到 Max Date 的：
| Month of Date | Ticker | Avg, Adj Close | Max, Date |
|---|---|---|---|
| 08/2013 | SYMC | $2 | 29/08/2013 |
原因是，max(Date) 是一個聚合表示式，只能在 group by 聚合 sql 下生效。但如果我們要計算最後一天的收盤價，就要執行 sum([Close value on last day]，表示式如下：
[Close value on last day] = if [Max Date] = [Date] then [Adj Close] else 0 end。
但問題是，這個表示式計算的明細級別是以天為粒度的，我們 max(Date) 在天粒度下是算不出來的：
| Date | Ticker | Adj Close | Max, Date |
|---|---|---|---|
| 29/08/2013 | SYMC | $1 | |
| 28/08/2013 | SYMC | $2 | |
| 27/08/2013 | SYMC | $3 |
原因就是上面說過的，聚合表示式不能在非聚合的明細級別中出現。因此我們利用 { include : max([Date]) } 表示式就能輕鬆實現下面的效果了：
| Date | Ticker | Adj Close | { include : max([Date]) } |
|---|---|---|---|
| 29/08/2013 | SYMC | $1 | 29/08/2013 |
| 28/08/2013 | SYMC | $2 | 29/08/2013 |
| 27/08/2013 | SYMC | $3 | 29/08/2013 |
{ include : max([Date]) } 表示式沒有給定 include 引數，意味著永遠以當前檢視的明細級別計算，因此這個欄位下推到明細表做計算時，也可以出現在明細表的每一行。接著按照上面的思路組裝表示式即可。
擴充一下，如果橫軸我們按年進行聚合，那麼對比值就是每年最後一天的收盤價。原因是 { include : max([Date]) } 會以當前年這個粒度計算 max([Date])，自然是當年的最後一天，然後下推到明細表，整整一年 365 行資料中，[Close value on last day] 大概是這樣：
| Date | Ticker | Adj Close | [Close value on last day] |
|---|---|---|---|
| 31/12/2013 | SYMC | $1 | $1 |
| 30/12/2013 | SYMC | $2 | $1 |
| ... | ... | ... | ... |
| 03/01/2013 | SYMC | $7 | $1 |
| 02/01/2013 | SYMC | $8 | $1 |
| 01/01/2013 | SYMC | $9 | $1 |
接著對比值按照 sum([Close value on last day]) 聚合即可。
10. 復購陣列
如下圖所示，希望檢視客戶第一次購買到第二次購買間隔季度的復購陣列：
關鍵在於如何求第一次與第二次購買的季度時間差。首先可以通過 [1st purchase] = { fixed [customer id] : min([order date]) } 計算每位客戶首次購買時間。
如何計算第二次購買時間？這裡有個小技巧。首先利用 [repeat purchase] = iif([order date] > [1st purchase], [order date], null) 得到一個新列，首次購買的那一行值為 null，我們可以利用 min 函式計算時忽略 null 的特性，得到第二次購買時間：[2nd purchase] = { fixed [customer id] : min([repeat purchase]) }。
最後利用 datediff 函式得到間隔的季度數：[quarters repeat to purchase] = datediff('quarter', [1st prechase], [2nd purchase])。
11. 範圍平均值差異百分比
如下圖所示，我們希望將趨勢圖的每個點，與選定區域（圖中兩個虛線範圍內）的均值做一個差異百分比，並生成一個新的折線圖放在上方。
重點是上面折線圖 y 軸欄位，差異百分比如何表示。首先我們要生成一個只包含指定區間的收盤值：
[Close value in reference period] = IF [Date] >= [Start reference date] AND [Date] <= [End reference date] THEN [Adj close] END，這段表示式只在日期在制定區間內時，才返回 [Adj close]，也就是隻包含這個區間內的值。
第二步，計算制定區間的平均值，這個用 FIX 表示式即可：[Average daily close value between ref date] = { fixed [Ticker] : AVG([Close value in reference period]) }。
第三步，計算百分比差異：[percent different from ref period] = ([Adj close] - [Average daily close value between ref date]) / [Average daily close value between ref date]。
最後就是用 [percent different from ref period] 這個欄位繪製上面的圖形了。
12. 相對週期過濾
如果我們想對比兩個週期資料差異，可能會遇到資料不全導致的錯誤。比如今年 3 月份資料只產出到 6 號，但卻和去年 3 月整月的資料進行對比，顯然是不合理的。我們可以利用 LOD 表示式解決這個問題：
相對週期過濾的重點是，不能直接用日期進行對比，因為今年資料總是比去年大。比如因為今年最新資料到 11.11 號，那麼去年 11.11 號之後的資料都要被過濾掉。
首先找到最新資料是哪一天，利用不包含條件的 FIX 表示式即可：[max date] = { max([date]) }。
然後利用 datepart 函式計算當前日期是今年的第幾天：
[day of year of max date] = datepart('dayofyear', [max date])，[day of year of order date] = datepart('dayofyear', [order date])。
所以 [day of year of max date] 就是一個卡點，任何超過今年這麼多天的資料都要過濾掉。因此我們建立一個過濾條件：[period filter] = [day of year of order date] <= [day of year of max date]。
把 [period filter] 欄位作為篩選條件即可。
13. 使用者登陸頻率
如何繪製一個使用者每個月登陸頻率？
要計算這個指標，得用使用者總活躍時間除以總登陸次數。
首先計算總活躍時間：利用 FIX 表示式計算使用者最早、最晚的登陸時間：
[first login] = { fixed [user id] : min([log in date]) }[last login] = { fixed [user id] : max([log in date]) }
計算其中月份 diff，就是使用者活躍月數：
[total months user is active] = datediff("month", [first login], [last login])
總登入次數比較簡單，也是固定使用者 ID 後，對登陸日期計數即可：
[numbers of logins per user] = { fixed [user id] : count([login date]) }
最後，我們用兩者相除，得到使用者登陸頻率：
[login frequency] = [total months user is active] / [numbers of logins per user]
製作圖表就很簡單了，把 [login frequency] 移到橫軸，count distinct 使用者 ID 作為縱軸即可。
14. 比例筆刷
這個是 LOD 最常見的場景，比如求各品類銷量佔此品類總銷量的貢獻佔比？
sum(sales) / sum({ fixed [category] : sum(sales) }) 即可。
當前詳細級別是 category + country，我們固定品類，就可以得到各品類在所有國家的累積銷量。
15. 按客戶群劃分的年度購買頻率
如何證明老客戶忠誠度更高？
我們可以如下圖，按照客戶群（2011 年、2012 年客戶）作為圖例，觀察他們每年購買頻次分佈。
如上圖所示，我們發現顧客註冊時間越早，各購買頻次的比例都更高，所以證明了老顧客忠誠度更高這一結論。注意這裡看的是至少購買 N 次，所以每條線相比才具有說服力。如果是購買 N 次，則可能老顧客購買 1 次較少，購買 10 次較多，難以直接對比。
首先我們生成圖例欄位，即按最早照購買年份劃分顧客群：[Cohort] = { fixed [customer id] : min(Year([order date])) }
然後就和我們第一個例子類似，計算每個訂單數量下，有多少顧客。唯一的區別是，我們不僅按照顧客 ID group，還要進一步對最早購買日期做拆分，即：{ fixed [customer id], [Cohort] : count([order id]) }。
上面的欄位作為 X 軸，Y 軸和第一個例子類似：count(customer id)，但我們想檢視的是至少購買 N 次，也就是這個購買次數是累計值，即至少購買 9 次 = 購買 9 次 + 購買 10 次 + ... 購買 MAX 次。所以是一種 DESC 的 windowsum，整體表示式應該類似 [Running Total] = WINDOW_SUM(count(customer id)), 0, LAST())。
最後，因為實際 Y 軸計算的是佔比，所以用剛才計算的至少購買 N 次指標除以各 Cohort 下總購買次數，即 [Running Total] / sum({ fixed [Cohort] : count([customer id]) })。
總結
上面的幾個例子，都是基於 fixed、include、exclude 這幾個基本 LOD 用法的疊加。但從實際例子來看，我們會發現真正的難點不在與 LOD 表示式的語法，而在於我們如何精確理解需求，拆解成合理的計算步驟，並在需要執行 LOD 的計算步驟正確的使用。
LOD 表示式看上去很神奇，似乎可以和資料 “神奇” 的貼合在一起，我們要理解到 LOD 背後就是表之間的 join，而不同明細級別就表示不同的 group by 規則這一背後原理，就能比較好的理解為什麼 LOD 表示式能這麼運作了。
討論地址是：精讀《15 大 LOD 表示式 - 下》· Issue #370 · dt-fe/weekly
如果你想參與討論，請 點選這裡，每週都有新的主題，週末或週一釋出。前端精讀 - 幫你篩選靠譜的內容。
關注 前端精讀微信公眾號
<img width=200 src="https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg">
版權宣告：自由轉載-非商用-非衍生-保持署名（創意共享 3.0 許可證）