Composite tables support column-wise storage that enables high performance during traversal.

1.2.1 Dump data to composite table

Dump data from a text file:

	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 When creating a composite table with the create function, write data structure explicitly (order of fields should be consistent with that in A1). @y option enables overwriting the file even when the file already exists; and error appears if the option is omitted.
A3 Append data in A1’s cursor to the composite table.

Dump data from the database:

	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 Keep same order of fields as A2.

1.2.2 Filtering, and grouping & aggregation

SQL

SELECT *
FROM Orders
WHERE Amount>1000

SPL

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

A1 Writing filtering condition in the cursor()function means that records are first judged according to the filtering condition before they are or aren’t added to the result set. Ineligible records will be directly skipped. cursor().select(…), however, first generates the result set with all records, including the ineligible ones, and then performs filtering. @x enables closing the composite table after the computation is finished.

---

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 Importing only the necessary fields shortens the time of scanning data and creating objects. @m option enables importing data in parallel.

---

SQL

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

SPL

Return a table sequence:

	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)

Return a sequence:

	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)