Use window measures in metric views
This feature is Experimental.
Window measures enable you to define measures with windowed, cumulative, or semiadditive aggregations in your metric views. These types of measures allow for more complex calculations, such as moving averages, period-over-period changes, and running totals. This page includes practical examples demonstrating how to work with window measures in metric views.
Define a window measure
Window measures enable defining measures with windowed, cumulative, or semi-additive aggregations. A window measure includes the following required values:
-
order: The dimension that determines the ordering of the window.
-
range: Defines the extent of the window, such as trailing, cumulative, or all data. Possible range values include the following:
current
: Includes rows where the window ordering value equals the current row’s value.cumulative
: Includes all rows where the window ordering value is less than or equal to the current row’s value.trailing <value> <unit>
: Includes rows from the current row going backward by the specified number of time units, such astrailing 3 months
. This does not include the current unit. For example,trailing 3 months
excludes the current month.leading <value> <unit>
: Includes rows from the current row going forward by the specified number of time units, such asleading 7 days
.all
: Includes all rows regardless of the window value.
-
semiadditive: Specifies how to summarize the measure when the order field is not included in the query's
GROUP BY
. Possible values includefirst
andlast
.
Trailing, moving, or leading window measure example
The following example calculates a measure over a trailing or leading window of time in the last 7 days.
version: 0.1
source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'
dimensions:
- 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: date
specifies that the date dimension orders the window.
range: The trailing 7 day
specification defines the window as the 7 days before each date, excluding the date itself.
semiadditive: last
indicates the last value in the 7-day window is used.
Period-over-period window measure example
Calculate the change from the previous period to the current period.
version: 0.1
source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'
dimensions:
- 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 conditions apply:
-
Two window measures are used: 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.
Cumulative (running) total measure example
Calculate a running total of a measure up to each point in time.
version: 0.1
source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'
dimensions:
- name: date
expr: o_orderdate
measures:
- name: running_total_sales
expr: SUM(o_totalprice)
window:
- order: date
range: cumulative
semiadditive: last
The following details highlight key parts of this definition:
order: date
ensures that the date
dimension orders the window.
range: cumulative
defines the window as all data up to and including each date.
semiadditive: last
makes sure that the last cumulative value is used when aggregating over dimensions.
Period to date measure example
Calculate a running total in a given period.
version: 0.1
source: samples.tpch.orders
filter: o_orderdate > DATE'1997-01-01'
dimensions:
- 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
The following details highlight key parts of this definition:
-
Two window measures are used: one for the cumulative sum over the
date
dimension and another to limit the sum to thecurrent
year. -
The cumulative sum is restricted by the
year
dimension to check that it is calculated only within the current year.
Semiadditive measure example
Calculate a measure that should not be summed over a specific dimension, such as a bank balance.
dimensions:
- name: date
expr: date
- name: customer
expr: customer_id
measures:
- name: semiadditive_balance
expr: SUM(balance)
window:
- order: date
range: current
semiadditive: last
The following details highlight key parts of this definition:
-
order:
date
ensures thedate
dimension orders the window. -
range:
current
restricts the window to a single day with no aggregation across days. -
semiadditive:
last
ensures that the most recent balance is returned 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. The following example queries a metric view:
SELECT
state,
DATE_TRUNC('month', date),
MEASURE(t7d_distinct_customers) as m
FROM sales_metric_view
WHERE date >= DATE'2024-06-01'
GROUP BY ALL