通过上一篇 精读《什么是 LOD 表达式》 的学习,你已经理解了什么是 LOD 表达式。为了巩固理解,结合场景复习是最有效的手段,所以这次我们结合 Top 15 LOD Expressions 这篇文章学习 LOD 表达式的 15 大应用场景,因篇幅限制,本文介绍 1~8 场景。
各下单次数的顾客数量是多少?
柱状图的 Y 轴显然是 count([customerID])
,因为要统计 当前维度下的客户总数。
这里插一句,对于柱状图的 Y 轴,在 sql 里就是对 X 轴
group by
后的聚合,因此 Y 轴就是对 X 轴各项的汇总。
柱状图的 X 轴要表达的是以何种粒度拆解,比如我们是看各城市数据,还是看各省数据。在这个场景下也不例外,我们要看 各下单次数下的数据,那么如何把下单次数转化为维度呢?
我们需要用 FIX 表达式制作一个维度字段,表示各顾客下单次数。很显然数据库是没有这个维度的,而且这个维度需要按照客户 ID group by 后,按照订单 ID count 聚合才能得到,因此可以利用 FIX 表达式:{ fixed [customerID] : count([orderId]) }
描述。
当我们看年客户销售量时,即便是逐年增长的,我们也会有一个疑问:每年销量中,首单在各年份的顾客分别贡献了多少?
因为关系到老客忠诚度和新客拓展速度,新客与老客差距过大都不好,那我们如何让 2021 年的柱状图按照 2019、2020、2021 年首单的顾客分层呢?这就是阵列分析。
我们要画一个柱状图,X、Y 轴分别是 [Year]
、sum([Sales])
。
为了让柱状图分层,我们需要一个表示颜色图例的维度字段,比如我们拖入已有的性别维度,每根柱子就会被划分为男、女两块。但问题是,我们制作并不存在的 “首单年份维度”?
答案是利用 FIX 表达式:{ fixed [customerID] : min([orderDate]) }
。
分析 每年各月份的盈利、亏损天数分布。如下图:
列是年到月的下钻,比较好实现,只要拖入字段 [year]
并下钻到月粒度,移除季度粒度即可。
行是 “高收益”、“正收益”、“亏损” 的透视图,值是在当前月份中天数。
那么如何计算高收益、亏损状态呢?因为最终粒度是天,所以我们要按天计,首先就要得到每天的利润总和,这些中间过程可以利用 LOD 的字段来完成,即创建一个 日利润字段(profitPerDay):{ fixed [orderDate] : sum([profit]) }
。
由于我们对利润总量不敏感,只希望拆分为三个阶段,所以利用 IF THEN 生成一个新字段 日利润指标(dailyProfitKPI):IF [profitPerDay] > 2000 THEN "Highly Profitable" ELSEIF [profitPerDay] <= 0 THEN "unprofitable" ELSE "profitable" END
。
所以创建的 [dailyProfitKPI]
指标是个维度,即如果当前行所在的天利润汇总如果大于 2000,值就是 "Highly Profitable"。所以在行上拖入 count(distinct [orderDate])
,把 [dailyProfitKPI]
拖入行的颜色透视即可。
LOD 表达式的一大特色就是计算跨详细级别的占比,比如我们要看 欧洲各国的销量在全世界占比:
显然这个图里所有国家之和不是 100%,因为欧洲加起来也才不到百分之二十,然而在当前详细级别下,是拿不到全球总销售量的,所以我们可以利用 FIX 表达式来实现:sum([sales]) / max({ sum([sales]) })
。
这里解释两点:
- 之所以用
max
是因为 LOD 表达式只是一个字段,并没有聚合方式,运算必须在相同详细级别下进行,由于总销量只有一条数据,所以我们用max
或者min
甚至sum
都行,结果都是一样的。 - 如果不加维度限制,就可以省略 “fix” 申明,所以
{ sum([sales]) }
实际上就是 FIX 表达式,它表示{ fixed : sum([sales]) }
。
看着年客户增长趋势图,你有没有想过,这个趋势图肯定永远是向上的?也就是说,看着趋势图朝上走,不一定说明业务做得好。
如果公司每年都比去年发展的好,每年的新增新客数应该要比去年多,所以 每年新客增长趋势图 才比较有意义,如果你看到这个趋势图的趋势朝上,说明每年的新客都比去年多,说明公司摆脱了惯性,每年都获得了新的增长。
所以我们要加一个筛选条件。新增一个维度字段,当这一单客户是今年新客时为 true,否则为 false,这样我们筛选时,只看这个字段为 true 的结果就行了。
那么这个字段怎么来呢?思路是,获取客户首单年份,如果首单年份与当前下单年份相同,值为 true,否则为 false。
我们利用 LOD 创建首单年份字段 [firstOrderDate]
:{ fixed [customerId] : min([orderDate]) }
,然后创建筛选字段 [newOrExist]
: IFF([firstOrderDate] = [orderDate], 'true', 'false')
。
入下图条形图所示,右侧是每项根据选择的分类的对比数据:
对比值计算方式是,用 当前的销量减去当前选中分类的销量。相信你可以猜到,但前分类的销量与当前视图详细级别无关,只与用户选择的 Category 有关。
如果我们已经有一个度量字段 - 选中分类销量 selectedSales
,应该再排除当前 category 维度的干扰,所以可用 EXCLUDE 表达式描述 selectedCategorySales
: { exclude [category] : sum([selectedSales]) }
。
接下来是创建 selectedSales
字段。背景知识是 [parameters].[category]
可以获得当前选中的维度值,那我们可以写个 IF 表达式,在维度等于选中维度时聚合销量,不就是选中销量吗?所以公式是:IF [category] = [parameters].[category] THEN sales ELSE 0 END
。
最后对比差异,只要创建一个 [diff]
字段,表达式为 sum(sales) - sum(selectedCategorySales)
即可。
如下图所示,当前的详细级别是国家,但我们却要展示每个国家平均最高交易额:
显然,要求平均最高交易额,首先要计算每个销售代表的最高交易额,由于这个详细级别比国家低,我们可以利用 INCLUDE 表达式计算销售代表最高交易额 largestSalesByRep
: { include [salesRep] : max([sales]) }
,并对这个度量字段求平均即可。
从这个例子可以看出,如果我们在一个较高的详细级别,比如国家,此时的 sum([sales])
是根据国家详细级别汇总的,而忽略了销售代表这个详细级别。但如果要展示每个国家的平均最高交易额,就必须在销售代表这个详细级别求 max([sales])
,由于是各国家的,所以我们不用 { fixed [salesRep] }
,而是 { include [salesRep] }
,这样最终计算的详细级别是:[country],[salesRep]
,这样才能算出销售在每个国家的最高交易额(因为也许某些销售同时在不同国家销售)。
在第六个例子 - 销量对比分析中,我们可以看到销量绝对值的对比,这次,我们需要计算实际销售额与目标的差距百分比:
如上图所示,左上角展示了实际与目标的差值;右上角展示了每个地区产品目标完成率;下半部分展示了每个产品实际销量柱状图,并用黑色横线标记出目标值。
左上角非常简单,[diffActualTraget]
: [profit] - [targetProfit]
,只要将当前利润与目标利润相减即可。
右上角需要分为几步拆解。我们的最终目标是计算每个地区产品目标完成率,显然公式是 当前完成产品数/总产品数。总产品数比较简单,在已有地区维度拆解下,计算下产品总数就行了,即 count(distinct [product])
;难点是当前完成产品数,这里我们又要用到 INCLUDE,为什么呢?因为地区粒度比产品粒度高,我们看地区汇总的时候,就不知道各产品的完成情况了,所以必须 INCLUDE product 维度计算利润目标差,公式是 [diffProductActualTraget]
:{ include [product] : sum(diffActualTraget) }
,然后当这个值大于 0 就认为完成了目标,我们可以再创建一个字段,即完成目标数,如果达成目标就是 1,否则是 0,这样便于求 “当前完成产品数”:aboveTargetProductCount
: IFF([diffProductActualTraget] > 0, 1, 0)
,那么当前完成产品数就是 sum([diffProductActualTraget])
,所以产品目标完成率就是 sum([diffProductActualTraget]) / count(distinct [product])
,将这个字段拖入指标,按照百分比格式化,就得到结果了。
通过上面的例子,我们可以总结出实际业务场景中几条使用心法:
- 首先对计算公式进行拆解,判断拆解后的字段是否数据集里都有,如果都有的话就结束了,说明是个简单需求。
- 如果数据集里没有,而且发现数据详细级别与当前不符(比如要得到每个国家销量,但当前维度是城市),就要用 FIXED 表达式固定详细级别。
- 如果不是明确的按照某个详细级别计算,就不要使用 FIXED,因为不太灵活。
- 当计算时要跳过某个指定详细级别,但又要保留视图里的详细级别时,使用 EXCLUDE 表达式。
- 如果计算涉及到比视图低的详细级别,比如计算平均或者最大最小时,使用 INCLUDE 表达式。
- 使用 FIXED 表达式创建的字段也可以进行二次计算,合理拆解多个计算字段并组合,会让逻辑更加清晰,易于理解。
如果你想参与讨论,请 点击这里,每周都有新的主题,周末或周一发布。前端精读 - 帮你筛选靠谱的内容。
关注 前端精读微信公众号
版权声明:自由转载-非商用-非衍生-保持署名(创意共享 3.0 许可证)