...

2.5 Getting records containing top/bottom N

 
performance optimization practice(38)

Find top 3 order records in terms of amount in each month the current year:

SQL

WITH m AS(
    SELECT *,rank() OVER(PARTITION BY year(OrderDate),month(OrderDate) 
        ORDER BY Amount DESC) r 
    FROM Orders 
    WHERE OrderDate>='2022-01-01')
SELECT * 
FROM m 
WHERE r<=3

SPL

A
1 >st=date(“2022-01-01”), start=days@o(st)
2 =file(“Orders.ctx”).open().cursor(OrderDate, Amount; OrderDate>=start)
3 =A2.groups(month@y(OrderDate):months; top(3;-Amount)).conj(#2)
4 =A3.run(OrderDate=date@o(OrderDate))

A3 month@y(OrderDate) returns a value that contains the year part and the month part.
 

performance optimization practice(38)
 
 •  154 View  •