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

6.2 应用实践

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

SQL

WITH a AS(
    SELECT sum(Amount) Amount
    FROM Orders2021
    WHERE Amount>500 or Quantity>50)
SELECT month(OrderDate) m,sum(Orders2021.Amount)/a.Amount p
FROM Orders2021,a
WHERE Orders2021.Amount>5000
GROUP BY month(OrderDate),a.Amount
ORDER BY month(OrderDate)

SPL

A B
1 =file(“Orders2021_Account.ctx”).open().cursor@x(OrderDate,Amount,Quantity)
2 cursor A1 =A2.select(Amount>500 || Quantity>50).total(sum(Amount))
3 cursor =A3.select(Amount>5000).groups(month(OrderDate):m; sum(Amount):p)
4 >A3.run(p=p/A2)

A2 用 cursor 关键字定义游标 A1 同步的管道。B2 在管道 A2 设置了第 1 种方式的分组汇总
A3 定义管道
B3 在管道 A3 设置了第 2 种方式的分组汇总

SQL

SELECT coalesce (s.City, b.City ) City,b.Amount ba,s.Amount sa
FROM (
    SELECT City,sum(Amount) Amount
    FROM Orders2021
    LEFT JOIN Employees ON Orders2021.EmployeeID=Employees.EmployeeID
    WHERE Amount>5000 or Quantity>50
    GROUP BY City) b
FULL JOIN (
    SELECT City,sum(Amount) Amount
    FROM Orders2021
    LEFT JOIN Customers ON Orders2021.CustomerID=Customers.CustomerID
    WHERE Amount<=1000 or Quantity<5
    GROUP BY City) s
ON b.City=s.City

SPL

A
1 =file(“Orders2021_Account.ctx”).open().cursor@x(CustomerID, EmployeeID, Amount,Quantity)
2 >Employees=file(“Employees.btx”).import@b().keys@i(EmployeeID)
3 =file(“Customers.ctx”).open().cursor@x(CustomerID,City)
4 >ch=channel(A1).select(Amount>5000 || Quantity>50). switch(EmployeeID,Employees:EmployeeID).groups(EmployeeID.City;sum(Amount):Amount)
5 =A1.select(Amount<=1000 || Quantity<5)
6 =joinx@1(A5:primary, CustomerID; A3:sub, CustomerID)
7 =A6.groups(sub.City; sum(primary.Amount):Amount)
8 =ch.result()
9 =join@f(A8:b,City;A7:s,City)
10 =A9.new([b.City,s.City].nvl():City,b.Amount:ba,s.Amount:sa)

A4 定义管道，及管道上的计算
A5-A7 对游标执行计算
A8 获得管道的运算结果

 

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