Agréger des données à l’aide de FetchXML

Utilisez FetchXML pour agréger des données dans Microsoft Dataverse en calculant la somme, la moyenne, la min, la valeur maximale et le nombre sur plusieurs lignes. Les fonctionnalités de regroupement et d’agrégation FetchXML vous permettent de résumer efficacement les jeux de données volumineux afin de créer des rapports et des analyses significatifs.

Pour retourner une valeur d’agrégation, vous devez :

• Définissez l’élément fetchaggregate sur true.

• Définissez l’attribut pour chaque alias.

• Définissez l’attribut aggregate pour chaque élément d’attribut sur l’une des fonctions d’agrégation suivantes :

  Fonction	Valeur retournée
  avg	Valeur moyenne des valeurs de colonne avec des données.
  count	Nombre de lignes.
  countcolumn	Nombre de lignes avec des données dans cette colonne.
  max	Valeur maximale des lignes de cette colonne.
  min	Valeur minimale des lignes de cette colonne.
  sum	Valeur totale des valeurs de colonne avec des données.

Tenez compte des points suivants :

• Le calcul des valeurs d’agrégation exclut les valeurs Null.
• Vous pouvez utiliser des données à partir de tables jointes à l’aide de l’élément link-entity.
• Vous pouvez appliquer des filtres pour limiter les résultats comme avec n’importe quelle requête.

Example

Supposons que vous avez 10 enregistrements de compte avec les données suivantes :

La requête suivante retourne des données d’agrégation pour la numberofemployees colonne.

<fetch aggregate='true'>
  <entity name='account'>
    <attribute name='numberofemployees'
      alias='Average'
      aggregate='avg' />
    <attribute name='numberofemployees'
      alias='Count'
      aggregate='count' />
    <attribute name='numberofemployees'
      alias='ColumnCount'
      aggregate='countcolumn' />
    <attribute name='numberofemployees'
      alias='Maximum'
      aggregate='max' />
    <attribute name='numberofemployees'
      alias='Minimum'
      aggregate='min' />
    <attribute name='numberofemployees'
      alias='Sum'
      aggregate='sum' />
  </entity>
</fetch>

Les résultats sont une seule ligne :

 --------------------------------------------------------------
 | Average | Count | ColumnCount | Maximum | Minimum | Sum    |
 --------------------------------------------------------------
 | 3,911   | 10    | 9           | 6,200   | 1,500   | 35,200 |
 --------------------------------------------------------------

Valeurs de colonne distinctes

Lorsque vous utilisez la countcolumn fonction d’agrégation, définissez l’attribut distinct pour retourner un nombre de valeurs uniques pour la colonne.

<attribute name='numberofemployees' 
   alias='ColumnCount' 
   aggregate='countcolumn' 
   distinct='true' />

Lorsque vous définissez cet attribut pour la requête précédente, les résultats retournent 8 plutôt que 9, car deux lignes du jeu de données ont 2 900 comme valeur d’employé.

Grouping

Regroupez les résultats d’une requête d’agrégation en ajoutant un élément d’attribut avec l’attribut groupby plutôt que l’attribut aggregate . Lors du regroupement, spécifiez un élément de commande avec une valeur alias fixée à celle du alias groupe.

Par exemple, la requête suivante retourne la somme des employés et le nombre par ville :

<fetch aggregate='true'>
   <entity name='account'>
      <attribute name='numberofemployees'
         alias='Total'
         aggregate='sum' />
      <attribute name='address1_city'
         alias='Count'
         aggregate='count' />
      <attribute name='address1_city'
         alias='City'
         groupby='true' />
      <order alias='City' />
   </entity>
</fetch>

La requête regroupe les résultats par valeur de ville, en combinant les résultats pour les trois lignes où la ville est Redmond.

Regroupement par parties d’une date

Vous pouvez sélectionner la partie de la date à utiliser lors du regroupement par date. Définissez l’attribut d’élémentdategrouping sur l’une des valeurs suivantes :

En savoir plus sur les paramètres de l’exercice fiscal

Par défaut, les regroupements de dates utilisent le fuseau horaire de l’utilisateur. Définissez l'attribut de l'élément usertimezone à "false" pour spécifier que le fuseau horaire UTC doit être utilisé à la place.

La requête suivante groupe les enregistrements de compte qui montrent le nombre d'employés par date de création.

<fetch aggregate='true'>
   <entity name='account'>
      <attribute name='numberofemployees'
         alias='Total'
         aggregate='sum' />
      <attribute name='createdon'
         alias='Day'
         groupby='true'
         dategrouping='day' />
      <attribute name='createdon'
         alias='Week'
         groupby='true'
         dategrouping='week' />
      <attribute name='createdon'
         alias='Month'
         groupby='true'
         dategrouping='month' />
      <attribute name='createdon'
         alias='Year'
         groupby='true'
         dategrouping='year' />
      <attribute name='createdon'
         alias='FiscalPeriod'
         groupby='true'
         dategrouping='fiscal-period' />
      <attribute name='createdon'
         alias='FiscalYear'
         groupby='true'
         dategrouping='fiscal-year' />
      <order alias='Month' />
   </entity>
</fetch>

Le tableau suivant montre le résultat à l’aide de l’exemple de jeu de données mentionné précédemment :

 -----------------------------------------------------------------------
 | Total  | Day | Week | Month | Year  | FiscalPeriod     | FiscalYear |
 -----------------------------------------------------------------------
 | 35,200 | 25  | 12   | 3     | 2,023 | Quarter 1 FY2023 | FY2023     |
 -----------------------------------------------------------------------
 | 0      | 27  | 35   | 8     | 2,023 | Quarter 3 FY2023 | FY2023     |
 -----------------------------------------------------------------------

Exemple de regroupement de dates de période fiscale

L’exemple suivant montre une expression d’agrégation FetchXML qui additionne le nombre total de commandes remplies et regroupe le résultat par semestre fiscal et année fiscale.

<fetch aggregate="true">
   <entity name="order">
      <attribute name="totalamount"
         aggregate="sum"
         alias="total" />
      <attribute name="datefulfilled"
         groupby="true"
         dategrouping="fiscal-period" />
   </entity>
</fetch>

Agrégat de lignes

Lorsqu’une table a une relation hiérarchique définie, vous pouvez retourner un agrégat de lignes sur la colonne de recherche pour la relation hiérarchique.

L’exemple suivant renvoie le nombre de comptes associés dans une colonne nommée CountChildren lorsque la colonne parentaccountid des comptes enfants est égale à la colonne accountid du compte actuel.

<fetch top='5'>
   <entity name='account'>
      <attribute name='name' />
      <attribute name='accountid'
         alias='numberOfChildren'
         rowaggregate='CountChildren' />
      <order attribute='accountid'
         descending='true' />
   </entity>
</fetch>

Limites

Les requêtes qui retournent des valeurs d’agrégation sont limitées à 50 000 enregistrements. Cette limite permet de maintenir les performances et la fiabilité du système. Si les critères de filtre dans votre requête retournent plus de 50 000 enregistrements, vous obtenez l’erreur suivante :

Nombre : -2147164125
Code : 8004E023
Message : AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Message d’erreur du client : la limite maximale d’enregistrement est dépassée. Réduisez le nombre d’enregistrements.

Pour éviter cette erreur, ajoutez des filtres appropriés à votre requête pour vous assurer qu’elle n’évalue pas plus de 50 000 enregistrements. Exécutez ensuite votre requête plusieurs fois et combinez les résultats. Les filtres appropriés dépendent de la nature de vos données, mais ils peuvent être une plage de dates ou un sous-ensemble de valeurs dans une colonne de choix.

Par limite de requête

Même avec la limite par défaut pour les requêtes agrégées appliquées, la requête peut prendre un certain temps. Vous pouvez utiliser l’attribut aggregatelimit dans une requête pour appliquer une limite inférieure personnalisée.

Si vous spécifiez l’attribut aggregatelimit , la requête ne retourne pas d’erreur AggregateQueryRecordLimit exceeded si la limite est atteinte. Au lieu de cela, la requête agrège au plus limit + 1 lignes arbitraires des données.

La limite par requête ne peut pas dépasser la limite d’agrégation par défaut.

Example

Dans cet exemple, la limite de lignes maximales personnalisée est de 5 :

<fetch aggregate='true'
   aggregatelimit = '5'>
   <entity name='account'>
      <attribute name='name'
         alias='account_count'
         aggregate='count' />
   </entity>
</fetch>

Les résultats sont une seule ligne agrégeant jusqu’à six des enregistrements :

 -----------------
 | account_count |
 -----------------
 | 6             |
 -----------------

Étapes suivantes

Découvrez comment compter des lignes.