Que sont les calculs personnalisés ?

Les calculs personnalisés vous permettent de définir des métriques dynamiques et des Transformations sans modifier les query du dataset. Cette page explique comment utiliser des calculs personnalisés dans les tableaux de bord AI/BI.

Pourquoi utiliser des calculs personnalisés ?

Les calculs personnalisés vous permettent de créer et de visualiser de nouveaux champs à partir de datasets de tableau de bord existants sans modifier le SQL source. Vous pouvez définir jusqu'à 200 calculs personnalisés par dataset.

Les calculs personnalisés sont des types suivants :

• Mesures calculées : Valeurs agrégées telles que les ventes totales ou le coût moyen. Les mesures calculées peuvent utiliser la commande AGGREGATE OVER pour compute des valeurs sur des plages de temps.
• Dimensions calculées : valeurs non agrégées ou Transformations telles que la catégorisation des tranches d'âge ou la mise en forme des chaînes.

Les calculs personnalisés se comportent de manière similaire aux vues de métriques, mais sont limités au dataset et au tableau de bord où ils sont définis. Pour définir des métriques personnalisées qui peuvent être utilisées avec d'autres assets de données, consultez les vues de métriques Unity Catalog.

Créer des métriques dynamiques avec des mesures calculées

Supposons que vous ayez le dataset suivant :

Élément	Région	Prix	Coût	Date
Apple	États-Unis	30	15	01-01-2024
Apple	Canada	20	10	01-01-2024
Oranges	États-Unis	20	15	02-01-2024
Oranges	Canada	15	10	02-01-2024

Élément	Région	Prix	Coût	Date
Apple	États-Unis	30	15	01-01-2024
Apple	Canada	20	10	01-01-2024
Oranges	États-Unis	20	15	02-01-2024
Oranges	Canada	15	10	02-01-2024

Vous voulez visualiser la marge bénéficiaire par région. Sans calculs personnalisés, vous devriez créer un nouveau dataset avec une colonne margin :

Région	Marge
États-Unis	0,40
Canada	0,43

Région	Marge
États-Unis	0,40
Canada	0,43

Bien que cette approche fonctionne, le nouveau dataset est statique et pourrait ne prendre en charge qu'une seule visualisation. Les filtres appliqués au dataset original n'affectent pas le nouveau dataset sans ajustements manuels supplémentaires.

Avec les calculs personnalisés, vous pouvez exprimer la marge bénéficiaire comme une agrégation en utilisant la formule suivante :

SQL

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

Cette mesure est dynamique. Lorsqu'il est utilisé dans une visualisation, il se met à jour automatiquement pour refléter les regroupements de la visualisation. Par exemple, la même mesure ci-dessus peut être utilisée pour visualiser la marge bénéficiaire par Region ou par Item, selon ce qui est sélectionné dans la visualisation.

Définir des valeurs non agrégées avec des dimensions calculées

Les dimensions calculées vous permettent de définir des valeurs non agrégées ou des transformations légères sans modifier le dataset source. C'est utile lorsque vous souhaitez organiser ou reformater des données pour la visualisation.

Par exemple, pour analyser les tendances d’âge par tranche d’âge au lieu d’âges individuels, vous pouvez définir une dimension age_group personnalisée à l’aide de l’expression suivante :

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

Définir des calculs sur une fenêtre

Une tâche courante dans les visualisations de tableau de bord consiste à calculer une agrégation sur une plage, telle que la somme glissante des Ventes sur les sept derniers jours. Les calculs personnalisés prennent en charge cette fonctionnalité via les fonctions de fenêtre, qui vous permettent d'effectuer des calculs sur un ensemble de lignes (une « fenêtre ») liées à la ligne actuelle.

Les tableaux de bord AI/BI prennent en charge deux types de fonctions de fenêtre :

• Fonctions de fenêtre scalaires, qui agrègent sur des groupements fixes et se comportent comme des fonctions scalaires. Utilisées seules, elles constituent des dimensions calculées.
• Fonctions de fenêtre d'agrégation, qui agrègent sur des regroupements dynamiques et se comportent comme des fonctions d'agrégation. Lorsqu'elles sont utilisées, elles forment des mesures calculées.

Les fonctions de fenêtre sont également la base des expressions de niveau de détail, qui vous permettent de contrôler la granularité de l'agrégation indépendamment des regroupements de votre visualisation.

Fonctions de fenêtre scalaires

Les fonctions de fenêtre scalaires utilisent l’opérateur OVER avec les clauses facultatives PARTITION BY et ORDER BY pour calculer des agrégations sur des lignes associées avant tout regroupement de visualisation. Elles agrègent un ensemble statique de partitions définies dans la fonction de fenêtre elle-même, avant d’être rejointes à la table sous-jacente non transformée en tant que dimension.

Exemple : calcul des ventes totales par région :

SQL

SUM(sales) OVER (PARTITION BY Region)

Exemple de calcul des ventes cumulées par région :

SQL

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

syntaxeOVER

SQL

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

Consultez les fonctions de fenêtre de la référence du langage SQL pour plus de détails.

Fonctions de fenêtre d'agrégation

Les fonctions de fenêtre d'agrégation utilisent l'opérateur AGGREGATE OVER pour calculer les agrégations par fenêtre après que le regroupement de la visualisation a été appliqué. Les groupes sur lesquels agréger sont automatiquement hérités de la visualisation dans laquelle l'expression est utilisée. Vous pouvez éventuellement utiliser une clause PARTITION BY avec * pour représenter toutes les partitions héritées et exclure des dimensions spécifiques à l'aide de la clause EXCEPT. La clause ORDER BY vous permet d'agréger partiellement les partitions résultantes sur des lignes adjacentes, offrant une fonctionnalité de "fenêtrage".

En utilisant le même dataset que l'exemple précédent, l'expression suivante calcule la marge bénéficiaire moyenne sur sept jours à l'aide de l'opérateur AGGREGATE OVER.

SQL

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

Après la création, cette mesure peut être appliquée dans toute visualisation.

syntaxeAGGREGATE OVER

SQL

<AGGREGATE_EXPRESSION> AGGREGATE OVER (
  [PARTITION BY * [EXCEPT (<field> [, ...])]]
  [ORDER BY <field> <frame_specification>]
)

Dans cette syntaxe :

• PARTITION BY * représente toutes les partitions héritées du regroupement de visualisation
• EXCEPT (<field> [, ...]) spécifie les dimensions à exclure de l'ensemble de partitions
• Les clauses PARTITION BY et ORDER BY sont facultatives, mais un AGGREGATE OVER () vide n'est pas valide.

La spécification du cadre peut être l'une des suivantes :

• CURRENT
• CUMULATIVE
• ALL
• (TRAILING|LEADING) <number> <unit> [INCLUSIVE|EXCLUSIVE]
  • <number> est un entier positif
  • <unit> est DAY, MONTH ou YEAR
  • Le mot-clé facultatif INCLUSIVE inclut la ligne actuelle dans la plage. EXCLUSIVE l'exclut. Par défaut : EXCLUSIVE.
  • Exemple : TRAILING 7 DAY INCLUSIVE ou LEADING 1 MONTH

Vous pouvez éventuellement ajouter une clause OFFSET pour décaler l'intégralité de la plage d'un intervalle spécifié :

• OFFSET <number> <unit>
  • Accepte les mêmes spécifications d'intervalle que TRAILING et LEADING
  • Exemple : TRAILING 7 DAY OFFSET -1 YEAR

Le tableau suivant indique comment la spécification de trame pour l'agrégat sur se compare à la clause SQL window frame équivalente.

Spécification de cadre	Clause de fenêtre SQL équivalente
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> (EXCLUSIVE, default)	RANGE BETWEEN <number> <unit> PRECEDING and 1 <unit> PRECEDING
TRAILING <number> <unit> INCLUSIVE	RANGE BETWEEN <number> <unit> PRECEDING and CURRENT ROW
LEADING <number> <unit> (EXCLUSIVE, default)	RANGE BETWEEN 1 <unit> FOLLOWING and <number> <unit> FOLLOWING
LEADING <number> <unit> INCLUSIVE	RANGE BETWEEN CURRENT ROW and <number> <unit> FOLLOWING

Spécification de cadre	Clause de fenêtre SQL équivalente
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> (EXCLUSIVE, default)	RANGE BETWEEN <number> <unit> PRECEDING and 1 <unit> PRECEDING
TRAILING <number> <unit> INCLUSIVE	RANGE BETWEEN <number> <unit> PRECEDING and CURRENT ROW
LEADING <number> <unit> (EXCLUSIVE, default)	RANGE BETWEEN 1 <unit> FOLLOWING and <number> <unit> FOLLOWING
LEADING <number> <unit> INCLUSIVE	RANGE BETWEEN CURRENT ROW and <number> <unit> FOLLOWING

Si le champ ORDER BY n'est pas groupé dans la visualisation, AGGREGATE OVER prend la valeur agrégée de la dernière ligne comme valeur à afficher pour chaque groupe. Ceci équivaut au comportement semi-additif « dernier ».

OVER contre AGGREGATE OVER

La principale différence entre OVER et AGGREGATE OVER est que OVER est une fonction scalaire et AGGREGATE OVER est une fonction d'agrégation. OVER nécessite une clause PARTITION BY pour définir des groupes, tandis que AGGREGATE OVER hérite de ses groupes de la visualisation environnante et peut incorporer des données en dehors du groupe actuel.

Utilisez la syntaxe OVER pour :

• Calculs de fenêtre qui doivent être utilisés dans des contextes non agrégés, comme les tables.
• Calculs de fenêtre qui doivent ignorer tous les regroupements et filtres de visualisation.
• Agrégation à un niveau de détail fixe: Calcul des agrégations à une granularité spécifique à l'aide de PARTITION BY.
• Utilisation des fonctions de classement et analytiques comme ROW_NUMBER, RANK, LAG.

Utilisez la syntaxe AGGREGATE OVER pour :

• Calculs de fenêtre qui peuvent être utilisés dans divers contextes de regroupement, ou qui doivent intégrer des données en dehors du groupe actuel.
• Calculs de fenêtre qui respectent les filtres de visualisation.
• Agrégation à un niveau de détail plus grossier que la visualisation : En excluant les dimensions à l'aide de PARTITION BY * EXCEPT (...).
• Plages temporelles résistantes aux lignes manquantes : fenêtres glissantes avec TRAILING ou LEADING.

Avantages en termes de performances

Les calculs personnalisés sont optimisés pour les performances. Pour les petits datasets (≤ 100 000 lignes et ≤ 100 Mo), les calculs s'exécutent dans le navigateur pour une réactivité plus rapide. Les datasets plus grands sont traités par le SQL Warehouse. Consultez Optimisation et mise en cache du dataset pour plus de détails.

Créer un calcul personnalisé

Cet exemple crée une mesure calculée basée sur le dataset samples.nyctaxi.trips. Cela suppose une connaissance générale de l'utilisation des tableaux de bord AI/BI. Si vous n'êtes pas familier avec la création de tableaux de bord AI/BI, consultez Créer un tableau de bord pour commencer.

1. Ouvrez un dataset existant ou créez-en un nouveau.

2. Cliquez sur + Ajouter un calcul personnalisé .

   

3. Un panneau **Créer un calcul** s'ouvre sur le côté droit de l'écran. Dans le champ de texte Nom , entrez Coût par mile .

4. (Facultatif) Dans le champ de texte Commentaire , saisissez « Utilise le montant du tarif et la distance du trajet pour calculer le coût par mile. »

5. Dans le champ Expression , saisissez les éléments suivants :

   SQL

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

6. Cliquez sur Créer .

Référencement d'autres calculs

Les calculs personnalisés peuvent faire référence à d'autres calculs personnalisés définis dans le même dataset. Cela vous permet de créer des métriques complexes en composant des calculs plus simples, favorisant ainsi la réutilisabilité et la maintenabilité.

Lorsque vous référencez un autre calcul personnalisé, utilisez directement son nom dans votre expression comme s’il s’agissait d’une colonne dans le dataset.

Par exemple, supposons que vous ayez créé ces mesures calculées :

• total_revenue : SUM(sale_amount)
• Coût total : SUM(cost_amount)

Vous pouvez créer une 3e mesure calculée qui référence les deux :

• **marge_bénéficiaire**: (MEASURE(total_revenue) - MEASURE(total_cost)) / MEASURE(total_revenue)

remarque

• Vous ne pouvez référencer les calculs que dans le même dataset.
• Les références circulaires ne sont pas autorisées (le calcul A ne peut pas référencer le calcul B si B référence A).
• Les calculs référencés doivent être créés avant de pouvoir être utilisés dans d'autres expressions.

Ajoutez des calculs personnalisés à une vue de métrique

info

Aperçu

Cette fonctionnalité est en aperçu public.

Vous pouvez définir des calculs personnalisés sur un dataset créé par une vue métrique. Seules la **table des résultats** et le **schéma** sont affichés lorsque vous ouvrez le dataset. Cliquez sur **Calcul personnalisé** pour définir un nouveau calcul personnalisé. Pour définir des métriques personnalisées supplémentaires que d'autres data assets peuvent utiliser, apportez des modifications à la définition de la vue. Voir les vues métriques Unity Catalog.

Pour définir une nouvelle vue métrique à partir de l'éditeur de dataset du tableau de bord, consultez Exporter en tant que vue métrique.

Afficher le schéma

Cliquez sur l'onglet Schéma dans le panneau des résultats pour afficher le calcul personnalisé et son commentaire associé.

Les mesures calculées sont répertoriées dans la section Mesures et sont marquées par une icône fx. La valeur associée à une mesure calculée est calculée dynamiquement lorsque vous définissez le GROUP BY dans une visualisation. Vous ne pouvez pas voir la valeur dans le tableau des résultats. Les dimensions calculées apparaissent dans la section Dimensions .

Utiliser un calcul personnalisé dans une visualisation

Vous pouvez utiliser la mesure calculée **Coût par mile** créée précédemment dans une visualisation.

Les mesures calculées s'agrègent automatiquement par rapport aux dimensions configurées dans votre graphique. Ce comportement est identique à la manière dont les dimensions et les mesures fonctionnent dans les vues métriques, où l'agrégation s'adapte dynamiquement aux regroupements que vous définissez dans votre visualisation.

1. Cliquez sur Page sans titre . Ensuite, placez un nouveau widget de visualisation sur la page.
2. Utilisez le panneau de configuration de la visualisation pour modifier les paramètres comme suit :

   • **Dataset** : Données de taxi.

   • Visualisation : barres

   • Axe X :

     • **Champ** : dropoff_zip
     • Type d'échelle : Catégoriel
     • Transformation : Aucun

   • Axe Y :

     • Coût par mile

remarque

Les visualisations de table prennent en charge les dimensions calculées, mais ne prennent pas en charge les mesures calculées.

L'image suivante montre le graphique.

Les visualisations avec des calculs personnalisés se mettent à jour automatiquement lorsque des filtres sont appliqués. Par exemple, l'ajout d'un filtre pickup_zip mettra à jour la visualisation pour afficher uniquement les données correspondant aux valeurs sélectionnées.

Modifier un calcul personnalisé

Pour modifier un calcul :

1. Cliquez sur le tab Data , puis sur le dataset associé au calcul que vous souhaitez modifier.
2. Cliquez sur l'onglet Schéma dans le panneau des résultats.
3. Mesures et Dimensions apparaissent sous la liste des champs de dataset. Cliquez sur le menu kebab à droite du calcul que vous souhaitez modifier. Ensuite, cliquez sur Modifier .
4. Dans le panneau Modifier le calcul personnalisé , mettez à jour les champs de texte que vous souhaitez modifier. Ensuite, cliquez sur Mettre à jour .

Supprimer un calcul personnalisé

Pour supprimer un calcul :

1. Cliquez sur l'onglet tab , puis sur le dataset associé à la mesure que vous souhaitez modifier.
2. Cliquez sur l'onglet Schéma dans le panneau des résultats.
3. La section Mesures apparaît sous la liste des champs. Cliquez sur le menu kebab à droite du calcul que vous souhaitez modifier. Ensuite, cliquez sur **Supprimer**.
4. Cliquez sur Supprimer dans la boîte de dialogue Supprimer qui s'affiche.

Utilisez des parameters dans les calculs personnalisés

Vous pouvez référencer les paramètres directement dans les calculs personnalisés en utilisant la syntaxe :keyword. Les paramètres vous permettent de modifier dynamiquement les mesures et les dimensions.

Par exemple, considérez un dataset avec une colonne d'âges d'utilisateur. Pour créer une définition dynamique de « adult », vous pourriez créer un calcul personnalisé à l'aide d'un parameter nommé adult_age_threshold:

SQL

CASE
  WHEN age > :adult_age_threshold THEN "adult"
END

Où :adult_age_threshold est un paramètre avec un type de données numérique. Sur le tableau de bord, les utilisateurs peuvent définir le paramètre :adult_age_threshold dynamiquement, ce qui met à jour la dimension.

Un calcul personnalisé qui fait référence à un paramètre hérite du type de données du paramètre, de sorte que les widgets en aval l'interprètent correctement sans configuration supplémentaire.

Les calculs personnalisés qui utilisent des paramètres fonctionnent avec les widgets de filtre. Lorsqu'un utilisateur sélectionne une valeur, le calcul personnalisé se met à jour automatiquement. Le paramètre sélectionné est également enregistré dans l'URL, de sorte que les liens mis en signet ou partagés conservent les mêmes paramètres de filtre lorsque le tableau de bord s'ouvre.

Limitations

Pour utiliser les calculs personnalisés, les conditions suivantes doivent être remplies :

• Les colonnes utilisées dans l'expression doivent appartenir au même dataset.
• Les expressions qui référencent des tables externes ou des sources de données ne sont pas prises en charge et peuvent échouer ou retourner des résultats inattendus.

Fonctions prises en charge

Pour une référence complète de toutes les fonctions prises en charge pour les calculs personnalisés, consultez la référence des fonctions de calcul personnalisées. Toute tentative d'utilisation d'une fonction non prise en charge entraîne une erreur.

Exemples

Les exemples suivants illustrent les utilisations courantes des calculs personnalisés. Chaque calcul personnalisé apparaît dans le schéma du dataset sur l'onglet tab. Sur le canevas, vous pouvez choisir le calcul personnalisé comme champ.

Filtrer et agréger les données de manière conditionnelle

Utilisez une instruction CASE pour agréger les données de manière conditionnelle. L'exemple suivant utilise le dataset samples.nyctaxi.trips et calcule la somme des tarifs pour toutes les courses qui start dans le code postal 10103.

SQL

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

Construire des chaînes

Utilisez la fonction CONCAT pour construire une nouvelle valeur de chaîne. Voir la fonctionconcat et la fonctionconcat_ws.

SQL

CONCAT(first_name, ' ', last_name)

Formater les dates

Utilisez DATE_FORMAT pour formater les chaînes de date qui apparaissent dans les visualisations.

SQL

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