SQL 和 SPL 的集合运算对比
【摘要】
集合运算是指集合间的和、交、并、差、异或、包含等基本运算。SQL 和 SPL 是大家比较熟悉的程序语言，本文将探讨对于集合运算问题，这两种语言的解决方案和基本原理。如何简便快捷的处理集合运算，这里为你全程解析，并提供 SQL 和 SPL 示例代码。结构化数据经常是以集合形式出现的，为了方便地计算这类数据，SQL 和 SPL 都提供了较丰富的集合运算。接下来我们通过一些例子来看一下，SQL 和 SPL 是如何解决集合运算问题的。SQL 和 SPL 的集合运算对比
1. 合集
集合 A 和 B 的合集是由所有属于集合 A 或属于集合 B 的成员所组成的集合，包括重复成员。
【例 1】 某商家有网店和实体店铺两种销售渠道，其销售数据分别存储在 SALES_ONLINE 和 SALES_STORE 两个表中。现在要查询 2014 年每种产品的总销售额。部分数据如下：
SALES_ONLINE：
ID |
CUSTOMERID |
ORDERDATE |
PRODUCTID |
AMOUNT |
1 |
HANAR |
2014/01/01 |
17 |
3868.6 |
2 |
ERNSH |
2014/01/03 |
42 |
1005.9 |
3 |
LINOD |
2014/01/06 |
64 |
400.0 |
4 |
OTTIK |
2014/01/07 |
10 |
1194.0 |
5 |
OCEAN |
2014/01/09 |
41 |
319.2 |
… |
… |
… |
… |
… |
SALES_STORE：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
1 |
EASTC |
2014/01/01 |
1 |
27 |
3063.0 |
2 |
ERNSH |
2014/01/02 |
8 |
70 |
2713.5 |
3 |
MAGAA |
2014/01/03 |
2 |
74 |
1675.0 |
4 |
SAVEA |
2014/01/07 |
7 |
45 |
2018.2 |
5 |
FOLIG |
2014/01/08 |
8 |
30 |
1622.4 |
… |
… |
… |
… |
… |
… |
两个表的数据结构并不完全相同，比如 SALES_STORE 有字段 SELLERID 用于存储销售人员。但是都有相同的订单日期、产品 ID、销售额等字段。
SQL的解决方案：
select
PRODUCTID, sum(AMOUNT) AMOUNT
from
(select PRODUCTID, AMOUNT
from SALES_ONLINE
where extract (year from ORDERDATE)=2014
union all
select PRODUCTID, AMOUNT
from SALES_STORE
where extract (year from ORDERDATE)=2014)
group by PRODUCTID
order by PRODUCTID
在 SQL 语句中 UNION 操作符用于合并两个或多个子结果集。UNION ALL 时会合并所有的记录，包括重复记录。UNION 合并的子结果集必须拥有相同的数据结构（相同数量的列）, 列也必须拥有相同的数据类型。
这里是以 ORACLE 为例，ORACLE 没有函数 YEAR，可以使用 extract (year from date) 来实现。
SPL的解决方案：
SPL中可以使用符号“|”来计算集合的合集。比如 A|B 就是集合 A 与集合 B 的合集。
A |
|
1 |
=T("SalesOnline.csv").select(year(ORDERDATE)==2014) |
2 |
=T("SalesStore.csv").select(year(ORDERDATE)==2014) |
3 |
=A1|A2 |
4 |
=A3.groups(PRODUCTID; sum(AMOUNT):AMOUNT) |
A1：从文件中导入线上销售表，并选出 2014 年的销售记录。
A2：从文件中导入实体店销售表，并选出 2014 年的销售记录。
A3：使用符号“|”计算两个集合的合集。
A4：分组汇总每种商品的总销售额。
值得注意的是，SPL 计算合集时并不要求集合有相同的数据结构，这是因为 SPL 的序列支持异构的成员。当我们想访问序列成员的共有字段产品 ID 和销售额时，又可以像普通数据表一样直接访问，对于使用者来说非常方便。
SPL同样也支持从数据库中读取数据表，比如 A1 可以改为：
A |
|
1 |
=connect("db").query("select * from SALES_STORE where extract (year from ORDERDATE)=2014") |
【例 2】 根据成绩表，查询数学前 4 名、英语前 3 名和体育前 2 名的成绩。部分数据如下：
CLASS |
STUDENTID |
SUBJECT |
SCORE |
1 |
1 |
English |
84 |
1 |
1 |
Math |
77 |
1 |
1 |
PE |
69 |
1 |
2 |
English |
81 |
1 |
2 |
Math |
80 |
… |
… |
… |
… |
SQL的解决方案：
SQL并没有提供多个集合求合集的方法，多个集合时还是使用 UNION ALL 操作符求合集。这里以 ORACLE 为例，先定义一个公开表，使用 ROW_NUMBER() OVER 计算各科按分数的降序排名，再取各科的前 N 名：
with cte1 as
(select
CLASS, STUDENTID, SUBJECT, SCORE,
ROW_NUMBER() OVER(PARTITION BY SUBJECT
ORDER BY SCORE DESC) grouprank
from SCORES)
select CLASS, STUDENTID, SUBJECT, SCORE
from cte1
where grouprank <= 4 and SUBJECT='Math'
UNION ALL
select CLASS, STUDENTID, SUBJECT, SCORE
from cte1
where grouprank <= 3 and SUBJECT='English'
UNION ALL
select CLASS, STUDENTID, SUBJECT, SCORE
from cte1
where grouprank <= 2 and SUBJECT='PE'
SPL的解决方案：
SPL提供了 A.conj() 函数，当 A 是集合的集合时，用于计算多个成员集合的合集。
A |
|
1 |
=T("Scores.csv") |
2 |
[Math,English,PE] |
3 |
[4,3,2] |
4 |
=A3.conj(A1.select(SUBJECT==A2(A3.#)).top(-~;SCORE)) |
A1：导入学生成绩。
A2：定义学科集合。
A3：定义与学科对应的名次集合。
A4：分别选出各学科的前 N 名，并使用函数 A.conj() 计算多个集合的合集。
SQL没有直接支持多个集合的合集运算，我们只能使用多个集合两两 UNION ALL 的办法，当集合数增加时会越来越复杂。而 SPL 则提供了函数 A.conj() 用于多个集合的合集运算，无论成员集合的数量有多少个，都可以用它来解决。
2. 交集
集合 A 和 B 的交集是指由所有属于集合 A 且属于集合 B 的成员所组成的集合。
【例 3】 某商家有网店和实体店铺两种销售渠道，其销售数据分别存储在 SALES_ONLINE 和 SALES_STORE 两个表中。现在要查询 2014 年在网店和实体店都有消费的客户有哪些。部分数据如下：
SALES_ONLINE：
ID |
CUSTOMERID |
ORDERDATE |
PRODUCTID |
AMOUNT |
1 |
HANAR |
2014/01/01 |
17 |
3868.6 |
2 |
ERNSH |
2014/01/03 |
42 |
1005.9 |
3 |
LINOD |
2014/01/06 |
64 |
400.0 |
4 |
OTTIK |
2014/01/07 |
10 |
1194.0 |
5 |
OCEAN |
2014/01/09 |
41 |
319.2 |
… |
… |
… |
… |
… |
SALES_STORE：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
1 |
EASTC |
2014/01/01 |
1 |
27 |
3063.0 |
2 |
ERNSH |
2014/01/02 |
8 |
70 |
2713.5 |
3 |
MAGAA |
2014/01/03 |
2 |
74 |
1675.0 |
4 |
SAVEA |
2014/01/07 |
7 |
45 |
2018.2 |
5 |
FOLIG |
2014/01/08 |
8 |
30 |
1622.4 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
在 SQL 语句中 INTERSECT 操作符用于计算两个或多个子结果集的交集。INTERSECT 的子结果集必须拥有相同的数据结构和相同数量的列, 列也必须拥有相似的数据类型。SQL 语句如下：
select
DISTINCT CUSTOMERID
from
SALES_ONLINE
where
extract (year from ORDERDATE)=2014
INTERSECT
select
DISTINCT CUSTOMERID
from
SALES_STORE
where
extract (year from ORDERDATE)=2014
早期的数据库，有可能不支持 INTERSECT 操作符。我们可以先求合集 (UNION ALL)，再通过分组计数进行过滤来实现：
select
CUSTOMERID, COUNT(*)
from
(select DISTINCT CUSTOMERID
from SALES_ONLINE
where extract (year from ORDERDATE)=2014
UNION ALL
select DISTINCT CUSTOMERID
from SALES_STORE
where extract (year from ORDERDATE)=2014)
group by
CUSTOMERID
HAVING
COUNT(*)>1
order by
CUSTOMERID
不支持 INTERSECT 操作符时，为了求交集额外的增加了一层分组汇总计算，让 SQL 语句变得十分复杂。
SPL的解决方案：
SPL中可以使用符号“^”来计算集合的交集。比如 A^B 就是集合 A 与集合 B 的交集。
A |
|
1 |
=T("SalesOnline.csv").select(year(ORDERDATE)==2014) |
2 |
=T("SalesStore.csv").select(year(ORDERDATE)==2014) |
3 |
=A1.id(CUSTOMERID)^A2.id(CUSTOMERID) |
A1：从文件中导入线上销售表，并选出 2014 年的销售记录。
A2：从文件中导入实体店销售表，并选出 2014 年的销售记录。
A3：使用函数 A.id() 计算客户 ID 的唯一值集合。再使用符号“^”计算两个集合的交集，即在网店和实体店都有消费的客户。
【例 4】 根据销售数据表，统计 2014 年每个月销售额都排前 10 名的客户名称。部分数据如下：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
10400 |
EASTC |
2014/01/01 |
1 |
27 |
3063.0 |
10401 |
HANAR |
2014/01/01 |
1 |
17 |
3868.6 |
10402 |
ERNSH |
2014/01/02 |
8 |
70 |
2713.5 |
10403 |
ERNSH |
2014/01/03 |
4 |
42 |
1005.9 |
10404 |
MAGAA |
2014/01/03 |
2 |
74 |
1675.0 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
with cte1 as
(select
extract (month from ORDERDATE) ORDERMONTH,
CUSTOMERID, SUM(AMOUNT) AMOUNT
from SALES
where extract (year from ORDERDATE)=2014
group by extract (month from ORDERDATE),CUSTOMERID
order by ORDERMONTH ASC, AMOUNT DESC),
cte2 as
(select
ORDERMONTH,CUSTOMERID,AMOUNT,
ROW_NUMBER()OVER(PARTITION BY ORDERMONTH ORDER BY AMOUNT DESC) grouprank
from cte1)
select
CUSTOMERID,count(*)
from cte2
where grouprank<=10
group by CUSTOMERID
having count(*)=12
SQL没有提供多个集合求交集的方法，多个集合时还是使用 INTERSECT 操作符求交集。取出每个月的前十名客户，再用 INTERSECT 操作符求合集显然不可取。这里使用了分组计数的方式，如果每月前十名的客户出现了 12 次，就说明客户在每个月都排入了前十名。
SPL的解决方案：
当 A 是由集合组成的集合时，SPL 提供了函数 A.isect() 计算所有成员集合的交集。
A |
|
1 |
=T("Sales.csv").select(year(ORDERDATE)==2014) |
2 |
=A1.group(month(ORDERDATE)) |
3 |
=A2.(~.groups(CUSTOMERID;sum(AMOUNT):AMOUNT)) |
4 |
=A3.(~.top(-10;AMOUNT).(CUSTOMERID)) |
5 |
=A4.isect() |
A1：从文件中导入销售表，并选出 2014 年的销售记录。
A2：销售表按月份分组。
A3：每月的销售记录再按客户分组。
A4：统计每月总销售额前十名的客户。
A5：使用函数 A.isect() 计算所有月份选出的客户的交集。
对于这个例子，尽管已经使用了取巧的方法来计算交集，SQL 语句仍然非常复杂。一方面原因是由于 SQL 语句没有提供计算多个集合的交集的方法。另外一方面原因，是由于 SQL 的分组子集无法保留，不能使用上次的分组结果继续进行分组等运算。分组这一部分暂时先放在一边，将来会单独整理出来讲解。
3. 并集
集合 A 和 B 的并集是由所有属于集合 A 或属于集合 B 的成员所组成的集合，但是不包括重复成员。
【例 5】 某商家有网店和实体店铺两种销售渠道，其销售数据分别存储在 SALES_ONLINE 和 SALES_STORE 两个表中。现在要查询 2014 年哪些产品在网店累计销售额超过 10000，或者在实体店销售次数大于 5 次。部分数据如下：
SALES_ONLINE：
ID |
CUSTOMERID |
ORDERDATE |
PRODUCTID |
AMOUNT |
1 |
HANAR |
2014/01/01 |
17 |
3868.6 |
2 |
ERNSH |
2014/01/03 |
42 |
1005.9 |
3 |
LINOD |
2014/01/06 |
64 |
400.0 |
4 |
OTTIK |
2014/01/07 |
10 |
1194.0 |
5 |
OCEAN |
2014/01/09 |
41 |
319.2 |
… |
… |
… |
… |
… |
SALES_STORE：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
1 |
EASTC |
2014/01/01 |
1 |
27 |
3063.0 |
2 |
ERNSH |
2014/01/02 |
8 |
70 |
2713.5 |
3 |
MAGAA |
2014/01/03 |
2 |
74 |
1675.0 |
4 |
SAVEA |
2014/01/07 |
7 |
45 |
2018.2 |
5 |
FOLIG |
2014/01/08 |
8 |
30 |
1622.4 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
select
PRODUCTID
from
(select PRODUCTID, sum(AMOUNT)
from SALES_ONLINE
where extract (year from ORDERDATE)=2014
group by PRODUCTID
having sum(AMOUNT)>10000)
union
select
PRODUCTID
from
(select PRODUCTID, count(*)
from SALES_STORE
where extract (year from ORDERDATE)=2014
group by PRODUCTID
having count(*)>5)
前面介绍过，在 SQL 语句中 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。当 UNION 单独使用时（没有 ALL），在合并记录时会去除重复记录。
SPL的解决方案：
SPL中可以使用符号“&”来计算集合的并集。比如 A&B 就是集合 A 与集合 B 的并集。
A |
|
1 |
=T("SalesOnline.csv").select(year(ORDERDATE)==2014) |
2 |
=T("SalesStore.csv").select(year(ORDERDATE)==2014) |
3 |
=A1.groups(PRODUCTID; sum(AMOUNT):AMOUNT).select(AMOUNT>10000) |
4 |
=A2.groups(PRODUCTID; count(~):COUNT).select(COUNT>5) |
5 |
=A3.(PRODUCTID)&A4.(PRODUCTID) |
A1：从文件中导入线上销售表，并选出 2014 年的销售记录。
A2：从文件中导入实体店销售表，并选出 2014 年的销售记录。
A3：分组汇总线上每种商品的总销售额，并选出大于 10000 的记录。
A4：分组汇总实体店每种商品的总销售次数，并选出超过 5 次的记录。
A5：使用符号“&”计算线上和实体店的产品集合的并集。
【例 6】 根据销售数据表，查询总销售次数排进过年度前 10 名的产品有哪些。部分数据如下：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
10400 |
EASTC |
2014/01/01 |
1 |
27 |
3063.0 |
10401 |
HANAR |
2014/01/01 |
1 |
17 |
3868.6 |
10402 |
ERNSH |
2014/01/02 |
8 |
70 |
2713.5 |
10403 |
ERNSH |
2014/01/03 |
4 |
42 |
1005.9 |
10404 |
MAGAA |
2014/01/03 |
2 |
74 |
1675.0 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
with cte1 as
(select
extract (year from ORDERDATE) ORDERYEAR,
PRODUCTID, COUNT(*) ORDERCOUNT
from SALES
group by extract (year from ORDERDATE),PRODUCTID
order by ORDERYEAR ASC, ORDERCOUNT DESC),
cte2 as
(select
ORDERYEAR,PRODUCTID,ORDERCOUNT,
ROW_NUMBER()OVER(PARTITION BY ORDERYEAR ORDER BY ORDERCOUNT DESC) grouprank
from cte1)
select
DISTINCT PRODUCTID
from cte2
where grouprank<=10
SQL没有提供多个集合求并集的方法。先按年份分组后，计算每种产品每年的总销售数量，利用排序后的行号计算出排名。最后选出所有年份排名前 10 的产品 ID，再使用 DISTINCT 操作符进行去重，即可得到并集。
SPL的解决方案：
当 A 是由集合组成的集合时，SPL 提供了函数 A.union() 计算所有成员集合的并集。
A |
|
1 |
=T("Sales.csv") |
2 |
=A1.group(year(ORDERDATE)) |
3 |
=A2.(~.groups(PRODUCTID;count(~):COUNT)) |
4 |
=A3.(~.top(-5;COUNT).(PRODUCTID)) |
5 |
=A4.union() |
A1：从文件中导入销售表。
A2：销售表按年份分组。
A3：每年的记录再按产品分组统计销售数量。
A4：选出每年销售数量前 10 名的产品 ID。
A5：使用函数 A.union() 计算所有年份选出的产品的并集。
4. 差集
集合 A 和 B 的差集是指由所有属于集合 A 且不属于集合 B 的成员所组成的集合。
【例 7】 某商家有网店和实体店铺两种销售渠道，其销售数据分别存储在 SALES_ONLINE 和 SALES_STORE 两个表中。现在要查询在实体店消费总金额超过 1000，在网店没有消费过的客户有哪些。部分数据如下：
SALES_ONLINE：
ID |
CUSTOMERID |
ORDERDATE |
PRODUCTID |
AMOUNT |
1 |
HANAR |
2014/01/01 |
17 |
3868.6 |
2 |
ERNSH |
2014/01/03 |
42 |
1005.9 |
3 |
LINOD |
2014/01/06 |
64 |
400.0 |
4 |
OTTIK |
2014/01/07 |
10 |
1194.0 |
5 |
OCEAN |
2014/01/09 |
41 |
319.2 |
… |
… |
… |
… |
… |
SALES_STORE：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
1 |
EASTC |
2014/01/01 |
1 |
27 |
3063.0 |
2 |
ERNSH |
2014/01/02 |
8 |
70 |
2713.5 |
3 |
MAGAA |
2014/01/03 |
2 |
74 |
1675.0 |
4 |
SAVEA |
2014/01/07 |
7 |
45 |
2018.2 |
5 |
FOLIG |
2014/01/08 |
8 |
30 |
1622.4 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
select
CUSTOMERID
from
(select
CUSTOMERID,count(*)
from
SALES_STORE
where
extract (year from ORDERDATE)=2014
group by
CUSTOMERID
having
count(*)>3)
MINUS
select
DISTINCT CUSTOMERID
from
SALES_ONLINE
where
extract (year from ORDERDATE)=2014
这里是以 ORACLE 为例，在 SQL 语句中 MINUS 操作符（有的数据库是 EXCEPT）用于计算差集。MINUS 合并的子结果集必须拥有相同的数据结构和相同数量的列, 列也必须拥有相似的数据类型。
SPL的解决方案：
SPL中可以使用符号“\”来计算集合的差集。比如 A\B 就是集合 A 与集合 B 的差集。
A |
|
1 |
=T("SalesOnline.csv").select(year(ORDERDATE)==2014) |
2 |
=T("SalesStore.csv").select(year(ORDERDATE)==2014) |
3 |
=A2.groups(CUSTOMERID; count(~):COUNT).select(COUNT>3) |
4 |
=A3.id(CUSTOMERID)\A1.id(CUSTOMERID) |
A1：从文件中导入线上销售表，并选出 2014 年的销售记录。
A2：从文件中导入实体店销售表，并选出 2014 年的销售记录。
A3：分组汇总实体店每个客户的总销售次数，并选出超过 3 次的记录。
A4：使用符号“\”计算实体店和线上客户的差集。
【例 8】 根据销售数据表，查询 2014 年只在一月份排进总销售额前 10 名的客户有哪些。部分数据如下：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
10400 |
EASTC |
2014/01/01 |
1 |
27 |
3063.0 |
10401 |
HANAR |
2014/01/01 |
1 |
17 |
3868.6 |
10402 |
ERNSH |
2014/01/02 |
8 |
70 |
2713.5 |
10403 |
ERNSH |
2014/01/03 |
4 |
42 |
1005.9 |
10404 |
MAGAA |
2014/01/03 |
2 |
74 |
1675.0 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
with cte1 as
(select
extract (month from ORDERDATE) ORDERMONTH,
CUSTOMERID, SUM(AMOUNT) AMOUNT
from SALES
where extract (year from ORDERDATE)=2014
group by extract (month from ORDERDATE),CUSTOMERID
order by ORDERMONTH ASC, AMOUNT DESC),
cte2 as
(select
ORDERMONTH,CUSTOMERID,AMOUNT,
ROW_NUMBER()OVER(PARTITION BY ORDERMONTH ORDER BY AMOUNT DESC) grouprank
from cte1)
select CUSTOMERID
from cte2
where grouprank<=10 and ORDERMONTH=1
MINUS
select CUSTOMERID
from cte2
where grouprank<=10 and ORDERMONTH<>1
SQL没有提供多个集合求差集的方法。先按月份分组后，计算每个客户每月的总销售额，利用排序后的行号计算出排名。最后选出一月份的客户集合，与其他月份的客户集合使用 MINUS 操作符计算差集。
SPL的解决方案：
当 A 是由集合组成的集合时，SPL 提供了函数 A.diff() 计算第一个成员集合与其他成员集合的差集。
A |
|
1 |
=T("Sales.csv").select(year(ORDERDATE)==2014) |
2 |
=A1.group(month(ORDERDATE)) |
3 |
=A2.(~.groups(CUSTOMERID;sum(AMOUNT):AMOUNT)) |
4 |
=A3.(~.top(-10;AMOUNT).(CUSTOMERID)) |
5 |
=A4.diff() |
A1：从文件中导入销售表，并选出 2014 年的销售记录。
A2：销售表按月份分组。
A3：每月的销售记录再按客户分组汇总。
A4：统计每月总销售额前十名的客户。
A5：使用函数 A.diff() 计算一月份与其他月份的客户的差集。
5. 异或集
有集合 A 和 B，属于 A 或 B，但不同时属于 A 和 B 的成员的集合称为 A 和 B 的异或集。
【例 9】 两个学期的成绩分别保存在不同的表中，查询一班上下学期只有一次进入总分前 10 名的学生。部分数据如下：
上学期 SCORES1：
CLASS |
STUDENTID |
SUBJECT |
SCORE |
1 |
1 |
English |
84 |
1 |
1 |
Math |
77 |
1 |
1 |
PE |
69 |
1 |
2 |
English |
81 |
1 |
2 |
Math |
80 |
… |
… |
… |
… |
下学期SCORES2：
CLASS |
STUDENTID |
SUBJECT |
SCORE |
1 |
1 |
English |
97 |
1 |
1 |
Math |
64 |
1 |
1 |
PE |
97 |
1 |
2 |
English |
56 |
1 |
2 |
Math |
82 |
… |
… |
… |
… |
SQL的解决方案：
SQL没有定义求异或集的运算符，如果用集合运算符，可以有两种方法：
1.（A UNION B）EXCEPT (A INTERSECT B);
2. (A EXCEPT B) UNION (B EXCEPT A);
这两种方法都比较麻烦，因为使用了多个视图，性能开销也会增大。这里我们使用了全外连接（FULL OUTER JOIN）来解决这个问题：
with cte1 as
(select STUDENTID,TOTALSCORE
from
(select STUDENTID, sum(SCORE) TOTALSCORE
from SCORES1
group by STUDENTID
order by TOTALSCORE DESC)
where rownum <= 10),
cte2 as
(select STUDENTID,TOTALSCORE
from
(select STUDENTID, sum(SCORE) TOTALSCORE
from SCORES2
group by STUDENTID
order by TOTALSCORE DESC)
where rownum <= 10)
select
COALESCE(cte1.STUDENTID, cte2.STUDENTID) STUDENTID,
cte1.TOTALSCORE, cte2.TOTALSCORE
from cte1
FULL OUTER JOIN cte2
ON cte1.STUDENTID=cte2.STUDENTID
where cte1.TOTALSCORE IS NULL
OR cte2.TOTALSCORE IS NULL
这里是以 ORACLE 为例，对于 MYSQL 这种不支持 FULL JOIN 的数据库，可以使用左外连接（LEFT JOIN） UNION 右外连接（RIGHT JOIN）的方法来实现，这里就不给出具体语句了。
SPL的解决方案：
SPL中可以使用符号“%”来计算集合的异或集。比如 A%B 就是集合 A 与集合 B 的异或集。
A |
|
1 |
=T("Scores1.csv") |
2 |
=T("Scores2.csv") |
3 |
=A1.groups(STUDENTID; sum(SCORE):SCORE) |
4 |
=A2.groups(STUDENTID; sum(SCORE):SCORE) |
5 |
=A3.top(-10;SCORE).(STUDENTID) |
6 |
=A4.top(-10;SCORE).(STUDENTID) |
7 |
=A5%A6 |
A1：从文件中导入上学期成绩表。
A2：从文件中导入下学期成绩表。
A3：上学期成绩表按学生 ID 分组汇总每个学生的总分数。
A4：下学期成绩表按学生 ID 分组汇总每个学生的总分数。
A5：选出上学期总分前 10 名的学生 ID。
A6：选出下学期总分前 10 名的学生 ID。
A7：使用符号“%”计算上下两个学期的异或集。
因为 SQL 没有求异或集的运算符，本例中的 SQL 语句非常复杂。而 SPL 只需要使用符号“%”就可以很方便的解决求异或集的问题。
【例 10】 查询客户 RATTC，在 2014 年是否排进过单月销售额的前三名。部分数据如下：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
10400 |
EASTC |
2014/01/01 |
1 |
27 |
3063.0 |
10401 |
HANAR |
2014/01/01 |
1 |
17 |
3868.6 |
10402 |
ERNSH |
2014/01/02 |
8 |
70 |
2713.5 |
10403 |
ERNSH |
2014/01/03 |
4 |
42 |
1005.9 |
10404 |
MAGAA |
2014/01/03 |
2 |
74 |
1675.0 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
with cte1 as
(select
extract (month from ORDERDATE) ORDERMONTH,
CUSTOMERID, SUM(AMOUNT) AMOUNT
from SALES
where extract (year from ORDERDATE)=2014
group by extract (month from ORDERDATE),CUSTOMERID
order by ORDERMONTH ASC, AMOUNT DESC),
cte2 as
(select
ORDERMONTH,CUSTOMERID,AMOUNT,
ROW_NUMBER()OVER(PARTITION BY ORDERMONTH ORDER BY AMOUNT DESC) grouprank
from cte1)
select count(*) CUSTOMER_EXISTS
from cte2
where grouprank<=3 and CUSTOMERID='RATTC'
SQL没有提供多个集合求异或集的方法。先按月份分组后，计算每个客户每月的总销售额，利用排序后的行号计算出排名。最后选出所有月份前三名的客户，并计算存在客户 "RATTC" 的记录数。如果记录数为 1 表示存在，如果记录数为 0 表示不存在。
SPL的解决方案：
当 A 是由集合组成的集合时，SPL 提供了函数 A.cor() 计算多个成员集合的异或集。
A |
|
1 |
=T("Sales.csv").select(year(ORDERDATE)==2014) |
2 |
=A1.group(month(ORDERDATE)) |
3 |
=A2.(~.groups(CUSTOMERID; sum(AMOUNT):AMOUNT)) |
4 |
=A3.new(~.top(-3; AMOUNT):TOP3) |
5 |
=A4.(TOP3.(CUSTOMERID).pos("RATTC")>0) |
6 |
=A5.cor() |
A1：从文件中导入销售表，并选出 2014 年的销售记录。
A2：销售表按月份分组。
A3：每月的销售记录再按客户分组汇总。
A4：统计每月总销售额前三名的客户。
A5：计算每组的客户是否包含 "RATTC"。
A6：使用函数 A.cor() 计算每个月是否包含客户 "RATTC" 的异或集。结果为 true 表示存在，false 表示不存在。
6. 从属和包含
对于两个集合 A 和 B，若集合 A 的任何一个成员都是集合 B 的成员，则称 B 包含 A。从属关系是元素与集合之间的关系，当集合 A 中存在成员 x 时，我们称 x 从属于 A。
【例 11】 在员工表中，统计 California, New York, Texas, Washington 各部门的平均工资。部分数据如下：
ID |
NAME |
SURNAME |
STATE |
DEPT |
SALARY |
1 |
Rebecca |
Moore |
California |
R&D |
7000 |
2 |
Ashley |
Wilson |
New York |
Finance |
11000 |
3 |
Rachel |
Johnson |
New Mexico |
Sales |
9000 |
4 |
Emily |
Smith |
Texas |
HR |
7000 |
5 |
Ashley |
Smith |
Texas |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
SQL 语句中，IN操作符可以在 WHERE 子句中规定从属关系。SQL 语句如下：
select
DEPT, avg(SALARY) AVGSALARY
from
EMPLOYEE
where
STATE in ('California','New York','Texas','Washington')
group by
DEPT
SPL的解决方案：
SPL中可以使用函数 A.contain(x) 判断 x 是否从属于集合 A。
A |
|
1 |
=T("Employee.csv") |
2 |
[California,New York,Texas,Washington] |
3 |
=A1.select(A2.contain(STATE)) |
4 |
=A3.groups(DEPT; avg(SALARY):SALARY) |
A1：从文件中导入员工表。
A2：定义几个州的常量集合。
A3：从员工表中选出州名从属于 A2 集合的记录。
A4：对 A3 选出的记录进行分组汇总每个部门的平均工资。
【例 12】 根据课程表和选课表，查询同时选修了 Matlab 和 Modern wireless communication system 课程的学生。部分数据如下：
COURSE：
ID |
NAME |
TEACHERID |
1 |
Environmental protection and sustainable development |
5 |
2 |
Mental health of College Students |
1 |
3 |
Matlab |
8 |
4 |
Electromechanical basic practice |
7 |
5 |
Introduction to modern life science |
3 |
… |
… |
… |
SELECT_COURSE：
ID |
STUDENT_NAME |
COURSE |
1 |
Rebecca Moore |
2,7 |
2 |
Ashley Wilson |
1,8 |
3 |
Rachel Johnson |
2,7,10 |
4 |
Emily Smith |
1,10 |
5 |
Ashley Smith |
5,6 |
… |
… |
… |
这个题目并不复杂，就是判断选课表的课程字段中，是否包含课程集合[3,6]（Matlab 和 Modern wireless communication system 的课程 ID）。
SQL的解决方案：
SQL的字段不支持集合数据类型，所以我们无法使用集合的包含关系来解决问题。这里以 ORACLE 为例，使用了函数 REGEXP_SUBSTR，对字符串按正则表达式进行拆分。将选课表与课程表进行左连接，找到所有选出这两门课程的记录。接下来将课程记录按 ID 分组，选出记录数大于等于 2 的，即为同时选出两门课程的。最后再根据选出的 ID，在选课表中查找出相应的记录。SQL 语句如下：
with cte1 as
(SELECT ID,REGEXP_SUBSTR(t1.COURSE ,'[^,]+',1,l) COURSE
FROM SELECT_COURSE t1,
(SELECT LEVEL l
FROM DUAL
CONNECT BY LEVEL<=10) t2
WHERE l<=LENGTH(t1.COURSE) - LENGTH(REPLACE(COURSE,','))+1)
select *
from SELECT_COURSE t5
inner join (
select ID, count(*)
from (
select t3.ID, COURSE
from cte1 t3
inner join (
select ID
from COURSE
where NAME='Matlab' or
NAME='Modern wireless communication system'
) t4
on t3.COURSE=t4.ID
)
group by ID
having count(*)>=2
) t6
on t5.ID=t6.ID
SPL的解决方案：
SPL中可以使用函数 A.pos(B) 获取集合 B 中的成员在集合 A 中的位置，不存在时返回 null。
A |
|
1 |
=T("Course.txt") |
2 |
=T("SelectCourse.txt") |
3 |
=A1.select(NAME=="Matlab" || NAME=="Modern wireless communication system").(ID) |
4 |
=A2.run(COURSE=COURSE.split@cp()) |
5 |
=A4.select(COURSE.pos(A3)!=null) |
A1：从文件中导入课程表。
A2：从文件中导入选课表。
A3：选出两门课程的 ID 集合。
A4：将选修的课程按逗号分割后，解析数字组成集合。
A5：使用函数 A.pos() 在选课中定位两门课程的 ID，不为空的即为同时选择了两门课程的记录。
在本例中，因为 SQL 没有集合类型的字段值，所以处理起来有些复杂。另外 SQL 也没有提供集合包含关系的判断方法，只好使用连接过滤的方式来处理了。SPL 不仅支持集合类型的字段值，还提供了丰富的函数用于集合的整体定位，可以用于集合的包含判断。
【例 13】 根据某地的天气数据，查询有哪些天是西风，且前两天是北风。部分数据如下：
WEATHER_DATE |
RAIN_FALL |
WIND_GUST_DIR |
WIND_GUST_SPEED |
RAIN_TODAY |
RAIN_TOMORROW |
2008/12/01 |
0.6 |
W |
44 |
No |
No |
2008/12/02 |
0.0 |
WNW |
44 |
No |
No |
2008/12/03 |
0.0 |
WSW |
46 |
No |
No |
2008/12/04 |
0.0 |
NE |
24 |
No |
No |
2008/12/05 |
1.0 |
W |
41 |
No |
No |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
题目非常简单，就是在 WIND_GUST_DIR 集合中查找有序子集 [N,N,W]。但是 SQL 对于有序计算的支持很差，这是因为 SQL是以无序集合作为基础的，感兴趣的朋友可以看一下相关文章《SQL 和 SPL 的有序运算对比》，这里就不再详细阐述了。在不支持窗口的函数的时候，我们只能依靠表间连接来实现跨行计算。SQL 语句如下：
select
curr.WEATHER_DATE, RAIN_FALL,curr.WIND_GUST_DIR,
WIND_GUST_SPEED,RAIN_TODAY,RAIN_TOMORROW
from
weather curr
inner join
(select
pre1.WEATHER_DATE,pre1.WIND_GUST_DIR
from weather pre1
left join
(select
WEATHER_DATE,WIND_GUST_DIR
from weather) pre2
on
pre1.WEATHER_DATE=pre2.WEATHER_DATE+1
where
pre1.WIND_GUST_DIR='N' and pre2.WIND_GUST_DIR='N') yest
on
curr.WEATHER_DATE=yest.WEATHER_DATE+1
where
curr.WIND_GUST_DIR='W'
order by WEATHER_DATE
上面的 SQL 不仅语句复杂，因为每次跨行访问都要与自身连接一次，效率也非常低下。从 2003 年起，SQL 标准中引入了窗口函数，带来了序的概念。有序计算变得容易了一些：
select
WEATHER_DATE,RAIN_FALL,WIND_GUST_DIR,
WIND_GUST_SPEED,RAIN_TODAY,RAIN_TOMORROW
from
(select
WEATHER_DATE,RAIN_FALL,WIND_GUST_DIR,
WIND_GUST_SPEED,RAIN_TODAY,RAIN_TOMORROW,
LAG(WIND_GUST_DIR,1)
OVER (
ORDER BY WEATHER_DATE ASC
) YESTERDAY_DIR,
LAG(WIND_GUST_DIR,2)
OVER (
ORDER BY WEATHER_DATE ASC
) BEFORE_YESTERDAY_DIR
from WEATHER)
where
WIND_GUST_DIR='W' and YESTERDAY_DIR='N'
and BEFORE_YESTERDAY_DIR='N'
order by WEATHER_DATE
SPL的解决方案：
SPL中可以在有序计算时，可以跨行访问记录。
A |
|
1 |
=T("weather.csv") |
2 |
=A1.select(WIND_GUST_DIR[-2]=="N" && WIND_GUST_DIR[-1]=="N" && WIND_GUST_DIR=="W") |
A1：从文件中导入天气数据。
A2：选出当天的风向是西风，并且前两天都是北风的记录。
总结
从上面的讨论可以看出。SQL可以支持两个集合的合、交、并、差计算，但是不能很好的支持异或集。而且对于多个集合的合、交、并、差、异或等计算，SQL 也不支持，只能采用其他的方法来解决。而 SPL 对于每种集合运算都提供了相应的函数进行支持，不但语句简洁易懂，效率也更高。
SQL的字段并不支持集合数据类型，当字段值是以标识符分隔的数据时，SQL 无法进行集合的包含运算。在处理有序子集的包含关系时，SQL 极端的不适应，即使使用了窗口函数，SQL 语句仍然很复杂。而 SPL 提供了很多整体定位函数，可以用于集合的包含关系。SPL 对于有序计算的支持非常完善，可以轻松的处理跨行运算问题。
另外，当查询比较复杂时，SQL 语句的复杂程度会成倍增加。比如经常要用到临时表、嵌套查询等等，使得 SQL 语句的编写和维护难度提升。而 SPL 只要按自然思维去组织计算逻辑，逐行书写出简洁的代码。
esProc 是专业的数据计算引擎，基于有序集合设计，同时提供了完善的集合运算，相当于 Java 和 SQL 优势的结合。在 SPL 的支持下，集合运算会非常容易。
SQL 与 SPL 对比系列：
SQL 和 SPL 的集合运算对比
SQL 和 SPL 的选出运算对比
SQL 和 SPL 的有序运算对比
SQL 和 SPL 的等值分组对比
SQL 和 SPL 的非等值分组对比
SQL 和 SPL 的有序分组对比
SQL 和 SPL 的一对一和一对多连接对比
SQL 和 SPL 的多对一连接对比
SQL 和 SPL 的多对多连接对比
SQL 和 SPL 的基本静态转置对比
SQL 和 SPL 的复杂静态转置对比
SQL 和 SPL 的动态转置对比
SQL 和 SPL 的递归对比
英文版