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

3.6 有序分组统计

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

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@x(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@mx(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@mx(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() 是默认数据按分组字段有序，采用累积方式聚合，结果返回的仍旧是游标，之后可以进一步计算，从而解决中间结果集超出内存容量的问题。

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