次の方法で共有


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

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 分かかります。 個人データは収集されません (プライバシー ステートメント)。