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 | ロサンゼルス |
| 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 属性を設定して、代わりにユーザーのタイムゾーンが使用されるように指定します。
次のクエリは、レコードが作成された時点による従業員数を示すアカウント レコードをグループ化します:
<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 分かかります。 個人データは収集されません (プライバシー ステートメント)。
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示