指标视图的高级技术

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

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

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

窗口测量

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

可以在目录资源管理器编辑器或 YAML 中添加窗口度量值。

在编辑器中添加窗口度量值

在指标视图编辑器的 UI 选项卡上，在编辑度量值时单击 “+ 窗口 ”。 + 窗口 在 生成器 和 自定义 模式下都可用。 编辑器中的窗口选项对应于 “定义窗口度量值”中所述的 YAML 字段。

有关创建和编辑度量值的详细信息，请参阅 “创建指标”视图。

定义窗口度量值

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

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

• range：定义窗口的范围。 支持的值包括 current、cumulative、trailing、leading 和 all。 trailing 或 leading 范围在日期或时间戳 order 字段上使用时间单位，例如 trailing 7 day；而在连续整数 order 列上则不使用任何单位，例如 trailing 3。 有关完整语法和说明，请参阅 支持 range 的值。 有关在 inclusive 和 exclusive 上的 trailing 和 leading 修饰符的详细信息，请参阅 包含或排除锚定行。

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

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

• 偏移：将窗框沿 order 场向后或向前移动固定幅度。 针对环比或同比等周期度量，在日期或时间戳 order 字段上使用诸如 -12 month 之类的日期偏移。 使用诸如 -1 这样的无单位数字偏移量，在连续整数 order 列上沿非日历日期的业务周期进行比较。 有关语法、支持的单位和约束，请参阅 Window 度量值。

你也可以引用整数参数作为窗口测度的rangeoffset值，这样调用者在查询时传递窗口大小。 参见 “传递窗口大小作为参数”。

offset 如何偏移窗口帧

有关最低计算和 YAML 规范版本要求，请参阅 指标视图功能可用性 。

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

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

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

包含或排除锚定行

有关最低计算和 YAML 规范版本要求，请参阅 指标视图功能可用性 。

对于 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 的最后一个值。

CREATE OR REPLACE VIEW catalog.schema.rolling_customers WITH METRICS LANGUAGE YAML AS
$$
  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
$$

环比窗口期度量示例

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

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 可避免除零错误。

数值索引列上的窗口计算

需要Databricks Runtime 19及以上版本和YAML规范版本1.1及以上。

日期 offset（例如 -12 month）沿着日期或时间戳列递进。 当比较轴是并非日历日期的业务期间时，日期列便不再合适。 财政周、ISO周和4-4-5会计周期都是例子。 相反，应按整数 索引列 对窗口度量进行排序，并应用无单位的数值 offset 或 range。 数值偏移量对索引执行整数运算，因此 offset: -1 表示序列中的前一个位置，而 range: trailing 3 表示最后三个位置。

数值偏移量或范围只有在列 order 满足以下所有要求时才返回正确结果。 查询时仅验证第一个需求。 如果某一列违反另外两列中的任一规则，返回的将是错误的数值而不是报错，因此请自行核实：

• 整数：该列为 TINYINT、SMALLINT、INT 或 BIGINT。 DATE， TIMESTAMP， DECIMAL， DOUBLE， 和 STRING 列不是有效的索引列。 如果要按日期进行比较，请改用带日期的 offset。 请参阅窗口框架的移动方式offset。
• 单调递增且连续（密集）：从每个实际周期到下一个周期，值严格递增 1，包括跨年度或季度边界的情况。 空档或重复值会导致 offset: -1 跳过或重复计入一个时间段。 像每年都会重置为 1 的 fiscal_week 这样的原始标签，本身并不是有效索引。
• 匹配比较粒度：索引粒度中的偏移步长或范围步长。 在月指数中， 是 offset: -1 上一个月， range: trailing 3 是过去三个月。 对于较粗粒度的比较（例如同比），可按年份索引对单独的度量值进行排序，或使用上一周期相同位置的度量值。 不要复用更细粒度（月份）的索引并进行分组。 参见前一个周期中的相同位置（两个窗口规格，无索引）

获取索引列

你可以从源头命名预先计算好的索引列，或者在度量视图中推导出一个。 食谱取决于日历的形状：

对齐索引所依赖的维度

为用于计算索引的每个指标视图字段指定一个range: all排序规格，例如，对于month_index = order_year * 12 + order_month，order_year和order_month。 无论指数是在度量视图中推导出，还是作为裸列预先计算上游，都适用此规则。 range: all规范规定，当查询按这些字段进行分组或过滤时，在窗口构造期间会按预期忽略这些字段。

这些规格不是可选的。 如果没有这些字段，按 order_year 或 order_month 分组的查询会按这些字段对窗口进行分区，这会将移位后的帧限制在单个周期内，因此比较测度会返回 NULL。

其他所有字段都没有 range: all 规范。独立于索引的字段（如 region）会正常分组，并且窗口会按值进行分区。

连续比较或滚动窗口（索引加数值范围）

当你指的是“相邻序列”时，可以使用带有数字偏移量或范围的索引，比如上一个周期、滚动 的N 个周期，或者至今的周期。 按索引排序，应用数字偏移或区间，并为索引所依赖的每个日历列添加 range: all 规范。 在下例中，指标依赖于 order_year 和 order_month：

version: 1.1
source: samples.tpch.orders

fields:
  - name: order_year
    expr: YEAR(o_orderdate) # INT breakdown column
  - name: order_month
    expr: MONTH(o_orderdate) # INT breakdown column
  - name: month_index # consecutive monotonic index across years
    expr: order_year * 12 + order_month

measures:
  - name: monthly_sales
    expr: SUM(o_totalprice)
    window:
      - order: order_year # breakdown, so range: all keeps GROUP BY order_year working
        range: all
        semiadditive: last
      - order: order_month # breakdown
        range: all
        semiadditive: last
      - order: month_index # the axis you compare along
        range: current
        semiadditive: last

  - name: monthly_sales_prev # previous month: same specs plus a unitless offset of -1 on the index
    expr: SUM(o_totalprice)
    window:
      - order: order_year
        range: all
        semiadditive: last
      - order: order_month
        range: all
        semiadditive: last
      - order: month_index
        range: current
        offset: -1 # one position back, crosses the year boundary correctly
        semiadditive: last

  - name: sales_trailing_3mo # rolling 3 months: unitless range on the index
    expr: SUM(o_totalprice)
    window:
      - order: order_year
        range: all
        semiadditive: last
      - order: order_month
        range: all
        semiadditive: last
      - order: month_index
        range: trailing 3 # the three index positions before the current month
        semiadditive: last

  - name: mom_pct_change
    expr: (MEASURE(monthly_sales) - MEASURE(monthly_sales_prev))
      / NULLIF(MEASURE(monthly_sales_prev), 0) * 100

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

• month_index 是一个连续的单调指数，跨年度边界每月递增 1。
• monthly_sales使用 range: current 按索引排序，并为 order_year 和 order_month 细分列指定 range: all 规范，以便能够按这些列分组。
• monthly_sales_prev 在索引上增加 offset: -1，以回溯一个月，从而正确地处理跨年度边界情形。
• sales_trailing_3mo 利用无单位范围 trailing 3 对三个指标位置求和。 由于索引是连续的，三个位置就是三个月，无需进行时间单位运算。 这假设该范围内的每个月都有一个源行。 如果缺少某个月份，该窗口就无法再准确对应三个自然月，因此在可能存在间隔时，应将源数据补齐为完整的周期集合。 该范围默认为 exclusive，因此涵盖当前范围前的三个月。 添加 inclusive 以改为包含当前月份。
• mom_pct_change 由基础度量和偏移度量构成。 当先验值为零时，使用方法 NULLIF 可以避免除以零的错误。

按对齐后的细分列对度量进行分组查询，而不是按原始索引：

SELECT
  order_year,
  order_month,
  MEASURE(monthly_sales),
  MEASURE(monthly_sales_prev),
  MEASURE(sales_trailing_3mo),
  MEASURE(mom_pct_change)
FROM catalog.schema.monthly_sales_mv
GROUP BY ALL

上一个周期中的相同位置（两个窗口规格，无索引）

像“去年同一个月”这样的比较是按位置对齐的，而不是绝对距离，因此不需要索引列。 使用两个窗口参数：一个用于平移父周期，另一个用于固定内部位置。

version: 1.1
source: samples.tpch.orders

fields:
  - name: order_year
    expr: YEAR(o_orderdate)
  - name: order_month
    expr: MONTH(o_orderdate)

measures:
  - name: monthly_sales
    expr: SUM(o_totalprice)

  - name: monthly_sales_py # same month, prior year
    expr: SUM(o_totalprice)
    window:
      - order: order_year # shift the year coordinate
        range: current
        offset: -1
        semiadditive: last
      - order: order_month # hold the month fixed
        range: current
        semiadditive: last

  - name: yoy_pct_change
    expr: (MEASURE(monthly_sales) - MEASURE(monthly_sales_py))
      / NULLIF(MEASURE(monthly_sales_py), 0) * 100

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

• monthly_sales_py 使用两个窗口规格。 order_year 说明符会使用 offset: -1 将年份坐标向后移动一位，而 order_month 说明符会使用 range: current 将月份固定不变。
• 比较按位置对齐，因此不需要索引列。
• yoy_pct_change 组成两个指标来表示百分比变化。

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

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

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: month
    expr: DATE_TRUNC('MONTH', date)
  - name: year
    expr: DATE_TRUNC('year', date)
measures:
  - name: ytd_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: cumulative
        semiadditive: last
      - order: year
        range: current
        semiadditive: last

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

• 该示例使用两个窗口规范：一个用于字段的 date 累积总和，另一个用于将总和限制为年份 current 。
• 该 year 字段限制累积总和，以便它在每年年初重置。
• month和year字段在订单字段date上构成一个日期层次结构：每个字段都是按名称在date字段上定义的，而不是在底层o_orderdate列上定义的，因此查询可以按这些字段对该度量进行分组。 请参阅 “按日期层次结构分组”字段。

半累加性度量示例

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

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 在按多天聚合时返回最新余额。

  注释

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

查询窗口度量值

可以使用窗口度量值查询指标视图，就像任何其他指标视图一样。 窗口度量是基于其 order 字段计算的，因此，按时间细分结果的查询必须引用该字段，可以直接引用，也可以通过在该字段上定义的日期层次结构字段来引用。 当查询未引用顺序字段时， semiadditive 关键字将确定返回的值，如 Semiadditive 度量示例中所述。

以下示例按 state 以及基于 order 字段 date 的月份表达式对一个窗口度量进行分组：

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

按日期层次结构字段分组

日期层次结构会将顺序字段向上汇总到较粗粒度的级别，例如周、月或年。 按名称将每个级别定义为订单字段上的字段，而不是在基础源列上定义：

fields:
  - name: date
    expr: o_orderdate
  # Date hierarchy: each level is defined on the order field `date`,
  # not on the underlying o_orderdate column.
  - name: month
    expr: DATE_TRUNC('MONTH', date)
  - name: year
    expr: DATE_TRUNC('year', date)

按层次结构中的某个级别对窗口度量进行分组，将返回该粒度下的度量值。 假设按 ytd_metric_view 中的方式将该周期至今示例创建为 ，则以下查询将返回截至每个月最后一天的 YTD 值：

SELECT month, MEASURE(ytd_sales) AS ytd_sales
FROM ytd_metric_view
GROUP BY month
ORDER BY month;

可组合性

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

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

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

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

定义可组合性的度量值

在本 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 语法参考