O que são cálculos personalizados?

Os cálculos personalizados permitem definir métricas e transformações dinâmicas sem modificar as consultas dataset . Esta página explica como usar cálculos personalizados em painéis de AI/BI .

Por que usar cálculos personalizados?

Cálculos personalizados permitem que você crie e visualize novos campos a partir de conjuntos de dados de painéis existentes sem alterar o SQL de origem. Você pode definir até 200 cálculos personalizados por dataset.

Os cálculos personalizados são de um dos seguintes tipos:

• Medidas calculadas : Valores agregados, como vendas totais ou custo médio. Medidas calculadas podem usar o comando AGGREGATE OVER para compute valores em intervalos de tempo.
• Dimensões calculadas : valores não agregados ou transformações, como categorização de faixas etárias ou formatação de strings.

Os cálculos personalizados se comportam de forma semelhante à visualização de métricas, mas são limitados ao dataset e ao painel onde são definidos. Para definir métricas personalizadas que podem ser usadas com outros ativos de dados, consulte a visualização de métricasUnity Catalog.

Crie métricas dinâmicas com medidas calculadas

Suponha que você tenha o seguinte dataset:

Item	Região	Preço	Custo	Data
Maçãs	EUA	30	15	2024-01-01
Maçãs	Canadá	20	10	2024-01-01
Laranjas	EUA	20	15	2024-01-02
Laranjas	Canadá	15	10	2024-01-02

Você quer visualizar a margem de lucro por região. Sem cálculos personalizados, você precisaria criar um novo dataset com uma coluna margin :

Região	Margem
EUA	0,40
Canadá	0,43

Embora essa abordagem funcione, o novo dataset é estático e pode suportar apenas uma única visualização. Os filtros aplicados ao dataset original não afetam o novo dataset sem ajustes manuais adicionais.

Com cálculos personalizados, você pode expressar a margem de lucro como uma agregação usando a seguinte fórmula:

SQL

(SUM(Price) - SUM(Cost)) / SUM(Price)

Essa medida é dinâmica. Quando usado em uma visualização, ele se atualiza automaticamente para refletir os agrupamentos da visualização. Por exemplo, a mesma medida acima pode ser usada para visualizar a margem de lucro por Region ou por Item, dependendo do que for selecionado na visualização.

Defina valores não agregados com dimensões calculadas.

As dimensões calculadas permitem que você defina valores não agregados ou transformações leves sem alterar o dataset de origem. Isso é útil quando você deseja organizar ou reformatar dados para visualização.

Por exemplo, para analisar tendências de idade por faixa etária em vez de idades individuais, você pode definir uma dimensão age_group personalizada usando a seguinte expressão:

SQL

CASE
   WHEN age < 18 THEN '<18'
   WHEN age >= 18 AND age < 25 THEN '18–24'
   WHEN age >= 25 AND age < 35 THEN '25–34'
   WHEN age >= 35 AND age < 45 THEN '35–44'
   WHEN age >= 45 AND age < 55 THEN '45–54'
   WHEN age >= 55 AND age < 65 THEN '55–64'
   WHEN age >= 65 THEN '65+'
END

Defina cálculos em uma janela.

Uma tarefa comum em visualizações de dashboards é compute uma agregação em um intervalo, como a soma acumulada das vendas nos últimos sete dias. Os cálculos personalizados suportam essa funcionalidade por meio de funções de janela, que permitem realizar cálculos em um conjunto de linhas (uma "janela") relacionadas à linha atual.

Os painéis de AI/BI suportam dois tipos de funções de janela:

• Funções de janela escalares, que agregam dados em agrupamentos fixos e se comportam como funções escalares. Quando usados isoladamente, eles formam dimensões calculadas.
• Funções de janela agregadas, que agregam agrupamentos dinâmicos e se comportam como funções agregadas. Quando utilizados, eles formam medidas calculadas.

As funções de janela também são a base para expressões de nível de detalhe, que permitem controlar a granularidade da agregação independentemente dos agrupamentos da sua visualização.

Funções de janela escalar

As funções de janela escalar usam o operador OVER com cláusulas opcionais PARTITION BY e ORDER BY para compute agregações entre linhas relacionadas antes que qualquer agrupamento de visualização tenha ocorrido. Eles se agregam em um conjunto estático de partições definidas na própria função de janela, antes de serem reunidos à tabela subjacente não transformada como uma dimensão.

Exemplo de cálculo do total de vendas por região:

SQL

SUM(sales) OVER (PARTITION BY Region)

Exemplo de cálculo de vendas cumulativas por região:

SQL

SUM(sales) OVER (PARTITION BY Region ORDER BY Date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

OVER sintaxe

SQL

<AGGREGATE_FUNCTION>(<column>) OVER (
  [PARTITION BY <dimensions>]
  [ORDER BY <column>]
  [ROWS|RANGE frame_specification]
)

Consulte a seção "Funções de janela" na referência da linguagem SQL para obter mais detalhes.

Funções de janela agregadas

As funções de janela agregada usam o operador AGGREGATE OVER para compute agregações em janelas após a aplicação do agrupamento de visualização. Ao contrário das funções de janela escalares, elas não aceitam uma cláusula PARTITION BY porque os grupos a serem agregados são herdados automaticamente da visualização na qual a expressão é usada. O campo ORDER BY permite agregar entre grupos relacionados.

Usando o mesmo dataset do exemplo anterior, a seguinte expressão calcula a margem de lucro média dos últimos sete dias usando o operador AGGREGATE OVER .

SQL

(
  (SUM(Price) - SUM(Cost)) / SUM(Price)
) AGGREGATE OVER (
  ORDER BY Date TRAILING 7 DAY
)

Após a criação, essa medida pode ser aplicada em qualquer visualização.

AGGREGATE OVER sintaxe

SQL

<AGGREGATE_EXPRESSION> AGGREGATE OVER (
  ORDER BY <column> <frame_specification>
)

A especificação do quadro pode ser uma das seguintes:

• CURRENT
• CUMULATIVE
• ALL
• (TRAILING|LEADING) <number> <unit>
  • <number> é um número inteiro positivo
  • <unit> é DAY, MONTH ou YEAR
  • exemplo: TRAILING 7 DAY ou LEADING 1 MONTH

A tabela a seguir identifica como a especificação de quadro para agregação se compara à cláusula de quadro de janela SQL equivalente.

Especificação do quadro	Cláusula de estrutura de janela SQL equivalente
CURRENT	RANGE BETWEEN CURRENT ROW AND CURRENT ROW
ALL	RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
CUMULATIVE	RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
TRAILING <number> <unit>	RANGE BETWEEN <number> <unit> PRECEDING and 1 <unit> PRECEDING
LEADING <number> <unit>	RANGE BETWEEN 1 <unit> FOLLOWING and <number> <unit> FOLLOWING

Se o campo ORDER BY não estiver agrupado na visualização, AGGREGATE OVER assume o valor agregado da última linha como o valor a ser exibido para cada grupo. Isso é equivalente ao comportamento semi-aditivo "final".

OVER versus AGGREGATE OVER

A principal diferença entre OVER e AGGREGATE OVER é que OVER é uma função escalar e AGGREGATE OVER é uma função agregada. OVER requer uma cláusula PARTITION BY para definir grupos, enquanto AGGREGATE OVER herda seus grupos da visualização circundante e pode incorporar dados fora do grupo atual.

Use a sintaxe OVER para:

• Cálculos de janela que precisam ser usados em contextos não agregados, como tabelas.
• Cálculos de janela que devem ignorar todos os agrupamentos e filtros de visualização.
• Agregação em um nível fixo de detalhes: Computar agregações em uma granularidade específica usando PARTITION BY.
• Utilizando funções de classificação e analíticas como ROW_NUMBER, RANK, LAG.

Use a sintaxe AGGREGATE OVER para:

• Cálculos de janela que podem ser usados em diversos contextos de agrupamento ou que precisam incorporar dados externos ao grupo atual.
• Cálculos de janela que respeitam os filtros de visualização.
• Agregando em um nível de detalhe mais grosseiro do que a visualização: Excluindo dimensões usando o quadro ALL .
• Intervalos baseados em tempo que são robustos a linhas ausentes: Janelas móveis com TRAILING ou LEADING.

Benefícios de desempenho

Cálculos personalizados são otimizados para desempenho. Para conjuntos de dados pequenos (≤100.000 linhas e ≤100 MB), os cálculos são executados no navegador para uma resposta mais rápida. Conjuntos de dados maiores são processados pelo SQL warehouse. Veja otimização de conjuntos de dados e armazenamento em cache para mais detalhes.

Crie um cálculo personalizado

Este exemplo cria uma medida calculada com base no dataset samples.nyctaxi.trips . Ele pressupõe conhecimento geral sobre como trabalhar com painéis AI/BI . Se você não estiver familiarizado com a criação de painéis AI/BI , consulte Criar um painel para começar.

1. Abra um dataset existente ou crie um novo.

2. Clique em Cálculo personalizado .

   

3. Um painel Criar Cálculo é aberto no lado direito da tela. No campo de texto Nome , insira Custo por milha .

4. (Opcional) No campo Descrição , digite "Utiliza o valor da tarifa e a distância da viagem para calcular o custo por milha."

5. No campo Expressão , insira o seguinte:

   SQL

   try_divide(SUM(fare_amount), SUM(trip_distance))

6. Clique em Criar .

Referenciando outros cálculos

Os cálculos personalizados podem fazer referência a outros cálculos personalizados definidos no mesmo dataset. Isso permite criar métricas complexas a partir de cálculos mais simples, promovendo a reutilização e a facilidade de manutenção.

Ao fazer referência a outro cálculo personalizado, use o nome dele diretamente na sua expressão, como se fosse uma coluna no dataset.

Por exemplo, suponha que você tenha criado estas medidas calculadas:

• receita_total : SUM(sale_amount)
• custo_total : SUM(cost_amount)

Você pode criar uma terceira medida calculada que faça referência a ambas:

• margem_de_lucro : (MEASURE(total_revenue) - MEASURE(total_cost)) / MEASURE(total_revenue)

nota

• Você só pode referenciar cálculos no mesmo dataset.
• Referências circulares não são permitidas (o cálculo A não pode referenciar o cálculo B se B referenciar A).
• Os cálculos referenciados devem ser criados antes de poderem ser usados em outras expressões.

Adicionar cálculos personalizados a uma viewde métricas

info

Visualização

Este recurso está em Visualização Pública.

Você pode definir cálculos personalizados sobre um dataset criado por uma view de métricas. Somente a Tabela de Resultados e o Esquema são exibidos quando você abre o dataset. Clique em Cálculo personalizado para definir um novo cálculo personalizado. Para definir métricas personalizadas adicionais que outros ativos de dados podem usar, faça alterações na definição view . Veja a visualização de métricasUnity Catalog.

Para definir uma nova view de métricas a partir do editor dataset do painel, consulte Exportar como uma viewde métricas.

ver o esquema

Clique na tab Esquema no painel de resultados para view o cálculo personalizado e seu comentário associado.

As medidas calculadas são listadas na seção Medidas e marcadas por um efeito. O valor associado a uma medida calculada é calculado dinamicamente quando você define GROUP BY em uma visualização. Você não pode ver o valor na tabela de resultados. As dimensões calculadas aparecem na seção Dimensões .

Use um cálculo personalizado em uma visualização

Você pode usar a medida calculada Custo por milha criada anteriormente em uma visualização.

As medidas calculadas são agregadas automaticamente em relação às dimensões configuradas no seu gráfico. Esse comportamento é semelhante ao funcionamento das dimensões e medidas na visualização de métricas, onde a agregação se adapta dinamicamente aos agrupamentos definidos na visualização.

1. Clique em Tela . Em seguida, coloque um novo widget de visualização na tela.
2. Use o painel de configuração de visualização para editar as configurações da seguinte maneira:

   • conjunto de dados: dados de táxi

   • Visualização: Barra

   • Eixo X:

     • Campo: dropoff_zip
     • Tipo de escala: Categórica
     • Transformação: Nenhuma

   • Eixo Y:

     • Custo por milha

nota

Visualizações de tabela oferecem suporte a dimensões calculadas, mas não a medidas calculadas.

A imagem a seguir mostra o gráfico.

Visualizações com cálculos personalizados são atualizadas automaticamente quando filtros são aplicados. Por exemplo, adicionar um filtro pickup_zip atualizará a visualização para mostrar apenas dados correspondentes aos valores selecionados.

Editar um cálculo personalizado

Para editar um cálculo:

1. Clique na tab Dados e, em seguida, clique no dataset associado ao cálculo que você deseja editar.
2. Clique na tab Esquema no painel de resultados.
3. Medidas e dimensões aparecem na lista de campos dataset . Clique no menu kebab à direita do cálculo que você deseja editar. Em seguida, clique em Editar .
4. No painel Editar cálculo personalizado , atualize os campos de texto que você deseja editar. Em seguida, clique em Atualizar .

Excluir um cálculo personalizado

Para excluir um cálculo:

1. Clique na tab Dados e, em seguida, clique no dataset associado à medida que você deseja editar.
2. Clique na tab Esquema no painel de resultados.
3. A seção Medidas aparece abaixo da lista de campos. Clique no menu kebab à direita do cálculo que você deseja editar. Em seguida, clique em Excluir .
4. Clique em Excluir na caixa de diálogo Excluir que aparece.

Limitações

Para usar cálculos personalizados, o seguinte deve ser verdadeiro:

• As colunas usadas na expressão devem pertencer ao mesmo dataset.
• Expressões que fazem referência a tabelas externas ou fonte de dados não são suportadas e podem falhar ou retornar resultados inesperados.

Funções suportadas

Para obter uma referência completa de todas as funções suportadas para cálculos personalizados, consulte Referência de função de cálculo personalizado. Tentar usar uma função não suportada resulta em erro.

Exemplos

Os exemplos a seguir demonstram usos comuns de cálculos personalizados. Cada cálculo personalizado aparece no esquema do dataset na tab dados. Na tela, você pode escolher o cálculo personalizado como um campo.

Filtrar e agregar dados condicionalmente

Use uma instrução CASE para agregar dados condicionalmente. O exemplo a seguir usa o dataset samples.nyctaxi.trips e calcula a soma das tarifas para todas as viagens que começam no código postal 10103.

SQL

SUM(CASE
  WHEN pickup_zip=10103 THEN fare_amount
  WHEN pickup_zip!=10103 THEN 0
END)

Construir strings

Use a função CONCAT para construir um novo valor de string. Veja a funçãoconcat e a funçãoconcat_ws.

SQL

CONCAT(first_name, ' ', last_name)

Formatar datas

Use DATE_FORMAT para formatar strings de data que aparecem nas visualizações.

SQL

DATE_FORMAT(tpep_pickup_datetime, 'YYYY-MM-dd')