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