3.27 Aggregation on sequences: intersection
Perform aggregation on a sequence of sequences through calculating intersection.
Find names of customers whose sales amounts in each month of the year 1014 rank in top 20 based on the following sales data table.
| 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(“db”).query@x(“select * from sales”) |
| 2 | =A1.select(year(OrderDate)==2014) |
| 3 | =A2.group(month(OrderDate)) |
| 4 | =A3.(~.group(Customer)) |
| 5 | =A4.(~.top(-20;sum(Amount))) |
| 6 | =A5.(~.(Customer)) |
| 7 | =A6.isect() |
A1 Connect to the data source and retrieve sales data table.
A2 Get data of the year 2014.
A3 Use group function to group data of the year 2014 by month.
A4 Group A3’s each group by customer.
A5 Loop records of each month to find customers whose sales amounts rank in top 20.
A6 List names of customers whose sales amounts rank in top 20 in each month.
A7 Use isect() function to get intersection of top-20 customers of all months.
Execution result:
| Member |
|---|
| HANAR |
| SAVEA |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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
