1.1 Simple set
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
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
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
