6.2 应用实践

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(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(CustomerID, EmployeeID, Amount,Quantity)
2 >Employees=file(“Employees.btx”).import@b().keys@i(EmployeeID)
3 =file(“Customers.ctx”).open().cursor(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 获得管道的运算结果

以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾