Skip to content

Latest commit

 

History

History
183 lines (105 loc) · 10.5 KB

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

File metadata and controls

183 lines (105 loc) · 10.5 KB

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