标签：13 聚合示例 52 FetchXml -----------------------------------------------------------

前言

FetchXML 包括分组和聚合功能，可用于计算多行数据的总和、平均值、最小值、最大值和计数。

若要返回聚合值，必须：

将aggregate设置为true。
为每个属性元素设置别名alias 属性。

将每个属性元素的aggregate属性设置为以下聚合函数之一:

函数	返回值
avg	包含数据的列值的平均值。
count	总行数
countcolumn	该列中包含数据的行数。
max	该列中行的最大值。
min	该列中行的最小值。
sum	包含数据的列值的总值。

请注意以下几点：

计算聚合值时不考虑 Null 值。
使用通过 link-entity 元素联接的表中的数据。
与任何查询一样，应用 filters 来限制结果。

数据聚合（Aggregate ）

假设有 10 条包含以下数据的客户记录：

 -----------------------------------------------------------------------------
 | name                 | createdon      | address1_city | numberofemployees |
 -----------------------------------------------------------------------------
 | 第四极咖啡屋 (示例)     | 2024/5/9 13:52 | 北票            | 9,500             |
 -----------------------------------------------------------------------------
 | 立特威公司 (示例)       | 2024/5/9 13:52 | 厦门            | 6,000             |
 -----------------------------------------------------------------------------
 | Adventure Works (示例) | 2024/5/9 13:52 | 西丰            | 4,300             |
 -----------------------------------------------------------------------------
 | Fabrikam, Inc. (示例)  | 2024/5/9 13:52 | 新民            | 2,700             |
 -----------------------------------------------------------------------------
 | 蓝天航空公司 (示例)     | 2024/5/9 13:52 | 曲阜            | 2,900             |
 -----------------------------------------------------------------------------
 | 城市电力照明公司 (示例)  | 2024/5/9 13:52 | 大连            | 2,900             |
 -----------------------------------------------------------------------------
 | Contoso 制药公司 (示例) | 2024/5/9 13:52 | 大连            | 1,500             |
 -----------------------------------------------------------------------------
 | 阿尔卑斯山滑雪馆舍 (示例)| 2024/5/9 13:52 | 邯郸            | 4,800             |
 -----------------------------------------------------------------------------
 | A. Datum 公司 (示例)    | 2024/5/9 13:52 | 大连            | 6,200             |
 -----------------------------------------------------------------------------
 | 佳酿酒庄 (示例)         | 2024/5/9 13:52 | 新民            | 3,900             |
 -----------------------------------------------------------------------------

以下查询返回列numberofemployees的聚合数据。

string fetchXml = @"<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>";
FetchExpression fetchExpression = new FetchExpression(fetchXml);

//Retrieve the data
EntityCollection entityCollection = service.RetrieveMultiple(query: fetchExpression);

返回结果集合

 --------------------------------------------------------------
 | Average | Count | ColumnCount | Maximum | Minimum | Sum    |
 --------------------------------------------------------------
 | 4,470   | 10    | 10          | 9,500   | 1,500   | 44,700 |
 --------------------------------------------------------------

非重复列值（Distinct）

使用countcolumn聚合函数，可以设置distinct属性以返回列的唯一值的计数。

string fetchXml = @"<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'  distinct='true' />
                             <attribute name='numberofemployees' alias='Maximum' aggregate='max' />
                             <attribute name='numberofemployees' alias='Minimum' aggregate='min' />
                             <attribute name='numberofemployees' alias='Sum' aggregate='sum' />
                           </entity>
                         </fetch>";
FetchExpression fetchExpression = new FetchExpression(fetchXml);

//Retrieve the data
EntityCollection entityCollection = service.RetrieveMultiple(query: fetchExpression);

当为前一个查询设置时，结果返回9而不是10，因为数据集中有两行的雇员人数值为2900。

 --------------------------------------------------------------
 | Average | Count | ColumnCount | Maximum | Minimum | Sum    |
 --------------------------------------------------------------
 | 4,470   | 10    | 9           | 9,500   | 1,500   | 44,700 |
 --------------------------------------------------------------

分组（Grouping）

通过添加带有groupby属性而不是聚合属性的属性元素，对聚合查询的结果进行分组。在分组时，应该指定一个order元素，并将别名值设置为组的别名。

例如，下面的查询返回雇员总数，并按城市计数:

string fetchXml = @"<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>";
FetchExpression fetchExpression = new FetchExpression(fetchXml);

//Retrieve the data
EntityCollection entityCollection = service.RetrieveMultiple(query: fetchExpression);

返回结果值如下：

 -------------------------
 | Total  | Count | City |
 -------------------------
 | 9,500  | 1     | 北票   |
 -------------------------
 | 10,600 | 3     | 大连   |
 -------------------------
 | 4,800  | 1     | 邯郸   |
 -------------------------
 | 2,900  | 1     | 曲阜   |
 -------------------------
 | 4,300  | 1     | 西丰   |
 -------------------------
 | 6,000  | 1     | 厦门   |
 -------------------------
 | 6,600  | 2     | 新民   |
 -------------------------

按日期的各个部分分组（dategrouping）

Value	描述
`day`	按月份中的某一天分组
`week`	按一年中的一周分组
`month`	按一年中的月份分组
`quarter`	按财政年度季度分组
`year`	按年份分组
`fiscal-period`	按会计年度期间分组
`fiscal-year`	按会计年度分组

以下查询按记录创建时间对显示员工数的客户记录进行分组：

string fetchXml = @"<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>";
FetchExpression fetchExpression = new FetchExpression(fetchXml1);

//Retrieve the data
EntityCollection entityCollection = service.RetrieveMultiple(query: fetchExpression);

返回结果

 -------------------------------------------------------------------
 | Total  | Day | Week | Month | Year  | FiscalPeriod | FiscalYear |
 -------------------------------------------------------------------
 | 44,700 | 9   | 19   | 5     | 2,024 | FY20242 季度   | FY2024     |
 -------------------------------------------------------------------

行聚合（Row aggregate）

当表具有定义了层次结构关系后，可以在“查找”列上返回层次结构关系的行聚合。

当子帐户记录的parentaccountid列等于当前帐户的accountid列时，下面的示例返回名为CountChildren的列中相关帐户的数量。

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

局限性

返回聚合值的查询限制为 50,000 条记录。此限制有助于保持系统性能和可靠性。如果查询中的筛选条件返回超过 50,000 条记录，则会收到以下错误：

Number: -2147164125
Code: 8004E023
Message: AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Client error message: The maximum record limit is exceeded. Reduce the number of records.

若要避免此错误，请向查询添加适当的筛选器，以确保查询的评估值不超过 50,000 条记录。然后多次运行查询并合并结果。适当的筛选器取决于数据的性质，但它们可以是日期范围或选项列中的值子集。

Per query limit

即使应用了聚合查询的默认限制，查询也可能需要一些时间才能完成。您可以在查询中使用aggregatelimit属性来应用自定义下限，如果结果高于自定义限制，则返回AggregateQueryRecordLimit exceeded错误。

在此示例中，自定义最大行数限制为 10：

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

The per query limit 不能超过默认聚合限制。

标签：13,聚合,示例,52,FetchXml,-----------------------------------------------------------
From： https://www.cnblogs.com/YuYangBlogs/p/18247712

Dynamics CRM 365 使用 FetchXml 聚合数据（Aggregate data using FetchXml）

前言

数据聚合（Aggregate ）

非重复列值（Distinct）

分组（Grouping）

按日期的各个部分分组（dategrouping）

行聚合（Row aggregate）

局限性

Per query limit

相关文章

赞助商

阅读排行

Dynamics CRM 365 使用 FetchXml 聚合数据（Aggregate data using FetchXml）

前言

数据聚合（Aggregate ）

非重复列值（Distinct）

分组 （Grouping）

按日期的各个部分分组 （dategrouping）

行聚合（Row aggregate）

局限性

Per query limit

相关文章

赞助商

阅读排行

分组（Grouping）

按日期的各个部分分组（dategrouping）