【表计算】TOTAL合计与WINDOW_SUM汇总的区别

编者按:在第一本出版时,笔者还没有完全理解total与window_sum的差异,时隔两年重写本书,完全重写了计算的内容。特别结合案例讲解total合计与window_sum汇总的差异。

by 喜乐君 May 8, 2022

May 9, 2022 修改补充,增加最后一段总结

——《数据可视化分析:Tableau原理与实践》(第二版)

9.7.1 最简单的参考线——表计算的“可视化形式”

参考线是可视化分析中重要组成部分,在很多分析中具有画龙点睛的作用。如图9-60所示,在“各个季度的利润趋势”中,从左侧“分析”窗格拖入“平均线”到视图中,可以一目了然地获得所有季度的利润均值;或者在“各个区域的利润”柱状图中,可以快速所有区域的利润均值,从而更好地横向对比。

图9‑60 拖曳参考线,增加表范围的平均值

注意,不管是折线图,还是柱状图,对于每个维度值而言,参考线的纸都是唯一的。也就是说,参考线对应的聚合值,相比视图维度的聚合值,是更高聚合度的详细级别。更具体的说:

  • 折线图中的平均值参考线:所有季度(计算依据)的利润总和的平均值
  • 柱状图中的平均值参考线:所有区域(计算依据)的利润总和的平均值

上述的参考线,都可以视为是如下表计算函数的“化身”:

WINDOW_AVG(SUM(利润)) 其中,维度字段都是计算依据,即整个表是范围

如果用SQL的逻辑来理解,就是如下的语法,这里的平均无关方向,以整个表为范围:

AVG(SUM(利润))OVER ()

因此,这里可以获得一个关键的结论:(这里的)参考线是表计算的“可视化形式”

在一些复杂的业务分析中,经常需要在参考线对应的聚合之上做进一步计算,比如聚合值大于平均值的标记为单独的颜色,此时就是上述自定义表计算与算术计算的结合。如图9-61所示。

SUM(利润)  >  WINDOW_AVG(SUM(利润))

图9‑61  使用自定义计算代替表计算,而后进一步计算

在后续的案例中,很多的参考线字段都可以转化为表计算,而基于自定义表计算与行级别计算、聚合计算的结合,可以进一步创建更复杂的计算,从而满足更复杂的分析需求。

特别提醒的是,很多Tableau用户(特别是高级用户)不习惯这个方式,而用FIXED表达式完成上述的过程,看似可行,但从计算性能、理解的复杂度、易用性角度,这都是“自负的陷阱”。

{ FIXED  :AVG({ FIXED  [区域]:  SUM([利润])})}

9.7.2   “合计利润率”:理解参考线对应的表计算

计算的复杂性主要来自于维度的复杂性;随着维度的增加,表计算必须考虑每个维度参与表计算的角色——既要作为直接聚合的依据,还要作为表计算的范围或者方向。这里介绍一个非常典型的可视化案例:各个类别、各个子类别的利润和利润率。

基础图形就是典型的条形图样式,如9-62中的主视图所示(暂时忽略参考线)。

图9‑62  各个类别、各子类别的利润与利润率

在上图中,从左侧“分析”窗格中,拖入“平均线”到视图中,置于“区”的位置,从而快速添加两个参考线。借助于参考线,不仅希望对比每个类别的均值(参考线的左右位置),而且可以对比子类别相比所属类别均值的差异(条形图和参考线比较)。

按照9.7.1小节的解释,参考线是自定义表计算的“可视化形式”,这里的两个参考线计算是:

  • WINDOW_AVG(SUM(利润))  其中,计算依据是子类别,计算范围是类别
  • WINDOW_AVG(SUM(利润率)) 其中,计算依据是子类别,计算范围是类别

这里的重点和难点是,利润率的平均值是否是分析所需要的“各个类别的利润率”?

由于利润率是利润总和与销售额总和的比值,上述的表计算可以进一步展开,即:

WINDOW_AVG(SUM(利润) / SUM(销售额)),其中,计算依据是子类别,计算范围是类别

思考一下:每个子类别的利润率是有分析意义的,多个子类别的利润率的算术平均值,是否同样有意义?

这个问题是第8章开篇的核心问题:每个计算都是相对于特定详细级别的计算,行级别的利润率计算([利润] / [销售额])没有意义,相对于视图详细级别的聚合计算(SUM(利润) / SUM(销售额))才有意义,与此同时,多个有意义的聚合计算的算术平均值(WINDOW_AVG(SUM(利润)/SUM(销售额))),也是没有意义的。这就是图9-62中,右侧“利润率”平均线的错误之所在。

在业务分析中,笔者不建议使用“平均利润率”这种具有歧义的用于,因为利润率的平均是没有分析意义的;有意义的是构成利润率的分子、分母分别合计之后的比值,笔者称之为“合计利润率”(total profit ratio。对应的表达式如下所示:

WINDOW_SUM(SUM(利润) ) / WINDOW_SUM(SUM(销售额))

从错误的“算术平均值”到正确的“合计比值”的转化过程,可以通过点击的参考线编辑实现。如图9-63所示,点击参考线,在弹出的界面中将默认的“平均值”改为“合计”。

图9‑63  各个类别、子类别的利润率(从左侧算术平均到右侧合计比值)

在参考线的设置中,每一种聚合方式,都对应窗口计算的聚合方式,要特别注意,这里的窗口聚合和直接聚合并不相同。图中右键的合计、总和、最小值、最大值、平均值和中位数,分别对应:

  • (窗口计算)合计:TOTAL(SUM(利润) / SUM(销售额))
  • (窗口计算)总和:WINDOW_SUM(SUM(利润) / SUM(销售额))
  • (窗口计算)最小值:WINDOW_MIN(SUM(利润) / SUM(销售额))
  • (窗口计算)最大值:WINDOW_MAX(SUM(利润) / SUM(销售额))
  • (窗口计算)中位数:WINDOW_MEDIAN(SUM(利润) / SUM(销售额))

在不同的场景下,要合理地选择符合业务逻辑、有分析价值的聚合方式。对于利润率而言,这里的合计才是有意义的。

9.7.3 理解TOTAL合计与WINDOW_SUM汇总的差异

在理解了正确的“合计利润率”和错误的“利润率平均”的之后,笔者重点介绍一下背后两个函数的差异。为了增强辨识度,笔者把TOTAL称之为“合计”,而把WINDOW_SUM称之为“汇总“,二者的差异体现在处理比值和重复数据时,采用了不同的计算逻辑。

先说比值的计算。

在上述的“合计利润率”中,基于WINDOW_SUM的表达式可以进一步简化为TOTAL计算,因此以下两个表达式是等价的:

  • WINDOW_SUM(SUM(利润) ) / WINDOW_SUM(SUM(销售额))
  • TOTAL(SUM(利润) / SUM(销售额))

也就是说,如果TOTAL合计中的聚合值是聚合的比值,对应的计算逻辑是分子和分母分别聚合,这一点在使用时要特别注意。

另一个关键区别是合计会自动去重。

在对客户ID、订单ID等需要离散维度字段做合计时,由于普遍存在跨类别的情形,因此合计和汇总也有明显的差异。如图9-64所示,展示了“各个类别、子类别的订单数量及小计”,同时增加了COUNTD([订单ID])字段的合计和汇总,这里合计明显小于汇总。

图9‑64  各个类别、子类别的订单数及合计、汇总

为什么会有这样的差异?这是不同函数对重复数据的处理逻辑不同。

一个订单ID普遍存在同时对应多个子类别甚至类别的情形,所以在“各类别、子类别的订单数量”中,会存在大量订单ID的重复。每个类别的合计订单数量,会对订单ID做去重处理,而WINDOW_SUM则仅仅是对“每个子类别的订单数量”的算术累加,而不会关心是否重复。

总结一下,TOTAL合计和WINDOW_SUM汇总在如下的情形中,会有明显的不同:

  1. 被聚合的聚合度量,来自维度字段的不重复计数,如订单ID、产品ID等
  2. 被聚合的度量度量,是多个聚合值的比值,比如利润率、折扣率、毛利率等
高级用户而言,可以尝试理解如下的内容。

如果要进一步理解TOTAL与WINDOW_SUM的差异,可以从“详细级别”(层次分析)的角度,理解两种计算在本质上的差异,统一回答上述的问题。简而言之:

TOTAL合计(包括总计Grand Total和小计SubTotal)是相比视图详细级别更高聚合度的级别完成聚合,它会忽略当前视图详细记,不受其影响。因此,TOTAL 是指定详细级别的聚合(aggregate at fixed level of detail),相当于FIXED LOD表达式(暂不考虑筛选器的影响,详见第10章)

WINDOW_SUM汇总(或者是WINDOW_XXX的其他形式)是对视图详细级别的直接聚合(如COUNTD([订单ID]))的二次聚合,一直加到计算依据的尽头,即范围字段为止,而不考虑累加是否有意义、被累加的聚合是否在明细上有重复。WINDOW_SUM二次聚合,和SUM直接聚合,是完全独立的两个环节。因此,WINDOW_SUM汇总是典型的聚合的二次聚合(secondary aggregate of  direct aggregate,相当于嵌套的LOD表达式({FIXED 类别: SUM({FIXED 类别,子类别 : SUM([利润])} )},暂不考虑筛选的影响 )。

发布者:喜乐君

喜乐君 | Tableau Partner,Tableau Desktop and Server QA Certification

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

您正在使用您的 WordPress.com 账号评论。 注销 /  更改 )

Twitter picture

您正在使用您的 Twitter 账号评论。 注销 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 注销 /  更改 )

Connecting to %s

%d 博主赞过: