近期，Tableau Prep2023.2增加了多个表计算函数，至此就有了如下内容：
- 排名（RANK）
- 差异（difference from）
- 差异百分比（percent difference from）
- 移动平均（moving average）
- 分片（tile）
本文所要阐述的是，Prep Builder中的表计算函数与Desktop有何不同？从而跳出“表计算必须基于聚合”的长久假设，通往更广阔的计算世界。
一、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投影明细表基础上使用。二者只是窗口函数的计算起点不同，窗口函数的计算逻辑则并无二致。
二、Desktop与Prep表计算：表计算的本质及其分类
Desktop和Prep表计算的共同之处，就是表计算的本质之所在，无关工具、无关位置，核心精神与SQL都是相通的。那就是：所有的表计算，都是指定数据范围中、明细行的、跨行偏移计算。
1、Tableau表计算的基本分类（简）
不管是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窗口聚合函数。
三、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的优势所在。
想要进一步深刻地理解表计算，及工具之间的差异，我们不禁要问：
- 为什么Tableau Desktop要表表计算限制在“聚合字段”之上完成，不能像SQL既……又……？
- Prep表计算和Desktop表计算之间，或者是明细表表计算与聚合表表计算之间，体现了怎么样的分析关系？
- 如果表计算的这个角度理解计算的分类，是否可以抽象出计算的两大阶段？
- 按照这个逻辑往下，是否可以把DAX中计算列、度量值，行上下文、筛选上下文，在Tableau的体系中找到对应？
- 是否可以从这个角度理解Tableau和DA X/Power BI之间的逻辑差异，从而更好地理解二者所面向的使用人群？
所有这些话题，都是沿着计算的展开，喜乐君将构建整个整个计算的宏大体系，既能高屋建瓴地审视Tableau等BI的计算体系，又能跳出工具理解分析背后的广阔分析世界。
这也是2024年新书《业务分析通识》的重要内容 ，敬请期待。
四、计算的两大阶段与不同工具的路径选择
1、计算的两大阶段
2、Tableau的路径选择
3、Power BI的路径选择
4、国产BI产品的困境
喜乐君 Jun 19, 2023