Usar medidas da janela em visões métricas

As medidas de janela permitem definir medidas com agregações em janela, cumulativas ou semiaditivas em suas exibições métricas. Esses tipos de medidas permitem cálculos mais complexos, como médias móveis, variações de período a período e totais correntes. Esta página inclui exemplos práticos que demonstram como trabalhar com medidas de janela em vistas métricas.

Definir uma medida de janela

As medidas de janela permitem definir métricas com agregações em janela, acumulativas ou semiaditivas. Uma medida de janela inclui os seguintes valores necessários:

• Ordem: A dimensão que determina a ordenação da janela.

• intervalo: Define a abrangência da janela, como trailing, cumulativa ou todos os dados. Os valores de intervalo possíveis incluem o seguinte:

  • current: Inclui linhas em que o valor de ordenação da janela é igual ao valor da linha atual.
  • cumulative: Inclui todas as linhas em que o valor de ordenação da janela é menor ou igual ao valor da linha atual.
  • trailing <value> <unit>: Inclui linhas a partir da linha atual, retrocedendo até ao número especificado de unidades de tempo, como trailing 3 months. Isto não inclui a unidade atual. Por exemplo, trailing 3 months exclui o mês atual.
  • leading <value> <unit>: Inclui linhas da linha atual daqui para frente pelo número especificado de unidades de tempo, como leading 7 days.
  • all: Inclui todas as linhas, independentemente do valor da janela.

• semiaditivo: Especifica como resumir a medida quando o campo de ordenação não é incluído na query GROUP BY. Os valores possíveis incluem first e last.

Exemplo de medição de janela deslizante, móvel ou antecipada

O exemplo a seguir calcula uma medida sobre uma janela temporal móvel de atraso ou avanço nos últimos 7 dias.

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

Para este exemplo, aplica-se a seguinte configuração:

Ordem:date Especifica que a dimensão Data ordena a janela.

intervalo: A trailing 7 day especificação define a janela como os 7 dias antes de cada data, excluindo a data em si.

semiaditivo:last indica que o último valor na janela de 7 dias é usado.

Exemplo de medida de janela período a período

Calcule a mudança do período anterior para o período atual.

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

Para este exemplo, aplicam-se as seguintes condições:

• São utilizadas duas medidas de janela: uma para calcular o total de vendas no dia anterior e outra para o dia atual.

• Uma terceira medida calcula a variação percentual (crescimento) entre os dias atuais e anteriores.

Exemplo de medida total cumulativa (em execução)

Calcule um total acumulado de uma medida até cada momento no tempo.

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

Os detalhes a seguir destacam partes fundamentais dessa definição:

Ordem:date garante que a dimensão date ordene a janela.

intervalo:cumulative define a janela como todos os dados até e incluindo cada data.

semiaditivo:last assegura que o último valor acumulado é utilizado ao agregar em dimensões.

Exemplo de medida de período até à data

Calcular um total acumulado em determinado período.

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

Os detalhes a seguir destacam partes fundamentais dessa definição:

• São utilizadas duas medidas de janela: uma para o montante acumulado sobre a date dimensão e outra para limitar a soma ao current ano.

• A soma acumulada year é limitada pela dimensão para verificar se é calculada apenas dentro do ano em curso.

Exemplo de medida semiaditiva

Calcule uma medida que não deve ser somada em uma dimensão específica, como um saldo bancário.

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

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

Os detalhes a seguir destacam partes fundamentais dessa definição:

• Ordem:date assegura que a date dimensão ordena a janela.

• intervalo:current restringe a janela a um único dia, sem agregação entre dias.

• semiaditivo:last garante que o saldo mais recente seja retornado ao agregar em vários dias.

Consultar uma medida de janela

Você pode consultar uma exibição métrica com uma medida de janela como qualquer outra exibição métrica. O exemplo a seguir consulta uma visualização de métrica:

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