什么是自定义计算？

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

为何使用自定义计算？

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

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

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

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

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

假设你有以下数据集：

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

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

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

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

此度量值是动态的。 在可视化效果中使用时，它会自动更新以反映应用于数据集的筛选器。

在一定范围内定义计算度量值

仪表板可视化中的一个常见任务是计算度量指标，例如 SUM(sales)，在一段范围内，例如过去 7 天。 对于仪表板数据集，使用 AGGREGATE OVER 命令定义这些类型的基于范围的度量值计算。

该 AGGREGATE OVER 命令可以将数据合并到当前组或分区之外。 例如，如果可视化效果按天对数据进行分组，则尾随的 7 天范围允许每天的数据点包含前 6 天的数据。 如果相同的度量值用于按月分组的可视化，则 7 天范围在每个月中仅使用最后 7 天。

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

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

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

AGGREGATE OVER 语法

该 AGGREGATE OVER 命令需要以下语法：

{expr} AGGREGATE OVER (ORDER BY {field} {frame})

Arguments

• expr

  用于评估的有效计算度量表达式

• 字段（必填）

  有效的列名称

• frame （必需） 可以是下列值之一：

  • CURRENT
  • CUMULATIVE
  • ALL
  • (TRAILING|LEADING) 数字单位
    • 数字是整数
    • 单位为 DAY、 MONTH或 YEAR
    • 示例： TRAILING 7 DAY 或 LEADING 1 MONTH

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

AGGREGATE OVER 与窗口函数相比

该 AGGREGATE OVER 命令类似于 SQL 中的窗口函数，这些函数通常用于计算指标。 但是，与窗口函数不同，无需在表达式中指定分区字段即可使用 AGGREGATE OVER 。 而是继承了来自可视化查询分组的分区。 这意味着可以更动态地使用这些度量值。 例如，通过将可视化效果的 y 轴和生产线的度量值添加到 x 轴，可以按生产线计算 7 天移动平均值。

ORDER BY如果未在可视化效果中对字段进行分组，则AGGREGATE OVER采用最后一行的聚合值作为要为每个组显示的值。 如果你熟悉半累加性度量值，则可以将其识别为等效于 last 半累加性行为。

使用自定义维度定义未聚合的值

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

例如，若要按年龄组而不是单个年龄分析年龄趋势，可以使用以下表达式定义自定义 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

性能优势

自定义计算针对性能进行优化。 对于小型数据集（≤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. 单击 “创建” 。

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

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