3.30 Concatenate two large tables through MERGE
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 Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
