Tableau TC 表计算的独特性和逻辑方法

差不多花了十几天的时间，一直在想Tableau表计算的逻辑，我想我应该找到了完整地理解表计算的究竟方法，思路和脉络先整理如下，然后通过视频完整表达，并完善其中的逻辑框架。

关于表计算的逻辑部分，视频已经开始更新（声音最近有点哑，见谅），感觉本文过于抽象的新手，推荐视频学习：

• 【视频】广义LOD表达式 （升级表计算部分）

一、什么是表计算的独特性和差异性？

按照官方介绍，“表计算是在本地对视图数据的二次聚合”，不过这句话是站到全局角度的概括，对于新人而言，反而徒增压力，不知其所以然。那如何更简单的介绍表计算？也许我们可以尝试用下面的几句话：

“表计算是对视图中数值的计算。” （英文版）
“表计算是 单一聚合度量，在维度间的行内计算。”（拆解版，适合新手）
“表计算是在本地对聚合数据的二次聚合计算。”（功能版本）

理解这句话是完整理解表计算的基础，而理解这句话的最好方式，是把它与行级别计算、视图计算做一个简单的比较。

• 行级别计算是在数据库对多个度量字段直接计算，比如profit/sales，类似于Excel的计算公式；
• 视图级别的计算（聚合计算），是在数据库对多个度量字段先聚合再计算，而后呈现在视图中，比如sum[profit]/sum[sales]，类似于Excel中数据透视图中的计算；
• 表计算，是在视图中对单一度量字段（聚合）内，不同行的相互计算，比如sum[profit]，计算2014年和2015年的差异。在不更改数据结构的前提下，聚合计算无法完成这种行内计算（多行之间的计算）。

典型代表是：差异计算。不建议大家刚上来用index来理解。

这是表计算的独特性和差异性，是行级别计算、聚合计算、狭义LOD计算都无法完成的。

Table Calculation—— computation on values in a table

总结三句话：

• 表计算是做行内计算；
• 表计算只针对单一度量值计算。
• 维度参与计算过程！（要害）

二、表计算的关键——维度如何参与计算？理解分区与依据

表计算是单一度量字段内的行内计算，而其关键，或者是独特性是维度参与计算过程。所以理解表计算的关键，是理解维度在表计算中的作用。

既然是单一度量字段内的计算，就有先后顺序和范围边界。比如计算差异，当前值和前一个数值计算差异，还是和后一个数值计算差异？如果度量字段被年和类别两个字段分类，跨越类别的差异没有意义，因此就需要设定一个边界，让每个类别都能重新开始，而非连续计算差异。

所以，使用表计算的关键是：根据需要设置你的计算依据和边界（分区），这一切的开关是：维度。

而这里的关键提示是，暂时放下lod的思路，放下此前的冰山模型和四步骤逻辑，完全用方向和边界的思路去理解表计算的内部逻辑。

1、表计算的关键是维度参与计算过程

2、表计算的逻辑（下图）。具有表计算的视图中，维度可以分为 方向维度和范围维度，非此即彼。

因为方向和范围是非此即彼的关系，方向的终点就是范围，所以理解的关键是方向字段。正因为此，在Tableau中，也没有单独设置范围的地方，因为设置方向就间接地确定了范围。

当只有一个方向字段时，相对容易理解，但是当我们有多个方向字段时，问题就会变得复杂，这里就需要辅助理解的方式方法。

3、单一方向字段

方向字段决定了表计算的核心逻辑。–范例：

• –沿着 年， 计算 （各年）销售额的同比差异；
• –沿着 订单日期month，计算（各日期）销售额的移动汇总；
• –沿着 类别，计算（各类别）销售额的排名；
• –沿着 省/自治区，计算  全国 销售额的汇总
• –沿着 省/自治区，计算  全国 销售额的总体平均值
• 沿着 订单日期month，计算（各日期）销售额的7天移动平均

4、多个方向字段的情形

表计算中最难的，在于多个方向维度时的设置，特别是还需要把表计算结果转化为可视化图形的时候（参见10大表计算的案例 ）​。按照我多年的使用经验，最佳的实践​经验如下：

• 永远都要使用“编辑表计算”的“特定维度”来设置方向，不要使用表/区​；
• 把方向维度和范围维度，分别放在列和行上，而非混在一起；
• 多个方向维度的走向，使用“长城凹凸图”来表示，列中先后顺序，要和“编辑表计算”中的​顺序一致。
• 多个方向字段，按照默认的“最深原则”，谁排在后面，方向从谁开始。这里建议大家画一个类似的“长城凹凸图”来辅助理解。

• 先做完计算，然后转化图形。

5、相对方向和分区，是上述问题的简化形式

但是不推荐使用。

 

三、相对和绝对设置

Tableau中有两种依据和分区的方法，分别称之为相对依据与分区、绝对依据与分区，前者更基本，后者更高级，适合于多维度下的表计算设定。

1、相对direction and scope

table ——pane ——cell

Tableau把每个度量在多个维度下的组合称之为表、区、和单元格三个层次，分别代表最大的范围、中等范围和最小范围。按照这个分类设置的计算依据和分区，就可以指引表计算的方向和边界。在有区的情况下，所有的依据和方向就是下面的组合，总共三种边界划分方法，内部又按照方向而不同：

这个有助于学习的理解，但是这个设置有一个重大的安全风险——当我们调整维度的位置时，分区就会变化，表计算结果就会随着变化，可能会在意料之外。

2、绝对 addressing and  partition

安全的方法是使用表、区和单元格的理念，把范围指定到具体的维度，这样即便维度位置更改，也不会影响每个单元格下的计算结果，相当于锁定了表计算的依据和分区。

指定某个维度作为分区字段，也就是仅在指定的维度内运算。

这里理解的关键是：所有勾选的是依据，没有勾选的是分区。

总结：

在数据准备阶段可以使用相对依据和分区，确定之后改为绝对依据和分区，然后调整数据为可视化视图。

 

四、表计算函数和分类

对于初学者而言，直接学习快速表不是捷径，可能会增加迷惑，因为部分快速表计算是聚合计算和表计算的组合，而非完全的表计算函数。

为了理解的方便，为了和和前面的讲解逻辑一致，我们可以把所有的集计算函数，按照是否需要维度指定方向分为：不需要方向维度的表计算和需要方向维度的表计算。

1、需要方向维度的表计算

这一类是典型的表计算，体现了表计算的本质特征——行内计算。典型的代表是快速表计算中的“差异”，背后是lookup的表计算，用来返回与当前聚合行的偏移n行的聚合结果。

LOOKUP(expression, [offset])

从上面的示例可以看出来，offset代表的是和当前聚合行的偏移，-1就是前个数值，0就是当前，1 则是后面的聚合数值，first则返回范围的第一个数值。

快速表计算中的“差异”和“差异百分比”，其实就是lookup表计算函数和聚合函数的组合。

差异 = ZN(SUM([数量])) – LOOKUP(ZN(SUM([数量])), -1)

差异百分比 = (ZN(SUM([数量])) – LOOKUP(ZN(SUM([数量])), -1)) / ABS(LOOKUP(ZN(SUM([数量])), -1))

 

另一个需要指定方向的常见表计算函数就是 RUNNING_SUM(expression)，就是名列快速表计算头把交椅的“汇总”。一般而言，我们都是从前往后看汇总，少数情况下，我们还需要从后往前看汇总，比如在LOD15-15中，计算“至少购买1次、2次……N次的客户数量”，这里的“至少”就需要倒叙的汇总逻辑实现。

这一类的表计算函数的关键是指定表计算的计算依据（方向），同时也就间接地指定了计算的范围——方向的尽头就是范围。方向，是理解此类函数的关键。

 

2、不需要方向维度的表计算函数

还有一些表计算与方向无关，仅需要设置范围，这一类函数的普遍特征是计算某个范围的聚合，多个数值的聚合无关顺序。最典型的代表是 window_sum窗口汇总函数，表计算的很多函数都是这个窗口计算的简化形式——包括汇总、移动平均、移动平均、lookup、previous_value等。后面我们会说一下其中的对等关系。

2.1 window_sum

比如，我们要计算每个类别的利润总额，相当于以类别为范围，返回每一个聚合行的二次聚合。此类某个范围的聚合计算，就使用窗口计算表计算最为合适。

这里window_sum( sum([利润])) 可以轻松返回三个分区的利润聚合。这一类函数的关键是设置范围（类别），但是，正如前面所说，维度的作用是非此即彼，方向的尽头就是边界，Tableau是通过设置计算方向间接地设置计算范围的，所以，即便是此类不需要方向的表计算函数，依然需要通过设置计算依据来确定范围 （设置计算依据：子类别）。——这是理解和设置此类函数的关键要害。

完整的window_XX窗口计算函数还有两个参数来设置范围的开始和结尾，通过这个可以实现更多的效果。下图中，可以返回全部的聚合，也可以范围类似于running_sum的移动聚合，甚至可以返回倒序的running_sum。

我用一个图来代表窗口计算和多个表计算的对应关系，如下：

快速表计算中的“移动平均计算”，就是按照指定的方向，指定行内计算的数量，计算聚合结果。比如股票常用的7天移动平均收盘价，就是每天都去看过去7天的累计平均值，从而减少波动查看长时间的变化趋势。本质是一个指定了窗口大小的window_avg计算.

窗口计算中还有一个比较偏僻的是window_pencitile（AGG, number,first(),last()）,用来返回指定范围的在某个百分位的数值，比如返回整个窗口75%位置的聚合利润是哪一个？

window_pencitile（sum([利润]), 0.75 ,first(),last()）

2.2 Total

如果指定从头到最后为窗口计算聚合，Tableau又为此此指定了一个更简化的表计算：total。可以把total( AGG )视为是window_sum( AGG)的简化形式。

在此基础上，Tableau构建了快速表计算之“总额百分比”，也就是每一个聚合相对于更高层次聚合的汇总的百分比，比如设置每个子类别的利润的“合计百分比”，对应的完整计算是：

合计百分比 = SUM([利润]) / TOTAL(SUM([利润]))

基于相同的设置，它和SUM([利润]) / window_sum(SUM([利润])) 等价。

2.3 Rank排序表计算

还有一个比较特别的表计算，它实在做行内差异计算，但与方向无关，因为它返回的只是在整个范围的先后顺序，就是rank排序。它也被加入了快速表计算之中。

还有一个与此相关的表计算时 百分位排名，即显示在这个范围中的百分比位置——rank_pencentile()。这个也荣誉地加入了快速表计算之列。

3、参数性质的表计算——无需参数

tableau有四个没有参数的表计算，分别是index（），first（），last（） 和size（）。前三者是偏移，第四个类似于计数。它们频繁用于高级的手动表计算之中，是调整范围，转化坐标轴的必备之选。

4、统计类表计算

统计是典型的范围计算，表计算提供了很多统计类表计算函数，主要有以下几类，在此就不多展开：

• –window_CORR  皮尔森相关系数
• –window_VORAR 样本协方差
• –window_VORARP 总体协方差
• –window__STDEV 样本标准差
• –window__STDEVP  总体标准差
• –window__VAR  样本方差
• –window_VARP 总体方差

5、快速表计算

至此，表计算函数介绍完了，其中常见的部分，以及和聚合函数的组合就加入了快速表计算，包括：

汇总（RUNNING_SUM）、差异（LOOKUP）、差异%（LOOKUP）、总和百分比（TOTAL）、排序（RANK）、百分位（RANK_PERCENTILE）、移动平均（WINDOW_AVG）。

另外还有几个日期有关的，同比增长、复合增长率、YTD合计、YTD同比。

• 同比增长，其实就是差异的时间版本；
• 复合增长率，是一种特定的计算方法，公式是 (当前利润/首年利润)^(1/年数) – 1，这里使用了Power幂函数，lookup查找first首年聚合，以及index当前年份数。
  • POWER(ZN(SUM([利润]))/LOOKUP(ZN(SUM([利润])), FIRST()),ZN(1/(INDEX()-1))) – 1
• YTD合计，只有有Year 和Date时才可用，因此需要两个日期维度，背后的实质依然是一个running_sum函数而已，只是设置每年重新开始——即每年的Year到当前的date。
• YTD同比，则是先计算合计，再计算差异百分比，从而查看每年的年度累计，相当于往年年度累计的百分比增长状况。

至此，我们就完整介绍了表计算的几乎所有函数，而快速表计算只是常见的部分组合而已。

​

依据“维度如何起作用”这个角度理解表计算，会比使用LOD的角度理解表计算更方便、更易懂​。从LOD的角度，我们需要谨记，表计算无论如何都无法完成低于视图层面的聚合，而只能完成​视图层面及其更高的聚合任务。

五、表计算疑难杂症

说到这里，表计算的基本只是已经讲完了，基本能涵盖90%以上的应用场景。不过还有几个地方，在某些生僻的地方会遇到，需要说明。

1、所在级别和重启间隔

“编辑表计算”时还有几个特别的选择题，明显用于特别的目的——所在级别用来控制计算依据即方向，重启间隔用来控制范围。为什么会有这样的设计呢？

因为默认的计算依据是指定字段在视图中的所有数据，而不管在当前的度量是否有数据，这样就会出现大量的无效数据。

因此就需要一种方法，既能让计算依据仅仅沿着指定依据内的计算，又不能越过指定的范围，就需要先把分类字段作为依据限定，然后通过“重启间隔”重新指定为范围。

2、排列顺序

排列顺序指定表计算的顺序，其实就是数据字段的顺序。

 

 

七、表计算与狭义LOD表达式

在部分情形下，表计算可以用LOD表达式来等价完成，一般具备以下几个条件：

• 不需要指定方向的计算；
• 计算边界为从始至终，即整个维度指定的范围；

比如指定分区的求和，常见的合计百分比背后的逻辑是指定分区的聚合求和total和视图的二次计算。

 

Oct 6, 2019 草图
Oct 8, 2019 框架更新
Oct 15, 2019
Nov 13, 2019 增加表计算的函数分类
Dec 6, 2019增加第五部分