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

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

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

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

ColumnSet.AllColumns property を false に設定したままにします。 ColumnSet(Boolean) コンストラクターを使用して設定しないでください。
XrmAttributeExpression クラスのインスタンスを ColumnSet.AttributeExpressions コレクションに追加します
返される列ごとに、次のプロパティを設定します。

XrmAttributeExpression プロパティ説明

AttributeName 列の LogicalName

エイリアス列のエイリアス値
集計したい列ごとに、AggregateType を実行する集計の種類に設定します。集計の種類について

XrmAttributeExpression プロパティ	説明
AttributeName	列の `LogicalName`
エイリアス	列のエイリアス値

結果をグループ化する列ごとに、次のプロパティを設定します。

XrmAttributeExpression プロパティ	説明
AggregateType	XrmAggregateType`.None`に設定
HasGroupBy	`true`に設定します。
DateTimeGrouping	グループ化に日時値が含まれる場合は、XrmDateTimeGrouping 列挙型メンバーを使用して適用する日時グループ化のタイプを指定します。日付の部分によるグループ化について

集計の種類

実行できる集計のタイプは、XrmAggregateType 列挙型メンバーです。

XrmAggregateType メンバー	説明
`Avg`	データを持つ列値の平均値です。
`Count`	返される行の数です。
`CountColumn`	その列にデータがある行の数です。
`Max`	その列に含まれる行の最大値です。
`Min`	その列に含まれる行の最小値です。
`None`	集計データをグループ化するために列を使用する場合にこれを使用します。 HasGroupBy 値を `true` に設定する必要があります。
`Sum`	データを持つ列値の合計値です。

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

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

例

次のデータを含む 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 列の集計データを返します。

QueryExpression query = new()
{
    EntityName = "account",
    ColumnSet = new ColumnSet(false)
    {
        AttributeExpressions = {
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Average",
                    aggregateType: XrmAggregateType.Avg)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Count",
                    aggregateType: XrmAggregateType.Count)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "ColumnCount",
                    aggregateType: XrmAggregateType.CountColumn)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Maximum",
                    aggregateType: XrmAggregateType.Max)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Minimum",
                    aggregateType: XrmAggregateType.Min)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Sum",
                    aggregateType: XrmAggregateType.Sum)
            }
        }
    }
};

結果は 1 行です:

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

グループ

集計クエリの結果をグループ化するには、HasGroupBy プロパティ AggregateType で XrmAttributeExpression を追加し、XrmAggregateType.None に設定します。

グループ化する場合、グループのエイリアスに設定されたエイリアスプロパティを持つ OrderExpression を使用して QueryExpression.Orders を指定する必要があります。

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

QueryExpression query = new()
{
      EntityName = "account",
      ColumnSet = new ColumnSet(false)
   {
         AttributeExpressions = {
            {
                  new XrmAttributeExpression(
                     attributeName: "numberofemployees",
                     alias: "Total",
                     aggregateType: XrmAggregateType.Sum)
            },
            {
                  new XrmAttributeExpression(
                     attributeName: "address1_city",
                     alias: "Count",
                     aggregateType: XrmAggregateType.Count)
            },
            {
                  new XrmAttributeExpression(
                     attributeName: "address1_city",
                     alias: "City",
                     aggregateType: XrmAggregateType.None){
                     HasGroupBy = true
            }
         }
      }
   }
};
query.Orders.Add(new OrderExpression(
            attributeName: "address1_city",
            alias: "City",
            orderType: OrderType.Ascending));

クエリは結果を City 値別にグループ化し、都市が "レドモンド" である 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	サンタクルーズ

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

日付でグループ化するときに、日付のどの部分を使用するかを選択できます。 XrmAttributeExpression.DateTimeGrouping を XrmDateTimeGrouping enum メンバーの 1 つに設定します。

XrmDateTimeGrouping メンバー	説明
`None`	DateTime グループ化なし
`Day`	日別のグループ化
`Week`	週別のグループ化
`Month`	月別のグループ化
`Quarter`	四半期別のグループ化
`Year`	年別のグループ化
`FiscalPeriod`	FiscalPeriod のグループ化
`FiscalYear`	FiscalYear のグループ化

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

QueryExpression query = new()
{
    EntityName = "account",
    ColumnSet = new ColumnSet(false)
    {
        AttributeExpressions = {
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Total",
                    aggregateType: XrmAggregateType.Sum)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Day",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Day
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Week",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Week
                }
            },
                                    {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Month",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Month
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Year",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Year
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "FiscalPeriod",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.FiscalPeriod
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "FiscalYear",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.FiscalYear
                }
            }
        }
    }
};
query.Orders.Add(new OrderExpression(
            attributeName: "createdon",
            alias: "Month",
            orderType: OrderType.Ascending));

次の表は、前述のサンプルデータセットを使用した結果を示しています。

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

会計期間の日付グループ化の例

以下は、処理済みの注文の総計を計算して結果を上半期/下半期ごとと会計年度ごとにまとめる QueryExpression です。

QueryExpression query = new()
{
    EntityName = "salesorder",
    ColumnSet = new ColumnSet(false)
    {
        AttributeExpressions = {
            {
                new XrmAttributeExpression(
                    attributeName: "totalamount",
                    alias: "total",
                    aggregateType: XrmAggregateType.Sum)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "datefulfilled",
                    alias: "date",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.FiscalPeriod
                }
            }
        }
    }
};
query.Orders.Add(new OrderExpression(
            attributeName: "datefulfilled",
            alias: "Date",
            orderType: OrderType.Ascending));

QueryExpression 集計の制限

このセクションでは、QueryExpression では現在利用できないが、FetchXml による集計で利用できる機能について説明します。

CountColumn で一意の数値を取得する

QueryExpression で CountColumn を使用して、一意の値を取得することはできません。 FetchXml を使用した個別の列値について

日付でグループ化する場合のタイムゾーン

日付の部分別のグループ化では常に UTC 時間が使用され、代わりにユーザーのタイムゾーンを使用するように指定する方法はありません FetchXml で利用可能

行の集計

表に階層関係が定義されている場合、階層関係の参照列で行集計を返すことはできません。 FetchXml を使用した行集計について

クエリごとの制限

構成可能な集計制限を指定する方法はありません。 FetchXml を使用したクエリごとの制限について

制限

返される集計値が 50,000 レコードに限定されたクエリ。この最大値で、システムの動作および信頼性を保持することができます。クエリ内のフィルター条件に 50,000 を超えるレコードが含まれる場合は次のエラーが表示されます:

番号: -2147164125
コード: 8004E023
メッセージ: AggregateQueryRecordLimit exceeded. Cannot perform this operation.
クライアントエラーメッセージ: 最大レコード制限を超えています。レコード数を減らしてください。

このエラーを回避するには、適切なフィルターをクエリに追加して、50,000 超のレコードを評価する必要がないことを確認します。その後、クエリを何度か実行して結果をまとめます。適切なフィルターはデータの性質によって異なりますが、日付範囲または選択列の値のサブセットである可能性があります。

次の手順

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

行の計測

注意

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

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

次の方法で共有

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

集計の種類

例

グループ

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

会計期間の日付グループ化の例

QueryExpression 集計の制限

CountColumn で一意の数値を取得する

日付でグループ化する場合のタイムゾーン

行の集計

クエリごとの制限

制限

次の手順

その他のリソース

次の方法で共有

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

集計の種類

例

グループ

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

会計期間の日付グループ化の例

QueryExpression 集計の制限

CountColumn で一意の数値を取得する

日付でグループ化する場合のタイム ゾーン

行の集計

クエリごとの制限

制限

次の手順

その他のリソース

日付でグループ化する場合のタイムゾーン