Compartilhar via

Agregar dados usando FetchXml

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

  • Defina o atributo fetch elementaggregate 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 Valor de retorno
    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.

Observe os seguintes pontos:

  • Valores nulos não são considerados ao calcular valores agregados.
  • Você pode usar dados de tabelas conectadas através do elemento de entidade-de-link.
  • Você pode aplicar filtros para limitar os resultados como em qualquer consulta.

Example

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

Número de funcionários Nome Endereço 1 Cidade Data da criação
NULO Conta de exemplo NULO 25/08/2023
1,500 Contoso Pharmaceuticals (exemplo) Redmond 3/25/2023
2,700 Fabrikam, Inc. (exemplo) Lynnwood 3/25/2023
2,900 Blue Yonder Airlines (exemplo) Los Angeles 3/25/2023
2,900 City Power & Light (exemplo) Redmond 3/25/2023
3,900 Coho Winery (exemplo) Phoenix 3/25/2023
4,300 Adventure Works (exemplo) Santa Cruz 3/25/2023
4,800 Alpine Ski House (exemplo) Missoula 3/25/2023
6,000 Litware, Inc. (exemplo) Dallas 3/25/2023
6,200 A. Datum Corporation (exemplo) Redmond 3/25/2023

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, você pode definir o distinct atributo para retornar uma contagem de valores exclusivos para a coluna.

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

Quando está configurado para a consulta anterior, os resultados retornam 8 em vez de 9 porque duas linhas no conjunto de dados têm 2.900 como valor para o número 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, você deve especificar um elemento de ordem com um alias valor 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 pelo valor da cidade, combinando os resultados das três linhas em que a cidade é 'Redmond'.

Total Contagem City
0 1 NULO
6,000 1 Dallas
2,900 1 Los Angeles
2,700 1 Lynnwood
4,800 1 Missoula
3,900 1 Phoenix
10,600 3 Redmond
4,300 1 Santa Cruz

Agrupamento por partes de uma data

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

Value Description
day Agrupar por dia do mês
week Agrupar por semana do ano
month Agrupar por mês do ano
quarter Agrupar por trimestre do ano fiscal
year Agrupar por ano
fiscal-period Agrupar por período do ano fiscal
fiscal-year Agrupar por ano fiscal

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 o atributo aggregatelimit for especificado, a consulta também não retornará um erro AggregateQueryRecordLimit exceeded se o limite for atingido; em vez disso, a consulta agregará no máximo limit + 1 linhas arbitrá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.

Contar linhas

  • Last updated on