4.2 维表上的计算
SQL
SELECT floor((year(curdate())-year(Employees.Birthday))/10) Age,
sum(Orders.Amount) Amount
FROM Orders
LEFT JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31'
GROUP BY floor((year(curdate())-year(Employees.Birthday))/10)
SPL
| A | |
|---|---|
| 1 | >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et) |
| 2 | >Employees=file(“Employees.btx”).import@b().derive(age(Birthday)\10:Age).keys@i(EmployeeID) |
| 3 | =file(“Orders_Time.ctx”).open().cursor@mx(EmployeeID,Amount;OrderDate>=start && OrderDate<=end) |
| 4 | =A3.switch(EmployeeID,Employees:EmployeeID) |
| 5 | =A4.groups(EmployeeID.Age;sum(Amount):Amount) |
A2 从集文件 Employees.btx 中读取维表数据至内存，预先把 Age 值计算好，然后设置主键并建立索引
维表上的计算列预先算好，可以减少关联后的计算量
1