Agregar datos mediante FetchXML

Artigo
03/14/2024

FetchXML incluye características de agrupación y agregación que le permiten calcular la suma, media, mínimo, máximo y recuento a lo largo de varias filas de datos.

Para devolver un valor agregado, debe:

Establezca el atributo elemento de fetch aggregate en verdadero.
Establezca el atributo alias para cada elemento de atributo.

Establezca el atributo aggregate para cada elemento de atributo en una de estas funciones agregadas:

Función	Valor de retorno
`avg`	El valor promedio de los valores de la columna con datos.
`count`	Número de filas.
`countcolumn`	El número de filas con datos en esa columna.
`max`	El valor máximo de las filas de esa columna.
`min`	El valor mínimo de las filas de esa columna.
`sum`	El valor total de los valores de la columna con datos.

Tenga en cuenta los aspectos siguientes:

Los valores nulos no se tienen en cuenta al calcular los valores agregados.
Puede utilizar datos de tablas unidas mediante el elemento vínculo-entidad.
Puedes aplicar filtros para limitar los resultados como con cualquier consulta.

Ejemplo

Digamos que tiene 10 registros de cuenta con los siguientes datos:

Número de empleados	Name	Dirección 1 Ciudad	Creadas el
NULL	Cuenta de ejemplo	NULL	8/25/2023
1.500	Contoso Pharmaceuticals (muestra)	Sevilla	3/25/2023
2.700	Fabrikam, Inc. (muestra)	Lynnwood	3/25/2023
2.900	Blue Yonder Airlines (muestra)	Los Angeles	3/25/2023
2.900	City Power & Light (muestra)	Sevilla	3/25/2023
3.900	Coho Winery (muestra)	Phoenix	3/25/2023
4.300	Adventure Works (ejemplo)	Santa Cruz	3/25/2023
4.800	Alpine Ski House (muestra)	Missoula	3/25/2023
6000	Litware, Inc. (muestra)	Dallas	3/25/2023
6.200	R: Datum Corporation (muestra)	Sevilla	3/25/2023

La siguiente consulta devuelve datos agregados para la columna numberofemployees.

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

Los resultados son una sola fila:

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

Valores de columna distintos

Al utilizar la función agregada countcolumn, puede configurar el atributo distinct para que devuelva un recuento de valores únicos para la columna.

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

Cuando se configura para la consulta anterior, los resultados devuelven 8 en lugar de 9 porque dos filas en conjunto de datos tienen 2900 como valor de número de empleados.

Agrupación

Agrupe los resultados de una consulta agregada agregando un elemento de atributo con el atributo groupby en lugar del atributo aggregate. Al agrupar, debe especificar un elemento de orden con un alias valor establecido en el alias del grupo.

Por ejemplo, la siguiente consulta devuelve la suma de empleados y cuenta por ciudad:

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

La consulta agrupa los resultados por valor de ciudad y combina los resultados de las tres filas donde la ciudad es "Redmond".

Total	Total	City
0	1	NULL
6000	1	Dallas
2.900	1	Los Angeles
2.700	1	Lynnwood
4.800	1	Missoula
3.900	1	Phoenix
10.600	3	Sevilla
4.300	1	Santa Cruz

Agrupación por partes de una fecha

Puede seleccionar qué parte de la fecha usar al agrupar por fecha. Establezca el atributo elemento de atributo dategrouping en uno de los siguientes valores:

valor	Descripción
`day`	Agrupar por el día del mes
`week`	Agrupar por la semana del año
`month`	Agrupar por el mes del año
`quarter`	Agrupar por el trimestre del año fiscal
`year`	Agrupar por el año
`fiscal-period`	Agrupar por el período del año fiscal
`fiscal-year`	Agrupar por el año fiscal

Más información acerca de la configuración del año fiscal

De forma predeterminada, las agrupaciones de fechas utilizan la zona horaria del usuario. Establezca el atributo elemento de atributo usertimezone en "false" para especificar que se utilice la zona horaria UTC en su lugar.

La siguiente consulta agrupa registros de cuentas que muestran el número de empleados según el momento en que se crearon los registros:

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

La siguiente tabla muestra el resultado usando el conjunto de datos de ejemplo 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     |
 -----------------------------------------------------------------------

Período fiscal ejemplo de agrupación de fechas

El siguiente ejemplo muestra una expresión de agregación FetchXML que suma el número total de pedidos completados y agrupa el resultado por semestre y año fiscal.

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

Agregación de filas

Cuando una tabla tiene una relación jerárquica definida, puede devolver un agregado de filas en la columna de búsqueda para la relación jerárquica.

El siguiente ejemplo devuelve el número de cuentas relacionadas en una columna denominada CountChildren cuando la columna de registros de cuenta secundaria parentaccountid es igual a la columna de cuenta actual accountid.

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

Limitaciones

Las consultas que devuelven valores agregados están limitadas a 50.000 registros. Este límite ayuda a mantener el rendimiento y confiabilidad del sistema. Si el criterio de filtra en su consulta devuelve más de 50.000 registros se mostrará el error siguiente:

Número: -2147164125
Código: 8004E023
Mensaje: AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Mensaje de error al cliente: Se supera el límite de registros máximos. Reduzca el número de registros.

Para evitar este error, agregue filtros adecuados a la consulta para asegurarse de que no evaluará más de 50.000 registros. Después, ejecute su consulta varias veces y combine los resultados. Los filtros adecuados dependen de la naturaleza de sus datos, pero podrían ser un rango de fechas o un subconjunto de valores en una columna de elección.

Límite por consulta

Incluso con el límite predeterminado para consultas agregadas aplicado, la consulta puede tardar algún tiempo en completarse. Puede utilizar el atributo aggregatelimit en una consulta para aplicar un límite inferior personalizado que devuelva el error AggregateQueryRecordLimit exceeded si los resultados son superiores a su límite personalizado.

En este ejemplo, el límite máximo de filas personalizado es 10:

<fetch aggregate='true'
   aggregatelimit = '10'>
   <entity name='opportunity'>
      <attribute name='name'
         alias='opportunity_count'
         aggregate='count' />
   </entity>
</fetch>

El límite por consulta no puede exceder el límite agregado predeterminado.

Pasos siguientes

Aprenda a contar las filas.

Contar filas

Nota

¿Puede indicarnos sus preferencias de idioma de documentación? Realice una breve encuesta. (tenga en cuenta que esta encuesta está en inglés)

La encuesta durará unos siete minutos. No se recopilan datos personales (declaración de privacidad).

Compartir por