SPL: Set Operations
Set operations refer to the basic operations among various sets, including conjunction, intersection, union, difference, exclusive OR, containment and so forth. Structured data is often in the form of sets, and SPL provides rich methods and functions to facilitate the computation of such data. Let's take a look at how SPL solves set operation problems with some examples.
1. Conjunction
The conjunction of set A and set B is the set consisting of all members of set A or set B, duplicate members included. For example, the conjunction of set [1,2] and set [1,3] is the set [1,2,1,3], and the duplicate member 1 appears twice in the conjunction.
[e.g.1] A certain merchant has two sales channels, online sales and physical stores, and the sales data are stored in the SALES_ONLINE and SALES_STORE tables respectively. Now we want to query the total sales of each product in 2014. Some of the data are as follows:
SALES_ONLINE：
ID 
CUSTOMERID 
ORDERDATE 
PRODUCTID 
AMOUNT 
1 
HANAR 
2014/01/01 
17 
3868.6 
2 
ERNSH 
2014/01/03 
42 
1005.9 
3 
LINOD 
2014/01/06 
64 
400.0 
4 
OTTIK 
2014/01/07 
10 
1194.0 
5 
OCEAN 
2014/01/09 
41 
319.2 
… 
… 
… 
… 
… 
SALES_STORE：
ID 
CUSTOMERID 
ORDERDATE 
SELLERID 
PRODUCTID 
AMOUNT 
1 
EASTC 
2014/01/01 
1 
27 
3063.0 
2 
ERNSH 
2014/01/02 
8 
70 
2713.5 
3 
MAGAA 
2014/01/03 
2 
74 
1675.0 
4 
SAVEA 
2014/01/07 
7 
45 
2018.2 
5 
FOLIG 
2014/01/08 
8 
30 
1622.4 
… 
… 
… 
… 
… 
… 
The data structure of above two tables are not exactly the same, for instance, SALES_STORE has the field SELLERID for storing sales people. But both have the same fields of order date, product ID, sales amount, etc.
The symbol "" can be used in SPL to compute the conjunction of sets. For example, AB is the conjunction of set A and set B.
The SPL script is as follows:
A 

1 
=T("SalesOnline.csv").select(year(ORDERDATE)==2014) 
2 
=T("SalesStore.csv").select(year(ORDERDATE)==2014) 
3 
=A1A2 
4 
=A3.groups(PRODUCTID; sum(AMOUNT):AMOUNT) 
A1: Import the online sales table from the file and select the sales records of 2014.
A2: Import the physicalstore sales table from the file and select the sales records of 2014.
A3: Calculate the conjunction of the two sets using the symbol "".
A4: Group and aggregate the total sales of each product.
It is worth noting that SPL does not require sets of the same data structure during calculation, because the sequences of SPL support heterogeneous members. When we want to access the common fields Product ID and Sales Amount of the sequence members, we can access them directly like a normal data table, which is very convenient for users.
[e.g.2] Query the scores of the top 4 in math, top 3 in English, and top 2 in P.E. according to the score table. Some of the data are as follows:
CLASS 
STUDENTID 
SUBJECT 
SCORE 
1 
1 
English 
84 
1 
1 
Math 
77 
1 
1 
PE 
69 
1 
2 
English 
81 
1 
2 
Math 
80 
… 
… 
… 
… 
SPL provides the A.conj() function to calculate the conjunction of all member sets when A is a set composed of sets.
The SPL script is as follows:
A 

1 
=T("Scores.csv") 
2 
[Math,English,PE] 
3 
[4,3,2] 
4 
=A3.conj(A1.select(SUBJECT==A2(A3.#)).top(~;SCORE)) 
A1: Import the student score table.
A2: Define the set of subjects.
A3: Define the set of rankings corresponding to the subjects.
A4: Select the top N rankings of each subject separately and use the A.conj() function to calculate the conjunction of the member sets.
2. Intersection
The intersection of set A and set B is the set consisting of all members which belong to set A and set B at the same time. For example, the intersection of set [1,2,3] and set [1,3,5] is the set [1,3].
[e.g.3] A certain merchant has two sales channels, online sales and physical stores, and the sales data are stored in the SALES_ONLINE and SALES_STORE tables respectively. Now we want to query the customers who have placed orders in both online and physical stores in 2014. Some of the data are as follows:
SALES_ONLINE：
ID 
CUSTOMERID 
ORDERDATE 
PRODUCTID 
AMOUNT 
1 
HANAR 
2014/01/01 
17 
3868.6 
2 
ERNSH 
2014/01/03 
42 
1005.9 
3 
LINOD 
2014/01/06 
64 
400.0 
4 
OTTIK 
2014/01/07 
10 
1194.0 
5 
OCEAN 
2014/01/09 
41 
319.2 
… 
… 
… 
… 
… 
SALES_STORE：
ID 
CUSTOMERID 
ORDERDATE 
SELLERID 
PRODUCTID 
AMOUNT 
1 
EASTC 
2014/01/01 
1 
27 
3063.0 
2 
ERNSH 
2014/01/02 
8 
70 
2713.5 
3 
MAGAA 
2014/01/03 
2 
74 
1675.0 
4 
SAVEA 
2014/01/07 
7 
45 
2018.2 
5 
FOLIG 
2014/01/08 
8 
30 
1622.4 
… 
… 
… 
… 
… 
… 
The symbol "^" can be used in SPL to calculate the intersection of sets. For example, A^B is the intersection of set A and set B.
The SPL script is as follows:
A 

1 
=T("SalesOnline.csv").select(year(ORDERDATE)==2014) 
2 
=T("SalesStore.csv").select(year(ORDERDATE)==2014) 
3 
=A1.id(CUSTOMERID)^A2.id(CUSTOMERID) 
A1: Import the online sales table from the file and select the sales records of 2014.
A2: Import the physicalstore sales table from the file and select the sales records of 2014.
A3: Use the A.id() function to calculate the set of distinct values of Customer IDs. Then use the symbol "^" to calculate the intersection of the two sets, i.e. customers who have placed orders in both online and physical stores.
[e.g.4] Count the names of the customers who rank the top 10 every month in 2014 based on the sales data table. Some of the data are as follows:
ID 
CUSTOMERID 
ORDERDATE 
SELLERID 
PRODUCTID 
AMOUNT 
10400 
EASTC 
2014/01/01 
1 
27 
3063.0 
10401 
HANAR 
2014/01/01 
1 
17 
3868.6 
10402 
ERNSH 
2014/01/02 
8 
70 
2713.5 
10403 
ERNSH 
2014/01/03 
4 
42 
1005.9 
10404 
MAGAA 
2014/01/03 
2 
74 
1675.0 
… 
… 
… 
… 
… 
… 
SPL provides the A.isect() function to calculate the intersection of all member sets when A is a set composed of sets.
The SPL script is as follows:
A 

1 
=T("Sales.csv").select(year(ORDERDATE)==2014) 
2 
=A1.group(month(ORDERDATE)) 
3 
=A2.(~.groups(CUSTOMERID;sum(AMOUNT):AMOUNT)) 
4 
=A3.(~.top(10;AMOUNT).(CUSTOMERID)) 
5 
=A4.isect() 
A1: Import the sales table from the file and select the sales records of 2014.
A2: Group the sales table by month.
A3: Group the records of each month by customer.
A4: Count the top ten customers in total sales of each month.
A5: Use A.isect() function to calculate the intersection of all the customers selected from every month.
3. Union
The union of set A and set B is the set consisting of all members of set A or set B, duplicate members not included. For example, the union of the set [1,2] and set [1,3] is the set [1,2,3]. It is important to note that the duplicate member 1 only appears once in the union set.
[e.g.5] A certain merchant has two sales channels, online sales and physical stores, and the sales data are stored in the SALES_ONLINE and SALES_STORE tables respectively. Now we want to query which products have accumulated more than 10,000 in sales amount online or more than 5 in sales number offline in 2014. Some of the data are as follows:
SALES_ONLINE：
ID 
CUSTOMERID 
ORDERDATE 
PRODUCTID 
AMOUNT 
1 
HANAR 
2014/01/01 
17 
3868.6 
2 
ERNSH 
2014/01/03 
42 
1005.9 
3 
LINOD 
2014/01/06 
64 
400.0 
4 
OTTIK 
2014/01/07 
10 
1194.0 
5 
OCEAN 
2014/01/09 
41 
319.2 
… 
… 
… 
… 
… 
SALES_STORE：
ID 
CUSTOMERID 
ORDERDATE 
SELLERID 
PRODUCTID 
AMOUNT 
1 
EASTC 
2014/01/01 
1 
27 
3063.0 
2 
ERNSH 
2014/01/02 
8 
70 
2713.5 
3 
MAGAA 
2014/01/03 
2 
74 
1675.0 
4 
SAVEA 
2014/01/07 
7 
45 
2018.2 
5 
FOLIG 
2014/01/08 
8 
30 
1622.4 
… 
… 
… 
… 
… 
… 
The symbol "&" can be used in SPL to calculate the union of sets. For example, A&B is the union of set A and set B.
The SPL script is as follows:
A 

1 
=T("SalesOnline.csv").select(year(ORDERDATE)==2014) 
2 
=T("SalesStore.csv").select(year(ORDERDATE)==2014) 
3 
=A1.groups(PRODUCTID; sum(AMOUNT):AMOUNT).select(AMOUNT>10000) 
4 
=A2.groups(PRODUCTID; count(~):COUNT).select(COUNT>5) 
5 
=A3.(PRODUCTID)&A4.(PRODUCTID) 
A1: Import the online sales table from the file and select the sales records of 2014.
A2: Import the physicalstore sales table from the file and select the sales records of 2014.
A3: Group and aggregate the total sales amount of each online product and select the records that are greater than 10,000.
A4: Group and aggregate the total sales number of each product in the physical store, and select the records that are greater than 5.
A5: Use the symbol "&" to calculate the union of the product sets of online and physical stores.
[e.g.6] Query which products rank in the top 10 of the year in total sales number based on the sales data table. Some of the data are as follows:
ID 
CUSTOMERID 
ORDERDATE 
SELLERID 
PRODUCTID 
AMOUNT 
10400 
EASTC 
2014/01/01 
1 
27 
3063.0 
10401 
HANAR 
2014/01/01 
1 
17 
3868.6 
10402 
ERNSH 
2014/01/02 
8 
70 
2713.5 
10403 
ERNSH 
2014/01/03 
4 
42 
1005.9 
10404 
MAGAA 
2014/01/03 
2 
74 
1675.0 
… 
… 
… 
… 
… 
… 
SPL provides the A.union() function to calculate the union of all member sets when A is a set composed of member sets.
The SPL script is as follows:
A 

1 
=T("Sales.csv") 
2 
=A1.group(year(ORDERDATE)) 
3 
=A2.(~.groups(PRODUCTID;count(~):COUNT)) 
4 
=A3.(~.top(5;COUNT).(PRODUCTID)) 
5 
=A4.union() 
A1: Import the sales table from the file.
A2: Group the sales table by year.
A3: Group the records of each year by product to count the sales number.
A4: Select the top 10 product IDs in sales number of each year.
A5: Use A.union() function to calculate the union of the selected products of all years.
4. Difference
The difference set of set A and set B is the set consisting of all members belonging to set A and not belonging to set B. For example, the difference of the set [1,2,3] and set [1,3,5] is the set [2].
[e.g.7] A certain merchant has two sales channels, online sales and physical stores, and the sales data are stored in the SALES_ONLINE and SALES_STORE tables respectively. Now we want to query the customers who have spend more than 1,000 in physical store but never placed any order online. Some of the data are as follows:
SALES_ONLINE：
ID 
CUSTOMERID 
ORDERDATE 
PRODUCTID 
AMOUNT 
1 
HANAR 
2014/01/01 
17 
3868.6 
2 
ERNSH 
2014/01/03 
42 
1005.9 
3 
LINOD 
2014/01/06 
64 
400.0 
4 
OTTIK 
2014/01/07 
10 
1194.0 
5 
OCEAN 
2014/01/09 
41 
319.2 
… 
… 
… 
… 
… 
SALES_STORE：
ID 
CUSTOMERID 
ORDERDATE 
SELLERID 
PRODUCTID 
AMOUNT 
1 
EASTC 
2014/01/01 
1 
27 
3063.0 
2 
ERNSH 
2014/01/02 
8 
70 
2713.5 
3 
MAGAA 
2014/01/03 
2 
74 
1675.0 
4 
SAVEA 
2014/01/07 
7 
45 
2018.2 
5 
FOLIG 
2014/01/08 
8 
30 
1622.4 
… 
… 
… 
… 
… 
… 
The symbol “\” can be used in SPL to calculate the difference set of sets. For example, A\B is the difference set of set A and set B.
The SPL script is as follows:
A 

1 
=T("SalesOnline.csv").select(year(ORDERDATE)==2014) 
2 
=T("SalesStore.csv").select(year(ORDERDATE)==2014) 
3 
=A2.groups(CUSTOMERID; count(~):COUNT).select(COUNT>3) 
4 
=A3.id(CUSTOMERID)\A1.id(CUSTOMERID) 
A1: Import the online sales table from the file and select the sales records of 2014.
A2: Import the physicalstore sales table from the file and select the sales records of 2014.
A3: Group and aggregate the total sales number of each customer of the physical store and select the records with more than 3.
A4: Use the symbol “\” to calculate the difference set of physicalstore and online customers.
[e.g.8] Query the customers who rank in the top 10 in total sales of January only in 2014 based on the sales data table. Some of the data are as follows:
ID 
CUSTOMERID 
ORDERDATE 
SELLERID 
PRODUCTID 
AMOUNT 
10400 
EASTC 
2014/01/01 
1 
27 
3063.0 
10401 
HANAR 
2014/01/01 
1 
17 
3868.6 
10402 
ERNSH 
2014/01/02 
8 
70 
2713.5 
10403 
ERNSH 
2014/01/03 
4 
42 
1005.9 
10404 
MAGAA 
2014/01/03 
2 
74 
1675.0 
… 
… 
… 
… 
… 
… 
SPL provides the A.diff() function to calculate the difference set of the first member set and other member sets when A is a set composed of sets.
The SPL script is as follows:
A 

1 
=T("Sales.csv").select(year(ORDERDATE)==2014) 
2 
=A1.group(month(ORDERDATE)) 
3 
=A2.(~.groups(CUSTOMERID;sum(AMOUNT):AMOUNT)) 
4 
=A3.(~.top(10;AMOUNT).(CUSTOMERID)) 
5 
=A4.diff() 
A1: Import the sales table from the file and select the sales records of 2014.
A2: Group the sales table by month.
A3: Group and aggregate the records of each month by customer.
A4：Count the top ten customers in total sales of every month.
A5: Use the A.diff() function to calculate the difference set of customers in January and other months.
5. Exclusive OR
There are set A and set B. The members that belong to either A or B but do not belong to both A and B constitute the exclusive OR set of A and B. For example, the exclusive OR set of set [1,2,3] and set [1,3,5] is set [2,5].
[e.g.9] The scores of two semesters are stored in separate tables. Query the students whose overall scores rank in the top 10 only once in the two semesters of Class One. Some of the data are as follows:
First semester SCORES1：
CLASS 
STUDENTID 
SUBJECT 
SCORE 
1 
1 
English 
84 
1 
1 
Math 
77 
1 
1 
PE 
69 
1 
2 
English 
81 
1 
2 
Math 
80 
… 
… 
… 
… 
Next semester SCORES2：
CLASS 
STUDENTID 
SUBJECT 
SCORE 
1 
1 
English 
97 
1 
1 
Math 
64 
1 
1 
PE 
97 
1 
2 
English 
56 
1 
2 
Math 
82 
… 
… 
… 
… 
The symbol "%" can be used in SPL to calculate the exclusive OR of sets. For example, A%B is the exclusive OR set of set A and set B.
The SPL script is as follows:
A 

1 
=T("Scores1.csv") 
2 
=T("Scores2.csv") 
3 
=A1.groups(STUDENTID; sum(SCORE):SCORE) 
4 
=A2.groups(STUDENTID; sum(SCORE):SCORE) 
5 
=A3.top(10;SCORE).(STUDENTID) 
6 
=A4.top(10;SCORE).(STUDENTID) 
7 
=A5%A6 
A1: Import the score table of first semester from the file.
A2: Import the score table of next semester from the file.
A3: Group and aggregate the total scores of each student in the first semester by student ID.
A4: Group and aggregate the total scores of each student in the next semester by student ID.
A5: Select the top 10 student IDs in total score of the first semester.
A6: Select the top 10 student IDs in total score of the next semester.
A7: Use the symbol "%"to calculate the exclusive OR set of the first and next semesters.
[e.g.10] Query if customer RATTC has been ranked in the top three of monthly sales in 2014. Some of the data are as follows:
ID 
CUSTOMERID 
ORDERDATE 
SELLERID 
PRODUCTID 
AMOUNT 
10400 
EASTC 
2014/01/01 
1 
27 
3063.0 
10401 
HANAR 
2014/01/01 
1 
17 
3868.6 
10402 
ERNSH 
2014/01/02 
8 
70 
2713.5 
10403 
ERNSH 
2014/01/03 
4 
42 
1005.9 
10404 
MAGAA 
2014/01/03 
2 
74 
1675.0 
… 
… 
… 
… 
… 
… 
SPL provides the A.cor() function to calculate the exclusive OR set of multiple member sets when A is a set composed of sets.
The SPL script is as follows:
A 

1 
=T("Sales.csv").select(year(ORDERDATE)==2014) 
2 
=A1.group(month(ORDERDATE)) 
3 
=A2.(~.groups(CUSTOMERID; sum(AMOUNT):AMOUNT)) 
4 
=A3.new(~.top(3; AMOUNT):TOP3) 
5 
=A4.(TOP3.(CUSTOMERID).pos("RATTC")>0) 
6 
=A5.cor() 
A1: Import the sales table from the file and select the sales records of 2014.
A2: Group the sales table by month.
A3: Group the monthly sales records by customer.
A4: Count the top three customers in total sales per month.
A5: Calculate whether "RATTC" exists among the customers of each group.
A6: Use the A.cor() function to calculate the exclusive OR set whether each month contains the customer "RATTC". "RATTC" exists if the result is true; "RATTC" does not exist if the result is false.
6. Belonging and containment
For two sets A and B, B is said to contain A if any member of set A is a member of set B. Belonging is the relationship between a member and a set, and we say that x belongs to A when there is a member x in set A. For example, set [1,2,3] contains set [1,2], and member 1 belongs to set [1,2,3].
[e.g.11] Calculate the average salary of each department in California, New York, Texas, and Washington based on the employee table. Some of the data are as follows:
ID 
NAME 
SURNAME 
STATE 
DEPT 
SALARY 
1 
Rebecca 
Moore 
California 
R&D 
7000 
2 
Ashley 
Wilson 
New York 
Finance 
11000 
3 
Rachel 
Johnson 
New Mexico 
Sales 
9000 
4 
Emily 
Smith 
Texas 
HR 
7000 
5 
Ashley 
Smith 
Texas 
R&D 
16000 
… 
… 
… 
… 
… 
… 
SPL provides the A.contain(x) function to determine whether x belongs to set A.
The SPL script is as follows:
A 

1 
=T("Employee.csv") 
2 
[California,New York,Texas,Washington] 
3 
=A1.select(A2.contain(STATE)) 
4 
=A3.groups(DEPT; avg(SALARY):SALARY) 
A1: Import the employee table from the file.
A2: Define a constant set of these states.
A3: Select the records from the employee table whose state names belong to the A2 set.
A4: Group the records selected by A3 to calculate the average salary of each department.
[e.g.12] Query the students who took both Matlab and Modern wireless communication system courses based on the course table and course selection table. Some of the data are as follows:
COURSE：
ID 
NAME 
TEACHERID 
1 
Environmental protection and sustainable development 
5 
2 
Mental health of College Students 
1 
3 
Matlab 
8 
4 
Electromechanical basic practice 
7 
5 
Introduction to modern life science 
3 
6 
Modern wireless communication system 
14 
… 
… 
… 
SELECT_COURSE：
ID 
STUDENT_NAME 
COURSE 
1 
Rebecca Moore 
2,7 
2 
Ashley Wilson 
1,8 
3 
Rachel Johnson 
2,7,10 
4 
Emily Smith 
1,10 
5 
Ashley Smith 
5,6 
… 
… 
… 
This problem is to determine whether the course fields of course selection table contain the course set [3,6] (the course IDs of Matlab and Modern wireless communication system), which is not complicated.
SPL provides the A.pos(B) function to obtain the position of a set B member in set A, and return null if the member does not exist in set A.
The SPL script is as follows:
A 

1 
=T("Course.txt") 
2 
=T("SelectCourse.txt") 
3 
=A1.select(NAME=="Matlab"  NAME=="Modern wireless communication system").(ID) 
4 
=A2.run(COURSE=COURSE.split@cp()) 
5 
=A4.select(COURSE.pos(A3)!=null) 
A1: Import the course table from the file.
A2: Import the course selection table from the file.
A3: Select the IDs of the two courses to create a set .
A4: Split the selected courses by comma and then parse the numbers to create the set.
A5: Use the A.pos() function to locate the IDs of the two courses in the selection table, and the returned result that is not null is just the record of selecting two courses at the same time.
Chinese version