Agregar dados usando QueryExpression

QueryExpression inclui recursos de agrupamento e agregação que permitem calcular soma, média, mínimo, máximo e contagem entre várias linhas de dados.

Para retornar um valor agregado, você deve:

• Deixe a propriedade ColumnSet.AllColumns definida como false. Não defina isso usando o construtor ColumnSet(Boolean).

• Adicionar instâncias da classe XrmAttributeExpression à coleção ColumnSet.AttributeExpressions

• Para cada coluna que você deseja retornar, defina estas propriedades:

  Propriedade XrmAttributeExpression	Description
  AttributeName	LogicalName da coluna
  Alias	O valor do alias para a coluna

• Para cada coluna que você deseja agregar, defina o AggregateType como o tipo de agregação que você deseja executar. Saiba mais sobre os tipos de agregação

• Para cada coluna pela qual você deseja agrupar os resultados, defina estas propriedades:

  Propriedade XrmAttributeExpression	Description
  AggregateType	Definir como XrmAggregateType.None
  HasGroupBy	Defina como true.
  DateTimeGrouping	Quando o agrupamento envolver valores de data e hora, especifique o tipo de agrupamento de data e hora a ser aplicado usando os membros da enumeração XrmDateTimeGrouping. Saiba mais sobre o agrupamento por partes de uma data

Tipos de agregação

Os tipos de agregação que você pode usar pertencem à XrmAggregateType enum.

Observe os seguintes pontos:

• Valores nulos não são considerados ao calcular valores agregados.
• Os dados agregados são retornados como um AliasedValue.
• Você pode usar dados de tabelas unidas usando a classe LinkEntity.
• Você pode aplicar filtros para limitar os resultados como em qualquer consulta.

Exemplo

Digamos que você tenha 10 registros de conta com os seguintes dados:

A consulta a seguir retorna dados agregados para a numberofemployees coluna.

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)
            }
        }
    }
};

Os resultados são uma única linha:

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

Grouping

Agrupe os resultados de uma consulta de agregação adicionando um XrmAttributeExpression com a propriedade HasGroupByAggregateType definida como XrmAggregateType.None.

Ao agrupar, você deve especificar um QueryExpression.Orders com uma OrderExpression que tenha a propriedade Alias definida como o alias do grupo.

Se um agrupado por valor for nulo, ele não aparecerá nos resultados.

Por exemplo, a consulta a seguir retorna a soma dos funcionários e a contagem por cidade:

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

A consulta agrupa os resultados por City valor, combinando os resultados das três linhas em que a cidade é 'Redmond'.

Agrupamento por partes de uma data

Você pode selecionar qual parte da data usar ao agrupar por data. Defina o XrmAttributeExpression.DateTimeGrouping como um dos membros da enumeração XrmDateTimeGrouping.

A consulta a seguir agrupa registros de conta mostrando o número de funcionários quando os registros foram criados:

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

A tabela a seguir mostra o resultado usando o conjunto de dados de exemplo mencionado anteriormente:

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

Exemplo de agrupamento de datas do período fiscal

O exemplo a seguir mostra uma QueryExpression que soma o número total de pedidos atendidos e agrupa o resultado por semestre fiscal e ano fiscal.

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

Limitações de agregação de QueryExpression

Esta seção descreve os recursos disponíveis usando a agregação com FetchXml que não estão disponíveis no momento usando QueryExpression.

Obter um número distinto com CountColumn

Você não pode obter um número distinto de valores usando CountColumn com QueryExpression. Saiba mais sobre valores de coluna distintos usando FetchXml

Fuso horário ao agrupar por data

O agrupamento por partes de uma data sempre usa a hora UTC e não há como especificar que o fuso horário do usuário deve ser usado, em vez disso , disponível no FetchXml

Agregação de linha

Quando uma tabela tem uma relação hierárquica definida, você não pode retornar uma agregação de linha na coluna de pesquisa para a relação hierárquica. Saiba mais sobre agregações de linha usando FetchXml

Limite por consulta

Não há como especificar um limite de agregação configurável. Saiba mais sobre os limites por consulta usando FetchXml

Erro de limite de registros em consulta agregada

As consultas que retornam valores agregados são limitadas a 50.000 registros. Esse limite ajuda a manter o desempenho e a confiabilidade do sistema. Se os critérios de filtro na consulta retornarem mais de 50.000 registros, você receberá o seguinte erro:

Número: -2147164125
Código: 8004E023
Mensagem: AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Mensagem de erro do cliente: o limite máximo de registro é excedido. Reduzir o número de registros.

Para evitar esse erro, adicione filtros apropriados à consulta para garantir que ela não avalie mais de 50.000 registros. Em seguida, execute a consulta várias vezes e combine os resultados. Os filtros apropriados dependem da natureza dos dados, mas podem ser um intervalo de datas ou um subconjunto de valores em uma coluna de escolha.

Próximas Etapas 

Saiba como contar linhas.