...

3.4 Order-based DISTINCT

 
performance optimization practice(38)

SQL

SELECT DISTINCT month(OrderDate)
FROM Orders
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' and EmployeeID=5

SPL

A
1 >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
2 =file(“Orders_Time.ctx”).open().cursor@m(OrderDate;OrderDate>=start && OrderDate <=end && EmployeeID==5;2)
3 =A2.groups@o(month(OrderDate):months)

A3 @o option enables order-based grouping, which compares each record with the previous one only.

SQL

WITH m AS(
    SELECT DISTINCT CustomerID,month(OrderDate) months
    FROM Orders
    WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31')
SELECT months,count(*) num
FROM m 
GROUP BY months

SPL

A
1 >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
2 =file(“Orders_Account.ctx”).open().cursor@m(CustomerID, OrderDate;OrderDate>=start && OrderDate <=end;2)
3 =A2.group@1(CustomerID,month(OrderDate))
4 =A3.groups(month(OrderDate):months;count(1):num)

A3 When data is ordered by the grouping field(s), use group@1 to perform DISTINCT operation. During the traversal, return each record where the grouping field value(s) are different from those in the previous record as cursor. No buffering is needed even if cannot the returned cursor data cannot fit into the memory. This way we can achieve DISTINCT operations where result sets are larger than the memory space. cs.group()takes it for granted that data is ordered by the grouping field(s).

 

performance optimization practice(38)
 
 •  130 View  •