O que são cálculos personalizados?

Cálculos personalizados permitem definir métricas e transformações dinâmicas sem modificar consultas de conjunto de dados. Este artigo explica como usar cálculos personalizados em painéis de IA/BI.

Por que usar cálculos personalizados?

Cálculos personalizados permitem criar e visualizar novos campos de conjuntos de dados de dashboard existentes sem alterar o SQL de origem. Você pode definir até 200 cálculos personalizados por conjunto de dados.

Cálculos personalizados são um dos seguintes tipos:

• Medidas calculadas: valores agregados, como vendas totais ou custo médio. Medidas calculadas podem usar o AGGREGATE OVER comando para calcular valores entre intervalos de tempo.
• Dimensões calculadas: valores ou transformações não agregados, como categorizar faixas etárias ou formatar cadeias de caracteres.

Cálculos personalizados se comportam de forma semelhante às exibições de métrica, mas têm como escopo o conjunto de dados e o painel em que são definidos. Para definir métricas personalizadas que podem ser usadas com outros ativos de dados, consulte visões de métricas do Unity Catalog.

Criar métricas dinâmicas com medidas calculadas

Suponha que você tenha o seguinte conjunto de dados:

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

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

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

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

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

Definir medidas calculadas em um intervalo

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

O AGGREGATE OVER comando pode incorporar dados fora do grupo ou partição atual. Por exemplo, se uma visualização agrupar dados por dia, um intervalo final de 7 dias permite que o ponto de dados de cada dia inclua 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 em cada mês.

Usando o mesmo conjunto de dados do exemplo anterior, a expressão a seguir calcula a margem de lucro média móvel de 7 dias.

(
  (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.

Sintaxe de AGGREGATE OVER

O AGGREGATE OVER comando requer a seguinte sintaxe:

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

Arguments

• expr

  Uma expressão válida de medida calculada para ser avaliada

• campo (obrigatório)

  Um nome de coluna válido

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

  • CURRENT
  • CUMULATIVE
  • ALL
  • (TRAILING|LEADING) unidade numérica
    • número é um inteiro
    • unidade é DAY, MONTHou 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.

AGGREGATE OVER em comparação com as funções de janela

O AGGREGATE OVER comando é semelhante às funções de janela no SQL, que geralmente são usadas para calcular métricas. No entanto, ao contrário das funções de janela, você pode usar AGGREGATE OVER sem especificar um campo de partição na expressão. Em vez disso, 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 do produto adicionando a medida ao eixo y de uma visualização e linha do produto ao eixo x.

Se o ORDER BY campo 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 semi-aditivas, poderá reconhecê-la como equivalente ao last comportamento semi-aditivo.

Definir valores não agregados com dimensões personalizadas

Dimensões calculadas permitem definir valores não agregados ou transformações leves sem alterar o conjunto de dados de origem. Isso é útil quando você deseja organizar ou reformatar dados para visualização.

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

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 pequenos conjuntos de dados (≤100.000 linhas e ≤100MB), os cálculos são executados no navegador para obter uma capacidade de resposta mais rápida. Conjuntos de dados maiores são processados pelo SQL Warehouse. Consulte a otimização e o cache do conjunto de dados para obter mais detalhes.

Criar um cálculo personalizado

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

1. Abra um conjunto de dados 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 milhas.

4. (Opcional) No Descrição campo de texto, 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:

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

6. Clique em Criar.

Adicionar cálculos personalizados a uma exibição de métrica

Você pode definir cálculos personalizados baseados em um conjunto de dados criado por uma visão métrica. Somente a Tabela de Resultados e o Esquema são mostrados quando você abre o conjunto de dados. 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 de exibição. Veja as visões métricas do Catálogo Unity.

Para definir uma nova visualização de métrica a partir do editor de dados do painel, consulte Exportar como uma visualização de métrica.

Exibir o esquema

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

As medidas calculadas são listadas na seção Medidas e marcadas por um ícone de medida calculado fx. O valor associado a uma medida calculada é calculado dinamicamente quando você define o GROUP BY em uma visualização. Não é possível ver o valor na tabela de resultados. Dimensões calculadas aparecem na seção Dimensões .

Usar 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 em seu gráfico. Esse comportamento é o mesmo que o funcionamento de dimensões e medidas em visões de métricas, onde a agregação se adapta dinamicamente aos grupos que você define em sua 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áxis
   • Visualização: barra
   • Eixo X:
     • Campo : dropoff_zip
     • Tipo de Escala: categórico
     • Transformação: Nenhuma
   • eixo Y:
     • Custo por milha

A imagem a seguir mostra o gráfico.

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

Editar um cálculo personalizado

Para editar um cálculo:

1. Clique na guia Dados e, em seguida, clique no conjunto de dados associado ao cálculo que você deseja editar.
2. Clique na guia Esquema no painel de resultados.
3. Medidas e Dimensões aparecem na lista de campos do conjunto de dados. 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. Então clique em Atualizar.

Excluir um cálculo personalizado

Para excluir um cálculo:

1. Clique na guia Dados e clique no conjunto de dados associado à medida que você deseja editar.
2. Clique na guia Esquema no painel de resultados.
3. A seção Medidas aparece sob a lista de campos. Clique no Menu de 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 conjunto de dados.
• Expressões que fazem referência a tabelas externas ou fontes de dados não têm suporte e podem falhar ou retornar resultados inesperados.

Funções com suporte

Para obter uma referência completa de todas as funções com suporte para cálculos personalizados, consulte referência de função de cálculo personalizado. A tentativa de usar uma função sem suporte resulta em um erro.

Exemplos

Os exemplos a seguir demonstram usos comuns para cálculos personalizados. Cada cálculo personalizado aparece no esquema do conjunto de dados na guia de 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 samples.nyctaxi.trips conjunto de dados e calcula a soma das tarifas para todos os passeios que começam no cep 10103.

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

Construir cadeias de caracteres

Use a CONCAT função para construir um novo valor de cadeia de caracteres. Consulte concat a função e concat_ws a função.

CONCAT(first_name, ' ', last_name)

Datas de formato

Use DATE_FORMAT para formatar cadeias de caracteres de data que aparecem em visualizações.

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