Aller au contenu principal
Dernière mise à jour le

Utiliser des expressions de niveau de détail (LOD) dans les vues métriques

Les expressions de niveau de détail (LOD) vous permettent de spécifier la granularité à laquelle calculer les agrégations, indépendamment des champs (également appelés dimensions) dans votre query. Ceci vous permet de compute des métriques comme le pourcentage du total, où le dénominateur agrège à une granulométrie plus grossière que la query.

Que sont les expressions de niveau de détail ?​

Les expressions de niveau de détail vous permettent de spécifier exactement les champs à utiliser lors du calcul d'un agrégat, quels que soient les champs présents dans votre query. Cela vous donne un contrôle précis sur la portée de vos calculs.

Il existe trois types d'expressions de niveau de détail :

  • Niveau de détail fixe : Agréger sur un ensemble de champs prédéfini spécifié dans l'expression elle-même, en ignorant les autres champs de la query.
  • Niveau de détail plus grossier : Agrégez à une granularité plus grossière que la query en excluant des champs spécifiques du regroupement.
  • Niveau de détail plus fin : Effectuez une agrégation à un niveau de granularité plus fin que la query en incluant des champs supplémentaires, puis réagrégez le résultat au niveau de granularité de la query.

Niveau de détail fixe​

Une expression de niveau de détail fixe compute un agrégat à une granularité que vous définissez, en ignorant les champs de votre query. Dans les vues de métriques, les expressions LOD fixes sont définies directement dans le champ expr d'une définition de colonne en utilisant des fonctions de fenêtre SQL avec des clauses PARTITION BY.

Quand utiliser un niveau de détail fixe​

Utilisez des expressions de niveau de détail fixe lorsque vous avez besoin des éléments suivants :

  • Aucune dépendance vis-à-vis des regroupements de query : Métriques avec partitionnement statique pour toutes les utilisations.
  • Agrégats au niveau du dataset : Agrégats globaux comparés aux regroupements au niveau des lignes (par exemple, pourcentage des ventes totales par priorité).
  • Hiérarchies multiniveaux : Métriques de niveau détaillé et de niveau agrégé disponibles dans la même vue de métrique.

Syntaxe​

Les expressions LOD fixes utilisent les fonctions de fenêtre SQL pour calculer des agrégats à une granularité définie. Placez la fonction de fenêtre directement dans le champ expr d’une définition de champ :

YAML
fields:
  - name: <lod_name>
    expr: <AGGREGATE_FUNCTION>(<column>) OVER (PARTITION BY <dim1>, <dim2>, ...)

Pour agréger l'ensemble du dataset, omettez la clause PARTITION BY et laissez des parenthèses vides après OVER.

Exemple : ventes totales par priorité de commande​

Supposons que vous souhaitiez définir une vue métrique où les ventes de chaque commande peuvent être comparées au total des ventes de son groupe prioritaire. L'exemple suivant calcule priority_total_price dans la requête source et l'expose comme un champ d'identité :

YAML
version: 1.1

source: samples.tpch.orders

fields:
  - name: order_priority
    expr: o_orderpriority
  - name: order_date
    expr: o_orderdate
  - name: priority_total_price
    expr: SUM(o_totalprice) OVER (PARTITION BY o_orderpriority)

measures:
  - name: total_sales
    expr: SUM(o_totalprice)

  - name: pct_of_priority_total
    expr: SUM(o_totalprice) / ANY_VALUE(priority_total_price)

Le champ priority_total_price définit le total fixe pour chaque groupe de priorité directement dans son champ expr. La mesure pct_of_priority_total divise les Ventes de commandes individuelles par ce total fixe pour produire un pourcentage, quelle que soit la façon dont la query groupe les résultats.

remarque

Lorsque vous référencez un champ de niveau de détail fixe dans une expression de mesure, enveloppez-le dans une fonction d'agrégation. Utilisez ANY_VALUE lorsque la valeur est constante au sein d’un groupe, comme dans l’exemple précédent.

Créez la vue métrique à l'aide de SQL.

Pour créer cette vue métrique en dehors de l'Explorateur de catalogues, enveloppez le YAML dans CREATE OR REPLACE VIEW ... WITH METRICS LANGUAGE YAML AS et placez la définition entre les délimiteurs $$ :

SQL
CREATE OR REPLACE VIEW catalog.schema.sales_by_priority WITH METRICS LANGUAGE YAML AS
$$
  version: 1.1

  source: samples.tpch.orders

  fields:
    - name: order_priority
      expr: o_orderpriority
    - name: order_date
      expr: o_orderdate
    - name: priority_total_price
      expr: SUM(o_totalprice) OVER (PARTITION BY o_orderpriority)

  measures:
    - name: total_sales
      expr: SUM(o_totalprice)

    - name: pct_of_priority_total
      expr: SUM(o_totalprice) / ANY_VALUE(priority_total_price)
$$

L'exemple de niveau de détail plus grossier sur cette page suit le même modèle.

Filtrage sur les expressions de niveau de détail fixe​

Les expressions à niveau de détail fixe sont computées avant l'application de tout filtre de temps de query. Pour appliquer un filtre à un calcul LOD fixe, incluez la condition de filtre dans l'expression de la fonction de fenêtre en utilisant une instruction CASE ou une clause FILTER.

Niveau de détail plus grossier​

Une expression de niveau de détail plus grossier agrège à une granularité plus grossière que la requête en excluant un ou plusieurs champs de la partition. Dans les vues d'indicateurs, les expressions LOD plus grossières sont implémentées à l'aide de mesures de fenêtre avec la spécification de plage all.

Quand utiliser un niveau de détail plus grossier​

Utilisez des expressions avec un niveau de détail plus grossier lorsque vous avez besoin :

  • Regroupements dynamiques : agrégats qui s'adaptent aux regroupements de query (par exemple, pourcentage du total pour tout champ sélectionné).
  • Agrégations sensibles aux filtres : Compute à une granularité plus grossière tout en respectant les filtres de query.

Syntaxe​

Pour chaque champ à exclure de la partition, définissez une mesure de fenêtre avec range: all:

YAML
measures:
  - name: <measure_name>
    expr: <AGGREGATE_EXPRESSION>
    window:
      - order: <field_to_exclude>
        range: all
        semiadditive: last

Pour exclure plusieurs champs, ajoutez une entrée au tableau window pour chaque champ.

Exemple : pourcentage des ventes totales​

Pour calculer le pourcentage du total des ventes pour chaque priorité de commande :

YAML
version: 1.1

source: samples.tpch.orders

fields:
  - name: order_priority
    expr: o_orderpriority

measures:
  - name: total_sales
    expr: SUM(o_totalprice)

  - name: all_priorities_sales
    expr: SUM(o_totalprice)
    window:
      - order: order_priority
        range: all
        semiadditive: last

  - name: pct_of_total_sales
    expr: SUM(o_totalprice) / MEASURE(all_priorities_sales)

Dans cet exemple :

  • total_sales agrégats au niveau de regroupement de la query.
  • all_priorities_sales utilise range: all pour calculer un total général pour toutes les priorités de commande, en ignorant le champ order_priority dans la query.
  • pct_of_total_sales divise les Ventes de niveau de priorité par le total général pour produire un pourcentage.

Niveau de détail plus fin​

Une expression de niveau de détail plus fin calcule un agrégat à une granularité plus fine que votre query, puis réagrège le résultat à la granularité de la query. L’agrégat interne est évalué sur les champs de la query plus un ou plusieurs champs supplémentaires que vous incluez, et un agrégat externe consolide ces valeurs. Dans les vues métriques, les expressions LOD plus fines sont des mesures qui utilisent un bloc partition avec une liste include et une fonction outer_aggregate.

Quand utiliser un niveau de détail plus fin​

Utilisez des expressions de niveau de détail plus fin lorsque vous avez besoin de :

  • Agrégations d'agrégations : calculez un agrégat par entité, tel que le chiffre d'affaires total par client, puis résumez-le au grain de la query, comme la moyenne pour l'ensemble des clients.
  • Résumés indépendants du grain : calculez la métrique interne à un grain plus fin que la requête et consolidez-la automatiquement lorsque le grain de la requête change.

Syntaxe​

Définissez une mesure avec un bloc partition. Le bloc partition ajoute des champs à la granularité de la query pour l’agrégat interne et spécifie l’agrégat qui ramène le résultat :

YAML
measures:
  - name: <measure_name>
    expr: <INNER_AGGREGATE_EXPRESSION>
    partition:
      include: [<field1>, <field2>, ...]
      outer_aggregate: <AGGREGATE_FUNCTION>

Dans cette syntaxe :

  • expr est l'agrégat interne. Elle est évaluée au niveau de grain de la query ainsi que sur les champs de include.
  • include énumère un ou plusieurs champs à ajouter au grain de la query lors du calcul de l’agrégat interne. Chaque entrée doit être un champ défini dans la vue Indicateur.
  • outer_aggregate est la fonction d’agrégation qui ramène les résultats internes à la granularité de la query.
remarque

Le outer_aggregate doit être une fonction d'agrégation à argument unique : SUM, AVG, MIN, MAX, COUNT ou MEDIAN. Les fonctions prenant plusieurs arguments telles que PERCENTILE et COUNT(DISTINCT ...) ne sont pas prises en charge en tant qu'agrégat externe, bien que vous puissiez les utiliser dans expr. Une mesure avec un niveau de détail plus fin ne peut pas non plus utiliser un bloc window, et elle ne peut pas référencer une autre mesure de fenêtre ou de détail plus fin.

Exemple : revenu moyen par client​

Supposons que vous souhaitiez obtenir le chiffre d'affaires moyen par client, résumé au grain utilisé par votre query, tel que la priorité de commande. Compute total revenue per client as the inner aggregate, then average across clients with the outer aggregate:

YAML
version: 1.1

source: samples.tpch.orders

fields:
  - name: order_priority
    expr: o_orderpriority
  - name: customer
    expr: o_custkey

measures:
  - name: total_revenue
    expr: SUM(o_totalprice)

  - name: avg_revenue_per_customer
    expr: SUM(o_totalprice)
    partition:
      include: [customer]
      outer_aggregate: AVG

Dans cet exemple :

  • total_revenue agrégats au niveau de regroupement de la query.
  • avg_revenue_per_customer calcule SUM(o_totalprice) au grain (order_priority, customer), en donnant le chiffre d'affaires total de chaque client au sein d'une priorité, puis utilise outer_aggregate: AVG pour moyenner ces totaux par client afin de revenir au grain de la requête.

Query la mesure comme n’importe quelle autre mesure. Les champs include sont ajoutés au grain de la query uniquement pendant le calcul de l’agrégat interne :

SQL
SELECT order_priority, MEASURE(avg_revenue_per_customer)
FROM catalog.schema.orders_mv
GROUP BY order_priority

Chaque ligne renvoie le chiffre d'affaires moyen par client pour cette priorité de commande.

Ressources supplémentaires​

Sur cette page