FetchXML を使用してデータを集計する

[アーティクル]
07/22/2024

FetchXML には、合計、平均、最小値、最大値、および複数のデータの行数を計算できるグループ化と集計の機能が含まれています。

集計値を返すには、次のことを行う必要があります:

取得の要素 aggregate 属性を true に設定します。
各属性要素に alias 属性を設定します。

各属性要素の aggregate 属性を次の集計関数のいずれかに設定します。

Function	値を返す
`avg`	データを持つ列値の平均値です。
`count`	返される行の数です。
`countcolumn`	その列にデータがある行の数です。
`max`	その列に含まれる行の最大値です。
`min`	その列に含まれる行の最小値です。
`sum`	データを持つ列値の合計値です。

次のポイントに注意します。

集計値を計算する場合、Null 値は考慮されません。
link-entity 要素を使用して結合されたテーブルのデータを使用できます。
他のクエリと同様に、フィルターを適用して結果を制限できます。

例

次のデータを含む 10 個のアカウントレコードがあるとします。

従業員数	件名	住所 1 市区町村	作成日
NULL	たとえば、「取引先企業」です	NULL	2023/8/25
1,500	Contoso Pharmaceuticals (サンプル)	レドモンド	2023/3/25
2,700	Fabrikam, Inc. (サンプル)	リンウッド	2023/3/25
2,900	Blue Yonder Airlines (サンプル)	ロサンゼルス	2023/3/25
2,900	City Power & Light (サンプル)	レドモンド	2023/3/25
3,900	Coho Winery (サンプル)	フェニックス	2023/3/25
4,300	Adventure Works (サンプル)	サンタクルーズ	2023/3/25
4,800	Alpine Ski House (サンプル)	ミズ―ラ	2023/3/25
6,000	Litware, Inc. (サンプル)	ダラス	2023/3/25
6,200	A. Datum Corporation (サンプル)	レドモンド	2023/3/25

次のクエリは、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>

結果は 1 行です:

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

前のクエリに設定すると、データセット内の 2 行の従業員数の値が 2,900 であるため、結果は 9 ではなく 8 を返します。

グループ

集約クエリの結果をグループ化するには、aggregate 属性ではなく、groupby 属性の属性要素を追加します。グループ化する場合は、alias の値をグループの alias に設定した順序要素を指定する必要があります。

たとえば、次のクエリは従業員の合計と都市ごとの数を返します:

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

このクエリは、結果を都市の値ごとにグループ化し、都市が「Redmond」である 3 つの行の結果を結合します。

トータル	数	都市
0	1	NULL
6,000	1	ダラス
2,900	1	Los Angeles
2,700	1	リンウッド
4,800	1	ミズ―ラ
3,900	1	フェニックス
10,600	3	レドモンド
4,300	1	サンタクルーズ

日付の部分ごとにグループ化する

日付でグループ化するときに、日付のどの部分を使用するかを選択できます。属性要素 dategrouping 属性を次のいずれかの値に設定します:

価値	Description
`day`	日付でのグループ化
`week`	週でのグループ化
`month`	月でのグループ化
`quarter`	会計年度の四半期でのグループ化
`year`	年でのグループ化
`fiscal-period`	会計年度の期でのグループ化
`fiscal-year`	会計年度でのグループ化

会計年度設定に関する詳細情報

デフォルトでは、日付のグループ化にはユーザーのタイムゾーンが使用されます。代わりにUTCタイムゾーンを使用するように指定するには、属性要素 usertimezone 属性を "false" に設定します。

次のクエリは、レコードが作成された時点による従業員数を示すアカウントレコードをグループ化します:

<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 属性を使用すると、結果がカスタム制限を超えた場合に AggregateQueryRecordLimit exceeded エラーを返すカスタム下限を適用できます。

この例では、カスタムの最大行数制限は 10 です:

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

クエリごとの制限は、既定の集計制限を超えることはできません。

次の手順

行の棚卸方法について説明します。

行の計測

注意

ドキュメントの言語設定についてお聞かせください。簡単な調査を行います。 (この調査は英語です)

この調査には約 7 分かかります。個人データは収集されません (プライバシーステートメント)。

次の方法で共有