什么是自定义计算？

使用自定义计算可以定义动态指标和转换，而无需修改数据集查询。 本页介绍如何在 AI/BI 仪表板中使用自定义计算。

为何使用自定义计算？

通过自定义计算，无需更改源 SQL，即可从现有仪表板数据集创建和可视化新字段。 每个数据集最多可以定义 200 个自定义计算。

自定义计算是以下类型之一：

• 计算度量值：总销售额或平均成本等聚合值。 计算度量值可以通过使用 AGGREGATE OVER 命令来计算跨时间范围的值。
• 计算维度：未聚合的值或转换，例如对年龄范围或格式字符串进行分类。

自定义计算的行为与度量视图类似，但其范围限制在定义它们的数据集和仪表板。 若要定义可用于其他数据资产的自定义指标，请参阅 Unity 目录指标视图。

使用计算度量值创建动态指标

假设你有以下数据集：

你想要按区域直观显示利润率。 如果没有自定义计算，则需要使用 margin 列创建新数据集：

虽然此方法有效，但新数据集是静态的，并且可能仅支持单个可视化效果。 应用于原始数据集的筛选器不会影响新数据集，无需进行额外的手动调整。

使用自定义计算，可以使用以下公式将利润率表示为聚合：

(SUM(Price) - SUM(Cost)) / SUM(Price)

此度量值是动态的。 在可视化效果中使用时，它会自动更新以反映可视化效果的分组。 例如，上述度量值可用于根据可视化中选择的内容，按 Region 或按 Item 来可视化利润率。

使用计算维度定义未聚合的值

使用计算维度可以定义未聚合的值或轻量转换，而无需更改源数据集。 为了实现可视化，组织或重新格式化数据时，这非常有用。

例如，若要按年龄组而不是单个年龄分析年龄趋势，可以使用以下表达式定义自定义 age_group 维度：

CASE
   WHEN age < 18 THEN '<18'
   WHEN age >= 18 AND age < 25 THEN '18-24'
   WHEN age >= 25 AND age < 35 THEN '25-34'
   WHEN age >= 35 AND age < 45 THEN '35-44'
   WHEN age >= 45 AND age < 55 THEN '45-54'
   WHEN age >= 55 AND age < 65 THEN '55-64'
   WHEN age >= 65 THEN '65+'
END

定义在窗口上的计算

仪表板可视化效果中的常见任务是跨范围计算聚合，例如过去 7 天内销售额的滚动总和。 自定义计算通过窗口函数支持此功能，这使你可以跨与当前行相关的一组行（一个“窗口”）执行计算。

AI/BI 仪表板支持两种类型的窗口函数：

• 标量窗口函数是指那些在固定分组上进行聚合并表现得类似标量函数的函数。 单独使用时，它们构成计算维度。
• 聚合窗口函数，用于聚合动态分组，并充当聚合函数。 使用时，它们形成计算得出的度量标准。

窗口函数也是 细节级别表达式的基础，使您可以独立于可视化分组来控制聚合粒度。

标量窗口函数

在发生任何可视化分组之前，标量窗口函数使用具有可选OVER 和PARTITION BY 子句的 ORDER BY 跨相关行的运算符进行聚合计算。 它们在窗口函数本身中定义的静态分区集上进行聚合，然后作为维度重新联接回未转换的基础表。

按区域计算总销售额的示例：

SUM(sales) OVER (PARTITION BY Region)

计算每个区域的累计销售额的示例：

SUM(sales) OVER (PARTITION BY Region ORDER BY Date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

OVER 语法

<AGGREGATE_FUNCTION>(<column>) OVER (
  [PARTITION BY <dimensions>]
  [ORDER BY <column>]
  [ROWS|RANGE frame_specification]
)

有关更多详细信息，请参阅 SQL 语言参考中的 Window 函数 。

聚合窗口函数

聚合窗口函数使用 AGGREGATE OVER 运算符在应用可视化分组后计算开窗聚合。 要聚合的组会自动继承自表达式所在的可视化。 可以选择使用 PARTITION BY 子句 * 来表示所有继承的分区，并使用 EXCEPT 子句排除特定维度。 该 ORDER BY 子句使你可以部分聚合相邻行中生成的分区，从而提供“窗口化”功能。

使用与上一示例相同的数据集，以下表达式使用 AGGREGATE OVER 运算符计算尾随的七天平均利润率。

(
  (SUM(Price) - SUM(Cost)) / SUM(Price)
) AGGREGATE OVER (
  ORDER BY Date TRAILING 7 DAY
)

创建后，可以在任何可视化效果中应用此度量值。

AGGREGATE OVER 语法

<AGGREGATE_EXPRESSION> AGGREGATE OVER (
  [PARTITION BY * [EXCEPT (<field> [, ...])]]
  [ORDER BY <field> <frame_specification>]
)

在此语法中：

• PARTITION BY * 表示从可视化效果分组继承的所有分区
• EXCEPT (<field> [, ...]) 指定要从分区集中排除的维度
• 这两 PARTITION BY 个子 ORDER BY 句都是可选的，但空 AGGREGATE OVER () 无效

帧规格可以是以下之一：

• CURRENT
• CUMULATIVE
• ALL
• (TRAILING|LEADING) <number> <unit>
  • <number> 是正整数
  • <unit> 为 DAY、 MONTH或 YEAR
  • 示例： TRAILING 7 DAY 或 LEADING 1 MONTH

下表展示了聚合帧规范与等效 SQL 窗口框架子句的对比情况。

ORDER BY如果未在可视化效果中对字段进行分组，则AGGREGATE OVER采用最后一行的聚合值作为要为每个组显示的值。 这相当于“最后”的半累加特性。

OVER 与 AGGREGATE OVER

主要区别OVERAGGREGATE OVER在于OVER是标量函数，AGGREGATE OVER是聚合函数。 OVER 需要一个 PARTITION BY 子句来定义组，同时 AGGREGATE OVER 从周围的可视化效果继承其组，并且可以将数据合并到当前组之外。

使用以下 OVER 语法：

• 需要在未聚合的上下文中使用的窗口计算，例如表格。
• 必须忽略所有可视化分组和筛选器的窗口计算。
• 以固定粒度进行聚合：使用PARTITION BY在特定细度计算聚合。
• 使用排名和分析函数，例如ROW_NUMBER， RANKLAG。

使用以下 AGGREGATE OVER 语法：

• 可在多种分组上下文中使用的窗口计算，或需要包含在当前组外的数据。
• 遵循可视化筛选器的窗口计算。
• 在比可视化更低的详细程度上进行聚合：排除使用 的维度。
• 对缺失行具有鲁棒性的时间范围：使用TRAILING或LEADING移动窗口。

性能优势

自定义计算针对性能进行优化。 对于小型数据集（≤100,000 行和≤100MB），计算在浏览器中运行，以提高响应速度。 较大的数据集由 SQL 仓库处理。 有关更多详细信息，请参阅 数据集优化和缓存 。

创建自定义计算

此示例基于 samples.nyctaxi.trips 数据集创建计算度量值。 它假设你具备操作 AI/BI 仪表板的基本知识。 如果不熟悉创作 AI/BI 仪表板，请参阅 创建仪表板 入门。

1. 打开现有数据集或创建新数据集。

2. 单击“ + 添加自定义计算”。

   

3. 此时会在屏幕右侧打开 “创建计算 ”面板。 在 名称 文本字段中，输入 每英里成本。

4. （可选）在 “批注 ”文本字段中，输入“使用票价金额和行程距离计算每英里的成本”。

5. 在 “表达式 ”字段中，输入以下内容：

   try_divide(SUM(fare_amount), SUM(trip_distance))
   

6. 单击 “创建” 。

引用其他计算

自定义计算可以引用同一数据集中定义的其他自定义计算。 这允许你通过编写更简单的计算、促进可重用性和可维护性来生成复杂的指标。

引用另一个自定义计算时，请直接在表达式中使用其名称，就好像它是数据集中的列一样。

例如，假设已创建以下计算度量值：

• total_revenue： SUM(sale_amount)
• total_cost： SUM(cost_amount)

可以创建引用这两者的第三个计算度量值：

• profit_margin： (MEASURE(total_revenue) - MEASURE(total_cost)) / MEASURE(total_revenue)

将自定义计算添加到指标视图

可以在指标视图创建的数据集的基础上定义自定义计算。 打开数据集时，仅显示 结果表 和 架构 。 单击 “自定义计算 ”以定义新的自定义计算。 若要定义其他数据资产可以使用的其他自定义指标，请更改视图定义。 请参阅 Unity 目录指标视图。

若要从仪表板数据集编辑器中定义新的指标视图，请参阅 “导出”作为指标视图。

查看架构

单击结果面板中的 “架构 ”选项卡以查看自定义计算及其关联的注释。

计算度量值列在 度量值 部分中，并由 fx 标记。 当您在可视化中设置 GROUP BY 时，相关的计算度量值将被动态计算。 在结果表中看不到该值。 计算维度显示在 “维度” 部分中。

在可视化效果中使用自定义计算

可以在可视化效果中使用以前创建的 每英里计算成本 度量值。

根据图表中配置的维度，计算出的度量值会自动聚合。 此行为与维度和度量值在度量视图中的工作方式相同，聚合会动态适应您在可视化中定义的分组。

1. 单击“画布”。 然后，在画布上添加新的可视化部件。
2. 使用可视化配置面板编辑设置，如下所示：
   • 数据集：Taxicab 数据
   • 可视化：条形图
   • X 轴：
     • 字段：dropoff_zip
     • 量表类型：分类
     • 转换：无
   • Y 轴：
     • 每英里的成本

下图显示了图表。

应用筛选器时，使用自定义计算的可视化效果会自动更新。 例如，添加 pickup_zip 筛选器将更新可视化效果，以仅显示与所选值匹配的数据。

编辑自定义计算

若要编辑计算，请执行以下操作：

1. 单击“ 数据 ”选项卡，然后单击与要编辑的计算关联的数据集。
2. 单击结果面板中的“架构”选项卡。
3. 度量值 和 维度 显示在数据集字段列表下。 单击 要编辑的计算右侧的 kebab 菜单。 然后单击“编辑”。
4. 在 “编辑自定义计算 ”面板中，更新要编辑的文本字段。 然后单击 更新。

删除自定义计算

若要删除计算，请执行以下操作：

1. 单击“ 数据 ”选项卡，然后单击与要编辑的度量值关联的数据集。
2. 单击结果面板中的“架构”选项卡。
3. “措施”部分出现在字段列表下。 单击要编辑的计算右侧的 然后单击 删除。
4. 在显示的 “删除”对话框中单击 “删除”。

局限性

若要使用自定义计算，必须满足以下条件：

• 表达式中使用的列必须属于同一数据集。
• 不支持引用外部表或数据源的表达式，并且可能会失败或返回意外结果。

支持的函数

有关自定义计算的所有受支持函数的完整参考，请参阅 自定义计算函数参考。 尝试使用不受支持的函数会导致错误。

示例

以下示例演示了自定义计算的常见用途。 每个自定义计算都显示在数据选项卡上数据集的架构中。在画布上，可以选择自定义计算作为字段。

有条件地筛选和聚合数据

使用 CASE 语句来有条件地聚合数据。 以下示例使用 samples.nyctaxi.trips 数据集并计算从 10103 邮政编码开始的所有行程的费用总和。

SUM(CASE
  WHEN pickup_zip=10103 THEN fare_amount
  WHEN pickup_zip!=10103 THEN 0
END)

构造字符串

使用该 CONCAT 函数构造新的字符串值。 请参阅 concat 函数 和 concat_ws 函数。

CONCAT(first_name, ' ', last_name)

设置日期格式

用于 DATE_FORMAT 设置可视化效果中显示的日期字符串的格式。

DATE_FORMAT(tpep_pickup_datetime, 'YYYY-MM-dd')