4.3 宽表
前面的例子均使用 switch 做维表的关联,能获得很好的性能,但还有几个问题:
1) 关联后,外键字段的本身值失去了,必须到维表中取主键才能获得,速度会变慢。
如上例:如果还想取得 EmployeeID,要用 EmployeeID. EmployeeID 才可以。
2) 如果在维表中找不到关联记录,外键字段会转换成 null,将彻底丢失原值。
3) 外键有可能由多个字段构成,这时候就不能用 switch 函数来转换了。
本例将介绍 join 函数来解决上述问题。
4.3.1 单主键维表
SQL
SELECT Categories.CategoryName,Products.ProductName,sum(Amount) Amount
FROM Orders
LEFT JOIN Products ON Orders.ProductID=Products.ProductID
LEFT JOIN Categories ON Categories.CategoryID=Products.CategoryID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31'
GROUP BY Categories.CategoryName,Products.ProductName
SPL
A | |
---|---|
1 | > st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et) |
2 | >Categories=file(“Categories.btx”).import@b().keys@i(CategoryID) |
3 | =file(“Orders_Time.ctx”).open().cursor@mx(ProductID,Amount;OrderDate>=start && OrderDate<=end) |
4 | =A3.join(ProductID,Products,ProductName,CategoryID; CategoryID,Categories,CategoryName) |
5 | =A4.groups(CategoryName,ProductName;sum(Amount):Amount) |
A4 利用 join 函数和维表 Products、Categories 关联,在 Orders 表中增加 ProductName,CategoryID,CategoryName 字段,形成宽表
A5 利用宽表做统计
join 将在原序表上增加字段来保存维表记录的地址,原字段值并不改变,上面的问题都能得到解决。不同的是,join 将返回新的序表,在做关联时要注意次序,既是事实表又是维表的数据表,要先关联自己的维表后得到新的数据表,然后再用来被事实表关联。
4.3.2 多主键维表
SQL
SELECT Areas.Country,Areas.Region,Areas.City,CityName,Products.ProductName,
sum(Amount) Amount
FROM Orders
LEFT JOIN Products ON Orders.ProductID=Products.ProductID
LEFT JOIN Suppliers ON Suppliers.SupplierID=Products.SupplierID
LEFT JOIN Areas ON Suppliers.Country=Areas.Country
and Suppliers.Region=Areas.Region
and Suppliers.City=Areas.City
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31'
GROUP BY Areas.Country,Areas.Region,Areas.City,CityName,Products.ProductName
SPL
A | |
---|---|
1 | >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et) |
2 | >Areas=file(“Areas.btx”).import@b().keys@i(Country,Region,City) |
3 | =file(“Orders_Time.ctx”).open().cursor@mx(ProductID,Amount;OrderDate>=start && OrderDate<=end) |
4 | =A3.join(ProductID,Products,ProductName,SupplierID.SupplierID; SupplierID,Suppliers,Country,Region,City;Country:Region:City,Areas,CityName) |
5 | =A4.groups(Country,Region,City,ProductName;CityName,sum(Amount):Amount) |
A4 Areas 维表的主键是三个,使用 join 函数可以解决多主键维表的关联问题
原来既是维度表又是事实表的表间关联要这样写,join 居然还有这样的神仙用法,对应的函数文档里没有提及这种写法。
相比较于其它语言的 join,spl 的表间关联确实做的不一样,更强大实用,符合自然思维。
还有另一个 joinx 实现关联外存维表,可以直接跟集文件和实表关联,不用读数进内存,让人眼前一亮。
不免心生疑问: fjoin 和 pjoin 有没有类似骨骼清奇的写法?😄
从表间关系图看,此案例中忘记引用了表 Products 和 Suppliers,
最后多主键维表中 A4 代码格 SupplierID.SupplierID 应该是写重复了,只要一个 SupplierID 就成,是吧?
这么经典的案例,大佬们有空时能不能 double-check 补全修正一下?