5.3 Iteration in groups

---

6.1.1 Positioning by member

1. Find the position of 5 in the sequence.

2. Determine whether 5 is in the sequence.

SPL

	A	B
1	[2,9,7,7,2,2,9,3,3,5]
2	=A1.pos(5)	/10
3	=A1.pos(5)>0	/true

SQL

1. Find the position of 5 in the sequence

SELECT position FROM (
    SELECT COLUMN_VALUE AS num, ROWNUM position
    FROM TABLE(SYS.ODCINUMBERLIST(2,9,7,7,2,2,9,3,3,5)))
WHERE num = 5;

2. Determine whether 5 is in the sequence

SELECT SIGN(COUNT(*)) AS result 
FROM TABLE(SYS.ODCINUMBERLIST(2,9,7,7,2,2,9,3,3,5))
WHERE COLUMN_VALUE = 5;

Python

sequence = np.array([2, 9, 7, 7, 2, 2, 9, 3, 3, 5])
indices = np.where(sequence == 5)[0] #9
is_present = len(np.where(sequence == 5)[0])!=0 #True

6.1.2 Positioning by segment

Position the segment a specified date belongs to.

SPL

	A	B
1	[1970-01-01,1980-01-01,1990-01-01,2000-01-01,2010-01-01,2020-01-01]
2	1965-02-03
3	1979-12-31
4	1980-01-01
5	2005-08-09
6	2023-03-23
7	=A1.pseg(A2)	/0
8	=A1.pseg(A3)	/1
9	=A1.pseg(A4)	/2
10	=A1.pseg(A5)	/4
11	=A1.pseg(A6)	/6
12	=[A2:A6].(A1.pseg(~))	/[0,1,2,4,6]

Neither pos()nor pseg() is a loop function, so in this example, the ‘~’ in A12 is the member of [A2:A6].

SQL

SQL doesn’t provide a particularly convenient method.

Python

from datetime import datetime
import bisect
date_list = ["1970-01-01","1980-01-01","1990-01-01","2000-01-01","2010-01-01","2020-01-01"]
date_list = [datetime.strptime(date, "%Y-%m-%d") for date in date_list]
date1 = datetime.strptime("1965-02-03", "%Y-%m-%d")
index1 = bisect.bisect_right(date_list, date1) #0
date2 = datetime.strptime("1979-12-31", "%Y-%m-%d")
index2 = bisect.bisect_right(date_list, date2) #1
date3 = datetime.strptime("1980-01-01", "%Y-%m-%d")
index3 = bisect.bisect_right(date_list, date3) #2
date4 = datetime.strptime("2005-08-09", "%Y-%m-%d")
index4 = bisect.bisect_right(date_list, date4) #4
date5 = datetime.strptime("2023-03-23", "%Y-%m-%d")
index5 = bisect.bisect_right(date_list, date5) #6

6.1.3 Positioning by condition

Find the position of the sales department employees in the employee table.

SPL

	A
1	=file(“EMPLOYEE.csv”).import@tc()
2	=A1.pselect@a(DEPT==“Sales”)

The pselect() will only select the position of the first member that meets condition. Adding the @a option will select the positions of all members that meet condition.

SQL

SELECT rn FROM (
    SELECT EMPLOYEE.*, ROWNUM AS rn FROM EMPLOYEE) 
WHERE DEPT = 'Sales';

Python

df = pd.read_csv('../EMPLOYEE.csv')
sales_employees_index = np.where(df['DEPT'] == 'Sales')[0]

6.1.4 Ordinary aggregation and positioning

Find the position of the highest-paid employee in the employee table.

SPL

	A	B
1	=file(“EMPLOYEE.csv”).import@tc()
2	=A1.pmax(SALARY)	/Position of one member
3	=A1.pmax@a(SALARY)	/Position of all members

SQL

1. Position of one member

SELECT rn FROM (
    SELECT EMPLOYEE.*, ROWNUM AS rn
    FROM EMPLOYEE
    ORDER BY SALARY DESC)
FETCH FIRST 1 ROWS ONLY;

2. Position of all members

SELECT rn FROM (
    SELECT EMPLOYEE.*, ROWNUM AS rn
    FROM EMPLOYEE)
WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE);

Python

df = pd.read_csv('../EMPLOYEE.csv')
#Position of one member
highest_salary_employee_index_first = np.argmax(df['SALARY'])
#Position of all members
highest_salary_employee_index_all = np.where(df['SALARY'] == df['SALARY'].max())[0]

Python adopts totally different methods to find the position of one member and the position of multiple members. When searching for the position of multiple members, Python needs to traverse twice, one is to calculate the maximum value and the other is to find the index.

6.1.5 Set aggregation and positioning

Find the position of the 10 highest-paid employees in the employee table.

SPL

	A
1	=file(“EMPLOYEE.csv”).import@tc()
2	=A1.ptop(-10,SALARY)

SQL

SELECT rn FROM (
    SELECT EMPLOYEE.*, ROWNUM AS rn
    FROM EMPLOYEE
    ORDER BY SALARY DESC)
FETCH FIRST 10 ROWS ONLY;

Python

df = pd.read_csv('../EMPLOYEE.csv')
top_10_positions = np.argsort(-df['SALARY'])[:10]

Pandas adopts the method of sorting and then taking the top 10 members.

---

6.2 Selection operation
Example codes for comparing SPL, SQL, and Python