在envision中使用left-by连接表

使用 By-At 连接表


首页 » 资源 » 此处

Envision 提供了一种隐式机制,用于连接包含 Id(项目索引)字段的表; 这种情况常见于表示销售历史记录或采购历史记录的情况。但这种隐式机制,即“自然连接”不适合较为复杂的情形。因此,Envision 提供了一种更通用的连接机制 –“by-at”。by-at 是一种特殊类型的聚合,可以对表进行任意连接。

脚本示例

在本页面,我们将使用样本数据集(可从您的 Lokad 账户里的 /sample 路径获取)。如果您尚未阅读过通过 Envision 读取文件章节,建议您先行阅读,仔细了解文件类型预期,因为该章节关系到更好地理解下面这段脚本。
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
前三行是读取文件的常用语句。在第 3 行,类型预期 Suppliers[*] 指定了供应商表不获取任何类型预期——它是一个既没有主键也没有外键的独立表。实际上,与文件夹中其他所有 .tsv 文件不同的是,文件 Lokad_Suppliers.tsv 中没有 Id 列。由于缺少此列,所以 Envision 无法隐式连接此表与Items表。因此,供应商表将按供应商聚合,每行一个供应商,而所有其他文件则是按项目进行详细说明。

第 5 行则是运用 by-at 聚合,之所以这样称呼,是因为它使用了 byat 关键字来连接Items 表与 Suppliers 表。我们也可以编写类似下面这样的语句:
Items.Moq = same(Suppliers.Moq) by Suppliers.Supplier at Items.Supplier
但根据 Envision 的惯例,Items 表名会被忽略。

在这个特殊示例中,我们填入了一个向量 Items.Moq,其数据获取自 Supplier 表。然后在 Items.SupplierSuppliers.Supplier 之间进行连接。

如果 Items 表中的供应商项没有相应的 Suppliers 表,则使用零值。但通过可选的 or 关键字,在进行 Envision 聚合器的常规行为之后,可以使用其他默认值。我们可以这样编写脚本:
Items.Moq = same(Suppliers.Moq) by Suppliers.Supplier at Items.Supplier or 0
第 6 行显示了最后得到的 Items 表,这是为了展示上一行中的 by-at 所执行计算的结果。与我们预计的一样,每个项目关联其相应供应商的 MOQ 值。

by-at 的一般语法

by-at 的一般语法如下:
T1.A = agg(T2.B) by [T2.X, T2.Y, T2.Z] if E at [T1.X, T1.Y, T1.Z] or T1.C
by-at 与 Envision 中的常用聚合器大体相似,能够执行元组匹配,即一次匹配n个字段。因此,所有常用的 Envision 聚合器都能使用,例如 summinmediansame 等等。与正则表达式一样,or 块也同样可以忽略。

by-at 的语义如下:

  • by [T2.X, T2.Y, T2.Z] 构建所有不同组的元组,如果存在,则使用条件 E 进行过滤。如果其中的每一组至少在左边有一个相匹配的组,则计算聚合的 agg(T2.B)
  • at [T1.X, T1.Y, T1.Z] 构建所有不同组的元组。对于其中每一组,赋予右边相对应的聚合值。
  • 如果 at 的组在右边没有对应的组,那么将采用默认的 T1.C 值。

与常规聚合器一样,or 语句也是可选的,它提供了组为空时要用作聚合结果的值。

by-at 提示

by-at 是一种很强大的结构,在许多情况下都可以使用,并非只是在常规的 SQL 中用于连接表。

一个表可以连接本身:例如,
Orders.DaySum = sum(Orders.Quantity) by Orders.Date at Orders.Date
示范了如何在不使用 Day 表的情况下对 Orders 表计算每日总订单量。

可以连接到日历表中的日期和星期:例如,
Day.Shift = sum(Orders.Quantity) by [Orders.Id, Orders.Date - 1] at [Day.Id, Day.Date]
示范了如何通过 by-at 转换一天的数量。

by-at 转化为 SQL

熟悉 SQL 的读者可能会注意到 Envision 表达式:
Moq = same(Suppliers.Moq) by Suppliers.Supplier at Supplier or defaultMoq

在 SQL 中有以下等效的语句,它采用左外连接:

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

Envision 语法强调的不是关系代数本身,而是类似 Excel 的计算。