通过

使用 FetchXml 聚合数据

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

若要返回聚合值，必须：

  • 将 fetch 元素aggregate 属性设置为 true。

  • 为每个属性元素设置alias属性。

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

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

请注意以下几点：

  • 计算聚合值时，不考虑 Null 值。
  • 可以使用通过 链接实体元素联接的表中的数据。
  • 可以 应用筛选器 来限制任何查询的结果。

Example

假设有 10 个帐户记录，其中包含以下数据：

员工数 Name 地址 1 城市 创建日期
Null 示例帐户 Null 8/25/2023
1,500 Contoso Pharmaceuticals（示例） 雷德蒙德 3/25/2023
2,700 Fabrikam, Inc.（示例） 林伍德 3/25/2023
2,900 Blue Yonder Airlines（示例） 洛杉矶 3/25/2023
2,900 City Power & Light（示例） 雷德蒙德 3/25/2023
3,900 Coho Winery（示例） Phoenix 3/25/2023
4,300 Adventure Works (示例) 圣克鲁兹 3/25/2023
4,800 Alpine Ski House（示例） 米苏拉 3/25/2023
6,000 Litware, Inc.（示例） Dallas 3/25/2023
6,200 答： Datum Corporation （示例） 雷德蒙德 3/25/2023

以下查询返回列的 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>

结果显示为单行：

 --------------------------------------------------------------
 | Average | Count | ColumnCount | Maximum | Minimum | Sum    |
 --------------------------------------------------------------
 | 3,911   | 10    | 9           | 6,200   | 1,500   | 35,200 |
 --------------------------------------------------------------

唯一列值

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

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

为上一个查询进行设置时，结果返回 8 而不是 9，因为数据集中有两行的员工人数值为 2,900。

Grouping

通过添加具有groupby而不是aggregate属性对聚合查询的结果进行分组。 分组时，应指定一个，并将其值设置为该组的。

例如，以下查询返回雇员总数和按城市的计数：

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

查询按 City 值对结果进行分组，并将城市为“Redmond”的三行的结果组合在一起。

Total 计数 City
0 1 Null
6,000 1 Dallas
2,900 1 洛杉矶
2,700 1 林伍德
4,800 1 米苏拉
3,900 1 Phoenix
10,600 3 雷德蒙德
4,300 1 圣克鲁兹

按日期部分分组

可以选择按日期分组时要使用的日期部分。 将 特性元素dategrouping 属性设置为以下值之一：

价值 Description
day 按月的日期分组
week 按年份的一周分组
month 按年度月份分组
quarter 按会计年度季度分组
year 按年份分组
fiscal-period 按会计年度期间分组
fiscal-year 按会计年度分组

详细了解会计年度设置

默认情况下，日期分组使用用户的时区。 将 属性元素usertimezone 属性设置为 "false" 指定改用 UTC 时区。

以下查询对帐户记录按记录创建时间进行分组，显示每组员工人数：

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

下表显示了使用前面提到的 示例 数据集的结果：

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

会计周期日期分组示例

以下示例显示了 FetchXML 聚合表达式，该表达式按会计学期和会计年度对完成的订单总数进行求和，并将结果分组。

<fetch aggregate="true">
   <entity name="order">
      <attribute name="totalamount"
         aggregate="sum"
         alias="total" />
      <attribute name="datefulfilled"
         groupby="true"
         dategrouping="fiscal-period" />
   </entity>
</fetch>

行聚合

当表 定义了分层关系时，可以对分层关系的查找列返回行聚合。

以下示例返回相关账户的数量，当子账户记录中parentaccountid列等于当前账户的accountid列时，此数量会显示在名为CountChildren的列中。

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

局限性

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

编号： -2147164125
代码：8004E023
消息：AggregateQueryRecordLimit exceeded. Cannot perform this operation.
客户端错误消息：超出最大记录限制。 请减少记录数。

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

每个查询的限制

即使应用了聚合查询的默认限制，查询可能需要一些时间才能完成。 可以使用 aggregatelimit 查询中的属性来应用自定义下限。

如果指定了属性aggregatelimit，那么在达到限制时，查询不会返回AggregateQueryRecordLimit exceeded错误，而是从数据中聚合最多limit + 1任意行。

每个查询限制不能超过默认聚合限制。

Example

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

<fetch aggregate='true'
   aggregatelimit = '5'>
   <entity name='account'>
      <attribute name='name'
         alias='account_count'
         aggregate='count' />
   </entity>
</fetch>

结果是聚合最多六条记录的一行：

 -----------------
 | account_count |
 -----------------
 | 6             |
 -----------------

后续步骤

了解如何计数行。

统计行数

  • Last updated on