tableau的高级计算

Tableau的高级计算是通过计算字段来完成的。

计算字段的本质是通过表达式，基于数据源中已有的数据创建数据源中不存在的数据。

计算字段分类

计算字段分为以下几类：

• Basic calculations：数据源级别((a row-level calculation))或者可视化级别(an aggregate calculation)
• Level of Detail (LOD) expressions ： 可以更加精细和自由地控制聚合的粒度。LOD calculations give you even more control on the level of granularity you want to compute. They can be performed at a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED) with respect to the granularity of the visualization.
• Table calculations：在图表上进行计算。

计算字段的表达式，可以引用已经存在的计算表达式，构成复杂的计算。

另外，Tableau的计算表达式中使用的函数是Tableau内置的，与数据源无关。在生成sql的时候，Tableau会把tab 函数翻译成对应数据源的函数。

比如:

ZN([price])-ZN([shipping_cost])

在mysql数据源中将翻译成：

IFNULL(company_sales_record.price, 0) - IFNULL(company_sales_record.shipping_cost, 0)

基础计算字段

基础计算字段分为聚合型和非聚合型，非聚合型计算字段就是在数据源表的每一行内的不同字段之间做计算，计算不能带聚合函数。聚合型计算字段带聚合函数，以图表中的维度作为分组依据。

LOD

LOD的语法是包含在大括号里的表达式，分成维度组，聚合表达式两部分，用冒号分割。其中维度组有个粒度控制符关键字，标明表达式的维度组合与图表中维度的关系。lod的语法定义如下：

{[FIXED | INCLUDE | EXCLUDE] <维度声明> : <聚合表达式>}

粒度控制关键字

FIXED

FIXED 详细级别表达式使用指定的维度做聚合计算，而不引用图表中的任何其他维度。它还会忽略除上下文筛选器、数据源筛选器和数据提取筛选器之外的所有筛选器。

在需要固定值、与图表无关的聚合值时（因为聚合与图表无关，因此可以当成维度使用)，fixed非常有用。

比如：

{ FIXED [Region] : SUM([Sales]) }

它会仅以Region作为分组维度做聚合。

INCLUDE

INCLUDE除了图表中的维度之外，还会将表达式中维度一起作为分组的一句。当需要纳入不在图表中的维度做聚合时，INCLUDE 详细级别表达式最有用。使用include可以让度量做二次聚合。

比如

{ INCLUDE [Customer Name] : SUM([Sales]) }

它总是会将Customer Name和图表中的其他维度放到一起作为分组依据，使聚合的粒度比图表更细。

EXCLUDE

EXCLUDE 详细级别表达式从表达式中显式移除维度，也就是说，这些表达式从图表详细级别中去除维度，在比图表中更粗粒度完成聚合。在需要总计、小计值时，exclude非常有用。

比如：

{EXCLUDE [Region]: SUM([Sales])}

它将Region排除，不作为聚合的分组依据。

全表范围

全表范围对于表范围详细级别表达式，不需要定界关键字，表示对整整表全局做聚合，代表“总计”的概念。

和过滤器的关系

过滤器分为以下几类

Extract Filters：数据源级别的过滤器，是最高优先级的，可以看成是ETL提取。

Data Source Filters :数据源级别的过滤器，直接作用在数据源上。

Context Filters：上下文过滤器，可以理解为数据集级别过滤器。

Dimension Filters:维度过滤器，相当于查询sql里的where

Measure Filters:度量过滤器，相当于查询sql里的having

Table CalcPlan Filters：图表过滤器，只影响图表的展示，不影响数据处理。

lod受过滤的影响如下：

可以看到，不会忽略的过滤器有context filters, data source filters, and extract filters，这些过滤器一定会影响LOD的计算

参考：

https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_overview.htm#filters-and-level-of-detail-expressions

https://help.tableau.com/current/pro/desktop/en-us/order_of_operations.htm

表达式结果类型

Fixed 的lod可以是度量也可以是维度（取决于所计算的字段），include和exclude的lod只能是度量

和图表聚合的关系

比图表维度更粗粒度的聚合：重复。这种情况下，图表指标上的聚合方式是无效的。
比图表维度更细粒度的聚合：再聚合。这种情况下，图表上的聚合方式会嵌套在lod的聚合之上。
和图表维度同粒度的聚合：什么都不做。这种情况下，聚合已经在写lod的时候写了额，因此图表指标上的聚合方式也是无效的。

• INCLUDE level of detail expressions will have either the same level of detail as the view or a finer level of detail than the view. Therefore, values will never be replicated.
• FIXED level of detail expressions can have a finer level of detail than the view, a coarser level of detail, or the same level of detail. The need to aggregate the results of a FIXED level of detail depends on what dimensions are in the view.
• EXCLUDE level of detail expressions always cause replicated values to appear in the view. When calculations including EXCLUDE level of detail expressions are placed on a shelf, Tableau defaults to the ATTR aggregation (as opposed to SUM or AVG) to indicate that the expression is not actually being aggregated and that changing the aggregation will have no effect on the view.

在写lod的时候，也可以显式的在外面套上聚合函数，这时候拖入图表中，如果需要聚合的话，聚合方式就是你显式写的那个，不需要聚合的话，这个聚合会被忽略掉。

LOD总结

LOD的本质是实现数据的多层处理，外层的处理是在里层的结果之上进行的，或者说外出处理的对象是里层处理的结果，里层处理结果是中间结果。因此，LOD是具备一定的ETL能力的。

表计算

表计算只能对图表内的数据进行计算，不考虑图表以外的任何数据。从下图的执行优先级也可以看出，表计算是排在measure Filter之后执行的。

重要概念

分区：用于确定计算的范围，计算只在分区内进行，不同分区的计算是单独进行的。
寻址：决定了计算的方向。 如果有多个维度，则是根据维度的顺序，先穿越后面的维度，再穿越前面的维度。
在表计算面板中，选中的所有维度都是寻址维度，剩下的是分区维度。

相对于：在对比型的表计算中，还有一个重要的概念叫“相对于”，由它在寻址方向上确定对比数据的具体位置。通常有：前一个、后一个、第一个、最后一个。

表计算的类型

差值：计算差值对比，属于对比型计算

差值百分比：计算差值的百分比，属于对比型计算

百分位比：计算当前数据与对比数据的比值，属于对比型计算

占比：计算当前数据与小计数据的比值。

排名：计算组内排名，属于值转化型计算

百分位数：将数值规划到0~100%之间，属于值转化型计算

累计计算：滑动求组内累计值

滑动计算：窗口滑动计算窗口内的值

注意，表计算也是可以嵌套的，通过添加二次计算实现，比如计算累计值的年对比。

参考：https://help.tableau.com/current/pro/desktop/en-us/calculations_tablecalculations_definebasic_runningtotal.htm#percent-of-total-calculation

SELECT
	(
		(
			TO_DAYS(
				ADDDATE(
					DATE_FORMAT( `company_sales_record`.`report_date`, '%Y-%m-%d 00:00:00' ),
					INTERVAL 0 SECOND 
				) 
				) - TO_DAYS(
				ADDDATE(
					DATE_FORMAT( `t0`.`__measure__0`, '%Y-%m-%d 00:00:00' ),
					INTERVAL 0 SECOND 
				) 
			) 
			) + (
			TIME_TO_SEC(
				ADDDATE(
					ADDDATE(
						DATE_FORMAT( `company_sales_record`.`report_date`, '%Y-%m-%d 00:00:00' ),
						INTERVAL 0 SECOND 
					),
					INTERVAL 0 SECOND 
				) 
				) - TIME_TO_SEC(
				ADDDATE(
					ADDDATE(
						DATE_FORMAT( `t0`.`__measure__0`, '%Y-%m-%d 00:00:00' ),
						INTERVAL 0 SECOND 
					),
					INTERVAL 0 SECOND 
				) 
			) 
		) / ( 60 * 60 * 24 ) 
	) AS `Calculation_505036481761304585`,
	`company_sales_record`.`customer_name` AS `customer_name` 
FROM
	`company_sales_record`
	INNER JOIN (
	SELECT
		`company_sales_record`.`customer_name` AS `customer_name`,
		MIN( `company_sales_record`.`report_date` ) AS `__measure__0` 
	FROM
		`company_sales_record` 
	GROUP BY
		1 
	) `t0` ON ( `company_sales_record`.`customer_name` <=> `t0`.`customer_name` ) 
GROUP BY
	1,
	2

不同类型计算字段的使用场景

1.图表里是否已经有需要的数据，如果有，就用表计算，否则用普通计算字段或者LOD。

2.需要的数据的是否是非聚合，还是聚合粒度恰好和图表里的聚合粒度一致？

参考：

https://www.tableau.com/about/blog/2016/4/guide-choosing-right-calculation-your-question-53667?_ga=2.179589668.1614904189.1628168106-580442927.1628168103

其他

Tableau没有办法直接处理时间上的计算比如同环比等等，只能依赖表计算，参考：http://www.raiahmad.com/time-intelligence-functions-in-tableau/

附录

度量转维度示例

求销售额的年累计Days Since First Day 默认是一个度量，但是当把它拖入图表是，可以把它转成维度使用。如果我们把它转成维度，统计不同间隔天数的购买行为的平均销售额。

生成的SQL如下：

SELECT
	(
		(
			TO_DAYS(
				ADDDATE(
					DATE_FORMAT( `company_sales_record`.`report_date`, '%Y-%m-%d 00:00:00' ),
					INTERVAL 0 SECOND 
				) 
				) - TO_DAYS(
				ADDDATE(
					DATE_FORMAT( `t0`.`__measure__0`, '%Y-%m-%d 00:00:00' ),
					INTERVAL 0 SECOND 
				) 
			) 
			) + (
			TIME_TO_SEC(
				ADDDATE(
					ADDDATE(
						DATE_FORMAT( `company_sales_record`.`report_date`, '%Y-%m-%d 00:00:00' ),
						INTERVAL 0 SECOND 
					),
					INTERVAL 0 SECOND 
				) 
				) - TIME_TO_SEC(
				ADDDATE(
					ADDDATE(
						DATE_FORMAT( `t0`.`__measure__0`, '%Y-%m-%d 00:00:00' ),
						INTERVAL 0 SECOND 
					),
					INTERVAL 0 SECOND 
				) 
			) 
		) / ( 60 * 60 * 24 ) 
	) AS `Calculation_505036481761304585`,
	AVG( `company_sales_record`.`price` ) AS `avg_price_ok` 
FROM
	`company_sales_record`
	INNER JOIN (
	SELECT
		`company_sales_record`.`customer_name` AS `customer_name`,
		MIN( `company_sales_record`.`report_date` ) AS `__measure__0` 
	FROM
		`company_sales_record` 
	GROUP BY
		1 
	) `t0` ON ( `company_sales_record`.`customer_name` <=> `t0`.`customer_name` ) 
GROUP BY
	1

如果加上时间维度作为颜色，还能对不不同时间段内的销售数据。

聚合的聚合

假设要计算每个用户的销售额，并且兼顾图表上的维度，创建计算字段SalesPerCustomer

{INCLUDE [customer_name]:SUM([price])}

在图表上使用这个计算字段，选择avg聚合，并且拖入area维度。此时计算的结果是：在各区域内人均消费额。

它涉及到两层聚合，第一层是在[area],[customer_name]上对price做sum聚合，第二层是在[area]上对第一层的结果做avg聚合。

它生产的SQL如下

SELECT
	`t0`.`area` AS `area`,
	AVG( `t0`.`__measure__1` ) AS `avg_Calculation_715016814507118592_ok` 
FROM
	(
	SELECT
		`company_sales_record`.`area` AS `area`,
		SUM( `company_sales_record`.`price` ) AS `__measure__1` 
	FROM
		`company_sales_record` 
	GROUP BY
		1,
		`company_sales_record`.`customer_name` 
	) `t0` 
GROUP BY
	1

非聚合计算字段嵌套LOD示例

要求：用户每次购买时间相对于第一次购买时间的间隔天数

先创建LOD计算字段 First Purchase Date

{FIXED [Customer Name] : MIN([report_dateOrder Date])}

再创建非聚合计算字段Days Since First Day，它引用了前一个计算字段First Purchase Date

DATETRUNC('day', [report_date])-DATETRUNC('day', [First Purchase Date])

对Days Since First Day执行明细查询，生成的SQL如下

SELECT
	(
		(
			TO_DAYS(
				ADDDATE(
					DATE_FORMAT( `company_sales_record`.`report_date`, '%Y-%m-%d 00:00:00' ),
					INTERVAL 0 SECOND 
				) 
				) - TO_DAYS(
				ADDDATE(
					DATE_FORMAT( `t0`.`__measure__0`, '%Y-%m-%d 00:00:00' ),
					INTERVAL 0 SECOND 
				) 
			) 
			) + (
			TIME_TO_SEC(
				ADDDATE(
					ADDDATE(
						DATE_FORMAT( `company_sales_record`.`report_date`, '%Y-%m-%d 00:00:00' ),
						INTERVAL 0 SECOND 
					),
					INTERVAL 0 SECOND 
				) 
				) - TIME_TO_SEC(
				ADDDATE(
					ADDDATE(
						DATE_FORMAT( `t0`.`__measure__0`, '%Y-%m-%d 00:00:00' ),
						INTERVAL 0 SECOND 
					),
					INTERVAL 0 SECOND 
				) 
			) 
		) / ( 60 * 60 * 24 ) 
	) AS `Calculation_505036481761304585`,
	`company_sales_record`.`customer_name` AS `customer_name` 
FROM
	`company_sales_record`
	INNER JOIN (
	SELECT
		`company_sales_record`.`customer_name` AS `customer_name`,
		MIN( `company_sales_record`.`report_date` ) AS `__measure__0` 
	FROM
		`company_sales_record` 
	GROUP BY
		1 
	) `t0` ON ( `company_sales_record`.`customer_name` <=> `t0`.`customer_name` ) 
GROUP BY
	1,
	2

对Days Since First Day求平均值，生成的SQL如下

SELECT
	AVG(
		(
			(
				TO_DAYS(
					ADDDATE(
						DATE_FORMAT( `company_sales_record`.`report_date`, '%Y-%m-%d 00:00:00' ),
						INTERVAL 0 SECOND 
					) 
					) - TO_DAYS(
					ADDDATE(
						DATE_FORMAT( `t0`.`__measure__0`, '%Y-%m-%d 00:00:00' ),
						INTERVAL 0 SECOND 
					) 
				) 
				) + (
				TIME_TO_SEC(
					ADDDATE(
						ADDDATE(
							DATE_FORMAT( `company_sales_record`.`report_date`, '%Y-%m-%d 00:00:00' ),
							INTERVAL 0 SECOND 
						),
						INTERVAL 0 SECOND 
					) 
					) - TIME_TO_SEC(
					ADDDATE(
						ADDDATE(
							DATE_FORMAT( `t0`.`__measure__0`, '%Y-%m-%d 00:00:00' ),
							INTERVAL 0 SECOND 
						),
						INTERVAL 0 SECOND 
					) 
				) 
			) / ( 60 * 60 * 24 ) 
		) 
	) AS `avg_Calculation_505036481761304585_ok`,
	`company_sales_record`.`customer_name` AS `customer_name` 
FROM
	`company_sales_record`
	INNER JOIN (
	SELECT
		`company_sales_record`.`customer_name` AS `customer_name`,
		MIN( `company_sales_record`.`report_date` ) AS `__measure__0` 
	FROM
		`company_sales_record` 
	GROUP BY
		1 
	) `t0` ON ( `company_sales_record`.`customer_name` <=> `t0`.`customer_name` ) 
GROUP BY
	2

LOD相互嵌套实例

现在要求这样一个问题，每种商品类型的销售额与平均商品类型销售额的差值。

首先我们要求平均商品类型销售额，它计算的对象是各商品类型的总额的平均值，数学公式是 （类型1总额+类型2总额+...类型n总额）/商品类型数。我们可以创建一个average sales total per product type计算字段表示它。

{ EXCLUDE [product_type] : AVG({FIXED [product_type]:sum([price])})}

然后在行上使用临时计算字段作为度量

SUM([price])-SUM([average sales total per product type])

列上拖入product_type维度

生成的SQL如下：

SELECT
	`t0`.`TEMP(Calculation_715016814539907077)(133835821)(0)` AS `TEMP(Calculation_715016814539907077)(133835821)(0)`,
	`t2`.`__measure__0` AS `TEMP(Calculation_715016814539907077)(919840309)(0)`,
	`t0`.`product_type` AS `product_type` 
FROM
	(
	SELECT
		`company_sales_record`.`product_type` AS `product_type`,
		SUM( `company_sales_record`.`price` ) AS `TEMP(Calculation_715016814539907077)(133835821)(0)` 
	FROM
		`company_sales_record` 
	GROUP BY
		1 
	) `t0`
	CROSS JOIN (
	SELECT
		AVG( `t1`.`__measure__1` ) AS `__measure__0` 
	FROM
		(
		SELECT
			SUM( `company_sales_record`.`price` ) AS `__measure__1` 
		FROM
			`company_sales_record` 
		GROUP BY
			`company_sales_record`.`product_type` 
		) `t1` 
	HAVING
		( COUNT( 1 ) > 0 ) 
	) `t2`

表计算示例

求销售额的年同比

求销售额的月环比

求销售额的历年累计