RaqForum 25 No.
198 View  • 
     ...

3.30 Concatenate two large tables through MERGE

 
spl-cookbook(224)

Concatenate-merge two large data tables of same structure for further query.
Both database db1 and database db2 contain sales record tables Sales of same structure. Each table is too large to be loaded into the memory at once. The task is to calculate the sales amount in each month of the year 2014.

OrderID Customer SellerId OrderDate Amount
10400 EASTC 1 2014/01/01 3063.0
10401 HANAR 1 2014/01/01 3868.6
10402 ERNSH 8 2014/01/02 2713.5
10403 ERNSH 4 2014/01/03 1005.9
10404 MAGAA 2 2014/01/03 1675.0
… … … … …

SPL script:

A
1 =connect(“db1”).cursor@x(“select * from Sales where year(OrderDate)=2014 order by OrderDate”)
2 =connect(“db2”).cursor@x(“select * from Sales where year(OrderDate)=2014 order by OrderDate”)
3 =[A1,A2].mergex(OrderDate)
4 =A3.groups@o(month(OrderDate):Month; count(~):Count)

A1 Retrieve Sales records of 2014 from db1 and sort them by OrderDate.
A2 Retrieve Sales records of 2014 from db2 and sort them by OrderDate.
A3 Use mergex function to concatenate-merge records of the two cursors by OrderDate.
A4 Use groups function to group records and calculate sales amount of each month; @o option enables creating a new group whenever the next month begins.

Execution result:

Month Count
1 33
2 29
… …
spl-cookbook(224)
 
 •  198 View  •