# 2.3 Sequence number reference

## 2.3.1 Single sequence

There is a sequence whose member’s weights are their positions in the origin sequence. The task is to calculate the weighted mean of this sequence.

### SPL

A | B | |
---|---|---|

1 | [6,7,1,0,1,5,2,4,8,4] | |

2 | =A1.sum(~*#)/A1.sum(#) | /3.9272727272727272 |

‘#’ represents the sequence number of current member when looping, and ‘~’ represents the current member.

### 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 needs to generate a table with sequence number to get the task done.

### 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’s Series comes with its own index, which can be used to perform the product operation between sequence number and member, as well as the sum operation of the sequence numbers themselves.

## 2.3.2 Multiple sequences

There are two sequences. It is required to add the two sequences when the sequence number is odd, and subtract them when the sequence number is even.

### 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]
```

2.4 Adjacent reference

Example codes for comparing SPL, SQL, and Python

*SPL Official Website**👉* *https://www.scudata.com*

*SPL Feedback and Help**👉* *https://www.reddit.com/r/esProc_SPL*

*SPL Learning Material**👉* *https://c.scudata.com*

*SPL Source Code and Package**👉* *https://github.com/SPLWare/esProc*

*Discord**👉* *https://discord.gg/cFTcUNs7*

*Youtube**👉* *https://www.youtube.com/@esProc_SPL*