Locate Operation on Ordered Sets
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:
| Teachers.txt | ||||||||
| Petitti | Matematica | mif | mig | vif | vig | null | null | … |
| Canales | Apesca | luc | lud | mac | mad | mic | mid | … |
| Lucero | NavegacionI | lub | luc | lud | lue | mab | mac | … |
| Bergamaschi | TecPesc | lua | luf | maa | maf | mia | mif | … |
| … | … | … | … | … | … | … | … | … |
Here’s the Courses table:
| Monday | Tuesday | Wednesday | Thursday | Friday |
| lua | maa | mia | jua | via |
| lub | mab | mib | jub | vib |
| luc | mac | mic | juc | vic |
| lud | mad | mid | jud | vid |
| lue | mae | mie | jue | vie |
| luf | maf | mif | juf | vif |
| lug | mag | mig | jug | vig |
【SPL script】
| A | B | |
| 1 | =file("Teachers.txt").import() | /Import data from Teachers.txt |
| 2 | =A1.new(#1:professor,~.array().to(3,A1.fno()).select(~):codeArray) | /Create a new table based on the Teacherstable, where column 1 contains professors’names and column 2 is lists of courses |
| 3 | =file("Courses.txt").import@t().conj(~.array()) | /Import data from Courses.txt, and concatenate all courses |
| 4 | =A3.(A2.select(codeArray.pos(A3.~)).(professor)) | /Loop through each of A3’s courses, use pos() function to locate the current course in A2’s course list, and get the eligible professor for this course |
| 5 | =create(Monday,Tuesday,Wednesday,Thursday,Friday).record(A4.(~.concat@c())) | /Create a Monday-to -Friday timetable and populate professors to it |
A5’s result:
| Monday | Tuesday | Wednesday | Thursday | Friday |
| Bergamaschi,Puebla | Bergamaschi,Pue… | Bergamaschi,Puebla | Bergamaschi,Pue… | Bergamaschi,Puebla |
| Lucero,Puebla,Lu… | Lucero,Mazza,Pu… | Lucero,Puebla,Chi… | Lucero,Mazza,Pe… | Lucero,Puebla,Vel… |
| Canales,Lucero,P… | Canales,Lucero,M… | Canales,Lucero,P… | Canales,Lucero,M… | Lucero,Velasco,Lu… |
| … | … | … | … | … |
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】
| A | B | |
| 1 | =connect("db") | /Connect to database |
| 2 | =A1.query("select * from Sales where year(OrderDate)=2014") | /Get records of 2014 from Salestable |
| 3 | =A1.query("select * from Customer") | /Query Customertable |
| 4 | =A3.(ID).sort() | /List customerIDs and sort them |
| 5 | =A2.align(A4.len(), A4.pos@b(CustomerID)) | /Group sales records of 2014 by A4’s customer IDs, during which pos function is used to locate the corresponding customer ID in A4’s list; since customer IDs are ordered, we use @b option to perform a fast binary search |
| 6 | =A3(A5.pos@a(null)) | /pos() function works with @ option to get all records of customers who didn’t place an order (A5’s value is null); by default the function returns only the first-found eligible record |
A6’s result:
| ID | Name | State | … |
| ALFKI | CMA-CGM | Texas | … |
| CENTC | Nedlloyd | Florida | … |
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:
| Date | Open | Close | Amount |
| 2019/12/31 | 3036.3858 | 3050.124 | 2.27E11 |
| 2019/12/30 | 2998.1689 | 3040.0239 | 2.67E11 |
| 2019/12/27 | 3006.8517 | 3005.0355 | 2.58E11 |
| 2019/12/26 | 2981.2485 | 3007.3546 | 1.96E11 |
| 2019/12/25 | 2980.4276 | 2981.8805 | 1.9E11 |
| … | … | … | … |
【SPL script】
| A | B | |
| 1 | =file("000001.csv").import@ct() | /Import the source data file |
| 2 | =A1.sort(Date) | /Sort data by date |
| 3 | =A2.pmax(Close) | /Use A.pmax() function to get the sequence number of the record having the highest closing price |
| 4 | =A2.calc(A3,Close/Close[-1]-1) | /Calculate the growth rate by dividing the current closing price by the previous closingprice |
A4’s result:
| Member |
| 0.010276967857506536 |
Similarly, we can use A.pmin() function to get the sequence number of the record containing the minimum value:
| A | B | |
| 3 | =A3.pmin(Close) | /Use A.pmin() function to get the sequence number of the record having the lowest closing price |
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:
| A | B | |
| 3 | =A2.pmax@a(Close) | / A.pmin()function works with @a option to get sequence numbers of all records having the highest closing price |
To locate a record from back to front, A.pmax() function has @z option:
| A | B | |
| 3 | =A2.pmax@z(Close) | / A.pmax()function works with @z option to get sequence number of the record with the highest closing price |
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:
| Date | Open | Close | Amount |
| 2019/12/31 | 3036.3858 | 3050.124 | 2.27E11 |
| 2019/12/30 | 2998.1689 | 3040.0239 | 2.67E11 |
| 2019/12/27 | 3006.8517 | 3005.0355 | 2.58E11 |
| 2019/12/26 | 2981.2485 | 3007.3546 | 1.96E11 |
| 2019/12/25 | 2980.4276 | 2981.8805 | 1.9E11 |
| … | … | … | … |
【SPL script】
| A | B | |
| 1 | =file("000001.csv").import@ct() | /Import the source data file |
| 2 | =A1.select(year(Date)==2019).sort(Date) | /Get stock records of 2019 and sort them by date |
| 3 | =A2.pselect@a(Close/Close[-1]>1.03) | / A.pselect ()function works with @a option to get sequence numbers of all records where closingprices increases by over 3% |
| 4 | =A3.new(A2(~).Date:Date, A2(~).Amount/A2(~-1).Amount:'Amount increase') | /Calculate growth rate of each day circularly |
A3’s result:
| Member |
| 161 |
| 187 |
| 211 |
A4’s result:
| Date | Amount increase |
| 2019/02/25 | 1.758490566037736 |
| 2019/03/29 | 1.3344827586206895 |
| 2019/05/10 | 1.3908629441624365 |
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:
| ID | NAME | BIRTHDAY | SALARY |
| 1 | Rebecca | 1974-11-20 | 7000 |
| 2 | Ashley | 1980-07-19 | 11000 |
| 3 | Rachel | 1970-12-17 | 9000 |
| 4 | Emily | 1985-03-07 | 7000 |
| 5 | Ashley | 1975-05-13 | 16000 |
| … | … | … | … |
【SPL script】
| A | B | |
| 1 | =connect("db") | /Connect to database |
| 2 | =A1.query("select * from EMPLOYEE") | /Query EMPLOYEEtable |
| 3 | [0,8000,12000] | /Define salary intervals |
| 4 | =A2.align@a(A3.len(),A3.pseg(SALARY)) | / A.pseg(y) function gets the interval number that a salaryvalue corresponds to |
| 5 | =A4.new(A3 (#):SALARY,~.count():COUNT) | /Count the employees in each interval |
A5’s result:
| SALARY | COUNT |
| 0 | 308 |
| 8000 | 153 |
| 12000 | 39 |
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:
| ID | NAME | HIREDATE | SALARY |
| 1 | Rebecca | 2005-03-11 | 7000 |
| 2 | Ashley | 2008-03-16 | 11000 |
| 3 | Rachel | 2010-12-01 | 9000 |
| 4 | Emily | 2006-08-15 | 7000 |
| 5 | Ashley | 2004-07-30 | 16000 |
| … | … | … | … |
【SPL script】
| A | B | |
| 1 | =connect("db") | /Connect to database |
| 2 | =A1.query("select * from EMPLOYEE") | /Query EMPLOYEEtable |
| 3 | [0,10,20] | /Define intervals of hire durations |
| 4 | =A2.align@a(A3.len(),A3.pseg(year(now())-~,year(HIREDATE))) | / A.pseg(y) function gets the interval number that a hire date corresponds to |
| 5 | =A4.new(A3(#):EntryYears,~.avg(SALARY):AvgSalary) | /Calculate average salary in each group |
A5’s result:
| EntryYears | AvgSalary |
| 0 | 6807.69 |
| 10 | 7417.78 |
| 20 | 7324.32 |
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:
| ID | NAME | BIRTHDAY | HIREDATE |
| 1 | Rebecca | 1974-11-20 | 2005-03-11 |
| 2 | Ashley | 1980-07-19 | 2008-03-16 |
| 3 | Rachel | 1970-12-17 | 2010-12-01 |
| 4 | Emily | 1985-03-07 | 2006-08-15 |
| 5 | Ashley | 1975-05-13 | 2004-07-30 |
| … | … | … | … |
【SPL script】
| A | B | |
| 1 | =connect("db") | /Connect to database |
| 2 | =A1.query("select * from EMPLOYEE order by HIREDATE") | /Query EMPLOYEEtable and sort it by hire date |
| 3 | =A2.psort(BIRTHDAY) | / A.psort()function gets the sequence numbersof employees’birthdays before they are sorted |
| 4 | =A2(A3.to(3).sort()) | /Get the first three EMPLOYEErecords according to the sequence number of birthdays |
A4’s result:
| ID | NAME | BIRTHDAY | HIREDATE |
| 296 | Olivia | 1968-11-05 | 2006-11-01 |
| 440 | Nicholas | 1968-11-24 | 2008-07-01 |
| 444 | Alexis | 1968-11-12 | 2010-12-01 |
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:
| ID | TITLE | Author | Label |
| 1 | Easy analysis of Excel | 2 | Excel,ETL,Import,Export |
| 2 | Early commute: Easy to pivot excel | 3 | Excel,Pivot,Python |
| 3 | Initial experience of SPL | 1 | Basics,Introduction |
| 4 | Talking about set and reference | 4 | Set,Reference,Dispersed,SQL |
| 5 | Early commute: Better weapon than Python | 4 | Python,Contrast,Install |
| … | … | … | … |
【SPL script】
| A | B | |
| 1 | =connect("db") | /Connect to database |
| 2 | =A1.query("select * from PostRecord") | /Query PostRecord table |
| 3 | =A2.conj(Label.split(",")).id() | /Split each Label value by comma and concatenate all values to get the unique labels |
| 4 | =A2.align@ar(A3.len(),A3.pos(Label.split(","))) | / A.pos()function finds the set of sequence numbers of all labels and then align@r() function groups PostRecordrecords by those sequencenumbers |
| 5 | =A4.new(A3(#):Label,~.count():Count).sort@z(Count) | /Count the frequencies of each label and sort the result in descending order |
A5’s result:
| Label | Count |
| SPL | 7 |
| SQL | 6 |
| Basics | 5 |
| … | … |
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:
| Country | Language |
| China | Chinese |
| UK | English |
| Singapore | English |
| Singapore | Malay |
| Singapore | Chinese |
| Singapore | Tamil |
| Malaysia | Malay |
| … | … |
【SPL script】
| A | B | |
| 1 | =connect("db") | /Connect to database |
| 2 | =A1.query("select * from Language") | /Query Languagetable |
| 3 | =A2.group(Country) | /Group records by country |
| 4 | =A3.select(~.(Language).contain("Chinese","English")) | / A.contain()function checks whether the current country uses both Chinese and English as theirofficial languages |
| 5 | =A4.(Country) | /Get the eligible country |
A5’s result:
| Member |
| Singapore |
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】
| A | B | |
| 1 | =connect("db") | /Connect to database |
| 2 | =A1.query("select * from Category").keys(ID) | /Query Categorytable and set ID as the primary key |
| 3 | =A1.query("select * from Product") | /Query Producttable |
| 4 | =A3.select(A2.pfind(CategoryID)==0) | / A.pfind()function gets sequencenumbersof records from Categorytable whose primary key values are the same as CategoryID values inProducttable; the result of 0 means thatthe current category ID doesn’t exist in Categorytable. And then get product records whose category IDs are not included by Categorytable |
A4’s result:
| ID | Name | CategoryID | … |
| 12 | German cheese | … | |
| 26 | Spun sugar | 9 | … |
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:
| Date | Open | Close | Amount |
| 2019/12/31 | 3036.3858 | 3050.124 | 2.27E11 |
| 2019/12/30 | 2998.1689 | 3040.0239 | 2.67E11 |
| 2019/12/27 | 3006.8517 | 3005.0355 | 2.58E11 |
| 2019/12/26 | 2981.2485 | 3007.3546 | 1.96E11 |
| 2019/12/25 | 2980.4276 | 2981.8805 | 1.9E11 |
| … | … | … | … |
【SPL script】
| A | B | |
| 1 | =file("000001.csv").import@ct() | /Import the source data file |
| 2 | =A1.select(year(Date)==2019) | /Get stock records of 2019 |
| 3 | =A2.ptop(-3, Close) | / A.ptop()gets the sequence numbers of 3 records when the closing prices stay the highest; -3 means getting top 3 in descending order, and a positive integer will get the specifictop N in ascending order |
| 4 | =A3.run(~=A2(~).Amount/A2(~+1).Amount-1) | /Calculate growth rate circularly by dividing the current amountby that of the prior day |
A3’s result:
| Member |
| 154 |
| 156 |
| 157 |
A4’s result:
| Member |
| -0.0278 |
| -0.0139 |
| 0.0112 |
There are more examples in SPL CookBook.
