Agregación de datos con Envision

Agregación de datos











Inicio » Recursos » Aquí

La agregación de datos es el proceso de combinación de varias líneas de una tabla a través de funciones especiales llamadas agregadores. Cada vez que se necesita realizar sumas, promedios, cuentas o medianas, los cálculos se realizan a través de agregadores. La agregación también proporciona un modo de combinar datos originados en diferentes tablas. Envision ofrece una sintaxis rica para respaldar todos esos escenarios. En esta página, detallamos e ilustramos el modo en que funcionan los agregadores con Envision.

Un script ilustrativo

En esta página utilizamos, una vez más, el conjunto de datos de ejemplo, al que debería poder acceder desde la ruta /sample en su cuenta de Lokad. El script a continuación es moderadamente complejo e ilustra algunos de los patrones de agregación de datos disponibles en Envision. La agregación de datos puede tener lugar tanto fuera como dentro de los mosaicos. Le recomendamos que lea Haciendo cálculos con Envision primero, ya que le será útil para comprender los contenidos de esta página.
read "/sample/Lokad_Items.tsv" as Items
read "/sample/Lokad_Orders.tsv" as Orders
read "/sample/Lokad_PO.tsv" as PO
expect Orders.Quantity : number


show label "Filtering data with Envision" a1f1 tomato

oend := max(Orders.Date)
currency := mode(Orders.Currency)

totalPurchased := sum(PurchaseOrders.NetAmount)
totalSold := sum(Orders.NetAmount) when date >= oend - 365
show table "Total purchased{$}" a2c2 with totalPurchased
show table "Sold over 1 year{$}" d2f2 with totalSold

VolumeSold = sum(Orders.NetAmount)
UnitSold = median(Orders.Quantity)
show table "Top sellers" a3f4 tomato with
  Name
  VolumeSold as "Sold{$}"
  UnitSold as "Median"
  order by VolumeSold desc

avgRet := avg(distinct(Orders.Date) by Orders.ClientCode)
avgQty := avg(sum(Orders.Quantity) by [Orders.ClientCode, Orders.Date])
show table "Average client returns" a5c5 with round(avgRet)
show table "Average backet quantity" d5f5 with avgQty

Orders.Brand = Brand
show table "Top Suppliers" a6f7 tomato with
  Supplier
  distinct(Category) as "Categories"
  sum(Orders.NetAmount) as "Sold{$}"
  mode(Orders.Brand) if (Orders.Brand != "Fellowes") as "Most frequent brand sold"
  group by Supplier
  order by sum(Orders.NetAmount) desc
Le sugerimos que, para empezar, copie y pegue este script en su cuenta de Lokad y que lo ejecute una vez para observar el panel de información resultante. Si todo funciona, debería ver el panel de información que se ilustra a continuación.

Image

Escalares para agregaciones de un solo valor

Una variable que no tiene afinidad con ningún "artículo" en Envision se denomina variable escalar (la variable no está unida a ninguna línea específica de la tabla Items). Pensando en la analogía de Excel, las variables de Envision son similares a las columnas de Excel por defecto: esas variables son vectores y contienen varios valores al mismo tiempo (de hecho, un valor por artículo). Si embargo, también es posible tener variables que se comportan como una sola celda de Excel, y que contienen solo un valor: esas son las variables escalares. El script anterior ilustra el modo en que es posible calcular dos variables escalares; las líneas pertinentes se copian a continuación para que resulte más claro.
totalPurchased := sum(PO.NetAmount)
totalSold := sum(Orders.NetAmount) when date >= oend - 365
show table "Total purchased{$}" a2c2 with totalPurchased
show table "Sold over 1 year{$}" d2f2 with totalSold
Es posible resaltar varios aspectos en estas pocas líneas de código. En primer lugar, las dos asignaciones en las líneas 1 y 2 se llevan a cabo utilizando :=, el operador de asignación escalar. Cuando se utiliza el operador := en lugar del operador de asignación simple =, significa que la agregación debería dar como resultado un solo valor que es independiente de los artículos. En segundo lugar, las variables totalPurchased y tableSold comienzan con nombres en minúscula. Si bien los nombres de las variables no distinguen entre mayúsculas y minúsculas en Envision, el modo en que se escriben estas dos variables en este caso no es arbitrario. Como práctica de codificación, le recomendamos utilizar la minúscula para la primera letra de las variables que contienen valores escalares. En cambio, los nombres de las tablas y los nombres de las columnas deberían comenzar con letra mayúscula. Siguiendo estas directrices hará que su script sea más legible y más fácil de depurar. En tercer lugar, la agregación escalar se produce por defecto dentro de la instrucción show table si la tabla tiene una sola columna. Por ejemplo, las líneas 1 y 3 podrían combinarse en una sola, como se ilustra a continuación.
show table "Total purchased{$}" a2c2 with sum(PO.NetAmount)
Las variables escalares numéricas también pueden utilizarse para realizar cálculos arbitrarios, como cualquier variable numérica en Envision. Por ejemplo, si bien podría no tener mucho sentido desde un punto de vista comercial, el siguiente cálculo podría agregarse al final del script de Envision una vez que se hayan definido las variables totalPurchased y totalSold:
mySum := (totalSold - totalPurchased) / 2
show table "My Sum" with mySum

Agregaciones multivalor

Envision también respalda la posibilidad de realizar agregaciones multivalor, generalmente agregando los datos a una determinada tabla y proyectando el resultado en las líneas de otra tabla. Así, la agregación es el método más frecuente para combinar datos que provienen de diferentes tablas. En particular, uno de los casos de uso más frecuentes consiste en tomar una tabla histórica, es decir, una tabla también indexada en una columna Date, y agregar esta tabla a un vector alineado con la tabla Items.

El primer script más arriba ilustra este patrón con una tabla que muestra los artículos más vendidos, es decir, los artículos pedidos de acuerdo con sus respectivos volúmenes de ventas, colocando los valores mayores al principio. Las líneas pertinentes del script se copian a continuación para que resulte más claro.
VolumeSold = sum(Orders.NetAmount)
UnitSold = median(Orders.Quantity)
show table "Top sellers" a3f4 tomato with
  Name
  VolumeSold as "Sold{$}"
  UnitSold as "Median"
  order by VolumeSold desc
Las líneas 1 y 2 están realizando agregaciones de un modo que es muy similar a las agregaciones escalares que hemos visto en la sección anterior. Sin embargo, las asignaciones se llevan a cabo con el signo igual = en lugar del operador := utilizado para las asignaciones escalares. Como resultado, tanto VolumeSold como UnitSold son vectores que forman parte de la tabla Items. Como resultado, estas variables son similares a las columnas en Excel, y contienen un valor por artículo.

Tanto sum como median son funciones especiales llamadas agregadores en Envision. Existen muchos otros agregadores en Envision, como avg, min, max, first, last, etc. Para ser concisos, no veremos todos los agregadores disponibles en Envision en esta página; no obstante, si desea más información, puede consultar nuestra lista completa de agregadores.

El resultado de estas dos agregaciones se muestra en la tabla definida con las líneas 3 a 7. Los vectores VolumeSold y UnitSold se enumeran como argumentos después de la palabra clave with, y sus valores se muestran en la tabla. Por último, en la línea 7, la instrucción order by indica que la tabla debería ordenarse comenzando con los artículos que tienen los valores VolumeSold mayores.

Los lectores que estén familiarizados con la sintaxis GROUP BY en SQL podrán preguntarse cómo sabe Envision qué agrupamiento debería utilizarse al calcular la sum en la línea 1. Por defecto, Envision realiza un agrupamiento utilizando las columnas que funcionan como "claves" del lado derecho de la asignación. En el caso de una variable que pertenece a la tabla Items —la tabla cuyo nombre está implícito—, la columna que actúa como clave (primaria) es la columna Id. Esto explica por qué se obtiene una agregación por artículo al utilizar el signo =.

Grupos de agregación explícita con by

Hasta aquí, las agregaciones que hemos llevado a cabo se valían de los patrones de agregación implícita de Envision. Sin embargo, el comportamiento de todos los agregadores puede modificarse con una palabra clave opcional by que se utiliza para especificar explícitamente el agrupamiento aplicable. Ilustremos el modo en que se utiliza la palabra clave by
VolumeSold = sum(Orders.NetAmount)
SameVolumeSold = sum(Orders.NetAmount) by Id // Same!
La línea 2 se asigna a un segundo vector llamado SameVolumeSold, pero los valores de este vector son estrictamente idénticos a los del vector VolumeSold en la línea 1. De hecho, en la línea 1, también se utiliza (aunque implícitamente) la opción by Id. Intuitivamente, cuando se utiliza la opción by, es como si los grupos se crearan primero de acuerdo con el objetivo de agrupamiento y luego como si el agregador se calculara para cada grupo por separado. La opción by ofrece la posibilidad de componer agregaciones bastante complejas, como se ilustra en el script al principio de esta página. Veamos las dos líneas de script en las que se realizan las agregaciones a través de opciones by.
avgRet := avg(distinct(Orders.Date) by Orders.ClientCode)
avgQty := avg(sum(Orders.Quantity) by (Orders.ClientCode, Orders.Date))
El agregador distinct cuenta el número de valores distintos observados dentro de cada grupo. En la línea 1, primero se agrupan las líneas de la tabla Orders de acuerdo con sus respectivos valores ClientCode y luego se cuenta la cantidad de fechas de pedido distintas para cada cliente. Intuitivamente, esta agregación puede interpretarse como el recuento de la cantidad de veces que ha regresado cada cliente. Luego, este resultado se agrega nuevamente en un solo valor escalar con el agregador avg que contiene la agregación interna distinct.

El escalar avgQty puede interpretarse como la cantidad de unidades compradas por cesta. El cálculo comienza con una instrucción sum() by; sin embargo, después de la opción by, no tenemos una sino dos variables separadas por comas y enumeradas entre paréntesis: (Orders.ClientCode, Orders.Date). Esta sintaxis debería interpretarse del siguiente modo: crear un grupo para cada par de ClientCode y Date. Desde una perspectiva de negocios, estamos tratando a todas las unidades compradas el mismo día como parte de la misma cesta, lo cual es una aproximación razonable para la mayoría de las situaciones. Por último, la llamada externa a avg genera el promedio final en todos los agregados sum calculados para todos los pares.

En términos más generales, la opción by respalda una cantidad arbitraria de variables después de la sintaxis sum(foo) por (arg1, arg2, …, argN). En la práctica, sin embargo, es bastante raro encontrar situaciones en las que tenga sentido agrupar de a más de 4 variables por vez. Además, el orden de los argumentos no afecta los grupos resultantes utilizados para calcular los agregados.

Agregación explícita dentro de una tabla con group by

A veces, a través de la agregación, es posible elaborar una nueva tabla que sea más relevante para ser utilizada dentro del panel de información que la tabla original no agregada. Por esta razón, Envision también respalda la posibilidad de agregar datos directamente desde la instrucción de declaración del mosaico. El modo más directo de visualizar esta capacidad de Envision es agregar los datos que se mostrarán en una tabla. Esto es precisamente lo que hacemos en el script al principio de esta página. Observemos nuevamente el las líneas pertinentes copiadas a continuación.
Orders.Brand = Brand
show table "Top Suppliers" a6f7 tomato with
  Supplier
  distinct(Category) as "Categories"
  sum(Orders.NetAmount) as "Sold{$}"
  mode(Orders.Brand) if (Orders.Brand != "Fellowes") as "Most frequent brand sold"
  group by Supplier
  order by sum(Orders.NetAmount) desc
La instrucción show table va desde la línea 2 hasta la línea 8 y, más específicamente, la agregación se especifica en la línea 7 con la instrucción group by, que tiene la misma semántica que la opción by que hemos visto en la sección anterior. En este punto, tal vez se pregunte por qué Envision no utiliza la palabra clave by en lugar de group by si la semántica es la misma. El if en la línea 6 es un filtro de agregador, del que hablaremos en la sección siguiente.

La respuesta es simple: es posible utilizar la opción by dentro de la lista de expresiones utilizadas después de with para un determinado mosaico (esta situación, sin embargo, no se ilustra aquí). Así, group by permite diferenciar entre una instrucción by que es parte de una expresión pasada al mosaico y una instrucción group by que se aplica a un mosaico en conjunto. En otras palabras, el group by se aplica a todas las expresiones enumeradas después de la palabra clave with, a diferencia de la instrucción by, que solo tiene impacto local.

Cuando se utiliza group by, todas las expresiones pasadas al mosaico después de la palabra clave with deberían ofrecer la posibilidad de ser agregadas. Por ejemplo, el script de una sola línea a continuación es incorrecto, porque Name no ofrece un patrón de agregación, mientras que se especifica group by Supplier.
show table "WRONG!" with Name group by Supplier
Sin embargo, si modificamos este script introduciendo un agregador, por ejemplo distinct, nuestro script de Envision se vuelve válido.
show table "CORRECT!" with distinct(Name) group by Supplier
La única excepción a esta regla es la agregación del objetivo mismo. En el script al comienzo de esta sección, tenemos una instrucción group by Supplier en la línea 7. En la línea 3, la variable Supplier aparece sin ningún agregador; aun así, el script sigue siendo válido, precisamente porque el agrupamiento tiene lugar de acuerdo con la variable Supplier.

La necesidad de exponer un agregador también aplica para la instrucción order by en la línea 8. De hecho, la tabla se agrega primero por proveedor, y solo después se ordena de acuerdo con la columna Id, a menos que se especifique otro tipo de ordenamiento. Como resultado, Envision necesita calcular un valor por grupo para ordenar todos esos grupos. Esto es exactamente lo que sucede con la instrucción group by sum(Orders.NetAmount).

Si bien ilustramos group by con el mosaico table, esta sintaxis no es específica del mosaico table, y es posible utilizar el mismo patrón con la mayoría de los demás mosaicos. Por ejemplo, sería posible extender el script que aparece al principio de esta página con un mosaico barchart agregado por Brand..
show barchart "Sales by brand" with sum(Orders.NetAmount) group by Brand
El group by también respalda el agrupamiento múltiple, es decir, el agrupamiento por vectores múltiples o expresiones múltiples, con una sintaxis idéntica a la de la opción by que hemos comentado anteriormente.

Filtros de agregación

Los agregadores de Envision también admiten filtros a través de la palabra clave if. El script anterior ilustra el uso de un filtro de este tipo en la línea 6:
Orders.Brand = Brand
show table "Top Suppliers" a6f7 tomato with
Supplier
distinct(Category) as "Categories"
sum(Orders.NetAmount) as "Sold{$}"
mode(Orders.Brand) if (Orders.Brand != "Fellowes") as "Most frequent brand sold"
group by Supplier
order by sum(Orders.NetAmount) desc

Aquí, la marca Fellowes se excluye explícitamente del reporte a través del filtro if. Los filtros son útiles dentro de la instrucción mosaico porque ofrecen la posibilidad de filtrar cada columna por separado. En cambio, un filtro where posicionado fuera de la instrucción mosaico hubiera filtrado todas las líneas seleccionadas de la tabla Orders de modo indiferente para todas las columnas.

Los filtros también pueden utilizarse dentro de una línea:
TwoAndMore = sum(Orders.1) if (Orders.Quantity >= 2)