1.4 Conversion from enumerated string to small integer
We can convert an enumerated string that may have a limited range of values to integers so that better storage and computing performance can be achieved.
1.4.1 Conversion during data dump
We can replace an enumerated field with the corresponding ordinal number in the sequence of values. Take ShipVia as an example:
A | |
---|---|
1 | =file(“ShipVia.btx”).import@bi() |
2 | =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).run(OrderDate=days@o(OrderDate), ShipVia=A1.pos@b(ShipVia)) |
3 | =file(“Orders.ctx”).create@y(CustomerID,OrderDate,ProductID, Quantity, Unit, Price, Amount, EmployeeID,EmployeeName,ShipVia) |
4 | =A3.append(A2) |
5 | =A3.close() |
A1 Import the sequence of unique values of ShipVia field. Generally, the values are already ordered in the source file. @i option enables reading them as a sequence.
We can also write the sequence of unique values directly in the SPL script:
A | |
---|---|
1 | [Federal Shipping,Speedy Express,United Package] |
A1 Convert ShipVia field to ordinal number of the corresponding value in the sequence. As the sequence of unique values are ordered, we can use @b option to perform a binary search.
1.4.2 Computation using converted data
SQL
SELECT sum(Orders.Quantity) AS Quantity,ShipVia
FROM Orders
WHERE ShipVia='Federal Shipping' or ShipVia='Speedy Express'
GROUP BY ShipVia
SPL
A | |
---|---|
1 | >arg1=“Federal Shipping”,arg2=“Speedy Express” |
2 | =file(“ShipVia.btx”).import@ib() |
3 | =arg1=A2.pos@b(arg1),arg2=A2.pos@b(arg2) |
4 | =file(“Orders.ctx”).open().cursor@mx(Quantity, ShipVia; ShipVia==arg1 || ShipVia==arg2) |
5 | =A4.groups(ShipVia;sum(Quantity):Quantity) |
6 | =A5.run(ShipVia=A2(ShipVia)) |
A3 Calculate the small integer corresponding to each parameter value according to the order of sequence of unique values.
A6 Convert ShipVia in the result set back to corresponding string value.
SQL
SELECT sum(Orders.Quantity) AS Quantity,ShipVia
FROM Orders
WHERE ShipVia in ('Federal Shipping','Speedy Express')
GROUP BY ShipVia
SPL
A | |
---|---|
1 | >arg=[“Federal Shipping”,“Speedy Express”] |
2 | =file(“ShipVia.btx”).import@ib() |
3 | =A2.(arg.contain@b(~)) |
4 | =file(“Orders.ctx”).open().cursor@mx(Quantity, ShipVia;A3(ShipVia)) |
5 | =A4.groups(ShipVia;sum(Quantity):Quantity) |
6 | =A5.run(ShipVia=A2(ShipVia)) |
A3 The filtering parameter is a set. According to order of the sequence of values, get the corresponding sequence of booleans that denote whether the current member is contained in the parameter set.
A4 To filter records, get a value from A3 according to the ordinal number and judge whether the current ShipVia field value is contained in the parameter set.
A6 Convert ShipVia in the result set back to corresponding string value.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL