3.4 有序 DISTINCT
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@mx(OrderDate;OrderDate>=start && OrderDate <=end && EmployeeID==5;2) |
| 3 | =A2.groups@o(month(OrderDate):months) |
A3 @o 表示有序分组，只和前一行对比
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@mx(CustomerID, OrderDate;OrderDate>=start && OrderDate <=end;2) |
| 3 | =A2.group@1(CustomerID,month(OrderDate)) |
| 4 | =A3.groups(month(OrderDate):months;count(1):num) |
A3 当数据按分组字段有序时，用 group@1 实现 DISTINCT 计算，在遍历过程中把与上一条不相同的记录作为游标逐步返回，即使内存放不下也不必做缓存，可以实现结果集超出内存容量时的 DISTINCT 运算。cs.group() 默认数据已经按分组字段有序了
