跳至正文

SQL、Tableau Desktop与Prep表计算深度对比

原文:SQL、Tableau Desktop与Prep表计算深度对比

Tableau Prep2023.2增加了多个表计算函数,至此就有了如下内容:

  • 排名(RANK)
  • 差异(difference from)
  • 差异百分比(percent difference from)
  • 移动平均(moving average)
  • 分片(tile)

本文所要阐述的是,Prep Builder中的表计算函数与Desktop有何不同?从而跳出“表计算必须基于聚合”的长久假设,通往更广阔的计算世界。

01—SQL窗口计算:Tableau表计算的参照点

简单的说,Tableau的核心技术VizQL,是把用户的交互转化为数据库的SQL查询。

因此,抛去Tableau可视化交互的“外壳”,Tableau背后依然是SQL查询与计算。从SQL窗口函数出发,是深度理解Tableau表计算及工具之间差别的关键。首先,SQL窗口计算window calculation,和Tableau 表计算table calculation,背后都是“指定范围的、跨行偏移计算”,window和table都是“范围”的同义词。

二者的难点都在于如何控制范围,如何指定偏移的方向。

window_function (expression) OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ]

1)举例而言,“各个区域、不同年度的销售额总和及其同比差异”。SQL中,可以使用LAG窗口函数,获得“前一个”聚合值。注意,在下面的查询中,喜乐君只为LAG指定了partition by分区,即“每个区域中”,而没有指定计算依据。SQL自动把日期视为依据寻找“前一个”(previous value),只是默认次序有所错位而已。

在SQL窗口函数中,如果over后面是空,那么视图中所有字段都会被视为计算依据。可见,partiton by是必须明确指定的,未指定的字段都会作为依据出现。上述题目的完整SQL查询应该是(基于Postgresql):

-- 增加窗口函数,返回每个区域中,前一个年度的聚合值 SELECT "Region" ,   date_part('year', "Order Date") as year,  sum("Sales"), LAG(sum("Sales")) OVER (partition by "Region" order by date_part('year', "Order Date") asc) FROM public.sales GROUP BY "Region",year order by "Region",date_part('year', "Order Date") asc -- 最后一行可以省略

2)这里有一个问题:SQL窗口函数,能否是基于原始表的明细行,而非聚合表的明细行呢?即LAG(sum(“Sales”))能否直接改为LAG(“Sales”) ?

LAG(sum(“Sales”))依赖于聚合查询结果(聚合表),而LAG(“Sales”) 依赖于底表明细行,表计算都在是SELECT之后计算的,所以两个难以同时存在于一个查询中(至少喜乐君还没有想到)。

如下所示,可以基于SLECT投影查询的明细行,使用LAG(“Sales”) 对投影的明细行做偏移计算。不指定over之后的分区情况下,所有字段都是计算的依据。

由此可见,SQL的窗口函数,既可以在GROUP BY分组聚合表基础上使用,也可以在SELECT投影明细表基础上使用。二者只是窗口函数的计算起点不同,窗口函数的计算逻辑则并无二致。

02—Desktop与Prep表计算:表计算的本质及其分类

Desktop和Prep表计算的共同之处,就是表计算的本质之所在,无关工具、无关位置,核心精神与SQL都是相通的。那就是:所有的表计算,都是指定数据范围中、明细行的、跨行偏移计算。

不管是Tableau表计算,还是SQL窗口函数,典型用例都是偏移查找、排序,它们既要考虑计算的范围边界,又要考虑计算的依据。非典型案例则是“合计”,合计仅关注计算范围,无关内部计算依据先后。

1)偏移查找:典型应用场景是同比、环比计算

正如前面SQL中所述案例一样。同环比的典型特征是:可比的字段作为依据(order by)、不可比字段作为分区(partition by),共同构成了偏移计算的约束条件。如下所示:

LAG(sum("Sales")) OVER ( partition by "Region"  order by date_part('year', "Order Date") asc)

Tableau中,同环比可以使用LOOKUP函数查找上一个值完成比较,对应SQL中的LAG/LEAD函数。

2)排序:典型应用场景是排序RANK函数和row- number索引

相比同环比的偏移,排序必须在指定范围中完成多次循环比较,所以它的工作量更多、性能更慢。

排序并非必须基于聚合值才行。在客户留存分析中,绝对日期到相对日期序列的转换也可以视为排序的特殊类型——对自身具有连续性数据值的编码。因此,在Tableau中,基于数据值的排序使用RANK函数,基于分类字段的自然编号使用INDEX函数——后者类似于SQL的Row_number函数。

3)合计:特殊的表计算,兼具表计算和聚合计算双重特征

它的特殊性在于既具有窗口计算的部分特征(指定范围),又具有聚合的特征(由多变少)。但又不像其他表计算具有完整表计算特征(表计算内部无关次序),又不像SUM聚合函数必须基于明细行开始。

在Tableau中,使用表计算完成二次合计的函数有WINDOW_SUM函数和TOTAL函数,二者在比值、不重复计数等特殊场景下有非常明显的不同。(这个内容展开颇为宏大,后续会在《数据可视化分析2.0》一书第9章表计算展开介绍)当然,SQL的SUM既可以作为直接聚合使用,又可以作为窗口函数使用,这就增加了业务用户区分不同形式聚合的负担。

好在Tableau中做了明确区分:只有从明细行出发的直接聚合,才可以使用SUM聚合;从聚合表出发的间接聚合,要使用WINDOW_SUM窗口聚合函数。

03—Desktop与Prep表计算不同之处

二者的不同,体现了Prep和desktop设计上的差异。

1、表计算相对的位置

在Prep中,表计算是基于明细行的,所以选择时无需选择聚合值;而在Desktop中,表计算必须基于聚合值方可完成。 

1.1 Prep的表计算:在明细行中的窗口计算,无关聚合

如下所示,当我们在prep新增排序或者其他计算时,所选数值字段无需聚合,在左侧自动生成的计算中也没有聚合函数。这个过程,类似于SQL中针对明细数据值的窗口函数:RANK(sales) over () 。

即便在Prep前面的节点中包含了聚合,在当前节点中,每个预先被聚合的值依然会视为当前工作表的明细行——可以视为前面的聚合表依然被物化为了全新的明细表,所以后面的过程无关聚合。

1.2 Desktop表计算:在逻辑聚合表上的表计算,基于聚合

在Desktop中,表计算必须基于聚合字段,否则都无法添加快速表计算。

这个过程,对应SQL中最常见的窗口函数——窗口函数,是对聚合后逻辑聚合表的二次计算。 如果用交叉表的方式查看desktop的窗口函数,就更容易和SQL中的对比。

2、表计算的指定方式

窗口计算/表计算的关键是如何指定计算的计算范围和依据。从这个角度看,prep和SQL一脉相承。

2.1 Prep表计算 vs SQL

在SQL中,窗口计算的范围和依据都必须指定到字段。SQL既可以是明确地引用字段从而确定窗口计算的范围和依据(explicitly),也可以使用“非此即彼”相对指定的方式(implicitly)——比如通过 partition by “Region”间接实现日期为计算依据。从这个角度看,Prep的表计算指定方式,类似于SQ L的方式——指定到字段名称。

当然,即便如此,Prep和SQL依然有所差别:Prep是必须指定依据(orderby),而SQL则是必须(如有)指定分区(partition by)。如下图所示,Prep中 Order by的部分是必须的(required),没有指定的字段默认构成分区。

但在SQL中正好相反,如果字段没有指定角色,那么就会作为依据——不管是否存在order by。这是和SQL明显不同的地方。

2.2 Desktop表计算与SQL

由于没有可视化UI的存在 ,不管是明确指定,还是间接指定,SQL窗口计算终归是绝对指定到字段名称(absolutely)。而在Desktop中,除了指定到字段名称,还出现了相对于可视化的“相对指定方式”(relatively),比如表、区、单元格,横穿、向下的方式。

这是可视化工具相比代码工具的升级之处,也是Desktop相比Prep的优势所在。

04—“延伸·到2024年”

【延伸】想要进一步深刻地理解表计算,及工具之间的差异,我们不禁要问:

  • 为什么Tableau Desktop要表表计算限制在“聚合字段”之上完成,不能像SQL既……又……?
  • Prep表计算和Desktop表计算之间,或者是明细表表计算与聚合表表计算之间,体现了怎么样的分析关系?
  • 如果表计算的这个角度理解计算的分类,是否可以抽象出计算的两大阶段?
  • 按照这个逻辑往下,是否可以把DAX中计算列、度量值,行上下文、筛选上下文,在Tableau的体系中找到对应?
  • 是否可以从这个角度理解Tableau和DAX/Power BI之间的逻辑差异,从而更好地理解二者所面向的使用人群?
  • 那些试图突围的国产BI,在计算体系方面,存在怎样的困境?

所有这些话题,都是沿着计算的展开,喜乐君将构建整个计算的宏大体系,既能高屋建瓴地审视Tableau等BI的计算体系,又能跳出工具理解分析背后的广阔分析世界。

喜乐君 
Jun 19, 2023 V1.1
Jun 20, 2023 V1.2