2.5 前 N 名 / 后 N 名所在记录
查询今年每个月金额最大的三个订单
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@x(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)返回值包含了年月分量
