Utiliser des mesures de fenêtre dans les vues métriques.

Les mesures de fenêtre vous permettent de définir des mesures avec des agrégations fenêtrées, cumulatives ou semi-additives dans vos vues de mesures. Ces types de mesures permettent des calculs plus complexes, tels que les moyennes mobiles, les changements de période sur période et les totaux en cours d’exécution. Cette page inclut des exemples pratiques illustrant comment utiliser des mesures de fenêtres dans les vues métriques.

Définir une mesure de fenêtre

Les mesures de fenêtre permettent de définir des mesures avec des agrégations fenêtrées, cumulatives ou semi-additives. Une mesure de fenêtre inclut les valeurs requises suivantes :

• ordre: La dimension qui détermine l’ordre de la fenêtre.

• étendue : Définit l’étendue de la fenêtre, telle que rétroactif, cumulatif ou toutes les données. Les valeurs de plage possibles sont les suivantes :

  • current: inclut des lignes où la valeur d’ordre de la fenêtre est égale à la valeur de la ligne actuelle.
  • cumulative: inclut toutes les lignes où la valeur de classement des fenêtres est inférieure ou égale à la valeur de la ligne actuelle.
  • trailing <value> <unit>: inclut les lignes de la ligne actuelle en arrière par le nombre spécifié d’unités de temps, telles que trailing 3 months. Cela n’inclut pas l’unité actuelle. Par exemple, trailing 3 months exclut le mois en cours.
  • leading <value> <unit>: inclut les lignes de la ligne actuelle en avant par le nombre spécifié d’unités de temps, telles que leading 7 days.
  • all: inclut toutes les lignes, quelle que soit la valeur de la fenêtre.

• semi-additive : Spécifie comment résumer la mesure lorsque le champ de commande n’est GROUP BYpas inclus dans la requête. Les valeurs possibles sont first et last.

Exemple de mesure de fenêtre traînante, glissante ou avancée

L’exemple suivant calcule une mesure sur une fenêtre de fin ou de début de temps au cours des 7 derniers jours.

version: 0.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'

dimensions:
  - name: date
    expr: o_orderdate

measures:
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: date
        range: trailing 7 day
        semiadditive: last

Pour cet exemple, la configuration suivante s’applique :

ordre:date spécifie que la dimension de date classe la fenêtre.

gamme: La trailing 7 day spécification définit la fenêtre comme étant les 7 jours avant chaque date, à l’exclusion de la date elle-même.

semi-additive :last indique que la dernière valeur de la fenêtre de 7 jours est utilisée.

Exemple de mesure de fenêtre période sur période

Calculez la modification de la période précédente à la période actuelle.

version: 0.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'

dimensions:
  - name: date
    expr: o_orderdate
measures:
  - name: previous_day_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: trailing 1 day
        semiadditive: last
  - name: current_day_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: current
        semiadditive: last
  - name: day_over_day_growth
    expr: (MEASURE(current_day_sales) - MEASURE(previous_day_sales)) / MEASURE(previous_day_sales) * 100

Pour cet exemple, les conditions suivantes s’appliquent :

• Deux mesures de fenêtre sont utilisées : une pour calculer le total des ventes le jour précédent et l’autre pour le jour actuel.

• Une troisième mesure calcule la variation du pourcentage (croissance) entre les jours actuels et les jours précédents.

Exemple de mesure de total cumulatif progressif

Calculez un total cumulatif d’une mesure à chaque instant dans le temps.

version: 0.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'

dimensions:
  - name: date
    expr: o_orderdate
measures:
  - name: running_total_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: cumulative
        semiadditive: last

Les détails suivants mettent en évidence les principales parties de cette définition :

commande :date garantit que la dimension date ordonne la fenêtre.

plage :cumulative définit la fenêtre comme toutes les données jusqu'à et y compris chaque date.

semi-additive :last vérifie que la dernière valeur cumulative est utilisée lors de l’agrégation sur les dimensions.

Exemple de mesure de période à date

Calculez un total cumulatif sur une période donnée.

version: 0.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1997-01-01'

dimensions:
  - name: date
    expr: o_orderdate
  - name: year
    expr: DATE_TRUNC('year', o_orderdate)
measures:
  - name: ytd_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: cumulative
        semiadditive: last
      - order: year
        range: current
        semiadditive: last

Les détails suivants mettent en évidence les principales parties de cette définition :

• Deux mesures de fenêtre sont utilisées : une pour la somme cumulative sur la date dimension et une autre pour limiter la somme à l’année current .

• La somme cumulative est limitée par la year dimension pour vérifier qu’elle est calculée uniquement dans l’année en cours.

Exemple de mesure semi-additive

Calculez une mesure qui ne doit pas être additionnée sur une dimension spécifique, telle qu’un solde bancaire.

dimensions:
  - name: date
    expr: date
  - name: customer
    expr: customer_id

measures:
  - name: semiadditive_balance
    expr: SUM(balance)
    window:
      - order: date
        range: current
        semiadditive: last

Les détails suivants mettent en évidence les principales parties de cette définition :

• commande :date garantit que la dimension date détermine l'ordre de la fenêtre.

• gamme:current limite la fenêtre à un seul jour sans agrégation sur plusieurs jours.

• semi-additif :last garantit que le solde le plus récent est renvoyé lors de l'agrégation sur plusieurs jours.

Interroger une mesure de fenêtre

Vous pouvez interroger une vue métrique avec une mesure de fenêtre comme n’importe quelle autre vue métrique. L’exemple suivant interroge une vue de métrique :

SELECT
   state,
   DATE_TRUNC('month', date),
   MEASURE(t7d_distinct_customers) as m
FROM sales_metric_view
WHERE date >= DATE'2024-06-01'
GROUP BY ALL