6.2 选出运算
6.2.1 简单成员选出
1. 偶数成员
2. 偶数位置的成员
3. 大于 30 的成员
4. 比前 1 值大的成员
5. 局部极大值 (等于之前 1 个值、当前值、之后 1 个值最大值的成员)
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. 偶数成员
SELECT COLUMN_VALUE AS value
FROM TABLE(SYS.ODCINUMBERLIST(13, 30, 45, 23, 42, 98, 61))
WHERE MOD(COLUMN_VALUE,2)=0;
2. 偶数位置的成员
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. 大于 30 的成员
SELECT COLUMN_VALUE AS value
FROM TABLE(SYS.ODCINUMBERLIST(13, 30, 45, 23, 42, 98, 61))
WHERE COLUMN_VALUE>30;
4. 比前一个成员大的成员
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. 局部极大值 (等于之前 1 个值、当前值、之后 1 个值最大值的成员)
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]
相较于原生 list 和 pandas 的 Series，numpy 的 ndarray 做的好。
6.2.2 记录选出
1. 位置选出
2. 条件选出
3. 选出非空值记录
SPL
| A | B | |
|---|---|---|
| 1 | =file(“EMPLOYEE.csv”).import@tc() | |
| 2 | =A1.select(#%2==0) | / 偶数位置成员 |
| 3 | =A1.select(GENDER==“F”&&DEPT==“Sales”) | / 女销售 |
| 4 | =A1.select(~.array().pos(null)>0) | / 不含空值的成员 |
SQL
1. 位置选出
SELECT * FROM (
SELECT e.*, ROWNUM AS rn
FROM EMPLOYEE e)
WHERE MOD(rn, 2) = 0;
2. 条件选出
SELECT * FROM EMPLOYEE
WHERE GENDER='F' AND DEPT='Sales';
3. 选出非空值记录
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] #偶数位置成员
female_sales_members = df[(df['GENDER'] == 'F')
& (df['DEPT'] == 'Sales')] #女销售
non_null_members = df.dropna() #/不含空值的成员
