2.3 序号引用
2.3.1 单序列
现有一个序列，它的权重是其在序列位置的序号，请计算这一序列的加权平均值。
SPL
| A | B | |
|---|---|---|
| 1 | [6,7,1,0,1,5,2,4,8,4] | |
| 2 | =A1.sum(~*#)/A1.sum(#) | /3.9272727272727272 |
“#”表示循环时当前成员的序号，~ 表示当前成员。
SQL
WITH data AS (
SELECT column_value as value, ROWNUM as seq_number
FROM TABLE(sys.odcinumberlist(6,7,1,0,1,5,2,4,8,4)))
SELECT SUM(value * seq_number) / SUM(seq_number) AS weighted_average
FROM data;
SQL 要生成一个带序号的表，才能完成计算。
Python
sequence = [6,7,1,0,1,5,2,4,8,4]
s = pd.Series(sequence)
result = ((s.index+1)*s).sum()/(s.index.values+1).sum() # 3.9272727272727272
Python 的 Series 自带索引，利用它可以完成序号和成员之间的乘积运算，也可以完成序号本身的求和计算。
2.3.2 多序列
有两个序列，要求序号是奇数时两序列相加，序号是偶数时，两序列相减。
SPL
| A | B | |
|---|---|---|
| 1 | [5,8,4,3,2,8,0,3,5,5] | |
| 2 | [2,7,2,3,3,6,9,0,1,6] | |
| 3 | =A1.(if(#%2==1,+A2(#),-A2(#))) | /[7,1,6,0,5,2,9,3,6,-1] |
SQL
WITH sequence1 AS (
SELECT COLUMN_VALUE AS value,ROWNUM AS seq
FROM TABLE(SYS.ODCINUMBERLIST(5,8,4,3,2,8,0,3,5,5))),
sequence2 AS (
SELECT COLUMN_VALUE AS value,ROWNUM AS seq
FROM TABLE(SYS.ODCINUMBERLIST(2,7,2,3,3,6,9,0,1,6)))
SELECT
CASE
WHEN MOD(s1.seq, 2) = 1 THEN s1.value + s2.value
WHEN MOD(s1.seq, 2) = 0 THEN s1.value - s2.value
END AS result
FROM sequence1 s1
JOIN sequence2 s2 ON s1.seq = s2.seq;
Python
seq1 = [5, 8, 4, 3, 2, 8, 0, 3, 5, 5]
seq2 = [2, 7, 2, 3, 3, 6, 9, 0, 1, 6]
s1 = pd.Series(seq1)
s2 = pd.Series(seq2)
result = s1.add(s2).where(s1.index % 2 == 0, s1.sub(s2)).tolist() #[7,1,6,0,5,2,9,3,6,-1]
