3.6 Order-based grouping & summarization
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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
