3.6 有序分组统计
SQL
SELECT count (DISTINCT CustomerID) num,year(OrderDate) years,
month(OrderDate) months
FROM Orders
WHERE OrderDate>='2022-01-01' and OrderDate<='2022-03-31' and EmployeeID=5
GROUP BY year(OrderDate), month(OrderDate)
ORDER BY years,months
SPL
A | |
---|---|
1 | >st=date(“2022-01-01”), et=date(“2022-03-31”), start=days@o(st), end=days@o(et) |
2 | =file(“Orders_Time.ctx”).open().cursor(OrderDate,CustomerID;OrderDate>=start && OrderDate<=end && EmployeeID==5) |
3 | =A2.groups@o(year(OrderDate):years,month(OrderDate):months; icount(CustomerID):num) |
A3 @o 选项执行有序分组,只需要和相邻的对比,效率更高
SQL
SELECT CustomerID,sum(Amount) Amount
FROM Orders
WHERE OrderDate>='2022-01-01' and OrderDate<='2022-03-31' and EmployeeID=5
GROUP BY CustomerID
ORDER BY CustomerID
SPL
A | |
---|---|
1 | >st=date(“2022-01-01”), et=date(“2022-03-31”), start=days@o(st), end=days@o(et) |
2 | =file(“Orders_Account.ctx”).open().cursor@m(CustomerID, Amount; OrderDate>=start && OrderDate <=end && EmployeeID==5;2) |
3 | =A2.groups@o(CustomerID; sum(Amount):Amount) |
SQL
WITH m AS(
SELECT CustomerID,sum(Amount) Amount
FROM Orders
WHERE OrderDate>='2022-01-01' and OrderDate<='2022-03-31'
GROUP BY CustomerID
)
SELECT *
FROM m
ORDER BY Amount DESC
LIMIT 10
SPL
A | |
---|---|
1 | >st=date(“2022-01-01”), et=date(“2022-03-31”), start=days@o(st), end=days@o(et) |
2 | =file(“Orders_Account.ctx”).open().cursor@m(CustomerID, Amount;OrderDate>=start && OrderDate <=end;2) |
3 | =A2.group@s(CustomerID; sum(Amount):Amount) |
4 | =A3.total(top(10;-Amount)) |
A3 cs.group@s() 是默认数据按分组字段有序,采用累积方式聚合,结果返回的仍旧是游标,之后可以进一步计算,从而解决中间结果集超出内存容量的问题。
以下是广告时间
对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅
嗯,还不太了解好多乾?
对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅
嗯,还不太了解好多乾?