Example codes for comparing SPL, SQL, and Python

---

1.1.1 Generic set constants

1. The set of numbers

2. The set of strings

3. The set of sets

4. The set of three-layer sets

SPL

	A
1	[1,3,5,7,9]
2	[“S”,“P”,“L”,“is”,“powerful”]
3	[[1,2,3],[4],[5,6,7,8]]
4	[[[1,2,3],[4],[5,6,7,8]],[[3,4,5],[6,7],[8,9]]]

SQL

Constants in SQL are always presented in the form of table:

1.

ID
----------
1
3
5
7
9

2.

STR
---------
S
P
L
is
powerful
l

3.

LIST_VALUES
--------------------
NUMBER_LIST_TYPE(1, 2, 3)
NUMBER_LIST_TYPE(4)
NUMBER_LIST_TYPE(5, 6, 7, 8)

4.

LIST_2D
------------
LISTS2D_TYPE(NUMBER_LIST_TYPE(1,2,3),
NUMBER_LIST_TYPE(4),
NUMBER_LIST_TYPE(5,6,7,8))
LISTS2D_TYPE(NUMBER_LIST_TYPE(3, 4, 5), 
NUMBER_LIST_TYPE(6, 7),
NUMBER_LIST_TYPE(8,9))

It can be seen that the storage method of SQL is a bit complicated.

Python

1) Native list

a = [1,3,5,7,9]
b = ["S","P","L","is","powerful"]
c = [[1,2,3],[4],[5,6,7,8]]
d = [[[1,2,3],[4],[5,6,7,8]],[[3,4,5],[6,7],[8,9]]]

2) The ndarray of numpy library

a = np.array([1,3,5,7,9])
b1 = np.array(["S","P","L","is","powerful"])
c1 = np.array([[1,2,3],[4],[5,6,7,8]],dtype=object)
d1 = np.array([[[1,2,3],[4],[5,6,7,8]],[[3,4,5],[6,7],[8,9]]],dtype=object)

3) Series of pandas library

a2 = pd.Series([1,3,5,7,9])
b2 = pd.Series(["S","P","L","is","powerful"])
c2 = pd.Series([[1,2,3],[4],[5,6,7,8]])
d2 = pd.Series([[[1,2,3],[4],[5,6,7,8]],[[3,4,5],[6,7],[8,9]]])

1.1.2 Set composition

1. Concatenate set and a single value into a new set.

2. Concatenate sets into a new set.

SPL

	A	B
1	[1,2,3]
2	4
3	[4,5]
4	=[A1,A2]	/[[1,2,3],4]
5	=[A1,A3]	/[[1,2,3],[4,5]]

SQL

SQL is usually a language used to handle database operations and is not suitable for direct operation on array.

Python

l1 = [1,2,3]
a = 4
l2 = [4,5]
l3 = [l1,a] 			#[[1, 2, 3], 4]
l4 = [l1,l2] 			#[[1, 2, 3], [4, 5]]

1.1.3 Retrieve member

1. Take the 3rd member

2. Take the 2nd, 6th, and 5th members

3. Take the 2nd to 4th members

4. Take even-positioned members

5. Take the last element

6. Take the 1st and 3rd members, the 5th to 7th members, and the second-to-last member.

SPL

	A	B
1	[2,3,10,8,5,4,9,5,9,1]
2	=A1(3)	/10
3	=A1([2,6,5])	/[3,4,5]
4	=A1.to(2,4)	/[3,10,8]
5	=A1.step(2,2)	/[3,8,4,5,1]
6	=A1.m(-1)	/1
7	=A1.m([1,3],5:7,-2)	/[2,10,5,4,9,9]

SQL

1. Take the 3rd member

SELECT element
FROM (SELECT element, ROWNUM rnum
  	  FROM (SELECT column_value AS element
    			FROM TABLE(sys.odcinumberlist(2,3,10,8,5,4,9,5,9,1))))
WHERE rnum = 3;

2. Take the 2nd, 6th, and 5th members

SELECT element
FROM (SELECT element, ROWNUM rnum
  	  FROM (SELECT column_value AS element
    			FROM TABLE(sys.odcinumberlist(2,3,10,8,5,4,9,5,9,1))))
WHERE rnum IN (2, 6, 5) 
ORDER BY CASE rnum WHEN 2 THEN 1 WHEN 6 THEN 2 WHEN 5 THEN 3 END;

3. Take the 2nd to 4th members

SELECT element
FROM (SELECT element, ROWNUM rnum
FROM (SELECT column_value AS element
FROM TABLE(sys.odcinumberlist(2,3,10,8,5,4,9,5,9,1))))
WHERE rnum BETWEEN 2 AND 4;

4. Take even-positioned members

SELECT element
FROM (SELECT element, ROWNUM rnum
  	  FROM (SELECT column_value AS element
    			FROM TABLE(sys.odcinumberlist(2,3,10,8,5,4,9,5,9,1))))
WHERE MOD(rnum, 2) = 0;

5. Take the last element

SELECT element
FROM (SELECT element, ROWNUM rnum
  	  FROM (SELECT column_value AS element
    			FROM TABLE(sys.odcinumberlist(2,3,10,8,5,4,9,5,9,1))))
WHERE rnum = (SELECT MAX(rnum)
  			 FROM (SELECT ROWNUM rnum
    				   FROM (SELECT column_value
      					 FROM TABLE(sys.odcinumberlist(2,3,10,8,5,4,9,5,9,1)))));

6. Take the 1st and 3rd members, the 5th to 7th members, and the second-to-last member.

SELECT element
FROM (SELECT element, ROWNUM rnum
FROM (SELECT column_value AS element
    			FROM TABLE(sys.odcinumberlist(2,3,10,8,5,4,9,5,9,1))))
WHERE rnum IN (1, 3)
OR (rnum BETWEEN 5 AND 7)
OR rnum=(SELECT MAX(rnum)-1
FROM (SELECT element, ROWNUM rnum
           		  FROM (SELECT column_value AS element
                  		FROM TABLE(sys.odcinumberlist(2,3,10,8,5,4,9,5,9,1)))));

Python

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

array = np.array([2, 3, 10, 8, 5, 4, 9, 5, 9, 1])
result1 = array[2] 						#10
result2 = array[[1, 5, 4]] 					#[3,4,5]
result3 = array[1:4]					 	#[3,10,8]
result4 = array[1::2] 						#[3,8,4,5,1]
result5 = array[-1] 						#1
result6 = array[[0, 2, *range(4, 7), -2]] 			#[2,10,5,4,9,9]

1.1.4 Comparison of sets

SPL

	A	B
1	=[5,2,1]<[5,2,1,2]	/true
2	=[5,2,1,1]<[5,2,1,2]	/true
3	=[5,2,1,3]>[5,2,1,2]	/true
4	=[5,3,1,1]>[5,2,1,2]	/true
5	=[5,2,1,2]==[5,2,1,2]	/true
6	=[1,2,5,2]!=[5,2,1,2]	/true

SQL

SQL is not good at comparing such sequence.

Python

print([5,2,1]<[5,2,1,2]) 		#True
print([5,2,1,1]<[5,2,1,2])		#True
print([5,2,1,3]>[5,2,1,2])		#True
print([5,3,1,1]>[5,2,1,2]) 		#True
print([5,2,1,2]==[5,2,1,2]) 		#True
print([1,2,5,2]!=[5,2,1,2]) 		#True

1.1.5 Set operations

1. Intersection

2. Difference

3. Union

4. Union All

SPL

	A	B	C
1	[2,5,1,3,3]
2	[3,6,4,2]
3	=A1^A2	=[A1,A2].isect()	/[2,3]
4	=A1\A2	=[A1,A2].diff()	/[5,1,3]
5	=A1&A2	=[A1,A2].union()	/[2,5,1,3,3,6,4]
6	=A1|A2	=[A1,A2].conj()	/[2,5,1,3,3,3,6,4,2]

SQL

1. Intersection

WITH set1 AS (
  SELECT COLUMN_VALUE AS element
  FROM TABLE(SYS.ODCINUMBERLIST(2,5,1,3,3))), 
set2 AS (
  SELECT COLUMN_VALUE AS element
  FROM TABLE(SYS.ODCINUMBERLIST(3,6,4,2)))
SELECT element FROM set1
INTERSECT 
SELECT element FROM set2;

2. Difference

WITH set1 AS (
  SELECT COLUMN_VALUE AS element
  FROM TABLE(SYS.ODCINUMBERLIST(2,5,1,3,3))), 
set2 AS (
  SELECT COLUMN_VALUE AS element
  FROM TABLE(SYS.ODCINUMBERLIST(3,6,4,2)))
SELECT element FROM set1
MINUS
SELECT element FROM set2;

3. Union

WITH set1 AS (
  SELECT COLUMN_VALUE AS element
  FROM TABLE(SYS.ODCINUMBERLIST(2,5,1,3,3))), 
set2 AS (
  SELECT COLUMN_VALUE AS element
  FROM TABLE(SYS.ODCINUMBERLIST(3,6,4,2)))
SELECT element FROM set1
UNION
SELECT element FROM set2;

4. Union All

WITH set1 AS (
  SELECT COLUMN_VALUE AS element
  FROM TABLE(SYS.ODCINUMBERLIST(2,5,1,3,3))), 
set2 AS (
  SELECT COLUMN_VALUE AS element
  FROM TABLE(SYS.ODCINUMBERLIST(3,6,4,2)))
SELECT element FROM set1
UNION ALL
SELECT element FROM set2;

SQL Performs set operations in mathematics, without considering duplicate elements.

Python

a = [2, 5, 1, 3, 3]
b = [3, 6, 4, 2, 3]
intersection = [x for x in a if x in b]
diff_a_b = [x for x in a if x not in b]
union = a + [x for x in b if x not in a]
sum_set = a + b

---

1.2 Structured data
Example codes for comparing SPL, SQL, and Python