Emplear medidas de ventana en vistas métricas

Las medidas de Windows le permiten definir medidas con agregaciones ventana, acumulativas o semiaditivas en sus vistas de métricas. Estos tipos de medidas permiten cálculos más complejos, como medias móviles, cambios de período a período y totales en ejecución. En esta página se incluyen ejemplos prácticos que muestran cómo manejar las medidas de ventana en vistas métricas.

Definición de una medida de ventana

Las medidas de intervalo permiten definir medidas con agregaciones de intervalo, acumulativas o semisumativas. Una medida de ventana incluye los siguientes valores necesarios:

• orden: Dimensión que determina la ordenación de la ventana.

• rango: Define la extensión de la ventana, como retrasado, acumulativo o todos los datos. Entre los valores de intervalo posibles se incluyen los siguientes:

  • current: incluye filas en las que el valor de ordenación de ventanas es igual al valor de la fila actual.
  • cumulative: incluye todas las filas en las que el valor de ordenación de ventanas es menor o igual que el valor de la fila actual.
  • trailing <value> <unit>: incluye filas de la fila actual que retroceden por el número especificado de unidades de tiempo, como trailing 3 months. Esto no incluye la unidad actual. Por ejemplo, trailing 3 months excluye el mes actual.
  • leading <value> <unit>: Incluye filas de la fila actual hacia adelante según el número especificado de unidades de tiempo, como leading 7 days.
  • all: incluye todas las filas independientemente del valor de la ventana.

• semiaditivo: Especifica cómo resumir la medida cuando el campo order no se incluye en la consulta.GROUP BY Los valores posibles incluyen first y last.

Ejemplo de medida de ventana retrasada, móvil o de adelanto

En el ejemplo siguiente se calcula una medida en un período de tiempo final o inicial en los últimos 7 días.

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

En este ejemplo, se aplica la siguiente configuración:

orden:date especifica que la dimensión de fecha ordena la ventana.

rango: La trailing 7 day especificación define la ventana como los 7 días anteriores a cada fecha, excepto la propia fecha.

semiaditivo:last indica que se utiliza el último valor de la ventana de 7 días.

Ejemplo de medida de ventana de período a período

Calcule el cambio del período anterior al período actual.

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

En este ejemplo, se aplican las condiciones siguientes:

• Se utilizan dos ventanas temporales: una para calcular las ventas totales del día anterior y otra para el día actual.

• Una tercera medida calcula el cambio porcentual (crecimiento) entre los días actuales y anteriores.

Ejemplo de medida total acumulado (en ejecución)

Calcule un total acumulado de una medida hasta cada punto en el tiempo.

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

Los detalles siguientes resaltan las partes clave de esta definición:

orden:date garantiza que la date dimensión ordena la ventana.

rango:cumulative define la ventana como todos los datos hasta e incluyendo cada fecha.

semiaditivo:last garantiza que se utilice el último valor acumulativo al agregar sobre dimensiones.

Ejemplo de medida de período a fecha

Calcular un total acumulado en un período determinado.

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

Los detalles siguientes resaltan las partes clave de esta definición:

• Se usan dos medidas de ventana: una para la suma acumulativa sobre la date dimensión y otra para limitar la suma al current año.

• La suma acumulativa está restringida por la year dimensión para comprobar que solo se calcula dentro del año actual.

Ejemplo de medida semiaditiva

Calcule una medida que no se debe sumar sobre una dimensión específica, como un saldo bancario.

dimensions:
  - name: date
    expr: date
  - name: customer
    expr: customer_id

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

Los detalles siguientes resaltan las partes clave de esta definición:

• orden:date garantiza que la date dimensión ordena la ventana.

• rango:current restringe la ventana a un solo día sin agregación a lo largo de los días.

• semiaditivo:last asegura que al agregar a lo largo de varios días, se devuelve el saldo más reciente.

Consultar una medida de ventana

Puede consultar una vista de métricas con una medida de ventana como cualquier otra vista de métrica. En el ejemplo siguiente se consulta una vista de métricas:

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