O que são cálculos personalizados?

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

Por que usar cálculos personalizados?

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

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

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

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

Crie 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 oferecer 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)

Esta 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 em visualizações de painel é calcular uma medida, como SUM(sales) em um intervalo, como os últimos 7 dias. Para conjuntos de dados de 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 de 7 dias à direita permitirá 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, esta medida pode ser aplicada em qualquer visualização.

AGGREGATE OVER sintaxe

O AGGREGATE OVER comando requer a seguinte sintaxe:

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

Arguments

• EXPR

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

• 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 número 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 em SQL, que são frequentemente 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 de produto adicionando a medida ao eixo y de uma visualização e a linha de produtos ao eixo x.

Se o ORDER BY campo não estiver agrupado na visualização, AGGREGATE OVER tomará o valor agregado da última linha como o valor a ser exibido para cada grupo. Se você está familiarizado com medidas semi-aditivas, você pode reconhecer isso como equivalente ao last comportamento semi-aditivo.

Definir valores não agregados com dimensões personalizadas

As 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

Os cálculos personalizados são otimizados para desempenho. Para conjuntos de dados pequenos (≤100.000 linhas e ≤100MB), os cálculos são executados no navegador para uma resposta mais rápida. Conjuntos de dados maiores são processados pelo SQL warehouse. Consulte Otimização e cache de conjuntos 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. Pressupõe conhecimentos gerais sobre como trabalhar com dashboards de IA/BI. Se você não estiver familiarizado com a criação de painéis de IA/BI, consulte Criar um painel 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 milha.

4. (Opcional) No campo de texto Descrição, digite "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 vista métrica

Você pode definir cálculos personalizados em cima de um conjunto de dados criado por uma vista de métricas. 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. Consulte Visualizações métricas do Catálogo Unity.

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

Ver 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 calculada 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. As dimensões calculadas aparecem na seção Dimensões .

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

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

As medidas calculadas são agregadas automaticamente em relação às dimensões configuradas no gráfico. Esse comportamento é o mesmo que as dimensões e medidas funcionam em exibições métricas, onde a agregação se adapta dinamicamente aos agrupamentos definidos na visualização.

1. Clique em Canvas. 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 do táxi
   • Visualização: Gráfico de barras
   • eixo X:
     • Campo: dropoff_zip
     • Tipo de escala: Categórica
     • Transform: Nenhum
   • 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, adicionar 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 à direita do cálculo que pretende editar. Em seguida, clique em Editar.
4. No painel Editar cálculo personalizado , atualize os campos de texto que deseja editar. Em seguida, clique em Atualizar.

Excluir um cálculo personalizado

Para excluir um cálculo:

1. Clique na guia Dados e, em seguida, clique no conjunto de dados associado à medida que você deseja editar.
2. Clique na guia Esquema no painel de resultados.
3. A secção Medidas aparece por baixo da lista de campos. Clique no à direita do cálculo que pretende editar. Em seguida, clique em Excluir.
4. Clique em Eliminar na caixa de diálogo Eliminar 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 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 personalizada. A tentativa de usar uma função sem suporte resulta em um erro.

Examples

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 CASE instrução para agregar dados condicionalmente. O exemplo a seguir usa o samples.nyctaxi.trips conjunto de dados e calcula a soma das tarifas para todas as viagens 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. Ver concat função e concat_ws função.

CONCAT(first_name, ' ', last_name)

Formatar datas

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

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