Agregar dados usando FetchXML

Use FetchXML para agregar dados no Microsoft Dataverse calculando soma, média, mínimo, máximo e contagem em várias linhas. Os recursos de agrupamento e agregação fetchXML permitem resumir grandes conjuntos de dados com eficiência para que você possa criar relatórios e análises significativos.

Para retornar um valor agregado, você deve:

• Defina o atributo do elemento de busca como true.

• Defina o alias atributo para cada elemento de atributo.

• Defina o aggregate atributo para cada elemento de atributo como uma destas funções de agregação:

  Função	Retornar valor
  avg	O valor médio dos valores de coluna com dados.
  count	O número de linhas.
  countcolumn	O número de linhas com dados nessa coluna.
  max	O valor máximo das linhas nessa coluna.
  min	O valor mínimo das linhas nessa coluna.
  sum	O valor total dos valores de coluna com dados.

Considere os seguintes pontos:

• O cálculo de valores agregados exclui valores nulos.
• Você pode usar dados de tabelas que estão vinculadas usando o elemento de entidade de ligação.
• Você pode aplicar filtros para limitar os resultados como em qualquer consulta.

Example

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

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

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

Os resultados são uma única linha:

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

Valores de coluna distintos

Ao usar a countcolumn função de agregação, defina o distinct atributo para retornar uma contagem de valores exclusivos para a coluna.

<attribute name='numberofemployees' 
   alias='ColumnCount' 
   aggregate='countcolumn' 
   distinct='true' />

Quando você define esse atributo para a consulta anterior, os resultados retornam 8 em vez de 9 porque duas linhas no conjunto de dados têm 2.900 como o número de valores de funcionários.

Grouping

Agrupe os resultados de uma consulta de agregação adicionando um elemento de atributo com o groupby atributo em vez do aggregate atributo. Ao agrupar, especifique um elemento de ordem com o valor alias definido para o alias grupo.

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

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

A consulta agrupa os resultados por valor de Cidade, combinando os resultados das três linhas em que a cidade está Redmond.

Agrupamento por partes de uma data

Você pode selecionar qual parte da data usar ao agrupar por data. Defina o atributo de elementodategrouping como um dos seguintes valores:

Saiba mais sobre as configurações do ano fiscal

Por padrão, os agrupamentos de datas usam o fuso horário do usuário. Defina o atributo elementousertimezone para "false" especificar que o fuso horário UTC seja usado.

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

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

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 expressão de agregação FetchXML que soma o número total de pedidos atendidos e agrupa o resultado por semestre fiscal e ano fiscal.

<fetch aggregate="true">
   <entity name="order">
      <attribute name="totalamount"
         aggregate="sum"
         alias="total" />
      <attribute name="datefulfilled"
         groupby="true"
         dategrouping="fiscal-period" />
   </entity>
</fetch>

Agregação de linha

Quando uma tabela tem uma relação hierárquica definida, você pode retornar uma agregação de linha na coluna de pesquisa para a relação hierárquica.

O exemplo a seguir retorna o número de contas relacionadas em uma coluna chamada CountChildren quando a coluna de registros da conta filha parentaccountid é igual à coluna da conta atual accountid.

<fetch top='5'>
   <entity name='account'>
      <attribute name='name' />
      <attribute name='accountid'
         alias='numberOfChildren'
         rowaggregate='CountChildren' />
      <order attribute='accountid'
         descending='true' />
   </entity>
</fetch>

Limitações

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.

Limite por consulta

Mesmo com o limite padrão para consultas agregadas aplicadas, a consulta pode levar algum tempo para ser concluída. Você pode usar o aggregatelimit atributo em uma consulta para aplicar um limite inferior personalizado.

Se você especificar o aggregatelimit atributo, a consulta não retornará um AggregateQueryRecordLimit exceeded erro se o limite for atingido. Em vez disso, a consulta agrega no máximo limit + 1 linhas aleatórias dos dados.

O limite por consulta não pode exceder o limite de agregação padrão.

Example

Neste exemplo, o limite máximo de linhas personalizado é 5:

<fetch aggregate='true'
   aggregatelimit = '5'>
   <entity name='account'>
      <attribute name='name'
         alias='account_count'
         aggregate='count' />
   </entity>
</fetch>

Os resultados são uma única linha agregando até seis dos registros.

 -----------------
 | account_count |
 -----------------
 | 6             |
 -----------------

Próximas etapas

Saiba como contar linhas.