指标视图的高级技术

指标视图的高级技术使你能够表达复杂的业务逻辑，并在语义层中重复使用定义。 本页介绍两种此类技术：

• 窗口度量：用于时间序列计算，例如移动平均值、累计总和和环比变化。
• 可组合性：通过引用其他度量值而不是重写其逻辑来生成复杂度量值。

本页假定熟悉基本指标视图建模概念。 请参阅 模型指标视图。

窗口测量

利用窗口度量值，可以在指标视图中使用开窗聚合、累积聚合或半累加聚合来定义度量值，支持移动平均值、期间内更改和运行总计等计算。

定义窗口度量值

窗口度量值包括以下必填字段：

• order：确定窗口排序的字段。

• range：定义窗口的范围。 支持的值包括 current、cumulative、trailing、leading 和 all。 有关完整语法和说明，请参阅 支持 range 的值。 有关在 inclusive 和 exclusive 上的 trailing 和 leading 修饰符的详细信息，请参阅 包含或排除锚定行。

• semiadditive：指定在查询 GROUP BY中不包含订单字段时如何聚合度量值。 可能的值： first 和 last。

窗口度量还支持以下可选字段：

• 偏移量：按固定间隔沿着 order 字段向前或向后移动窗口帧。 将其用于环比度量值，例如月环比或年同比。 有关语法、支持的单位和约束，请参阅 Window 度量值。

offset 如何偏移窗口帧

需要 Databricks Runtime 18.1 和 YAML 版本 1.1 或更高版本。 请参阅 版本要求。

range 字段定义了窗口相对于锚定行的形状，而 offset 则沿 order 按指定的间隔滑动该框架。 下表显示了相对于锚点行range、在有和没有值为offset的k时每个t值的帧：

offset 独立于 semiadditive。 first或last选择仍然控制着当order不在查询的GROUP BY中时该度量值如何折叠。

为了获得最佳效果，请将offset与order的天然纹理相匹配。 对于月度数据，优先使用 offset: -12 month 而不是 offset: -365 day，因为按月和按年的算术运算会考虑月份天数不固定以及闰年，而 day 的算术运算则不会。

包含或排除锚定行

需要 Databricks Runtime 18.1 和 YAML 版本 1.1 或更高版本。 请参阅 版本要求。

对于 trailing 和 leading 范围，可选的 inclusive 或 exclusive 关键字用于控制锚定行的窗口值（例如“今天”）是否包含在滚动窗口中：

以下示例演示在使用 inclusive 时，exclusive 和 2025-01-05 如何影响锚定日期 trailing 3 day 的滚动窗口。

假设基础数据每天有一行，具有以下值：

每个修饰符都会选取相对于锚点的连续三天对应的数据行，并将其值求和：

leading 范围遵循相反方向的相同逻辑。

滞后、移动或前导窗口测量示例

以下示例计算下订单的唯一客户的滚动 7 天计数。 此指标通过显示截至每一日期的一周中购买的不同客户数来跟踪客户参与趋势。

version: 1.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'

fields:
  - name: date
    expr: o_orderdate

measures:
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: date
        range: trailing 7 day
        semiadditive: last

对于此示例，以下配置适用：

• order: date 指定 date 字段对窗口进行排序。
• range: trailing 7 day 将窗口定义为每个日期之前的 7 天，不包括日期本身。
• semiadditive: last 如果不是分组列，则返回 7 天窗口中 date 的最后一个值。

环比窗口期度量示例

以下示例通过将今天的收入（所有订单价格的总和）与昨天的收入进行比较来计算日间销售额增长。 此指标标识每日销售趋势，并显示收入的百分比变化。

version: 1.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'

fields:
  - name: date
    expr: o_orderdate
measures:
  - name: previous_day_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: trailing 1 day
        semiadditive: last
  - name: current_day_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: current
        semiadditive: last
  - name: day_over_day_growth
    expr: (MEASURE(current_day_sales) - MEASURE(previous_day_sales)) / MEASURE(previous_day_sales) * 100

对于此示例，以下配置适用：

• 使用了两个窗口度量值：一个用于计算前一天的总销售额，另一个用于计算当天的总销售额。

• 第三个度量值计算当前和前几天之间的百分比变化（增长）。

使用 offset 的同比窗口度量值示例

offset 修饰符是环比度量值的基础构件。 定义基础度量值的偏移副本，然后将两者组合起来，以便在指标视图中直接表示差值、比率或增长率。

以下示例通过将每个月的销售额与上一年的同月进行比较来计算年销售额同比增长。 该偏移度量值使用 offset: -12 month 沿着 month 字段向前回溯 12 个月。

version: 1.1
source: main.default.monthly_sales

fields:
  - name: month
    expr: month
  - name: category
    expr: category

measures:
  - name: monthly_sales
    expr: SUM(sales)
    window:
      - order: month
        range: current
        semiadditive: last

  - name: monthly_sales_py
    expr: SUM(sales)
    window:
      - order: month
        range: current
        semiadditive: last
        offset: -12 month

  - name: yoy_growth
    expr: MEASURE(monthly_sales) - MEASURE(monthly_sales_py)

  - name: yoy_growth_pct
    expr: (MEASURE(monthly_sales) - MEASURE(monthly_sales_py))
      / NULLIF(MEASURE(monthly_sales_py), 0)

对于此示例，以下配置适用：

• monthly_sales 是本月的基本度量值，对销售额求和。
• monthly_sales_py 是使用 offset: -12 month 将同一度量值向后移动 12 个月后的结果。 对于 2025 年 1 月，它返回 2024 年 1 月的值。
• yoy_growth 并 yoy_growth_pct 撰写两个度量值，以表达绝对和百分比变化。 当上一年值为零时，使用 NULLIF 可避免除零错误。

累计（持续更新的）总值示例

以下示例从数据集的开头计算到每个日期的累计销售收入。 此运行总数显示随时间推移产生的总收入量，有助于跟踪年度收入目标的进度或分析长期增长模式。

version: 1.1
source: samples.tpch.orders

filter: o_orderdate > DATE'1998-01-01'

fields:
  - name: date
    expr: o_orderdate
  - name: customer
    expr: o_custkey

measures:
  - name: running_total_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: cumulative
        semiadditive: last

对于此示例，以下配置适用：

• order: date 按时间顺序对窗口进行排序。
• range: cumulative 将窗口定义为从数据集开头到每个日期（含该日期）的所有数据。
• 当查询的semiadditive: last中不包含date时，GROUP BY返回最新的累计值，而不是对所有日期求和。

截至当前期间的度量值示例

以下示例计算年初至今的销售收入。 该指标显示每年从 1 月 1 日起至当前日期的累计收入，并在每个新年开始时重置。

version: 1.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1997-01-01'

fields:
  - name: date
    expr: o_orderdate
  - name: year
    expr: DATE_TRUNC('year', o_orderdate)
measures:
  - name: ytd_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: cumulative
        semiadditive: last
      - order: year
        range: current
        semiadditive: last

对于此示例，以下配置适用：

• 使用了两个窗口定义：一个用于对 date 字段进行累计求和，另一个用于将求和范围限制在 current 年。
• 该 year 字段限制累积总和，以便它在每年年初重置。

半累加性度量示例

以下示例计算不应在日期之间求和的帐户余额（不能将星期一的余额添加到星期二的余额以获取总余额）。 相反，当聚合多天时，指标将返回最近的余额。 但是，该度量值仍可跨客户求和，以在给定的一天显示所有帐户的总余额。

version: 1.1

fields:
  - name: date
    expr: date
  - name: customer
    expr: customer_id

measures:
  - name: semiadditive_balance
    expr: SUM(balance)
    window:
      - order: date
        range: current
        semiadditive: last

对于此示例，以下配置适用：

• order: date 按时间顺序对窗口进行排序。

• range: current 将窗口限制为一天，且不跨天聚合。

• semiadditive: last 在按多天聚合时返回最新余额。

  注释

  此时间窗口仍然对所有客户的余额进行求和，以获得每天的总体余额。

查询窗口度量值

可以使用窗口度量值查询指标视图，就像任何其他指标视图一样。 以下示例查询指标视图中的数据：

SELECT
   state,
   DATE_TRUNC('month', date),
   MEASURE(t7d_customers) as m
FROM my_metric_view
WHERE date >= DATE'2024-06-01'
GROUP BY ALL

可组合性

指标视图是可组合的。 可以生成引用现有字段的新字段和度量值，而不是从头开始重写逻辑。 这样可以减少重复，并使复杂的指标定义更易于维护。

可组合性体现在两个层面：一是在单个指标视图内部，二是在指标视图之间，即当一个指标视图用作另一个指标视图的数据源时。

可组合性支持以下引用模式：

• 新字段中的早期字段。
• 新度量值中的字段和已有度量值
• 用作新字段来源的指标视图中的字段。
• 用作新度量值来源的指标视图中的字段和度量值。

定义可组合性的度量值

在本 measures 部分中，可以从源指标视图引用度量值或之前在同一指标视图中定义的度量值。 此方法可提高语义层的一致性、可审核性和维护。

示例：平均订单值（AOV）

以下示例使用两个原子度量 total_revenue 值（订单价格和）和 order_count （订单数）定义平均订单值（AOV）。 该 avg_order_value 度量值引用两个原子度量值。

version: 1.1

source: samples.tpch.orders

measures:
  # Total Revenue
  - name: total_revenue
    expr: SUM(o_totalprice)

  # Order Count
  - name: order_count
    expr: COUNT(1)

  # Composed Measure: Average Order Value (AOV)
  - name: avg_order_value
    # Defines AOV as Total Revenue divided by Order Count
    expr: MEASURE(total_revenue) / MEASURE(order_count)

total_revenue如果定义更改（例如，要排除税），avg_order_value则会自动使用更新后的定义。

使用条件逻辑的可组合性

可以使用可组合性来创建复杂比率、条件百分比和增长率，而无需依赖窗口函数进行简单的周期内计算。

示例：履行率

以下示例计算履行率：状态 'F' 为（已履行）的订单百分比。 该度量将已完成的订单除以总订单。

version: 1.1

source: samples.tpch.orders

measures:
  # Total Orders (denominator)
  - name: total_orders
    expr: COUNT(1)

  # Fulfilled Orders (numerator)
  - name: fulfilled_orders
    expr: COUNT(1) FILTER (WHERE o_orderstatus = 'F')

  # Composed Measure: Fulfillment Rate (Ratio)
  - name: fulfillment_rate
    expr: MEASURE(fulfilled_orders) / MEASURE(total_orders)
    format:
      type: percentage

可组合性的最佳做法

1. 首先定义原子度量值：在定义引用它们的度量值之前，先建立基本度量值（SUM、 COUNT、 AVG）。
2. 为引用使用MEASURE()：在MEASURE()中引用另一个度量值时使用expr函数。 请勿手动重复聚合逻辑。 例如，如果两个值的度量值已存在，请避免 SUM(a) / COUNT(b) 。
3. 确定可读性优先级：使用明确的数学公式撰写度量值。 例如， MEASURE(gross_profit) / MEASURE(total_revenue) 比单个复杂 SQL 表达式更清晰。
4. 添加语义元数据：使用语义元数据设置下游工具的组合度量值（例如百分比或货币）的格式。 在 指标视图中查看代理元数据。

后续步骤

• 模型指标视图
• 指标视图中的代理元数据
• 指标视图的具体化
• 指标视图 YAML 语法参考