6.1 Positioning operations

---

6.2.1 Select simple members

1. Even-numbered member

2. Even-positioned member

3. Member greater than 30

4. Member with a value greater than the previous one

5. Local maximum value member (the member with maximum value among the previous value, current value, and next value)

SPL

	A	B
1	[13,30,45,23,42,98,61]
2	=A1.select(~%2==0)	[30,42,98]
3	=A1.select(#%2==0)	[30,23,98]
4	=A1.select(~>30)	[45,42,98,61]
5	=A1.select(#!=1&&>[-1])	[30,45,42,98]
6	=lth=A1.len(),A1.select(#!=1&&#!=lth&&==[-1:1].max())	[45,98]

SQL

1. Even-numbered member

SELECT COLUMN_VALUE AS value
FROM TABLE(SYS.ODCINUMBERLIST(13, 30, 45, 23, 42, 98, 61))
WHERE MOD(COLUMN_VALUE,2)=0;

2. Even-positioned member

SELECT value FROM (
    SELECT value, ROWNUM AS rn
    FROM (
        SELECT COLUMN_VALUE AS value
        FROM TABLE(SYS.ODCINUMBERLIST(13, 30, 45, 23, 42, 98, 61))))
WHERE MOD(rn, 2) = 0; 

3. Member greater than 30

SELECT COLUMN_VALUE AS value
FROM TABLE(SYS.ODCINUMBERLIST(13, 30, 45, 23, 42, 98, 61))
WHERE COLUMN_VALUE>30;

4. Member with a value greater than the previous one

SELECT value FROM (
    SELECT value, LAG(value) OVER (ORDER BY 1) AS prev_value
    FROM (
        SELECT COLUMN_VALUE AS value
        FROM TABLE(SYS.ODCINUMBERLIST(13, 30, 45, 23, 42, 98, 61))))
WHERE prev_value IS NOT NULL AND value > prev_value; 

5. Local maximum value member (the member with maximum value among the previous value, current value, and next value)

SELECT value FROM (
    SELECT value, 
        LAG(value) OVER (ORDER BY 1) AS prev_value, 
        LEAD(value) OVER (ORDER BY 1) AS next_value
    FROM (
        SELECT COLUMN_VALUE AS value
        FROM TABLE(SYS.ODCINUMBERLIST(13, 30, 45, 23, 42, 98, 61))))
WHERE prev_value IS NOT NULL AND value >= prev_value
AND next_value IS NOT NULL AND value > next_value;

Python

sequence = np.array([13, 30, 45, 23, 42, 98, 61]) 
even_members = sequence[sequence % 2 == 0]                  #[30 42 98]
even_position_members = sequence[1::2]                      #[30 23 98]
greater_than_30_members = sequence[sequence > 30]           #[45 42 98 61]
larger_than_previous_members = sequence[1:][sequence[1:] > sequence[:-1]]       #[30 45 42 98]
extreme_members = np.extract((sequence[1:-1] > sequence[:-2]) & 
    (sequence[1:-1] > sequence[2:]), sequence[1:-1])        #[45 98]

Compared to the native list and pandas’s Series, numpy’s ndarray works better.

6.2.2 Select records

1. Select by position

2. Select by condition

3. Select non-null records

SPL

	A	B
1	=file(“EMPLOYEE.csv”).import@tc()
2	=A1.select(#%2==0)	/Even-positioned members
3	=A1.select(GENDER==“F”&&DEPT==“Sales”)	/Female sales
4	=A1.select(~.array().pos(null)>0)	/Non-null members

SQL

1. Select by position

SELECT * FROM (
    SELECT e.*, ROWNUM AS rn
    FROM EMPLOYEE e)
WHERE MOD(rn, 2) = 0;

2. Select by condition

SELECT * FROM EMPLOYEE
WHERE GENDER='F' AND DEPT='Sales';

3. Select non-null records

SELECT * FROM EMPLOYEE
WHERE EID IS NOT NULL
AND NAME IS NOT NULL
AND SURNAME IS NOT NULL
AND GENDER IS NOT NULL
AND STATE IS NOT NULL
AND BIRTHDAY IS NOT NULL
AND HIREDATE IS NOT NULL
AND DEPT IS NOT NULL
AND SALARY IS NOT NULL;

Python

df = pd.read_csv('../EMPLOYEE.csv')
even_members = df.iloc[1::2]                        #Even-positioned members
female_sales_members = df[(df['GENDER'] == 'F')
                 & (df['DEPT'] == 'Sales')]         #Female sales
non_null_members = df.dropna()                      #Non-null members

---

6.3 Positioning calculation
Example codes for comparing SPL, SQL, and Python