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

FetchXML inclut des fonctionnalités de regroupement et d’agrégation qui vous permettent de calculer la somme, la moyenne, la min, la valeur maximale et le nombre sur plusieurs lignes de données.

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

• Définir l’attribut de 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.

Notez les points suivants :

• Les valeurs Null ne sont pas prises en compte lors du calcul des valeurs d’agrégation.
• Vous pouvez utiliser des données 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 disposez de 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, vous pouvez définir l’attribut distinct pour retourner un nombre de valeurs uniques pour la colonne.

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

Pour la requête précédente, les résultats retournent 8 au lieu de 9, car deux lignes du jeu de données ont pour valeur 2 900 en tant que nombre d’employés.

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, vous devez spécifier un élément de tri avec une alias valeur définie pour le 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 City, 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'élément d'attributdategrouping à 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 l’attribut aggregatelimit est spécifié, la requête ne retourne pas non plus d’erreur AggregateQueryRecordLimit exceeded si la limite est atteinte, et au lieu de cela, la requête agrège au-dessus de la plupart limit + 1 des 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.