"Converting a date to a small integer helps to obtain better storage and computing performance. [ .."

mars RaqForum 25 No.
182 View • 1 Years ago

1.3 Converting date to small integer

Converting a date to a small integer helps to obtain better storage and computing performance.

1.3.1 Conversion during data dump

SPL provides a space saving method. The method uses days@o(date) to convert year/month to the number of months between the date data and the year 1970, and represents the day with five binary digits (As a month has 31 days at most, five-digit binary numbers can represent all numbers from 0 to 31). The conversion is equivalent to ((yyyy-1970)*12+(mm-1))*32+dd, which lets us to represent any date between the year 1970 and the year 2140 using small numbers.

In SPL, we can directly use year(int), month(int) and day(int) to get the year part, month part and day part of the conversion result. This makes it convenient to perform aggregation involving the year part, month part or day part.

	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).run(OrderDate=days@o(OrderDate))
2	=file(“Orders.ctx”).create@y(CustomerID,OrderDate,ProductID, Quantity, Unit, Price, Amount, EmployeeID,EmployeeName,ShipVia)
3	=A2.append(A1)
4	=A2.close()

A1 Convert OrderDate to a small integer.

1.3.2 Computation using converted date

SQL

SELECT *
FROM Orders
WHERE OrderDate>='2022-01-01' and OrderDate<='2022-03-15'

SPL

	A
1	>st=date(“2022-01-01”), et=date(“2022-03-15”), start=days@o(st), end=days@o(et)
2	=file(“Orders.ctx”).open().cursor@m(;OrderDate>=start && OrderDate<end).run(OrderDate=date@o(OrderDate))
3	=A2.fetch()

A1 Convert parameters start and end to small integers.
A2 Convert OrderDate in the result set back to date value.

SQL

SELECT sum(Amount) Amount,year(OrderDate) years,month(OrderDate) months
FROM Orders
WHERE OrderDate>='2022-01-01'
GROUP BY year(OrderDate),month(OrderDate)
ORDER BY years,months

SPL

	A
1	>st=date(“2022-01-01”), start=days@o(st)
2	=file(“Orders.ctx”).open().cursor@m(OrderDate,Amount;OrderDate>=start)
3	=A2.groups(year(OrderDate):years,month(OrderDate):months; sum(Amount):Amount)

A3 To aggregate by year and month, just use year(int) and month(int) to obtain the year part and month part of the small integer to which a date is converted.

SQL

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

SPL

	A
1	>st=date(“2022-01-01”), et=date(“2022-03-31”), start=days@o(st), end=days@o(et)
2	=file(“Orders.ctx”).open().cursor@m(EmployeeID, Amount; OrderDate>=start && OrderDate<end)
3	=A2.groups(EmployeeID;sum(Amount):Amount)

SQL

SELECT quarter(OrderDate) quart,sum(Amount) Amount
FROM Orders
WHERE OrderDate>='2022-01-01'
GROUP BY quarter(OrderDate)

SPL

	A
1	> st=date(“2022-01-01”), start=days@o(st)
2	=file(“Orders.ctx”).open().cursor@m(OrderDate,Amount;OrderDate>=start)
3	=A2.groups(month(OrderDate)\3+1:Quarter; sum(Amount):Amount)