Tableau嵌套LOD：客户购买力分析的多遍聚合

注：本文是《数据可视化分析：Tableau原理与实践》第二版重写内容。

10.5.2  多遍聚合：客户购买力分析的嵌套LOD计算

在10.2和10.3小节中，笔者借用“客户购买力”指标，分析了如何在视图详细级别中引用“更低聚合度”详细级别的聚合。两个购买力指标分别是：

• 客户购买力指标1: 每位客户的最大交易金额的均值
• 客户购买力指标2: 每位客户的累计销售额的均值

它们都是从“省份*客户”的详细级别（reference LOD），从数据表明细行级别计算聚合，前者是MAX最大交易金额，后者是SUM累计销售额；而后计算AVG平均值作为省份的“客户购买力指标”。前者以商品为购买力单位（买的越贵越好），后者以客户为购买力单位（累计贡献越多越好）。

这两个角度都各有偏颇，“最大交易金额”会忽略单品购买少，但是订单总额高的客户；而“累计销售额”则会把1个月的新客户和12各月的老客户对等。交易的视角太小，客户的视角太大，此时，可以从订单的角度来看，把客户的一次完整购买作为分析对象，从每个客户的所有订单中，选择金额最高的订单作为该客户的购买力标记。于是，就有了如下的问题：

问题6：2021年，各省份的客户购买力指标（每位客户所有订单销售额总和的最高额的销售额的均值）

理解了业务背景，这里展开为四个步骤介绍具体的过程。

1. 解析问题结构

每个问题都是由三个部分构成的，从最终结果来看，这里的结构很清晰。

问题6：2021年，各省份的客户购买力指标（每位客户所有订单销售额总和的最高额的销售额的均值）

• 筛选范围：“2021年”，筛选都是逻辑判断，各种形式都指向计算YEAR(订单日期)=2021
• 问题描述：维度构成问题的详细级别，即视图详细级别（Viz LOD）
• 聚合答案：“客户购买力指标”回答问题，答案必然是聚合，每个省份对应一个聚合值

问题的难点在于，“客户购买力指标”本身是包含了另一个详细级别的“子问题”。可以把这个指标进一步转化为问题的标准结构，即“每个客户的最大订单金额”。

进一步解析如下：

• 筛选范围：无
• 问题描述：客户，即“子问题”对应视图详细级别，相对于“母问题”而言，本书把称之为引用详细级别（Reference LOD）
• 聚合答案：“最大订单金额”，聚合方式是最大值MAX，计作“MAX([订单金额])”

如果分析使用的数据详细级别（IT的角度叫“主键”）是“订单ID”，子问题就可以使用FIXED指定引用的详细级别，计算对应的聚合后，最后求平均就可以了，如下所示：

AVG（{ FIXED [客户ID] : MAX([ 订单金额 ])}）

不过，此时的“子问题”中又嵌套了一个更小的“子子问题”：每笔订单的金额总和。与之对应，就需要引用一个更细的详细级别“订单”。这个分解过程与上面完全相同。最终计算应该是：

（订单）总和的最大值的平均值，AVG（MAX（SUM()）））

接下来，需要确认每个聚合对应的详细级别，然后通过表达式指定在一起。

2. 确定详细级别

在分析问题的过程中，问题中包含的详细级别也已经逐步清晰。从类别上看，问题中只有三类详细级别，本案例的难点在于存在两个引用详细级别：

• 数据表详细级别（Table LOD）：即代表数据表明细行唯一性的字段或者字段组合，IT称之为“主键”（PK，Primary Key），本例使用的超市数据为 “订单ID*产品ID”
• 引用详细级别（reference LOD）：预先聚合对应的详细级别，在这个案例中，存在两个。
  • 客户的详细级别，用来计算每个客户的最大订单金额，聚合方式对应MAX
  • 订单的详细级别，用来预先将明细中的交易数据，聚合为订单数据，对应SUM
• 最终问题详细级别（Viz LOD）：对应问题的维度，本案例是“省份”

三者看似相互独立，实则相互依赖。数据表详细级别是其他详细级别聚合的起点，引用详细级别的预先聚合，最终也将统一到问题详细级别展现。它们的彼此关系，参见10.4小节讲解。

在不同的详细级别中，对应的聚合全然不同，AVG依赖主视图，MAX依赖订单，而SUM依赖于交易明细。它们彼此又有前后关系，可视化是表达层次问题的绝好方法，可以使用如图10-28的示意图直观地理解。

图10‑28  客户购买力分析中，不同详细级别的彼此关系

简单的问题，比如“各省份的销售额总和”，可以理解为从数据表明细直接到问题详细级别的聚合过程（图中标记a所示），这个过程代表商品的角度——累计有多少金额的产品被销售。不过，基于订单总额的最大值作为“客户购买力指标”，进而获得“各省的客户购买力指标”，则需要以两个引用详细级别作为桥梁，使用不同的聚合类型获得。

接下来的关键，是将不同详细级别与对应的聚合类型，统一到一个计算中来。

3. 依据LOD完成聚合

首先，基于订单的详细级别，完成“每个订单的销售额总和”。

如果订单ID在系统中能唯一指导到某个省份的某个客户，则可以{FIXED [订单] : SUM(销售额)}完成。很多客户的系统中无法做到这一点，因此建议完整地表述如下：

{FIXED [省份], [客户],  [订单] : SUM(销售额)}

为了理解方便，可以把这个字段保存为“订单的销售额”，从而简化后续的计算。

其次，基于客户详细级别，计算“每个客户的最大订单金额”

一个客户会有多个订单，可以使用MAX([订单的销售额])为每个客户保留一个值；不过，这个计算只有在客户的详细级别（reference LOD_02）才有意义，把聚合指定到详细级别，如下：

{FIXED [省份], [客户]  : MAX(订单的销售额)}

注意，由于一个客户可能在多个省有消费，此时每个客户在各个省份中要单独计算最大订单金额，因此客户详细级别（reference LOD_02）必须同时指定省份和客户两个维度。

为了理解方便，可以把这个字段保存为“客户的最大订单金额”，从而简化后续计算。

最后，基于主视图详细级别，计算省份的“客户购买力”

每个省份有很多客户，数量不一，因此不能使用SUM求和，而应该计算 多个客户的最大订单额的平均值，这样每个省份对应一个唯一聚合值。由于省份就是问题的详细级别，无需FIXED指定，只需要简单的聚合计算即可，如下：

AVG（[客户的最大订单金额]）

多次嵌套可能降低计算的性能，高级用户也可以直接把上述过程整合在一个计算中，如下：

AVG({ FIXED [省份], [客户]  : MAX(

{FIXED [省份], [客户],  [订单] : SUM(销售额)} ) } )

考虑到问题中还有“2021年”的筛选器，FIXED LOD计算默认不受它的影响，因此要么使用上下文筛选器调整优先级，要么使用INCLUDE LOD替代上述的逻辑。既然主视图的详细级别是省份，因此只需要引用其他的维度字段即可，简化为：

AVG({  INCLUDE [客户]  : MAX(

{INCLUDE [客户],  [订单] : SUM(销售额)} ) } )

如图10-29所示，作为对比，这里添加了三个聚合，分别是交易的均值、最大交易的均值和最大订单的均值。分别代表不同的观察视角。

图10‑29  2020年，不同省份的客户购买力（多个指标）

在之前分析中，虽然笔者引入了“订单的销售额”“客户的最大订单金额”作为中间字段，简化了理解过程，不过在最终计算的过程中，笔者通常习惯于一气呵成，这样可以保持思维的连贯性，确认之后可以保存为字段，并在字段中增加必要的说明，帮助后续修改和理解。

4. 可视化展示

这个题目的问题类型较为简单，因此在第三步就完成了基本的条形图。在逻辑复杂，特别是筛选和问题维度中包含引用详细级别的聚合是，建议从交叉表开始，在计算完成后再判断和选择可视化类型。

选择图表的逻辑、增加参数等互动方式的过程，参考第5章介绍，这里不再赘述。

这里简化一下图形，仅保留“客户购买力字段”（参考最大订单金额），排序的优先选项是条形图，不过这里省份众多，又是单一度量值，也可以调整为柱状图，并做调整如下10-30所示。

图10‑30  2020年，不同省份的客户购买力（单一指标）

“没有对比就没有分析”，除了排序，这里增加了参考线，从而并以参考线为基准区分客户购买力较高的省份和较低的省份，辅助以颜色表示。

这里的关键是参考线的计算，在第9章中讲“参考线是表计算的化身”，默认拖入平均值，就是对视图聚合值的WINDOW_AVG计算，而编辑调整为“合计”，就转化为了TOTAL计算。这二者分别对应不同的计算逻辑。

WINDOW_AVG是以当前视图详细级别的聚合结果为二次聚合起点，对视图中各省份的购买力指标的算术平均。如果各个省份的客户购买力用A代表，那么计算逻辑如下（n为省份数量）：

这个过程可以直接用自定义表计算完成：

WINDOW_AVG( AVG({  INCLUDE [客户]  : MAX(

{INCLUDE [客户],  [订单] : SUM(销售额)} ) } ) )

而TOTAL的逻辑则截然不同，TOTAL合计有一些LOD表达式的影子，它不考虑当前视图详细级别的影响，直接从明细基础上做合计——这里的明细，不一定数据表的明细，而是AVG计算映射的详细级别。这个嵌套LOD的案例中，合计的对象是每个客户的最大订单金额。

如果用C代表每个省份下每个客户的最大订单金额，及引用详细级别的聚合值，那么Total的计算逻辑如下（m为各省份中客户的算术之和，一个客户在两个省份有销售，计作2次）：

如果要在图10-30中手动计算TOTAL的值，应该是如下的表达式：

{ EXCLUDE  [省/自治区]:AVG({INCLUDE  [省/自治区],[客户 Id]:MAX(

{INCLUDE  [省/自治区],[客户 Id],[订单 Id]: SUM([销售额])})})}

为什么在这个过程中，嵌套的INCLUDE中增加了   [省/自治区]呢？这就涉及到嵌套LOD的一个额外知识了。