Tableau Prep 行级别表计算：商品变价场景分析

---

今日在某零售客户的项目中，遇到一个典型的表计算场景——这里是行级别的表计算，而非聚合后的表计算。

一、调价的业务过程和数据

比如，一个商品在上市之后会有多次价格变更（称之为“调价”），比如产品 A 上市时定价20元，后来两次调价，如下图所示：

为此，数据库就需要设置“商品调价明细表”记录上述过程，主要的字段会包含：

• ID（调价唯一编码）
• 商品 ID
• 生效日期
• 设定价格

假设产品 A 上市后调价2次，产品 B 上市后调价1次，产品 C 上市后调价4次，示例数据如下所示：

ID	商品ID (Product ID)	设定价格 (Set Price)	生效日期 (Effective Date)
1	A	￥100.00	2025/1/15
2	A	￥95.00	2025/3/1
3	B	￥250.00	2025/2/10
4	C	￥55.00	2025/1/20
5	C	￥58.00	2025/2/25
6	C	￥62.50	2025/4/10
7	C	￥60.00	2025/7/1

上述数据表是典型的业务事实表，包含了调价过程的“计量型属性”（measure attributes）和“维度型属性”。

不过，从分析的角度看，分析师很难将上述数据和商品销售过程匹配起来，为了完成区间的对应，每一次调价的记录还需要增加“调价失效日期”字段。

• 商品调价对应的 调价失效日期 ，等于 该商品下一次调价的生效日期。

可以看出，这是一个典型的“偏移计算”的场景，这里没有聚合、没有LEFT 之类的行级别计算，只有“找到下一次调价”对应行的生效日期的“查找动作”。

二、SQL 和 Tableau Prep 完成偏移计算

以上面的数据表为例，偏移查找的逻辑如下：

• 范围或者说分区：指定商品为查找的边界，不能跨商品查找，因此说 Partition by = 商品
• 依据或者说方向：指定生效日期为查找的方向，按照从前往后的次序排序，而后查找“下一个”，因此说 Order by = 生效日期，默认 asc 升序

如图所示：

基于上述的逻辑，可以使用 SQL 的窗口计算函数LEAD完成，如下所示：

LEAD(生效日期)  OVER  (PARTITION BY 产品名称  ORDER BY 生效日期  ASC )

LEAD（头）查找上一行，与之对应的 LAG（腿）查找下一行。

上述过程不能在 Tableau Desktop 中完成，因为 Desktop 为了性能阉割了“行级别表计算”，只保留了基于聚合的表计算——这里的“生效日期”显然不是聚合值。

行级别表计算应该在 Prep 中完成。Prep Builder 中有如下的表计算函数：

• Partition by 分区
• Order By 依据
• Lookup 查找

这里的偏移计算，可以用如下的 Prep 完成：

其中，Partition by 和 order by 都需要在包含在大括号之中，相比 SQL 的逻辑，确实是复杂了一点。

{PARTITION [plu_code] :
    {ORDERBY [eff_date] asc :
       LOOKUP([eff_date], 1)
    }
}

于此以来，我们就获得了每个商品变价对应的生效日期和失效日期，就可以使用 Desktop 完成甘特图、计算变价的时间周期等分析。

三、Tableau Prep 支持的表计算函数

窗口计算使分析师能够对整个表或数据集中的所选行（分区）执行计算。例如，在将排名应用于所选行时，可以使用以下计算语法：

{PARTITION [field]: {ORDERBY [field]: RANK() }}

• PARTITION（可选）：指定要对其执行计算的行。可以指定多个字段，但如果要使用整个表，可以省略此部分，Tableau Prep 会将所有行视为分区。例如，{ORDERBY [Sales] : RANK() }。
• ORDERBY（必需）：指定要用于生成排名序列的一个或多个字段。
• Rank ()（必需）：指定要计算的排名类型或 ROW_NUMBER ()。Tableau Prep 支持 rank()、RANK_DENSE()、RANK_MODIFIED()、RANK_PERCENTILE() 和 ROW_NUMBER() 函数。
• DESC 或 ASC（可选）：表示降序 (DESC) 或升序 (ASC) 顺序。 默认情况下，排名按降序DESC排序，因此无需在表达式中指定此项。如果要更改排序顺序，请向表达式中添加 ASC。也可以在函数中同时包括这两个选项。

举例来说，如果要对所选行进行排名，但希望按升序对行进行排序，然后按降序应用排名，则应在表达式中包括这两个选项。例如：

{PARTITION [Country], [State]: {ORDERBY [Sales] ASC,[Customer Name] DESC: RANK() }}

Tableau Prep 常见的分析函数：

函数	描述	结果
RANK()	从 1 开始按升序或降序为每一行分配整数排名。如果行具有相同的值，则它们共享分配给该值的第一个实例的排名。在计算下一行的排名时，将添加具有相同排名的行数，因此您可能无法获得连续排名值。 示例计算：{ORDERBY [Commission] DESC: RANK()}
RANK_DENSE()	从 1 开始按升序或降序为每一行分配整数排名。如果行具有相同的值，则它们共享分配给该值的第一个实例的排名，但不会跳过任何排名值，因此您会看到连续排名值。 示例计算：{ORDERBY [Commission] DESC: RANK_DENSE()}
RANK_MODIFIED()	从 1 开始按升序或降序为每一行分配整数排名。如果行具有相同的值，则它们共享分配给该值的最后一个实例的排名。Rank_Modified 的计算方式为 Rank + (Rank + Number of duplicate rows - 1)。 示例计算：{ORDERBY [Commission] DESC: RANK_MODIFIED()}
RANK_PERCENTILE()	从 0 到 1 按升序或降序为每一行分配百分比排名。RANK_PERCENTILE 的计算方式为 (Rank-1)/(Total rows-1)。 示例计算：{ORDERBY [Commission] DESC: RANK_PERCENTILE()} 注意：如果出现平局，Tableau Prep 会向下舍队，类似于 SQL 中的 PERCENT_RANK()。
ROW_NUMBER()	为每个唯一行分配连续的行 ID。不会跳过任何行号值。如果具有重复行并使用此计算，则每次运行流程时，如果行的顺序发生更改，结果可能会发生变化。 示例计算：{ORDERBY [Commission] DESC: ROW_NUMBER()}

以下示例显示了应用于同一数据集的上述每个函数的比较。

除此之外，Tableau Prep 还支持分片计算：

通过创建计算字段，使用“分片”功能将行分布到指定数量的存储桶中。选择要作为分布依据的字段，以及要使用的组（分片）的数量。还可以选择其他字段来创建分区，其中分片的行分布在组中。

举例来说，如果有几行学生数据并想查看哪些学生在前 50% 和后 50% 中，可以将数据分组为两个分片。

以下示例显示了学生成绩上半部分和下半部分的两个组。此方法的语法是：

{ORDERBY [Grade] DESC:NTILE(2)}

您还可以创建一个分区，其中字段的每个值都是一个单独的分区，并将数据划分为每个分区的组。

以下示例显示为“Subject”（科目）字段创建分区。为每个科目创建一个分区，并为“Grade”（成绩）字段创建两个组（分片）。然后将行均匀地分配到三个分区的两个组中。此方法的语法是：

{PARTITION [Subject]:{ORDERBY [Grade] DESC:NTILE(2)}}

在分析库存 ABC 分类时，如果想要计算库存金额占据前25%、后25%的物料，就可以用分片函数来完成四等分了。

官方推荐：

• Tableau Prep 表计算
• SQL、Tableau Desktop与Prep表计算对比 2024/1 xilejun