# Locate Operation on Ordered Sets

【Abstract】

Sometimes it’s a record’s position in an ordered sets that we are more concerned with in order to accomplish the computing goal. For instance, we want to find which row the order whose ID is 752084 settles in the orders table, or which trading date when SSE Composite Index has the highest closing price in the year of 2019. Here we have the efficient and fast ways to locate records and provide sample scripts in esProc SPL.

# 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 1In 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). Heres 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 Teachers table, 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

A5s 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 2Based on the following associated Sales table and Customer table, find the customers who didnt 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 Sales table 3 =A1.query("select * from  Customer") / Query Customer table 4 =A3.(ID).sort() / List customer IDs 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

A6s 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 3Based 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 closing price

A4s 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 4Based 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 closing prices increases by over 3% 4 =A3.new(A2(~).Date:Date,  A2(~).Amount/A2(~-1).Amount:'Amount increase') / Calculate growth rate of each day circularly

A3s result:

 Member 161 187 211

A4s 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 5Based 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 EMPLOYEE table 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 salary value corresponds to 5 =A4.new(A3 (#):SALARY,~.count():COUNT) / Count the employees in each interval

A5s 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 6Based 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 EMPLOYEE table 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

A5s 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 7Based 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 EMPLOYEE table and sort it by hire date 3 =A2.psort(BIRTHDAY) / A.psort() function gets the sequence numbers of employees’ birthdays before they are sorted 4 =A2(A3.to(3).sort()) / Get the first three EMPLOYEE records according to the sequence number of birthdays

A4s 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 doesnt contain f.

Example 8Based 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 PostRecord records by those sequence numbers 5 =A4.new(A3(#):Label,~.count():Count).sort@z(Count) / Count the frequencies of each label and sort the result in descending order

A5s result:

 Label Count SPL 7 SQL 6 Basics 5 … …

# 7. Set membership test

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

Example 9The 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 Language table 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 their official languages 5 =A4.(Country) / Get the eligible country

A5s result:

 Member Singapore

# 8. Location by primary key

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

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

SPL script

 A B 1 =connect("db") /Connect to database 2 =A1.query("select * from  Category").keys(ID) / Query Category table and set ID as the primary key 3 =A1.query("select * from Product") /Query Product table 4 =A3.select(A2.pfind(CategoryID)==0) / A.pfind() function gets sequence numbers of records from Category table whose primary key values are the same as CategoryID values in Product table; the result of 0 means that the current category ID doesn’t exist in Category table. And then get product records whose category IDs are not included by Category table

A4s 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. Lets look at how to get sequence numbers of the top/bottom N members.

Example 11The 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 specific top N in ascending order 4 =A3.run(~=A2(~).Amount/A2(~+1).Amount-1) / Calculate growth rate circularly by dividing the current amount by that of the prior day

A3s result:

 Member 154 156 157

A4s result:

 Member -0.0278 -0.0139 0.0112

There are more examples in SPL CookBook.