Advanced techniques for metric views
Advanced techniques for metric views let you express complex business logic and reuse definitions across your semantic layer. This page explains two such techniques:
- Window measures : for time-series calculations such as moving averages, running totals, and period-over-period changes.
- Composability : for building complex measures by referencing other measures rather than rewriting their logic.
This page assumes familiarity with basic metric view modeling concepts. See Model metric views.
The examples on this page use the TPC-H sample dataset, which models a wholesale supply chain. For more information about the TPC-H dataset, see tpch. For an end-to-end tutorial using this dataset with metric views, see Tutorial: build a metric view with joins and data modeling.
ウィンドウメジャー
実験段階
This feature is Experimental.
Window measures enable you to define measures with windowed, cumulative, or semiadditive aggregations in your metric views. They support calculations such as moving averages, period-over-period changes, and running totals.
You can add a window measure in the Catalog Explorer editor or in YAML.
Add a window measure in the editor
On the UI tab of the metric view editor, click + Window while editing a measure. + Window is available in both Builder and Custom mode. The window options in the editor correspond to the YAML fields described in Define a window measure.
For more information about creating and editing measures, see Create a metric view.
ウィンドウメジャーを定義します
A window measure includes the following required fields:
-
order : The field that determines the ordering of the window.
-
range : Defines the extent of the window. Supported values include
current,cumulative,trailing,leading, andall. For full syntax and descriptions, see Supportedrangevalues. For details on theinclusiveandexclusivemodifiers ontrailingandleading, see Include or exclude the anchor row. -
semiadditive : Specifies how to aggregate the measure when the order field is not included in the query's
GROUP BY. Possible values:firstandlast.
A window measure also supports the following optional field:
- offset : Shifts the window frame backward or forward along the
orderfield by a fixed interval. Use this for period-over-period measures such as month-over-month or year-over-year. For syntax, supported units, and constraints, see Window measures.
How offset shifts the window frame
See Metric view feature availability for minimum compute and YAML specification version requirements.
The range field defines the shape of the window relative to the anchor row, and offset slides that frame by the specified interval along order. The following table shows the frame for each range value with and without an offset of k, relative to the anchor row t:
範囲 | Frame without offset | Frame with |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
| entire partition | entire partition (unchanged) |
offset is independent of semiadditive. The first or last choice still controls how the measure collapses when order is not in the query's GROUP BY.
For best results, match offset to the natural grain of order. For monthly data, offset: -12 month is preferred over offset: -365 day because month and year arithmetic respects variable-length months and leap years, while day arithmetic does not.
Include or exclude the anchor row
See Metric view feature availability for minimum compute and YAML specification version requirements.
For trailing and leading ranges, the optional inclusive or exclusive keyword controls whether the anchor row's window value (for example, today) is part of the rolling window:
キーワード | Meaning | Anchor row in range? |
|---|---|---|
| n units including the anchor row. | はい |
| n units not including the anchor row. | No |
The following example shows how inclusive and exclusive affect the rolling window for the anchor date 2025-01-05 with trailing 3 day.
Assume the underlying data has one row per day with the following values:
Date | Value |
|---|---|
|
|
|
|
|
|
|
|
Each modifier selects three days of rows relative to the anchor and sums their values:
Modifier | Dates in window | 値 | 合計 |
|---|---|---|---|
|
|
|
|
|
|
|
|
leading ranges follow the same logic in the opposite direction.
後続、移動、または先行するウィンドウメジャーの例
The following example calculates a rolling 7-day count of customers who placed orders. This metric tracks customer engagement trends over time by showing how many distinct customers made purchases in the week leading up to each date.
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
For this example, the following configuration applies:
order: datespecifies that thedatefield orders the window.range: trailing 7 daydefines the window as the 7 days before each date, excluding the date itself.semiadditive: lastreturns the last value in the 7-day window whendateis not a grouping column.
Create the metric view using SQL
To create this metric view outside Catalog Explorer, wrap the YAML in CREATE OR REPLACE VIEW ... WITH METRICS LANGUAGE YAML AS and place the definition between the $$ delimiters:
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
$$
The other complete definitions on this page follow the same pattern.
期間ごとのウィンドウメジャーの例
The following example calculates day-over-day sales growth by comparing today's revenue (sum of all order prices) to yesterday's revenue. This metric identifies daily sales trends and shows the percentage change in revenue.
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
For this example, the following configuration applies:
- The example uses two window measures: one for calculating total sales on the previous day and one for the current day.
- A third measure calculates the percentage change (growth) between the current and previous days.
Year-over-year window measure example using offset
The offset modifier is the building block for period-over-period measures. Define a shifted copy of a base measure, then compose the two to express deltas, ratios, or growth rates directly in the metric view.
The following example calculates year-over-year sales growth by comparing each month's sales to the same month in the prior year. The shifted measure uses offset: -12 month to look back 12 months along the month field.
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)
For this example, the following configuration applies:
monthly_salesis the base measure, summing sales for the current month.monthly_sales_pyis the same measure shifted backward by 12 months usingoffset: -12 month. For January 2025, it returns the value for January 2024.yoy_growthandyoy_growth_pctcompose the two measures to express the absolute and percentage change. UsingNULLIFavoids divide-by-zero errors when the prior-year value is zero.
Cumulative (running) total measure example
The following example calculates cumulative sales revenue from the beginning of the dataset up to each date. This running total shows how much total revenue has been generated over time, useful for tracking progress toward annual revenue goals or analyzing long-term growth patterns.
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
For this example, the following configuration applies:
order: dateorders the window chronologically.range: cumulativedefines the window as all data from the beginning of the dataset up to and including each date.semiadditive: lastreturns the most recent cumulative value whendateis not included in the query'sGROUP BY, rather than summing across all dates.
Period-to-date measure example
The following example calculates year-to-date (YTD) sales revenue. This measure shows the cumulative revenue generated from January 1st of each year up to the current date, resetting at the beginning of each new year.
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
For this example, the following configuration applies:
- The example uses two window specifications: one for the cumulative sum over the
datefield and another to limit the sum to thecurrentyear. - The
yearfield restricts the cumulative sum so that it resets at the beginning of each new year. - The
monthandyearfields form a date hierarchy on the order fielddate: each is defined on thedatefield by name, not on the underlyingo_orderdatecolumn, so queries can group this measure by them. See Group by a date hierarchy field.
半加法メジャーの例
The following example calculates account balances, which must not be summed across dates (you can't add Monday's balance to Tuesday's balance to get total balance). Instead, when aggregating across multiple days, the measure returns the most recent balance. However, the measure can still be summed across customers to show total balance across all accounts on a given day.
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
For this example, the following configuration applies:
order: dateorders the window chronologically.range: currentrestricts the window to a single day with no aggregation across days.semiadditive: lastreturns the most recent balance when aggregating over multiple days.
This window measure still sums over all customers to get the overall balance per day.
Query a window measure
You can query a metric view with a window measure like any other metric view. A window measure is computed along its order field, so a query that breaks results down over time must reference that field, either directly or through a date hierarchy field defined on it. When the query doesn't reference the order field, the semiadditive keyword determines the value returned, as described in Semiadditive measure example.
The following example groups a window measure by state and by a month expression over the order field 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
Group by a date hierarchy field
A date hierarchy rolls the order field up to coarser grains, such as week, month, or year. Define each level as a field over the order field by name, not over the underlying source column:
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)
Grouping a window measure by a hierarchy level returns the measure at that grain. Assuming the period-to-date example is created as ytd_metric_view as it is in the Period-to-date measure example, the following query returns the YTD value as of the last date in each month:
SELECT month, MEASURE(ytd_sales) AS ytd_sales
FROM ytd_metric_view
GROUP BY month
ORDER BY month;
Defining a hierarchy level on the underlying source column, such as DATE_TRUNC('MONTH', o_orderdate), breaks its link to the order field date, even though the expressions look equivalent. Grouping a window measure by such a field returns incorrect results.
Composability
Metric views are composable. You can build new fields and measures that reference existing ones rather than rewriting logic from scratch. This reduces duplication and makes complex metric definitions easier to maintain.
Composability works at two levels: within a single metric view, and across metric views when one metric view is used as the source for another.
Composability supports the following reference patterns:
- Earlier fields in new fields.
- Fields and earlier measures in new measures.
- Fields from metric views used as source in new fields.
- Fields and measures from metric views used as source in new measures.
構成可能性を考慮したメジャーを定義する
In the measures section, you can reference measures from the source metric view or measures defined earlier in the same metric view. This approach improves consistency, auditability, and maintenance of your semantic layer.
メジャーのタイプ | 説明 | 例 |
|---|---|---|
Atomic | A simple, direct aggregation on a source column. These form the building blocks. |
|
Composed |
|
|
Example: Average order value (AOV)
次の例では、 total_revenue （注文価格の合計）とorder_count （注文数）という2つのAtomicメジャーを使用して平均注文額（AOV）を定義します。avg_order_valueメジャーは、両方のAtomicメジャーを参照します。
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)
If the total_revenue definition changes (for example, to exclude tax), avg_order_value automatically uses the updated definition.
Composability with conditional logic
You can use composability to create complex ratios, conditional percentages, and growth rates without relying on window functions for simple period-over-period calculations.
Example: Fulfillment rate
次の例では、履行率、つまりステータスが'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
Best practices for composability
- まずAtomicメジャーを定義します : それらを参照するメジャーを定義する前に、基本メジャー (
SUM、COUNT、AVG) を確立します。 - 参照には
MEASURE()使用します 。exprで別の小節を参照する場合は、MEASURE()関数を使用します。集計ロジックを手動で繰り返さないでください。例えば、両方の値に対するメジャーが既に存在する場合は、SUM(a) / COUNT(b)を避けてください。 - 読みやすさを優先する ：明確な数式を用いてメジャーを作成する。例えば、
MEASURE(gross_profit) / MEASURE(total_revenue)は単一の複雑な SQL 式よりも分かりやすい。 - Add semantic metadata : Use semantic metadata to format composed measures (for example, percentages or currency) for downstream tools. See Agent metadata in metric views.