开封
乾学院 6 号会员
3 回帖  •  682 浏览  • 
     ...

1.2 组表

 
SPL性能优化基础练习(38)

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

1.2.1 把数据表转储成组表

文本转储

A
1 =file(“Orders.txt”).cursor@t(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@x(;Amount>1000)
2 =A1.fetch()

A1 将过滤条件写到 cursor()函数里，这样会先判断过滤条件再决定是否生成结果集记录，不满足条件的数据会被直接跳过。如果写成 cursor().select(…)，则将先生成结果集记录再做过滤，不满足条件的数据也会被生成。@x 表示计算完成后自动关闭组表

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@mx(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@mx(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@mx(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)>250000

SPL

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

 

SPL性能优化基础练习(38)