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

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

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

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

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

截屏2019-10-10上午8.09.57

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

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

截屏2019-10-10上午8.11.07

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

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

Table Calculation—— computation on values in a table

总结三句话:

  • 表计算是做行内计算;
  • 表计算只针对单一度量值计算。
  • 维度参与计算过程!(要害)

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

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

截屏2019-10-10上午8.16.33.png

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

所以,使用表计算的关键是:根据需要设置你的计算依据和边界(分区),这一切的开关是:维度。

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

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

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

截屏2019-10-15下午7.11.34

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

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

3、单一方向字段

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

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

4、多个方向字段的情形

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

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

2 表计算方向 5

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

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

但是不推荐使用。

 

三、相对和绝对设置

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

1、相对direction and scope

table ——pane ——cell

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

截屏2019-10-19上午9.06.54

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

2、绝对 addressing and  partition

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

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

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

table_calculations11.png

总结:

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

 

四、表计算函数和分类

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

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

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

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

LOOKUP(expression, [offset])

截屏2019-11-12下午9.01.20从上面的示例可以看出来,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次的客户数量”,这里的“至少”就需要倒叙的汇总逻辑实现。

截屏2019-11-12下午9.09.31.png

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

 

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

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

2.1 window_sum

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

截屏2019-11-12下午9.19.33

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

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

截屏2019-11-12下午9.27.18

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

window 窗口计算的对等关系

快速表计算中的“移动平均计算”,就是按照指定的方向,指定行内计算的数量,计算聚合结果。比如股票常用的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()。这个也荣誉地加入了快速表计算之列。

截屏2019-11-12下午9.47.13

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

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

截屏2019-11-12下午9.54.37.png

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同比,则是先计算合计,再计算差异百分比,从而查看每年的年度累计,相当于往年年度累计的百分比增长状况。

截屏2019-11-13上午12.51.21.png

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

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

 

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

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

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

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

 

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