今日在某零售客户的项目中，遇到一个典型的表计算场景——这里是行级别的表计算，而非聚合后的表计算。
📌 一、调价的业务过程和数据
📌 二、SQL 和 Tableau Prep
📌 三、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 )上述过程不能在 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)
}
}三、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)}}
官方推荐：
- Tableau Prep 表计算
- SQL、Tableau Desktop与Prep表计算对比 2024/1 xilejun
No comments yet