FetchXML には、複数のデータ行の合計、平均、最小、最大、カウントを計算できるグループ化機能と集計機能が含まれています。
集計値を返すには、次の手順を実行する必要があります。
fetch 要素
aggregate属性を true に設定します。各
aggregateの属性を、次のいずれかの集計関数に設定します。機能 値を返す avgデータを含む列値の平均値。 count行の数。 countcolumnその列にデータがある行の数。 maxその列の行の最大値。 minその列の行の最小値。 sumデータを含む列値の合計値。
次の点に注意してください。
- 集計値を計算する場合、null 値は考慮されません。
- link-entity 要素を使用して結合されたテーブルのデータを使用できます。
- フィルターを 適用 して、任意のクエリと同様に結果を制限できます。
Example
たとえば、次のデータを含むアカウント レコードが 10 個あるとします。
| 従業員数 | 名前 | 住所 1 市区町村 | 作成日 |
|---|---|---|---|
| NULL | アカウントの例 | NULL | 2023/8/25 |
| 1,500 | Contoso Pharmaceuticals (例) | レドモンド | 3/25/2023 |
| 2,700 | Fabrikam, Inc. (サンプル) | リンウッド | 3/25/2023 |
| 2,900 | ブルー・ヨンダー航空 (サンプル) | ロサンゼルス | 3/25/2023 |
| 2,900 | シティ パワー & ライト (サンプル) | レドモンド | 3/25/2023 |
| 3,900 | コホー ワイナリー (サンプル) | Phoenix | 3/25/2023 |
| 4,300 | Adventure Works (サンプル) | サンタクルーズ | 3/25/2023 |
| 4,800 | アルパイン スキー ハウス (サンプル) | ミズーラ | 3/25/2023 |
| 6,000 | Litware, Inc. (サンプル) | Dallas | 3/25/2023 |
| 6,200 | A. 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>
結果は 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 を返します。
Grouping
集計クエリの結果をグループ化するには、ではなく、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 | 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>
行集計
テーブルに 階層リレーションシップが定義されている場合は、階層リレーションシップの参照列で行集計を返すことができます。
次の例では、子アカウントレコードのCountChildren列が現在のアカウントparentaccountid列と等しい場合に、accountidという名前の列の関連するアカウントの数を返します。
<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>
結果は、最大 6 つのレコードを集計する 1 つの行です。
-----------------
| account_count |
-----------------
| 6 |
-----------------
次のステップ
行をカウントする方法について説明します。