Pular para o conteúdo principal

O que são cálculos personalizados?

Os cálculos personalizados permitem que o senhor defina métricas e transformações dinâmicas sem modificar as consultas do site dataset. Este artigo explica como usar cálculos personalizados nos painéis do site AI/BI.

Por que usar cálculos personalizados?

Os cálculos personalizados permitem que o senhor crie e visualize novos campos a partir do conjunto de dados do painel existente sem modificar a fonte SQL. Você pode definir dois tipos de cálculos personalizados:

  • Medidas calculadas : Valores agregados, como vendas totais ou custo médio. Estes podem utilizar o comando “ AGGREGATE OVER ” para calcular valores ao longo de 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 às visualizações métricas, mas têm como escopo o site dataset e o painel em que são definidos. Para definir métricas personalizadas que podem ser usadas com outros dados ativos, consulte a visualizaçãoUnity Catalog métricas.

Crie métricas dinâmicas com medidas calculadas

Suponha que o senhor 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ê deseja visualizar a margem de lucro por região. Sem cálculos personalizados, o senhor precisaria criar um novo dataset com uma coluna margin:

Região

Margem

EUA

0,40

Canadá

0,43

Embora essa abordagem funcione, o novo site 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 é atualizado automaticamente para refletir os filtros aplicados ao site dataset.

Defina medidas calculadas em um intervalo

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

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 consecutivos 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á somente os últimos 7 dias de cada mês.

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

SintaxeAGGREGATE OVER

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 uma das seguintes opções:

    • CURRENT
    • CUMULATIVE
    • ALL
    • (TRAILING|LEADING) unidade numérica
      • número é um número inteiro
      • a unidade é DAY, MONTH ou YEAR
      • exemplo: TRAILING 7 DAY ou LEADING 1 MONTH

A tabela a seguir identifica como a especificação do quadro para agregados over se compara à cláusula equivalente do quadro da janela SQL.

Especificação do quadro

Cláusula de moldura de janela SQL equivalente

ATUAL

INTERVALO ENTRE A LINHA ATUAL E A LINHA ATUAL

TODOS

INTERVALO ENTRE PRECEDENTE ILIMITADO E SEGUIMENTO ILIMITADO

CUMULATIVO

INTERVALO ENTRE A LINHA ANTERIOR ILIMITADA E A ATUAL

ARRASTANDO <NUMBER> <UNIT>

INTERVALO ENTRE <NUMBER> <UNIT> ANTERIOR E 1 <UNIT> ANTERIOR

LIDERANDO <NUMBER> <UNIT>

INTERVALO ENTRE 1 <UNIT> FOLLOWING e <NUMBER> <UNIT> FOLLOWING

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

O comando " AGGREGATE OVER " é semelhante às funções de janela no SQL, frequentemente utilizadas para calcular métricas. No entanto, 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, é possível 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 usa o valor agregado da última linha como o valor a ser exibido para cada grupo. Se você estiver familiarizado com medidas semiaditivas, talvez reconheça isso como equivalente ao comportamento semiaditivo last.

Defina valores não agregados com dimensões personalizadas

As dimensões calculadas permitem que o senhor defina valores não agregados ou transformações leves sem alterar a fonte dataset. 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 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

Os 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. Consulte otimização e armazenamento em cache de conjuntos de dados para obter mais detalhes.

Crie um cálculo personalizado

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

  1. Abra um site existente dataset ou crie um novo.

  2. Clique em Cálculo personalizado .

    O botão de cálculo personalizado é destacado no canto superior direito do painel de resultados.

  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 , digite o seguinte:

    SQL
    try_divide(SUM(fare_amount), SUM(trip_distance))
  6. Clique em Criar .

O editor de cálculos personalizados com os valores das instruções preenchidas.

Adicionar cálculos personalizados a uma métrica view

info

Visualização

Esse recurso está em Public Preview.

É possível definir cálculos personalizados em cima de um dataset criado por um métricas view. Apenas 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 dados ativos podem utilizar, altere a definição de “ view ”. Consulte a visualização métricas emUnity Catalog.

Para definir uma nova métrica view no editor do painel dataset, consulte Criar uma métrica view.

visualizar o esquema

Clique no Schema tab 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 Ícone de medida calculada fx. O valor associado a uma medida calculada é calculado dinamicamente quando você define o 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 .

Uma medida calculada aparece no esquema tab.

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

Você pode usar a medida calculada de custo por milha criada anteriormente em uma 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 forma:
    • conjunto de dados: Dados de táxi

    • Visualização: Bar

    • Eixo X:

      • Campo: dropoff_zip
      • Tipo de escala: Categórica
      • Transformar: Nenhuma
    • Eixo Y:

      • Custo por milha
nota

As visualizações de tabela suportam dimensões calculadas, mas não suportam medidas calculadas.

A imagem a seguir mostra o gráfico.

Um gráfico de barras mostrando o custo por milha versus o CEP de entrega.

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

Editar um cálculo personalizado

Para editar um cálculo:

  1. Clique em Data (Dados) tab e, em seguida, clique em dataset associado ao cálculo que o senhor deseja editar.
  2. Clique no Schema tab no painel de resultados.
  3. Medidas e dimensões aparecem na lista de campos dataset. Clique no menu de Ícone do menu Kebab. 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 em Data (Dados) tab e, em seguida, clique em dataset associado à medida que o senhor deseja editar.
  2. Clique no Schema tab no painel de resultados.
  3. A seção Medidas aparece abaixo da lista de campos. Clique no menu de Ícone do menu Kebab. kebab à direita do cálculo que você deseja editar. Em seguida, clique em Excluir .
  4. Clique em Excluir na caixa de diálogo Excluir exibida.

Limitações

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

  • As colunas usadas na expressão devem pertencer ao mesmo dataset.
  • As 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

As tabelas a seguir listam as funções suportadas. A tentativa de usar uma função não suportada resulta em um erro.

Funções agregadas

Todas as medidas calculadas devem ser agregadas. Há suporte para as seguintes operações de agregação:

Agregação

Descrição

avg (expr) ou média (expr)

Retorna a média calculada em uma coluna ou expressão.

contagem (*)

Retorna o número de linhas em um grupo.

contagem (DISTINCT expr)

Retorna o número de linhas exclusivas em um grupo.

soma (expr)

Retorna o total de valores em uma coluna ou expressão.

máximo (expr)

Retorna o valor máximo em uma coluna ou expressão.

min (expr)

Retorna o valor mínimo em uma coluna ou expressão.

percentil (expr, porcentagem [, frequência])

Retorna o valor percentil exato de expr na porcentagem especificada em um grupo.

primeiro (expr [, ignoreNull])

Retorna o primeiro valor de expr para um grupo.

último (expr [, ignoreNull])

Retorna o último valor de expr para o grupo.

contar_se

Retorna a contagem de linhas que satisfazem uma determinada condição.

Mediana

Retorna a mediana de um conjunto de valores.

stddev

Retorna o desvio padrão de um conjunto de valores.

variância

Retorna a variância de um conjunto de valores.

Operações aritméticas

O senhor pode combinar expressões com as seguintes operações aritméticas:

Operação

Descrição

expr 1 + expr 2

Retorna a soma de expr1 e expr2.

expr 1 - expr 2

Retorna a diferença ao subtrair expr2 de expr1.

multiplicador * multiplicando

Retorna o produto de duas expressões.

dividendo/divisor

Retorna o resultado da divisão do dividendo pelo divisor.

- expirar

Retorna o valor negado da expressão.

+ expr

Retorna o valor da expressão.

try_add (expr 1, expr 2)

Adiciona dois valores. Se ocorrer um erro, retorna NULL.

try_subtract (expr 1, expr 2)

Subtrai expr2 de expr1. Se ocorrer um erro, retorna NULL.

try_multiply (multiplicador, multiplicando)

Multiplica dois números. Se ocorrer um erro, retorna NULL.

try_divide (dividendo, divisor)

Divide o dividendo pelo divisor. Se ocorrer um erro, retorna NULL.

potência ou potência

Retorna o resultado de expr1 elevado à potência de expr2.

Boolean funções e operadores

Os cálculos personalizados suportam comparação básica e operadores Boolean, como =, ==, <=, >=, <, >, is null, AND, OR, NOT, !. Você também pode avaliar expressões usando as seguintes funções:

Função

Descrição

é nulo (expr)

Retorna true se expr for NULL.

não é nulo (expr)

Retorna true se expr não for NULL.

Funções de elenco

Use as seguintes funções para converter valores em um tipo especificado:

Função

Descrição

cast(expr AS type)

Converte o valor expr para o tipo de dados de destino type.

try_cast(expr AS type)

Converte o valor expr para o tipo de dados de destino type com segurança.

Funções de data, timestamp e intervalo

Use as seguintes funções para trabalhar com datas, carimbos de data/hora e intervalos:

Função

Descrição

datediff(endDate, startDate)

Retorna o número de dias de startDate a endDate.

timestampdiff(unit, start, stop)

Retorna a diferença entre dois timestamps medidos em unidades.

date_format(expr, fmt)

Converte um carimbo de data/hora em uma cadeia de caracteres no formato fmt.

timediff(unit, start, stop)

Retorna a diferença entre dois timestamps medidos em unidades.

date_part

Extrai uma parte específica, como ano, mês ou dia, de uma data ou timestamp.

date_trunc

Trunca uma data ou um carimbo de data/hora para uma unidade especificada, como ano ou mês.

funções de strings

Use as seguintes funções para transformar strings:

Função

Descrição

concat(expr1, expr2[, …])

Retorna a concatenação dos argumentos.

concat_ws(sep[, expr1[, …]])

Retorna a concatenação de strings separada por sep.

Funções diversas

As seguintes funções também são suportadas:

Função

Descrição

CASE expr { WHEN opt1 THEN res1 } […] [ELSE def] END

Retorna resN para o primeiro optN que é igual a expr ou def se nenhum corresponder.

CASE { WHEN cond1 THEN res1 } […] [ELSE def] END

Retorna resN para o primeiro condN avaliado como verdadeiro ou def se nenhum for encontrado.

coalesce(expr1, expr2 [, …])

Retorna o primeiro argumento não nulo.

nvl(expr1, expr2)

Retorna expr2 se expr1 for NULL ou expr1 caso contrário.

Exemplos

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

Filtre e agregue dados condicionalmente

Use uma declaração CASE para agregar dados condicionalmente. O exemplo a seguir utiliza o samples.nyctaxi.trips dataset 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 cadeia de caracteres. Veja a funçãoconcat e a funçãoconcat_ws.

SQL
CONCAT(first_name, ' ', last_name)

Formatar datas

Use DATE_FORMAT para formatar a data strings que aparece nas visualizações.

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