Dynamics CRM 365 使用 FetchXml 聚合数据(Aggregate data using 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 不能超过默认聚合限制。

posted @   Destiny、Yang  阅读(203)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· 【.NET】调用本地 Deepseek 模型
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
点击右上角即可分享
微信分享提示