Partager via

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

  • Article

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 :

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

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é).