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

FetchXML inclut les fonctionnalités de regroupement et d’agrégation qui vous permettent de calculer une somme, une moyenne, un nombre minimal et maximal entre différentes lignes de données.

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

• Définir l’attribut aggregate de l’élément fetch sur true.

• Définir l’attribut alias pour chaque élément attribute.

• Définir l’attribut aggregate pour chaque élément attribute sur l’une de ces fonctions d’agrégation :

  Fonction	Valeur renvoyée
  avg	La valeur moyenne des valeurs de la colonne avec des données.
  count	Le nombre de lignes.
  countcolumn	Le nombre de lignes avec des données dans cette colonne.
  max	La valeur maximale des lignes dans cette colonne.
  min	La valeur minimale des lignes dans cette colonne.
  sum	La valeur totale des valeurs de la colonne avec des données.

Notez les points suivants :

• Les valeurs nulles ne sont pas prises en compte lors du calcul des valeurs d’agrégation.
• Vous pouvez utiliser les données des tables jointes à l’aide de l’élément link-entity.
• Vous pouvez appliquer des filtres pour limiter les résultats comme pour toute requête.

Exemple

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

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

<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

Lors de l’utilisation de la fonction d’agrégation countcolumn, vous pouvez définir l’attribut distinct pour renvoyer un nombre de valeurs uniques pour la colonne.

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

Lorsqu’ils sont définis pour la requête précédente, les résultats renvoient 8 au lieu de 9 car deux lignes du jeu de données ont 2 900 comme valeur du nombre d’employés.

Regroupement

Regroupez les résultats d’une requête d’agrégation en ajoutant un élément attribute avec l’attribut groupby au lieu de l’attribut aggregate. Lors du regroupement, vous devez spécifier un élément order avec une valeur alias définie sur l’alias du groupe.

Par exemple, la requête suivante renvoie 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 selon la valeur Ville, en combinant les résultats des 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 dategrouping de l’élément attribute sur l’une des valeurs suivantes :

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

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

La requête suivante regroupe les enregistrements de compte indiquant le nombre d’employés selon la date de création des enregistrements :

<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 en utilisant 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 par période fiscale

L’exemple suivant montre une expression d’agrégation FetchXML qui totalise le nombre des commandes exécutées et classe le résultat par semestre fiscal et exercice.

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

Agrégation de lignes

Lorsqu’une table a une relation hiérarchique définie, vous pouvez renvoyer 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 enregistrements de compte 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>

Limitations

Les requêtes qui renvoient des valeurs agrégées sont limitées à 50 000 enregistrements. Cette limite aide à gérer les performances et la fiabilité du système. Si les critères de filtre de votre requête renvoient plus de 50 000 enregistrements, vous obtenez le message d’erreur suivant :

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

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

Limite par requête

Même en appliquant la limite par défaut pour les requêtes d’agrégation, l’exécution de 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 qui renvoie l’erreur AggregateQueryRecordLimit exceeded si les résultats sont supérieurs à votre limite personnalisée.

Dans cet exemple, la limite de lignes maximale personnalisée est de 10 :

<fetch aggregate='true'
   aggregatelimit = '10'>
   <entity name='opportunity'>
      <attribute name='name'
         alias='opportunity_count'
         aggregate='count' />
   </entity>
</fetch>

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

Étapes suivantes

Découvrez comment compter des lignes.