开封
乾学院 6 号会员
86 浏览  • 
     ...

1.1 简单集合

 
spl sql python代码示例对比(31)

SPL SQL Python 代码示例对比

1.1.1 泛型集合常数

1. 数值集合
2. 字符串集合
3. 集合的集合
4. 三层集合的集合

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

常数在 SQL 中都是以表的形式出现:

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))

SQL 的存储方式有点复杂。

Python

1) 原生 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) numpy 库的 ndarray

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) pandas 库的 Series

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 集合构成

1. 集合与单值拼成新集合
2. 集合与集合拼成新集合

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 通常是用来处理数据库操作的语言，不适用于直接操作数组。

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 成员取出

1. 第 3 个成员
2. 第 2,6,5 个成员
3. 第 2 到 4 个的成员
4. 偶数位置成员
5. 最后一个成员
6. 第 1,3 个, 第 5 到 7 个, 倒数第 2 个成员

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. 第 3 个成员

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. 第 2、6、5 个成员

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. 第 2 到 4 个成员

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. 偶数位置成员

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. 最后一个成员

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. 第 1、3 个、第 5 到 7 个、倒数第 2 个成员

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

相较于原生 list 和 pandas 的 Series，numpy 的 ndarray 做的好。

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 集合比较

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 不擅长这类序列的比较。

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 集合运算

1. 交集
2. 差集
3. 并集
4. 和集

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. 交集

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. 差集

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. 并集

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. 和集

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 做的数学上的集合运算，不考虑重复元素。

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 结构化数据
SPL SQL Python 代码示例对比

spl sql python代码示例对比(31)