开封
乾学院 6 号会员
461 浏览  • 
     ...

4.7 临时维表和区间维表

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

SQL

SELECT a.CustomerID,a.OrderDate,a.ProductID,a.Quantity,a.Unit,
    a.Price,a.Amount,a.EmployeeID,a.EmployeeName,a.ShipVia
FROM Orders a
JOIN (
    SELECT ProductID,0.01*sum(Quantity) Quantity
    FROM Orders 
    WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' 
    GROUP BY ProductID) b 
    ON a.ProductID=b.ProductID
WHERE  a.OrderDate>='2021-01-01' and a.OrderDate<='2021-12-31' and a.Quantity>b.Quantity

SPL

A
1 >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
2 =file(“Orders_Time.ctx”).open().cursor@mx(Quantity,ProductID;OrderDate>=start && OrderDate<=end)
3 =A2.groups(ProductID;0.01*sum(Quantity):Quantity).keys@i(ProductID)
4 =file(“Orders_Time.ctx”).open().cursor@mx(;OrderDate>=start && OrderDate<=end)
5 =A4.switch(ProductID,A3:ProductID)
6 =A5.select(Quantity>ProductID.Quantity)
7 =A6.switch(ProductID).fetch()

A3 通过分组聚合，算出每种 ProductID 的销售数量的 1%，此时 ProductID 值唯一，因此结果可以当成维表使用
A6 通过关联字段引用维表记录计算
A7 将关联字段恢复成原主键值，取出结果数据

SQL

SELECT sum(Amount* (CASE WHEN Amount<=1000 THEN 0.1 
            WHEN Amount>1000 and Amount<=2000 THEN 0.2 
            WHEN Amount>2000 and Amount<=3000 THEN 0.3 
            WHEN Amount>3000 and Amount<=4000 THEN 0.4 
        ELSE 0.5 END)
    ) Amount,EmployeeID
FROM Orders
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31'
GROUP BY EmployeeID

SPL

imagepng

A
1 >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
2 =file(“Orders_Time.ctx”).open().cursor@mx(Amount,EmployeeID;OrderDate>=start && OrderDate<=end)
3 =file(“Commission.txt”).import@t()
4 =A3.(Amount)
5 =A2.join(A4.pseg(Amount),A3:#,Rate)
6 =A5.groups(EmployeeID;sum(Amount*Rate):Amount)

A3 产生一个内存区间维表
A5 用 pseg 算出当前记录的 Amount 位于维表的哪个区间段，返回记录序号，再用序号关联

 

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