《15 大 LOD 表达式 - 上》

当前期刊数: 216

通过上一篇 精读《什么是 LOD 表达式》 的学习，你已经理解了什么是 LOD 表达式。为了巩固理解，结合场景复习是最有效的手段，所以这次我们结合 Top 15 LOD Expressions 这篇文章学习 LOD 表达式的 15 大应用场景，因篇幅限制，本文介绍 1～8 场景。

1. 客户下单频次

各下单次数的顾客数量是多少？

柱状图的 Y 轴显然是 count([customerID])，因为要统计 当前维度下的客户总数。

这里插一句，对于柱状图的 Y 轴，在 sql 里就是对 X 轴 group by 后的聚合，因此 Y 轴就是对 X 轴各项的汇总。

柱状图的 X 轴要表达的是以何种粒度拆解，比如我们是看各城市数据，还是看各省数据。在这个场景下也不例外，我们要看 各下单次数下的数据，那么如何把下单次数转化为维度呢？

我们需要用 FIX 表达式制作一个维度字段，表示各顾客下单次数。很显然数据库是没有这个维度的，而且这个维度需要按照客户 ID group by 后，按照订单 ID count 聚合才能得到，因此可以利用 FIX 表达式：{ fixed [customerID] : count([orderId]) } 描述。

2. 阵列分析

当我们看年客户销售量时，即便是逐年增长的，我们也会有一个疑问：每年销量中，首单在各年份的顾客分别贡献了多少？

因为关系到老客忠诚度和新客拓展速度，新客与老客差距过大都不好，那我们如何让 2021 年的柱状图按照 2019、2020、2021 年首单的顾客分层呢？这就是阵列分析。

我们要画一个柱状图，X、Y 轴分别是 [Year]、sum([Sales])。

为了让柱状图分层，我们需要一个表示颜色图例的维度字段，比如我们拖入已有的性别维度，每根柱子就会被划分为男、女两块。但问题是，我们制作并不存在的 “首单年份维度”？

答案是利用 FIX 表达式：{ fixed [customerID] : min([orderDate]) }。

3. 日利润指标

分析 每年各月份的盈利、亏损天数分布。如下图：

列是年到月的下钻，比较好实现，只要拖入字段 [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] 拖入行的颜色透视即可。

4. 占总体百分比

LOD 表达式的一大特色就是计算跨详细级别的占比，比如我们要看 欧洲各国的销量在全世界占比：

显然这个图里所有国家之和不是 100%，因为欧洲加起来也才不到百分之二十，然而在当前详细级别下，是拿不到全球总销售量的，所以我们可以利用 FIX 表达式来实现：sum([sales]) / max({ sum([sales]) })。

这里解释两点：

1. 之所以用 max 是因为 LOD 表达式只是一个字段，并没有聚合方式，运算必须在相同详细级别下进行，由于总销量只有一条数据，所以我们用 max 或者 min 甚至 sum 都行，结果都是一样的。
2. 如果不加维度限制，就可以省略 “fix” 申明，所以 { sum([sales]) } 实际上就是 FIX 表达式，它表示 { fixed : sum([sales]) }。

5. 新客增长趋势

看着年客户增长趋势图，你有没有想过，这个趋势图肯定永远是向上的？也就是说，看着趋势图朝上走，不一定说明业务做得好。

如果公司每年都比去年发展的好，每年的新增新客数应该要比去年多，所以 每年新客增长趋势图 才比较有意义，如果你看到这个趋势图的趋势朝上，说明每年的新客都比去年多，说明公司摆脱了惯性，每年都获得了新的增长。

所以我们要加一个筛选条件。新增一个维度字段，当这一单客户是今年新客时为 true，否则为 false，这样我们筛选时，只看这个字段为 true 的结果就行了。

那么这个字段怎么来呢？思路是，获取客户首单年份，如果首单年份与当前下单年份相同，值为 true，否则为 false。

我们利用 LOD 创建首单年份字段 [firstOrderDate]：{ fixed [customerId] : min([orderDate]) }，然后创建筛选字段 [newOrExist]: IFF([firstOrderDate] = [orderDate], 'true', 'false')。

6. 销量对比分析

入下图条形图所示，右侧是每项根据选择的分类的对比数据：

对比值计算方式是，用 当前的销量减去当前选中分类的销量。相信你可以猜到，但前分类的销量与当前视图详细级别无关，只与用户选择的 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) 即可。

7. 平均最高交易额

如下图所示，当前的详细级别是国家，但我们却要展示每个国家平均最高交易额：

显然，要求平均最高交易额，首先要计算每个销售代表的最高交易额，由于这个详细级别比国家低，我们可以利用 INCLUDE 表达式计算销售代表最高交易额 largestSalesByRep: { include [salesRep] : max([sales]) }，并对这个度量字段求平均即可。

从这个例子可以看出，如果我们在一个较高的详细级别，比如国家，此时的 sum([sales]) 是根据国家详细级别汇总的，而忽略了销售代表这个详细级别。但如果要展示每个国家的平均最高交易额，就必须在销售代表这个详细级别求 max([sales])，由于是各国家的，所以我们不用 { fixed [salesRep] }，而是 { include [salesRep] }，这样最终计算的详细级别是：[country]，[salesRep]，这样才能算出销售在每个国家的最高交易额（因为也许某些销售同时在不同国家销售）。

8. 实际与目标

在第六个例子 - 销量对比分析中，我们可以看到销量绝对值的对比，这次，我们需要计算实际销售额与目标的差距百分比：

如上图所示，左上角展示了实际与目标的差值；右上角展示了每个地区产品目标完成率；下半部分展示了每个产品实际销量柱状图，并用黑色横线标记出目标值。

左上角非常简单，[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])，将这个字段拖入指标，按照百分比格式化，就得到结果了。

总结

通过上面的例子，我们可以总结出实际业务场景中几条使用心法：

1. 首先对计算公式进行拆解，判断拆解后的字段是否数据集里都有，如果都有的话就结束了，说明是个简单需求。
2. 如果数据集里没有，而且发现数据详细级别与当前不符（比如要得到每个国家销量，但当前维度是城市），就要用 FIXED 表达式固定详细级别。
3. 如果不是明确的按照某个详细级别计算，就不要使用 FIXED，因为不太灵活。
4. 当计算时要跳过某个指定详细级别，但又要保留视图里的详细级别时，使用 EXCLUDE 表达式。
5. 如果计算涉及到比视图低的详细级别，比如计算平均或者最大最小时，使用 INCLUDE 表达式。
6. 使用 FIXED 表达式创建的字段也可以进行二次计算，合理拆解多个计算字段并组合，会让逻辑更加清晰，易于理解。

讨论地址是：精读《15 大 LOD 表达式 - 上》· Issue ##369 · dt-fe/weekly

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

关注 前端精读微信公众号

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