Unión de tablas con la instrucción left-by de Envision

Unión de tablas con By-At











Inicio » Recursos » Aquí

Envision proporciona un mecanismo implícito para unir tablas cuando estas contienen un campo _Id_ (índice de artículo), como resulta ser frecuente en la representación del historial de ventas o de compras. Sin embargo, este mecanismo implícito —llamado uniones naturales— no es adecuado para situaciones más complejas. Por este motivo, Envision ofrece un mecanismo de unión más general llamado by-at. El by-at es un tipo especial de agregación que realiza uniones arbitrarias entre tablas.

Un script ilustrativo

En esta página utilizamos el conjunto de datos de ejemplo, que debería estar disponible en la ruta /sample en su cuenta de Lokad. Si no lo ha hecho aún, le sugerimos que lea primero la entrada sobre la lectura de archivos con Envision prestando especial atención a las expectativas sobre los tipos de archivo, ya que esta sección es particularmente relevante para comprender mejor el script a continuación.

read "/sample/Lokad_Items.tsv"
read "/sample/Lokad_Orders.tsv" as Orders
read "/sample/Lokad_Suppliers.tsv" as Suppliers[*]

Moq = same(Suppliers.Moq) by Suppliers.Supplier at Supplier
show table "Item list" a1d4 tomato with Id, Name, Supplier, Moq

Las primeras tres líneas son nuestras instrucciones habituales para la lectura de archivos. En la línea 3, la expectativa de tipo Suppliers[*] especifica que la tabla de proveedores no está obteniendo ningún tipo de expectativas: es una tabla independiente sin clave primaria ni clave externa. De hecho, el archivo Lokad_Suppliers.tsv —a diferencia de todos los demás archivos .tsv de la carpeta— no contiene una columna Id. Por este motivo, Envision no puede unir implícitamente esta tabla con la tabla Items. De hecho, la tabla del proveedor se agrega por proveedor con un proveedor por fila, mientras que todos los demás archivos se detallan por artículo.

En la línea 5, aprovechamos la agregación by-at, llamada de este modo porque utiliza las palabras clave by y at para realizar una unión entre la tabla Items y las tablas Suppliers. También podríamos haber escrito:
Items.Moq = same(Suppliers.Moq) by Suppliers.Supplier at Items.Supplier
Sin embargo, debido a las convenciones habituales de Envision, se omite la tabla Items.

En este ejemplo específico, hemos rellenado un vector Items.Moq con los datos obtenidos de la tabla Suppliers. Se ha realizado una unión entre los dos campos Items.Supplier y Suppliers.Supplier.

Si hubiera una entrada de proveedor en la tabla Items que no cuenta con una contrapartida en la tabla Suppliers, se utilizará un valor cero. Sin embargo, a través de la palabra clave or, siguiendo el comportamiento habitual de los agregadores de Envision, podrían utilizarse otros valores predeterminados. Podríamos haber escrito:
Items.Moq = same(Suppliers.Moq) by Suppliers.Supplier at Items.Supplier or 0

En la línea 6, la tabla Items resultante se muestra para ilustrar el cálculo realizado por la instrucción by-at en la línea anterior. Como se esperaba, cada artículo se asocia con el valor MOQ de su proveedor correspondiente.

Sintaxis general de la instrucción by-at

La sintaxis general de la instrucción by-at es:

T1.A = agg(T2.B) by [T2.X, T2.Y, T2.Z] if E at [T1.X, T1.Y, T1.Z] or T1.C

De modo muy similar a los agregadores habituales de Envision, la instrucción by-at ofrece la posibilidad de realizar una correspondencia de tupla, es decir, hacer corresponder n campos por vez. Luego se pueden utilizar todos los agregadores habituales de Envision: sum pero también min, median, same, etc. El bloque or puede omitirse al igual que se habría con una agregación común.

La semántica de la instrucción by-at es la siguiente:

  • Compilar todos los grupos distintos de tuplas para la by ([T2.X, T2.Y, T2.Z)], filtrando con la condición E si estuviera presente. Para cada uno de esos grupos, si existe al menos un grupo que coincida en la instrucción left, calcular el agregado agg(T2.B).
  • Compilar todos los grupos distintos de tuplas para la at [T1.X, T1.Y, T1.Z]. Para cada uno de esos grupos, asignar el agregado correspondiente de la derecha.
  • Para los grupos de la at que no tienen un grupo correspondiente a la derecha, utilizar el valor predeterminado T1.C.

Al igual que los agregadores habituales, la instrucción or es opcional y proporciona el valor que debe utilizarse como resultado de la agregación si el grupo está vacío.

Recomendaciones para el uso de la instrucción by-at

La instrucción by-at es una potente construcción que puede aprovecharse en muchas situaciones, no solo para unir tablas desde la perspectiva de SQL habitual.

Una tabla puede combinarse consigo misma: Por ejemplo:
Orders.DaySum = sum(Orders.Quantity) by Orders.Date at Orders.Date
ilustra cómo calcular totales diarios en la tabla Orders sin recurrir a la tabla Day.

Las tablas calendario Día y Semana pueden combinarse: Por ejemplo:
Day.Shift = sum(Orders.Quantity) by [Orders.Id, Orders.Date - 1] at [Day.Id, Day.Date]
ilustra cómo cambiar a las cantidades de un día a través de la instrucción by-at.

Traducción de by-at a SQL

Para los lectores que ya estén familiarizados con SQL, destacamos que esta expresión de Envision
Moq = same(Suppliers.Moq) by Suppliers.Supplier at Supplier or defaultMoq

tiene el siguiente equivalente en SQL, que utiliza un outer left join:

UPDATE Items LEFT OUTER JOIN Suppliers ON Items.Supplier = Suppliers.Supplier SET Moq = COALESCE(Suppliers.Moq, defaultMoq)

La sintaxis de Envision enfatiza cálculos en estilo Excel más que el álgebra relacional en sí.