继续上一篇精读《15 大 LOD 表达式 - 上》，这次继续总结Top15LODExpressions文章的第9到15个场景。9、如何实现某一时间段内最后一天的股票日均收盘价与当月最后一天收盘价的对比趋势图？如图所示，要比较的不是某个时间段，而是当月最后一天的收盘价，所以必须用LOD表达式。假设原表如下：DateTickerAdjClose29/08/2013SYMC$128/08/2013SYMC$227/08/2013SYMC$3我们按照月份为横轴聚合，求avg([AdjClose])为纵轴。但是为了计算比较，我们需要一个MaxDate字段如下：DateTickerAdjCloseMax,Date29/08/2013SYMC$129/08/201328/08/2013SYMC$229/08/201327/08/2013SYMC$329/08/2013如果我们使用max(Date)expression聚合后可以看到MaxDate:MonthofDateTickerAvg,AdjCloseMax,Date08/2013SYMC$229/08/2013原因是max(Date)是聚合表达式，只能在groupby中使用聚合sql生效。但是如果我们要计算最后一天的收盘价，就需要执行sum([最后一天的收盘价]，表达式如下：[最后一天的收盘价]=if[MaxDate]=[Date]then[AdjClose]else0end。但是问题是这个表达式计算的详细程度是按照天的粒度计算的，而我们的max(Date)不能按照天的粒度计算：DateTickerAdjCloseMax,Date29/08/2013SYMC$128/08/2013SYMC$227/08/2013SYMC$3原因如前所述，聚合表达式不能出现在非聚合明细层次。所以我们可以通过{include:max([Date])}表达式轻松实现如下效果：DateTickerAdjClose{include:max([Date])}29/08/2013SYMC$129/08/201328/08/2013SYMC$229/08/201327/08/2013SYMC$329/08/2013{include:max([Date])}表达式没有指定include参数，也就是说总是在当前视图的详细级别计算，所以这个字段被下推到明细表用于计算时，也可以出现在明细表的每一行中。然后按照上面的思路组装表达式。展开来说，如果我们在横轴上按年汇总，那么比较值就是每年最后一天的收盘价。原因是{include:max([Date])}会以当年的粒度计算max([Date])，自然就是当年的最后一天，然后下推到明细表。全年共有365行数据。[最后一天收盘价]大概是这样的：DateTickerAdjClose[最后一天收盘价]31/12/2013SYMC$1$130/12/2013SYMC$2$1......03/01/2013SYMC$7$102/01/2013SYMC$8$101/01/2013SYMC$9$1然后比较值可以根据sum([最后一天的收盘价])进行聚合。10、回购数组如下图所示。我想查看客户第一次购买和第二次购买之间的季度回购数组：关键是如何找到第一次购买和第二次购买之间的季度时间差。首先，可以通过[1stpurchase]={fixed[customerid]:min([orderdate])}计算每个客户的首次购买时间。二次购买时间如何计算？这是一个小技巧。先用[repeatpurchase]=iif([orderdate]>[1stpurchase],[orderdate],null)得到一个新列，第一个purchase的行的值为null，我们可以用min函数忽略计算特征时为null，得到第二次购买时间：[2ndpurchase]={fixed[customerid]:min([repeatpurchase])}。最后，使用datediff函数获取间隔中的季度数：[quartersrepeattopurchase]=datediff('quarter',[1stprechase],[2ndpurchase])。11.范围均值差百分比如下图所示。我们要将趋势图的每个点与所选区域的平均值（图中两条虚线范围内）做一个差值百分比，生成一个新的折线图。在顶端。重点是上面折线图的y轴字段，差异百分比是怎么表示的。首先我们需要生成一个只包含指定区间的收盘价：[参考期收盘价]=IF[日期]>=[起始参考日期]AND[日期]<=[结束参考日期]THEN[调整收盘价]END，只有当日期在指定的区间内时，这个表达式才返回[Adjclose]，即只包括这个区间内的值。第二步是计算指定区间的平均值。这可以通过FIX表达式来完成：[参考日期之间的平均每日收盘价]={固定[Ticker]:AVG([参考期间的收盘价])}。第三步是计算百分比差异：[与参考期的百分比差异]=([调整收盘价]-[参考日期之间的平均每日收盘价])/[参考日期之间的平均每日收盘价]。最后用【percentdifferentfromrefperiod】字段画出上面的图形。12、相对周期过滤如果我们要比较两个周期的数据差异，可能会遇到数据不完整导致的错误。比如今年3月份的数据，只输出到6号，但是和去年3月份整个月的数据对比，显然是不合理的。我们可以用LOD表达式来解决这个问题：相对循环过滤的要点是不能直接和日期比较，因为今年的数据总是比去年大。例如，因为今年最新的数据是11.11，所以会过滤掉去年11.11之后的数据。先找出最新数据是哪一天，无条件使用FIX表达式：[maxdate]={max([date])}。然后使用datepart函数计算当前日期：[dayofyearofmaxdate]=datepart('dayofyear',[maxdate]),[dayofyearoforderdate]=datepart('dayofyear',[订购日期]）。所以[dayofyearofmaxdate]是一个卡点，必须过滤掉今年任何超过这个天数的数据。所以我们创建一个过滤条件：[periodfilter]=[dayofyearoforderdate]<=[dayofyearofmaxdate]。只需使用[periodfilter]字段作为过滤条件即可。13.用户登录频率如何绘制用户每月的登录频率？要计算此指标，请将用户的总活动时间除以登录总数。首先计算总活跃时间：使用FIX表达式计算用户最早和最晚登录时间：[firstlogin]={fixed[userid]:min([logindate])}[lastlogin]={fixed[userid]:max([logindate])}计算月差异，即用户活跃的月份数：[用户活跃的总月数]=datediff("month",[firstlogin],[lastlogin])totallogintimes比较简单。固定用户ID后，统计登录日期：[每个用户的登录次数]={fixed[userid]:count([logindate])}最后，我们将两者相除得到用户登录频率：[登录频率]=[用户活跃的总月数]/[每个用户的登录次数]制作图表非常简单，将[登录频率]移动到水平轴，并将不同的用户ID作为垂直轴。14.比例笔刷这是LOD最常见的场景。例如，每个类别的销售额对该类别总销售额的贡献是多少？sum(sales)/sum({fixed[category]??:sum(sales)})就可以了。当前的详细程度是类别+国家/地区。如果我们固定类别，我们可以得到每个类别在所有国家的累计销售额。15.按客户群划分的年度购买频率如何表明年长客户更忠诚？我们可以拿下图，根据客户群（2011年、2012年的客户）作为图例，观察他们每年的购买频次分布。如上图所示，我们发现客户注册越早，每次购买频次的比例越高，这也印证了老客户忠诚度更高的结论。注意这里看到的是至少买了N次，所以每条线的对比是有说服力的。如果是N次购买，有可能老顾客少买一次，多买10次，所以很难直接比较。首先我们生成一个图例字段，即按照最早购买年份划分客户组：[Cohort]={fixed[customerid]:min(Year([orderdate]))}然后，类似于我们的第一个例如，计算每个订单数量下，有多少客户。唯一不同的是，我们不仅按照customerID分组，还进一步拆分了最早购买日期，即：{fixed[customerid],[Cohort]:count([orderid])}。上面的字段作为X轴，Y轴类似于第一个例子：count(customerid)，但是我们要查的是至少有N次购买，也就是购买次数是a累计值，即至少购买9次=购买9次+购买10次+...购买MAX次。所以它是DESC的窗口和，整体表达式应该像[RunningTotal]=WINDOW_SUM(count(customerid)),0,LAST())。最后，因为实际Y轴计算的是比例，所以将刚刚计算出的至少N次购买的指数除以每个Cohort下的购买总数，即[RunningTotal]/sum({fixed[Cohort]:count([客户ID]）}）。总结以上例子，都是基于fixed、include、exclude这些基本LOD用法的叠加。但从实际例子中我们会发现，真正的难点不在于LOD表达式的语法，而是我们如何准确理解需求，将其拆解成合理的计算步骤，并在需要运行LOD的计算步骤中正确使用.LOD表情看起来很神奇，似乎可以和数据“神奇”地契合在一起。我们要明白，LOD的背后是表与表之间的join，不同的细节层次代表不同的groupby规则。您可以更好地理解为什么LOD表达式会这样工作。讨论地址为：精读《15 大 LOD 表达式 - 下》·第370期·dt-fe/weekly想参与讨论的请戳这里，每周都有新话题，周末或周一发布。前端精读——帮你过滤靠谱的内容。关注前端精读微信公众号
