2.2 Using variables

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

‘#’ 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

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