Utilizar medidas da janela em vistas métricas

As medidas de janelas permitem definir medidas com agregações por janela, cumulativas ou semi-aditivas nas suas visualizações de métricas. Esses tipos de medidas permitem cálculos mais complexos, como médias móveis, alterações de período ao longo do período e totais em execução. Esta página inclui exemplos práticos demonstrando como trabalhar com dimensões de janela em visões métricas.

Definir uma medida de janela

As medidas em janelas permitem definir medidas com agregações que podem ser em janelas, cumulativas ou semi-aditivas. Uma medida de janela inclui os seguintes valores necessários:

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

• intervalo: Define a faixa da janela, como à direita, 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 pelo número especificado de unidades de tempo, como trailing 3 months. Isso 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.

• semiadditivo: Especifica como resumir a medida quando o campo de ordem não está incluído na consulta.GROUP BY Os valores possíveis incluem first e last.

Exemplo de medida de janela de arrasto, móvel ou de avanço

O exemplo a seguir calcula uma medida em uma janela de tempo retroativa ou antecipada 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, a seguinte configuração se aplica:

ordem:date especifica que a dimensão da data ordena a janela.

intervalo: A trailing 7 day especificação define o período de 7 dias como os 7 dias anteriores a cada data, excluindo a data em si.

semiadditivo:last indica que é usado o último valor da janela de 7 dias.

Exemplo de medida da janela ponto a ponto

Calcule a alteração 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, as seguintes condições se aplicam:

• Dois intervalos de tempo são usados: um para calcular o total de vendas no dia anterior e outro para o dia atual.

• Uma terceira medida calcula a alteraçã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 realçam as principais partes desta definição:

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

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

semiadditivo:last garante que o último valor cumulativo seja usado ao agregar por dimensões.

Exemplo de medida acumulada até a data

Calcule um total acumulado em um 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 realçam as principais partes desta definição:

• Duas medidas de janela são usadas: uma para a soma cumulativa sobre a date dimensão e outra para limitar a soma ao current ano.

• A soma cumulativa é restrita pela year dimensão para verificar se ela é calculada somente no ano atual.

Exemplo de medida semiadditiva

Calcule uma medida que não deve ser resumida 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 realçam as principais partes desta 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 os dias.

• semiadditivo:last garante que o saldo mais recente seja retornado ao agregar ao longo de vários dias.

Consultar uma medida de janela

Você pode consultar uma exibição de métrica com uma medida de janela como qualquer outra exibição de métrica. O exemplo a seguir consulta uma visão 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