Técnicas avançadas para views de métricas
Técnicas avançadas para views de métricas permitem expressar lógica de negócios complexa e reutilizar definições em toda a sua camada semântica. Esta página explica duas dessas técnicas:
- Medidas de janela : para cálculos de série temporal, como médias móveis, totais contínuos e mudanças de período a período.
- Componibilidade : para criar medidas complexas referenciando outras medidas em vez de reescrever sua lógica.
Esta página pressupõe familiaridade com os conceitos básicos de modelagem de view de métricas. Consulte views de métricas do modelo.
Os exemplos desta página usam o dataset de exemplo TPC-H, que modela uma cadeia de suprimentos de atacado. Para mais informações sobre o dataset TPC-H, consulte tpch. Para um tutorial completo usando este dataset com views de métricas, consulte Tutorial: criar uma view de métricas com junções e modelagem de dados.
Medidas da janela
Experimental
Este recurso é Experimental.
As medidas de janela permitem definir medidas com agregações em janela, cumulativas ou semiaditivas em suas views de métricas. Elas oferecem suporte a cálculos como médias móveis, variações entre períodos e totais acumulados.
Você pode adicionar uma medida de janela no editor do Catalog Explorer ou em YAML.
Adicionar uma medida de janela no editor
Na tab UI do editor de view de métricas, clique em + Window ao editar uma medida. A + Window está disponível nos modos Builder e Custom . As opções de window no editor correspondem aos campos YAML descritos em Definir uma medida de window.
Para obter mais informações sobre como criar e editar medidas, consulte Criar view de métricas.
Defina uma medida da janela
Uma medida de janela inclui os seguintes campos obrigatórios:
-
ordem : O campo que determina a ordenação da janela.
-
**range**: Define a extensão da janela. Os valores compatíveis incluem
current,cumulative,trailing,leadingeall. Para a sintaxe e descrições completas, consulte Valoresrangecompatíveis. Para obter detalhes sobre os modificadoresinclusiveeexclusiveemtrailingeleading, consulte Incluir ou excluir a linha âncora. -
semiadditive : Especifica como agregar a medida quando o campo de ordem não está incluído no
GROUP BYda query. Valores possíveis:firstelast.
Uma medida de janela também suporta o seguinte campo opcional:
- deslocamento : Move o quadro da janela para trás ou para frente ao longo do campo
orderpor um intervalo fixo. Use isso para medidas entre períodos, como mês a mês ou ano a ano. Para sintaxe, unidades compatíveis e restrições, consulte Medidas de janela.
Como offset altera o quadro da janela
Consulte disponibilidade do recurso de view de métricas para requisitos mínimos de compute e versão de especificação YAML.
O campo range define a forma da janela em relação à linha âncora, e offset desliza esse quadro pelo intervalo especificado ao longo de order. A tabela a seguir mostra o quadro para cada valor range com e sem um offset de k, em relação à linha âncora t:
intervalo | Quadro sem deslocamento | Quadro com |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
| partição inteira | partição inteira (inalterada) |
offset é independente de semiadditive. A escolha first ou last ainda controla como a medida é agrupada quando order não está na GROUP BY da query.
Para obter os melhores resultados, combine offset com o grão natural de order. Para dados mensais, offset: -12 month é preferível a offset: -365 day porque a aritmética de mês e ano respeita meses de duração variável e anos bissextos, enquanto a aritmética day não.
Incluir ou excluir a linha âncora
Consulte disponibilidade do recurso de view de métricas para requisitos mínimos de compute e versão de especificação YAML.
Para intervalos trailing e leading, a palavra-chave opcional inclusive ou exclusive controla se o valor da janela da linha de ancoragem (por exemplo, hoje) faz parte da janela contínua:
Palavra-chave | Significado | Ancorar linha no intervalo? |
|---|---|---|
| n unidades incluindo a linha âncora. | Sim |
| *n* unidades **não incluindo** a linha âncora. | Não |
O exemplo a seguir mostra como inclusive e exclusive afetam a janela contínua para a data âncora 2025-01-05 com trailing 3 day.
Assuma que os dados subjacentes têm uma linha por dia com os seguintes valores:
Data | Valor |
|---|---|
|
|
|
|
|
|
|
|
Cada modificador seleciona três dias de linhas em relação à âncora e soma seus valores:
Modificador | Datas na janela | Valores | Soma |
|---|---|---|---|
|
|
|
|
|
|
|
|
leading intervalos seguem a mesma lógica na direção oposta.
Exemplo de medida de janela final, em movimento ou inicial
O exemplo a seguir calcula uma contagem contínua de 7 dias de clientes que fizeram pedidos. Esta métrica rastreia as tendências de engajamento do cliente ao longo do tempo, mostrando quantos clientes distintos fizeram compras na semana anterior a cada data.
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
Para este exemplo, a seguinte configuração se aplica:
order: dateespecifica que o campodateordena a janela.range: trailing 7 daydefine a janela como os 7 dias anteriores a cada data, excluindo a própria data.semiadditive: lastretorna o último valor na janela de 7 dias quandodatenão é uma coluna de agrupamento.
Crie a view de métrica usando SQL
Para criar esta view de métricas fora do Catalog Explorer, envolva o YAML em CREATE OR REPLACE VIEW ... WITH METRICS LANGUAGE YAML AS e coloque a definição entre os delimitadores $$:
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
$$
As outras definições completas nesta página seguem o mesmo padrão.
Exemplo de medida de janela de período a período
O exemplo a seguir calcula o crescimento de vendas diário comparando a receita de hoje (soma de todos os preços de pedidos) com a receita de ontem. Esta métrica identifica as tendências de vendas diárias e mostra a variação percentual na receita.
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
Para este exemplo, a seguinte configuração se aplica:
- O exemplo usa duas medidas de janela: uma para calcular as vendas totais no dia anterior e uma para o dia atual.
- Uma terceira medida calcula a variação percentual (crescimento) entre os dias atual e anterior.
Exemplo de medida de janela ano a ano usando offset
O modificador offset é o elemento construtivo para medidas de variação entre períodos. Defina uma cópia deslocada de uma medida base e, em seguida, componha as duas para expressar deltas, proporções ou taxas de crescimento diretamente na view de métricas.
O exemplo a seguir calcula o crescimento de ventas ano a ano comparando as ventas de cada mês com as ventas do mesmo mês do ano anterior. A medida defasada usa offset: -12 month para retroceder 12 meses no campo month.
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)
Para este exemplo, a seguinte configuração se aplica:
monthly_salesé a medida base, somando as vendas para o mês atual.monthly_sales_pyé a mesma medida deslocada 12 meses para trás usandooffset: -12 month. Para janeiro de 2025, ele retorna o valor para janeiro de 2024.yoy_growtheyoy_growth_pctcompõem as duas medidas para expressar a alteração absoluta e percentual. UsarNULLIFevita erros de divisão por zero quando o valor do ano anterior é zero.
Exemplo de medida cumulativa (em execução) total
O exemplo a seguir calcula a receita de vendas cumulativa desde o início do dataset até cada data. Este total acumulado mostra quanta receita total foi gerada ao longo do tempo, útil para o acompanhamento do progresso em relação às metas anuais de receita ou para a análise de padrões de crescimento de longo prazo.
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
Para este exemplo, a seguinte configuração se aplica:
order: dateordena a janela cronologicamente.range: cumulativedefine a janela como todos os dados desde o início do dataset até e incluindo cada data.semiadditive: lastretorna o valor cumulativo mais recente quandodatenão está incluído naGROUP BYda query, em vez de somar em todas as datas.
Exemplo de medida acumulada até a data
O exemplo a seguir calcula a receita de vendas acumulada no ano (YTD). Esta medida mostra a receita cumulativa gerada a partir de 1º de janeiro de cada ano até a data atual, redefinindo-se no início de cada novo ano.
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
Para este exemplo, a seguinte configuração se aplica:
- O exemplo usa duas especificações de janela: uma para a soma cumulativa sobre o campo
datee outra para limitar a soma ao anocurrent. - O campo
yearrestringe a soma cumulativa para que ela se Reset no início de cada novo ano. - Os campos
montheyearformam uma hierarquia de data no campo de ordemdate: cada um é definido no campodatepelo nome, e não na coluna subjacenteo_orderdate, assim, as queries podem agrupar esta medida por eles. Consulte Agrupar por um campo de hierarquia de data.
Exemplo de medida semiaditiva
O exemplo a seguir calcula os saldos da account, que não devem ser somados em diferentes datas (não é possível adicionar o saldo de segunda-feira ao saldo de terça-feira para obter o saldo total). Em vez disso, ao agregar em vários dias, a medida retorna o saldo mais recente. No entanto, a medida ainda pode ser somada entre clientes para mostrar o saldo total em todas as contas em um determinado dia.
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
Para este exemplo, a seguinte configuração se aplica:
order: dateordena a janela cronologicamente.range: currentrestringe a janela a um único dia, sem agregação entre dias.semiadditive: lastretorna o saldo mais recente ao agregar em vários dias.
Esta medida de janela ainda soma todos os clientes para obter o saldo geral por dia.
Query uma medida de janela
Você pode query uma view de métricas com uma medida de janela como qualquer outra view de métricas. Uma medida de janela é computada junto com seu campo order, então uma query que divide os resultados ao longo do tempo deve referenciar esse campo, seja diretamente ou através de um campo de hierarquia de data definido nele. Quando a query não referencia o campo de ordem, a palavra-chave semiadditive determina o valor retornado, conforme descrito em Exemplo de medida semiaditiva.
O exemplo a seguir agrupa uma medida de janela por state e por uma expressão de mês sobre o campo de ordenação 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
Agrupar por um campo de hierarquia de data
Uma hierarquia de datas agrega o campo de ordem em granularidades mais grossas, como semana, mês ou ano. Defina cada nível como um campo sobre o campo de ordenação pelo nome, não sobre a coluna de origem subjacente:
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)
Agrupar uma medida de janela por um nível de hierarquia retorna a medida nesse nível de detalhe. Supondo que o exemplo de período até a data seja criado como ytd_metric_view, assim como no exemplo de medida de período até a data, a seguinte query retorna o valor YTD a partir da última data de cada mês:
SELECT month, MEASURE(ytd_sales) AS ytd_sales
FROM ytd_metric_view
GROUP BY month
ORDER BY month;
Definir um nível de hierarquia na coluna de origem subjacente, como DATE_TRUNC('MONTH', o_orderdate), quebra seu Link para o campo de ordem date, mesmo que as expressões pareçam equivalentes. Agrupar uma medida de janela por um campo desse tipo retorna resultados incorretos.
Componibilidade
View de métricas são combináveis. Você pode criar novos campos e medidas que referenciam os existentes, em vez de reescrever a lógica do zero. Isso reduz a duplicação e torna as definições complexas de métricas mais fáceis de manter.
A composabilidade funciona em dois níveis: dentro de uma única view de métricas e entre views de métricas quando uma view de métricas é usada como fonte para outra.
A Composability oferece suporte aos seguintes padrões de referência:
- Campos anteriores em novos campos.
- Campos e medidas anteriores em novas medidas.
- Campos de view de métricas usados como fonte em novos campos.
- Campos e medidas de view de métricas usados como fonte em novas medidas.
Definir medidas com componibilidade
Na seção measures, você pode fazer referência a medidas da view de métricas de origem ou a medidas definidas anteriormente na mesma view de métricas. Essa abordagem melhora a consistência, a capacidade de auditoria e a manutenção da sua camada semântica.
Tipo de medida | Descrição | Exemplo |
|---|---|---|
Atômico | Uma agregação simples e direta em uma coluna de origem. Estes formam os blocos de construção. |
|
Composto | Uma expressão que combina matematicamente uma ou mais outras medidas usando a função |
|
Exemplo: Valor médio do pedido (AOV)
O exemplo a seguir define o Valor Médio do Pedido (AOV) usando duas medidas atômicas: total_revenue (soma dos preços do pedido) e order_count (número de pedidos). A medida avg_order_value faz referência a ambas as medidas atômicas.
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)
Se a definição total_revenue mudar (por exemplo, para excluir impostos), avg_order_value usará automaticamente a definição atualizada.
Componibilidade com lógica condicional
É possível usar a componibilidade para criar proporções complexas, porcentagens condicionais e taxas de crescimento sem depender de funções de janela para cálculos simples de período a período.
Exemplo: Taxa de cumprimento
O exemplo a seguir calcula a taxa de cumprimento: a porcentagem de pedidos com status 'F' (cumpridos). A medida divide os pedidos cumpridos pelo total de pedidos.
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
Práticas recomendadas para componibilidade
- Defina as medidas atômicas primeiro : Estabeleça as medidas fundamentais (
SUM,COUNT,AVG) antes de definir as medidas que as referenciam. - Use
MEASURE()para referências : use a funçãoMEASURE()ao fazer referência a outra medida em umexpr. Evite repetir a lógica de agregação manualmente. Por exemplo, eviteSUM(a) / COUNT(b)se medidas para ambos os valores já existirem. - Priorize a legibilidade : Componha medidas usando fórmulas matemáticas claras. Por exemplo,
MEASURE(gross_profit) / MEASURE(total_revenue)é mais claro do que uma única expressão SQL complexa. - Adicionar metadados semânticos : Use metadados semânticos para formatar medidas compostas (por exemplo, porcentagens ou moeda) para ferramentas downstream. Consulte Metadados de agente em views de métricas.