"[链接]4.5.1 过滤后复用索引 SQL SELECT Suppliers.Region,sum(Amount) Amount FROM Orders LEFT JOIN Products .."

Mars 开封
乾学院 6 号会员
255 浏览 • 1 年前

...

4.5 维表过滤

计算＆AI

SPL性能优化基础练习(38)

## 4.5.1 过滤后复用索引

SQL

SELECT Suppliers.Region,sum(Amount) Amount
FROM Orders
LEFT JOIN Products ON Orders.ProductID= Products.ProductID
LEFT JOIN Suppliers ON Products.SupplierID=Suppliers.SupplierID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' and Products.CategoryID not in (1,2,3)
GROUP BY Suppliers.Region

SPL

	A
1	>st=date(“2021-01-01”), et=date(“2021-12-31”), start= days@o(st), end=days@o(et)
2	=Products.select@i([1,2,3].pos@b(CategoryID)==null)
3	=file(“Orders_Time.ctx”).open().cursor@m(ProductID, Amount; OrderDate>=start && OrderDate<=end, ProductID:A2)
4	=A3.groups(ProductID.SupplierID.Region;sum(Amount):Amount)

A2 对维表过滤时使用 @i 选项将复用索引
A3 把和维表的关联放到产生游标的语句中，关联不上的记录不生成，减少生成记录的时间

索引复用并不会总是更快，因为要把过滤掉的记录从索引表删除，如果过滤掉的记录数很多（剩下的较少），这个动作也不会很快。而剩下记录较少时，重建索引很可能更快。具体采用哪种方式，要根据实际情况决定。

4.5.2 过滤后重建索引

SQL

SELECT Suppliers.Region,sum(Amount) Amount
FROM Orders
LEFT JOIN Products ON Orders.ProductID= Products.ProductID
LEFT JOIN Suppliers ON Products.SupplierID=Suppliers.SupplierID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' and Products.CategoryID in (1,2,3)
GROUP BY Suppliers.Region

SPL

	A
1	>st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
2	=Products.select([1,2,3].pos@b(CategoryID)!=null).derive@o().keys@i(ProductID)
3	=file(“Orders_Time.ctx”).open().cursor@m(ProductID,Amount;OrderDate>=start && OrderDate<=end,ProductID:A2)
4	=A3.groups(ProductID.SupplierID.Region;sum(Amount):Amount)

A2 维表过滤后返回序列，用 derive@o() 变回序表，再重建索引

本例过滤掉的记录数很多（剩下的较少），重建索引很快。

4.5.3 多层维表的过滤

SQL

SELECT Suppliers.Region,sum(Amount) Amount
FROM Orders
INNER JOIN Products ON Orders.ProductID= Products.ProductID
INNER JOIN Suppliers ON Products.SupplierID=Suppliers.SupplierID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' 
    and Suppliers.SupplierID in (1,2,3) and Products.CategoryID in (1,2,3,4,5)
GROUP BY Suppliers.Region

SPL

多层维表的过滤，需要先把维表各自过滤后再建立关联关系，关联时只保留能关联上的记录。

	A
1	=Suppliers.select@i([1,2,3].pos@b(SupplierID)!=null)
2	=Products.select@i([1,2,3,4,5].pos@b(CategoryID)!=null)
3	=A2.switch@i(SupplierID, A1:SupplierID)

A1 过滤供应商维表
A2 过滤产品维表
A3 将过滤后的 Products 和 Suppliers 建立关联，@i 选项表示关联不上的记录删除

统计时直接使用：

	A
…	/ 接上面代码
4	>st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
5	=file(“Orders_Time.ctx”).open().cursor@m(ProductID, Amount; OrderDate>=start && OrderDate<=end, ProductID:A3)
6	=A5.groups(ProductID.SupplierID.Region; sum(Amount):Amount)

A5 和过滤后的维表关联，关联不上的记录不再生成

4.5.4 反向过滤

SQL

SELECT sum(Amount)
FROM Orders
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' and EmployeeID not in (
    SELECT EmployeeID 
    FROM Employees 
    WHERE Title='Sales Representative')

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().select(Title==“Sales Representative”).derive().keys@i(EmployeeID)
3	=file(“Orders_Time.ctx”).open().cursor@m(Amount;OrderDate>=start && OrderDate<=end,EmployeeID:Employees:null)
4	=A3.groups(;sum(Amount))