1.2 组表

组表支持列存,在遍历时能获得更好的性能。

1.2.1 把数据表转储成组表

文本转储

A
1 =file(“Orders.txt”).cursor@mt(CustomerID:string, OrderDate:datetime, ProductID:string, Quantity:int, Unit:string, Price:decimal, Amount:decimal, EmployeeID:int,EmployeeName:string,ShipVia:string)
2 =file(“Orders.ctx”).create@y(CustomerID,OrderDate,ProductID, Quantity, Unit, Price, Amount, EmployeeID,EmployeeName,ShipVia)
3 =A2.append(A1)
4 =A2.close()

A2 创建组表时要在 create 函数中写清数据结构(字段顺序要和 A1 一致),@y 表示文件已存在时强制重新创建,缺省将出错
A3 将游标中的数据写入组表 (只支持游标)

数据库转储

A
1 =connect(“sqlserver2012”)
2 =A1.cursor@x(“select CustomerID,OrderDate,ProductID, Quantity, Unit, Price, Amount, EmployeeID,EmployeeName,ShipVia from Orders”)
3 =file(“Orders.ctx”).create@y(CustomerID,OrderDate,ProductID, Quantity, Unit, Price, Amount, EmployeeID,EmployeeName,ShipVia)
4 =A3.append(A2)
5 =A3.close()

A3 字段顺序要和 A2 一致

1.2.2 过滤与分组汇总运算

SQL

SELECT *
FROM Orders
WHERE Amount>1000

SPL

A
1 =file(“Orders.ctx”).open().cursor(;Amount>1000)
2 =A1.fetch()

A1 将过滤条件写到 cursor()函数里,这样会先判断过滤条件再决定是否生成结果集记录,不满足条件的数据会被直接跳过。如果写成 cursor().select(…),则将先生成结果集记录再做过滤,不满足条件的数据也会被生成


SQL

SELECT EmployeeID,sum(Amount) AS Amount
FROM Orders
WHERE OrderDate>='2022-01-01'
GROUP BY EmployeeID
ORDER BY EmployeeID

SPL

A
1 =file(“Orders.ctx”).open().cursor@m(EmployeeID,Amount;OrderDate>=date(“2022-01-01”,“yyyy-MM-dd”))
2 =A1.groups(EmployeeID;sum(Amount):Amount)

A1 只读需要的字段,减少扫描和创建对象时间,@m 选项表示并行取数


SQL

SELECT sum(Amount) AS Amount, sum(Quantity) AS Quantity,
count(1) AS num
FROM Orders
WHERE OrderDate>='2022-01-01'

SPL
返回序表

A
1 =file(“Orders.ctx”).open().cursor@m(Amount,Quantity;OrderDate>=date(“2022-01-01”,“yyyy-MM-dd”))
2 =A1.groups(;sum(Amount):Amount, sum(Quantity): Quantity, count(1):num)

返回序列

A
2 =A1.total(sum(Amount), sum(Quantity), count(1))

SQL

SELECT EmployeeID,count(1) num
FROM Orders
WHERE Amount>1000
GROUP BY EmployeeID

SPL

A
1 =file(“Orders.ctx”).open().cursor@m(EmployeeID;Amount>1000)
2 =A1.groups(EmployeeID;count(1):num)

SQL

SELECT EmployeeID,sum(Amount) AS Amount
FROM Orders
WHERE OrderDate>='2022-01-01'
GROUP BY EmployeeID
HAVING sum(Amount)>2100000

SPL

A
1 =file(“Orders.ctx”).open().cursor@m(EmployeeID,Amount;OrderDate>=date(“2022-01-01”,“yyyy-MM-dd”))
2 =A1.groups(EmployeeID;sum(Amount):Amount).select(Amount>250000)

以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾