RaqForum 25 No.
321 View  • 
     ...

3.6 Order-based grouping & summarization

 
performance optimization practice(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 option enables order-based grouping, which involves comparisons only with the neighboring record and is faster.

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() by default takes data as ordered by grouping field, summarizes data cumulatively and returns a cursor before moving on to the next-phase computation. This solves the issue of memory overflow caused by large intermediate result sets.

 

performance optimization practice(38)
 
 •  321 View  •