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

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

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

• ColumnSet.AllColumns property を false に設定したままにします。 ColumnSet(Boolean) コンストラクターを使用して設定しないでください。

• XrmAttributeExpression クラス のインスタンスを ColumnSet.AttributeExpressions コレクションに追加します

• 返される列ごとに、次のプロパティを設定します。

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

• 集計したい列ごとに、AggregateType を実行する集計の種類に設定します。 集計の種類について

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

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

集計の種類

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

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

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

例

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

次のクエリは、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 つの行の結果を結合します。

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

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

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

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

次の手順

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