Pular para o conteúdo principal
Última atualização em

Use expressões de nível de detalhe (LOD) em visualizações de métricas

Expressões de nível de detalhe (LOD) permitem especificar a granulosidade na qual calcular agregações, independentemente dos campos (também chamados dimensões) em sua query. Isto permite computar métricas como percentual do total, onde o denominador agrega em uma granulosidade mais grosseira do que a query.

O que são expressões de nível de detalhe?​

As expressões de nível de detalhe permitem especificar exatamente quais campos usar ao calcular um agregado, independentemente dos campos presentes na sua query. Isso proporciona um controle granular sobre o escopo de seus cálculos.

There are three types of level of detail expressions:

  • Nível de detalhe fixo : Agregue sobre um conjunto predefinido de campos especificados na própria expressão, ignorando outros campos na query.
  • **Nível de detalhe mais grosseiro**: agregue em uma granularidade mais grosseira do que a query, excluindo campos específicos do agrupamento.
  • Nível de detalhe mais refinado : faça a agregação em uma granularidade mais refinada do que a da query incluindo campos adicionais e, em seguida, reaja o resultado de volta para a granularidade da query.

Nível de detalhe fixo​

Uma expressão de nível de detalhe fixo calcula um agregado em uma granularidade que você define, ignorando os campos em sua query. Em views de métricas, as expressões LOD fixas são definidas diretamente no campo expr de uma definição de coluna usando funções de janela SQL com cláusulas PARTITION BY.

Quando usar um nível fixo de detalhe​

Use expressões de nível de detalhe fixo quando precisar do seguinte:

  • Sem dependência de agrupamentos de query : Métricas com particionamento estático em todos os usos.
  • Agregados em nível de dataset : agregados globais comparados com agrupamentos em nível de linha (por exemplo, porcentagem do total de vendas por prioridade).
  • **Hierarquias multinível**: métricas de nível de detalhe e de rollup disponíveis na mesma view de métricas.

Sintaxe​

Expressões LOD fixas usam funções de janela SQL para calcular agregações em uma granularidade definida. Coloque a função de janela diretamente no campo expr de uma definição de campo:

YAML
fields:
  - name: <lod_name>
    expr: <AGGREGATE_FUNCTION>(<column>) OVER (PARTITION BY <dim1>, <dim2>, ...)

Para agregar sobre o dataset inteiro, omita a cláusula PARTITION BY e deixe parênteses vazios após OVER.

Exemplo: vendas totais por prioridade de pedido​

Suponha que você queira definir uma view de métricas onde as vendas de cada pedido possam ser comparadas com as vendas totais de seu grupo de prioridade. O exemplo a seguir computa priority_total_price na query de origem e o expõe como um campo de identidade:

YAML
version: 1.1

source: samples.tpch.orders

fields:
  - name: order_priority
    expr: o_orderpriority
  - name: order_date
    expr: o_orderdate
  - name: priority_total_price
    expr: SUM(o_totalprice) OVER (PARTITION BY o_orderpriority)

measures:
  - name: total_sales
    expr: SUM(o_totalprice)

  - name: pct_of_priority_total
    expr: SUM(o_totalprice) / ANY_VALUE(priority_total_price)

O campo priority_total_price define o total fixo para cada grupo de prioridade diretamente em seu campo expr. A medida pct_of_priority_total divide as vendas de pedidos individuais por esse total fixo para produzir uma porcentagem, independentemente de como a query agrupa os resultados.

nota

Ao referenciar um campo de nível de detalhe fixo em uma expressão de medida, inclua-o em uma função agregada. Use ANY_VALUE quando o valor for constante dentro de um grupo, como no exemplo anterior.

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 $$:

SQL
CREATE OR REPLACE VIEW catalog.schema.sales_by_priority WITH METRICS LANGUAGE YAML AS
$$
  version: 1.1

  source: samples.tpch.orders

  fields:
    - name: order_priority
      expr: o_orderpriority
    - name: order_date
      expr: o_orderdate
    - name: priority_total_price
      expr: SUM(o_totalprice) OVER (PARTITION BY o_orderpriority)

  measures:
    - name: total_sales
      expr: SUM(o_totalprice)

    - name: pct_of_priority_total
      expr: SUM(o_totalprice) / ANY_VALUE(priority_total_price)
$$

O exemplo de nível de detalhe mais grosseiro nesta página segue o mesmo padrão.

Filtragem em expressões de nível de detalhe fixo​

Expressões de nível de detalhe fixo são computadas antes que quaisquer filtros em tempo de query sejam aplicados. Para aplicar um filtro a um cálculo LOD fixo, inclua a condição de filtro dentro da expressão da função de janela usando uma instrução CASE ou uma cláusula FILTER.

Nível de detalhe mais grosseiro​

Uma expressão de nível de detalhe mais grosseiro agrega em uma granularidade mais grosseira do que a query, excluindo um ou mais campos da partição. Em views de métricas, expressões LOD mais grosseiras são implementadas usando medidas de janela com a especificação de intervalo all.

Quando usar um nível de detalhe mais grosseiro​

Use expressões de nível de detalhe mais abrangente quando for necessário:

  • **Agrupamentos dinâmicos**: Agregados que se adaptam a agrupamentos de query (por exemplo, porcentagem do total para qualquer campo selecionado).
  • Agregações Sensíveis a Filtros : Computar em uma granularidade mais grosseira respeitando filtros de tempo de query.

Sintaxe​

Para cada campo a ser excluído da partição, defina uma medida de janela com range: all:

YAML
measures:
  - name: <measure_name>
    expr: <AGGREGATE_EXPRESSION>
    window:
      - order: <field_to_exclude>
        range: all
        semiadditive: last

Para excluir vários campos, adicione uma entrada à matriz window para cada campo.

Exemplo: % do total de vendas​

Para calcular a porcentagem do total de vendas para cada prioridade de pedido:

YAML
version: 1.1

source: samples.tpch.orders

fields:
  - name: order_priority
    expr: o_orderpriority

measures:
  - name: total_sales
    expr: SUM(o_totalprice)

  - name: all_priorities_sales
    expr: SUM(o_totalprice)
    window:
      - order: order_priority
        range: all
        semiadditive: last

  - name: pct_of_total_sales
    expr: SUM(o_totalprice) / MEASURE(all_priorities_sales)

Neste exemplo:

  • total_sales agregações no nível de agrupamento da query.
  • all_priorities_sales usa range: all para calcular um total geral em todas as prioridades de pedido, ignorando o campo order_priority na query.
  • pct_of_total_sales divide as vendas de nível de prioridade pelo total geral para produzir uma porcentagem.

Nível mais refinado de detalhes​

Uma expressão de nível de detalhe mais refinado calcula um agregado em uma granularidade mais refinada do que a sua query e, em seguida, reagrega o resultado de volta ao grão da query. O agregado interno é avaliado nos campos da query mais um ou mais campos adicionais incluídos por você, e um agregado externo consolida esses valores novamente. Nas metric views, expressões LOD mais refinadas são medidas que usam um bloco partition com uma lista include e uma função outer_aggregate.

Quando usar um nível mais refinado de detalhe​

Use expressões de nível de detalhe mais refinado quando precisar:

  • Aggregations of aggregations : Calculate a per-entity aggregate, such as total revenue per customer, and then summarize it, such as the average across customers, at the query's grain.
  • Grain-independent summaries : Compute the inner metric at a finer grain than the query and roll it up automatically as the query grain changes.

Sintaxe​

Defina uma medida com um bloco partition. O bloco partition adiciona campos à granularidade da query para o agregado interno e especifica o agregado que consolida o resultado novamente:

YAML
measures:
  - name: <measure_name>
    expr: <INNER_AGGREGATE_EXPRESSION>
    partition:
      include: [<field1>, <field2>, ...]
      outer_aggregate: <AGGREGATE_FUNCTION>

Nesta sintaxe:

  • expr é a agregação interna. É avaliado na granularidade da consulta mais os campos em include.
  • include lista um ou mais campos para adicionar ao grão da query ao calcular o agregado interno. Cada entrada deve ser um campo definido na view de métricas.
  • outer_aggregate é a função de agregação que consolida os resultados internos de volta para o grão da query.
nota

O outer_aggregate deve ser uma função de agregação de argumento único: SUM, AVG, MIN, MAX, COUNT ou MEDIAN. O COUNT(DISTINCT ...) e funções de vários argumentos, como PERCENTILE, não são aceitos como a agregação externa, embora você possa usá-los em expr. Uma medida de nível de detalhe mais refinado também não pode usar um bloco window, e não pode fazer referência a outra medida refinada ou de janela.

Exemplo: receita média por cliente​

Suponha que você queira a receita média por cliente, resumida no nível de granularidade que sua query usa, como prioridade de pedido. Compute total revenue per customer as the agregação interna, e depois faça a média entre os clientes com a agregação externa:

YAML
version: 1.1

source: samples.tpch.orders

fields:
  - name: order_priority
    expr: o_orderpriority
  - name: customer
    expr: o_custkey

measures:
  - name: total_revenue
    expr: SUM(o_totalprice)

  - name: avg_revenue_per_customer
    expr: SUM(o_totalprice)
    partition:
      include: [customer]
      outer_aggregate: AVG

Neste exemplo:

  • total_revenue agregações no nível de agrupamento da query.
  • avg_revenue_per_customer calcula o compute SUM(o_totalprice) na granularidade (order_priority, customer), fornecendo a receita total de cada cliente dentro de uma prioridade e, em seguida, usa outer_aggregate: AVG para calcular a média desses totais por cliente de volta para a granularidade da query.

Consulte a medida como qualquer outra medida. Os campos include são adicionados ao grão da query apenas enquanto o agregado interno é computado:

SQL
SELECT order_priority, MEASURE(avg_revenue_per_customer)
FROM catalog.schema.orders_mv
GROUP BY order_priority

Each row returns the average per-customer revenue for that order priority.

Recursos adicionais​

Nesta página