12.2 Judge belongingness of a large set
Get records from a table through belongingness of a large set.
Calculate sale amount of each big customer in the year 2014 based on the following sale table.
| ID | Customer | SellerId | Date | 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 |
| … | … | … | … | … |
When there are 10 or more members in a specified constant sequence, we can first sort it and then adds @b option to A.contain() function to perform a binary search.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”).query@x(“select * from Sales”) |
| 2 | =[“SAVEA”,“QUICK”,“ERNSH”,“HUN”,“RATTC”,“HANAR”,“FOLKO”,“QUEEN,MEREP”,“WHITC”,“FRANK”,“KOENE”].sort() |
| 3 | =A1.select(year(Date)==2014 && A2.contain@b(Customer)) |
| 4 | =A3.groups(month(Date):Month; sum(Amount):Amount) |
A1 Connect to the database and query Sales table.
A2 Create a sequence of constants where members are big customers and sort it.
A3 Get records of big customers in the year 2014.
A4 Group selected records by month and calculate sale amount in each month.
Execution result:
| Month | Amount |
|---|---|
| 1 | 16947.3 |
| 2 | 27793.3 |
| 3 | 14602.7 |
| … | … |
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
