O que são cálculos personalizados?

Cálculos personalizados permitem que você defina métricas e transformações dinâmicas sem modificar consultas dataset . Este artigo 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)

Esta medida é dinâmica. Quando usado em uma visualização, ele é atualizado automaticamente para refletir os filtros aplicados ao dataset.

Definir medidas calculadas em um intervalo

Uma tarefa comum em visualizações de painel é compute uma medida, como SUM(sales) em um intervalo, como os últimos 7 dias. Para o conjunto de dados do painel, use o comando AGGREGATE OVER para definir esses tipos de cálculos de medidas baseados em intervalo.

O comando AGGREGATE OVER pode incorporar dados fora do grupo ou partição atual. Por exemplo, se uma visualização agrupa dados por dia, um intervalo de 7 dias permite que os pontos de dados de cada dia incluam dados dos 6 dias anteriores. Se a mesma medida for usada em uma visualização agrupada por mês, o intervalo de 7 dias usará apenas os últimos 7 dias de cada mês.

Usando o mesmo dataset do exemplo anterior, a expressão a seguir calcula a margem de lucro média dos últimos 7 dias.

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

O comando AGGREGATE OVER requer a seguinte sintaxe:

{expr} AGGREGATE OVER (ORDER BY {field} {frame})

Argumentos

• expr

  Uma expressão de medida calculada válida para avaliar

• campo (obrigatório)

  Um nome de coluna válido

• quadro (obrigatório) Pode ser um dos seguintes:

  • CURRENT
  • CUMULATIVE
  • ALL
  • (TRAILING|LEADING) unidade numérica
    • número é um inteiro
    • unidade é 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
ATUAL	INTERVALO ENTRE LINHA ATUAL E LINHA ATUAL
TODOS	INTERVALO ENTRE O ANTERIOR ILIMITADO E O SEGUINTE ILIMITADO
CUMULATIVO	INTERVALO ENTRE A LINHA ANTERIOR E A LINHA ATUAL ILIMITADA
RASTREANDO <NUMBER> <UNIT>	INTERVALO ENTRE <NUMBER> <UNIT> ANTERIOR e 1 <UNIT> ANTERIOR
PRINCIPAL <NUMBER> <UNIT>	INTERVALO ENTRE 1 <UNIT> SEGUINDO e <NUMBER> <UNIT> SEGUINDO

AGGREGATE OVER comparado às funções de janela

O comando AGGREGATE OVER é semelhante às funções de janela em SQL, que são frequentemente usadas para calcular métricas. Entretanto, diferentemente das funções de janela, você pode usar AGGREGATE OVER sem especificar um campo de partição na expressão. Em vez disso, ele herda partições do agrupamento da consulta de visualização. Isso significa que você pode usar essas medidas de forma mais dinâmica. Por exemplo, você pode calcular uma média móvel de 7 dias por linha de produto adicionando a medida ao eixo y de uma visualização e a linha de produto ao eixo x.

Se o campo ORDER BY não estiver agrupado na visualização, AGGREGATE OVER usará o valor agregado da última linha como o valor a ser exibido para cada grupo. Se você estiver familiarizado com medidas semiaditivas, poderá reconhecer isso como equivalente ao comportamento semiaditivo last .

Defina valores não agregados com dimensões personalizadas

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

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 de texto Descrição , insira “Usa 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 .

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 no editor dataset do painel, consulte Criar 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.

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')