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

Article
03/14/2024

FetchXML inclut des fonctionnalités de regroupement et d’agrégation qui vous permettent de calculer une somme, une moyenne, un nombre minimal et maximal dans plusieurs 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 :

Nombre d’employés	Nom	Adresse 1 Ville	Date de création
NULL	Exemple de compte	NULL	25/08/2023
1 500	Contoso Pharmaceuticals (exemple)	Colombes	25/03/2023
2 700	Fabrikam, Inc. (exemple)	Lynnwood	25/03/2023
2 900	Blue Yonder Airlines (exemple)	Los Angeles	25/03/2023
2 900	City Power & Light (exemple)	Colombes	25/03/2023
3 900	Coho Winery (exemple)	Phoenix	25/03/2023
4 300	Adventure Works (exemple)	Santa Cruz	25/03/2023
4 800	Alpine Ski House (exemple)	Missoula	25/03/2023
6 000	Litware, Inc. (exemple)	Dallas	25/03/2023
6 200	A. Datum Corporation (exemple)	Colombes	25/03/2023

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 ».

Total	Nombre	Ville
0	1	NULL
6 000	1	Dallas
2 900	1	Los Angeles
2 700	1	Lynnwood
4 800	1	Missoula
3 900	1	Phoenix
10 600	3	Colombes
4 300	1	Santa Cruz

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 :

active	Description
`day`	Regrouper par jour du mois
`week`	Regrouper par semaine de l’année
`month`	Regrouper par mois de l’année
`quarter`	Regrouper par trimestre de l’exercice
`year`	Regrouper par année
`fiscal-period`	Regrouper par période de l’exercice
`fiscal-year`	Regrouper par exercice

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

Par défaut, les regroupements de dates utilisent l’heure UTC. Définissez l’attribut usertimezone de l’élément attribute pour spécifier que le fuseau horaire de l’utilisateur 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 lenombre total de commandes exécutées et regroupe 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.

Compter des lignes

Notes

Pouvez-vous nous indiquer vos préférences de langue pour la documentation ? Répondez à un court questionnaire. (veuillez noter que ce questionnaire est en anglais)

Le questionnaire vous prendra environ sept minutes. Aucune donnée personnelle n’est collectée (déclaration de confidentialité).

Share via