精读《15 大 LOD 表达式 - 下》

接着上一篇 精读《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

如果你想参与讨论，请 点击这里，每周都有新的主题，周末或周一发布。前端精读 - 帮你筛选靠谱的内容。

关注 前端精读微信公众号

版权声明：自由转载-非商用-非衍生-保持署名（创意共享 3.0 许可证）