# Membership Test for Structured Data

## 1. Set membership test

Get records from a table according to whether a value is included in a specified set (where the number of members is within 10).

【Example 1】 Calculate the average salary in each department in first-tier cities based on the employee table. Below is part of the source data:

【Analysis】

When getting data from the employee table, we need to check whether the city where an employee settles is included in the constant set of [Beijing, Shanghai, Guangzhou, Shenzhen]. When the number of members in the set is within 10, we can use A.contain() function to get the eligible records.

【SPL script】

A4’s result:

Get records from a table according to whether a value is included in a specified set (that has members over 10).

【Example 2】 Calculate the sales amount of every big customer in each month of the year 2014 based on the sales table. Below is part of the source data:

【Analysis】

Similarly, we need to check whether the Customer value in each sales record is included in the constant set of big customers. In this case the number of members in the set is relatively large (above 10). We first sort the constant set, and then use @b option with A.contain() function to perform a binary search.

【SPL script】

A4’s result:

## 2. Foreign key mapping membership test

Get records from a table according to whether a record is included in the foreign key references in its associated table.

【Example 3】 Find how many students are there in each class who select the Matlab course. Below are SelectCourse table and Course table:

【Analysis】

We need to check whether the CourseID in a record is “Matlab” when getting records from the SelectCourse table. Before that we get a set of course records from the Course table where the course name is “Matlab”, and then get records from the SelectCourse table where the CourseID is contained in the set.

【SPL script】

A6’s result:

## 3. Non-foreign key mapping membership test

Get records from a table according to whether a record is included in the non-foreign key references in its associated table.

【Example 4】 Find the number of students in each class who have a score of above 80 for any subject. Below are Score table and Student table:

【Analysis】

When getting records from the Student table, we need to check whether the current student has a subject whose score is above 80. First we select all Score table records where the Score value is greater than 80 and perform distinct by StudentID to generate a set of records of unique student IDs whose corresponding scores are above 80. Then we get Student table records whose IDs are included in that set of records.

【SPL script】

A7’s result:

In an optimized and efficient way, test and get records from a table according to whether a record is included in the non-foreign key references in its associated table.

【Example 5】Get the number of customers in each city who placed an order in 2014. Below are Sales table and Customer table:

【Analysis】

When getting records from the Sales table, we need to check whether the current customer placed an order in 2014. First we select sales table records of 2014 and perform distinct by customer ID to generate a set of records of unique customer IDs who have sales records in 2014. Then we get Costomer table records whose IDs are included in that set of records.

【SPL script】

A6’s result:

## 4. Foreign key mapping non-membership test

Get records from a table according to whether a record is not included in the non-foreign key references in its associated table.

【Example 6】 Find the total sales amount of each new customer in the year of 2014. Below are Sales table and Customer table:

【Analysis】

When getting records from the Sales table, we need to make sure the current customer didn’t place an order in 2014. First we select sales table records of 2014 and then we get those whose customer IDs are not included in the Customer table.

【SPL script】

A5’s result:

Find more examples in SPL CookBook