# 1. Locate members

To locate a member in another ordered set, we compare the member to each member of the target set to find its position.

【Example 1】 In the Teachers.txt below, column 1 contains professors’ names, column 2 has subjects, and other columns contain course code (null means an absence of code). Here’s part of the source table:

Here’s the Courses table:

【SPL script】

A5’s result:

A member could appear more than once when locating it in an ordered set. For instance, we may find that there are more than one student who get a 100 score for math and that more than one employee who has the name Ashley.

【Example 2】 Based on the following associated Sales table and Customer table, find the customers who didn’t place an order in the year of 2014. The following picture shows their relationship:

【SPL script】

A6’s result:

# 2. Locate the maximum/minimum value

To locate the maximum/minimum value is to get the sequence number(s) of the records containing the maximum or minimum value.

【Example 3】 Based on the stock transaction table, calculate the growth rate of the date when the SSE Composite Index has its higher closing price compared with the prior day. Below is part of the source table:

【SPL script】

A4’s result:

Similarly, we can use A.pmin() function to get the sequence number of the record containing the minimum value:

There could be more than one record containing the maximum value. To return sequence numbers of all eligible records, just use @a option with A.pmax() function:

To locate a record from back to front, A.pmax() function has @z option:

# 3. Conditional locate operation

A conditional locate operation calculates a specific condition on each member of an ordered set and returns the sequence numbers of members that generates a "true", such as getting sequence numbers of employees who are above 50 years old from the employee table, finding sequence number of math scores that are over 90 from the score table, and so on.

【Example 4】 Based on the stock transaction table, calculate the growth rate of each of the transaction dates when the closing prices increases by over 3% compared with the previous transaction date. Below is part of the source table:

【SPL script】

A3’s result:

A4’s result:

So we can see that the transaction amount of each of the three days increases greatly compared with the prior day.

# 4. Location by intervals

Besides locating a member in ordered set according to a specific value or expression, sometimes we perform the location by specified interval for further grouping and aggregation. [0,18,35,60] is a set of ages that represents teenagers, young adults, middle-aged and older adults in order. The interval number where age 20 falls in is 2, which means they are a young adult. Here we define a set of salaries [0,8000,15000,30000] and try to find the interval number corresponding to the salary 25000, the result is 3.

【Example 5】 Based on the following EMPLOYEE table, count the employees in different salary ranges: below 8000, between 8000 and 120000, and above 12000. Here’s part of the source table:

【SPL script】

A5’s result:

At some occasions we need to first calculate the interval values and then find the number of intervals in a set corresponding to a member.

【Example 6】 Based on the following EMPLOYEE table, calculate the average salary for employees of different hire durations: below 10 years, between 10 to 20 years and above 20 years. Below is part of the source table:

【SPL script】

A5’s result:

# 5. Get original positions for sorted members

Sorting is the rearrangement of members in a set of a certain order by a key word using a specific method. It aims to sort out the currently “unordered” set of records. But, the original order is not always useless or meaningless. For instance, a result orders table after query is arranged by datetime, but we need to sort it out by sales amount. Yet the datetime will be useless in subsequent calculations, so we also want to retain the original order while ordering the records by the specific condition.

【Example 7】 Based on the following EMPLOYEE table, get records of the thee eldest employees and arrange them by hire dates in temporal order. Below is part of the source table:

【SPL script】

A4’s result:

# 6. Locate a subset

Apart from locating individual members, we can locate a subset of set at a time. For instance, the result of locating the set [c,d,a] in [a,b,c,d,e] is [3,4,1], and the locating set [c,f] in [a,b,c,d,e] returns a null because the larger set contains doesn’t contain f.

【Example 8】 Based on the PostRecord table, group records by labels and count the frequencies of each label. Below is part of the source table:

【SPL script】

A5’s result:

# 7. Set membership test

In certain scenarios, it’s not the sequence numbers of set B’s members in set A that we are concerned with, but that whether set A includes all members of set B.

【Example 9】 The following table records official languages of different countries. Now we want to find countries that use both Chinese and English as their official languages. Below is part of the source data:

【SPL script】

A5’s result:

# 8. Location by primary key

We have a special method for finding sequence numbers of members among primary key values.

【Exmaple 10】 Below are the associated Product table and Category table, find product records whose category IDs are not included in the Category table. Here’s their relationship:

【SPL script】

A4’s result:

# 9.Finding sequence numbers of top/bottom N records

Finding sequence numbers of top/bottom N records is a common computing goal, such as finding students whose math scores rank in top 3 in the class, getting 5 employees who have the shortest hire durations. Let’s look at how to get sequence numbers of the top/bottom N members.

【Example 11】 The following table stores SSE Composite Index transaction information. We want to find the growth rate of each of the 3 days with the highest closing prices in 2019, compared with the prior day. Below is part of the source data:

【SPL script】

A3’s result:

A4’s result:

There are more examples in SPL CookBook.