SQL 和 SPL 的非等值分组对比
【摘要】
前文提到的分组运算，都是以相同的字段值或表达式结果为基准来分组。除此以外还有一些常见但特殊的分组运算。比如将客户按照北上广深的顺序分组统计总销售额，将家庭按年收入分为富裕、中产、小康、贫困组等等。SQL 和 SPL 是大家比较熟悉的程序语言，本文将探讨对于分组问题，这两种语言的解决方案和基本原理。如何简便快捷的处理分组运算，这里为你全程解析，并提供 SQL 和 SPL 示例代码。SQL 和 SPL 的非等值分组对比
我们之前碰到的分组问题都有一个特点，以相同的字段值或表达式结果为基准来分组，这种分组称为等值分组，是最常见的分组形式。等值分组具有完全划分的特点，即：
（1）分组结果没有空子集。
（2）原集合的任何成员都属于且只属于某一个子集。
我们学习了等值分组和完全划分。那是不是还有非等值分组和不完全划分？是的。还有具体如下特点的不完全划分：
（1）分组结果包含空子集。
（2）分组结果不包含所有原集合成员。
（3）同一个成员分到两个组中。
一．对齐分组
对照一个基准集合，将待分组集合成员的某个字段或表达式与基准集合成员比较，相同者则分到同一个组中，最后拆分出来的组数和基准集合成员数是相同的。这种分组我们称为对齐分组。对齐分组可能会有空组，也可能有成员未分配到任何一个组中。
【例 1】按课程表顺序查询有哪些课没有学生选修。课程表和选课表部分数据如下：
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 |
COURSEID |
STUDENTID |
1 |
6 |
59 |
2 |
6 |
43 |
3 |
5 |
52 |
4 |
5 |
44 |
5 |
5 |
37 |
… |
… |
… |
SQL的解决方案：
在 SQL 中并不支持对齐分组。我们可以先在选课表中计算选出课程的唯一值，然后在课程表中选出不包含这些课程的记录。SQL 语句如下：
select *
from COURSE
where
ID not in
(select DISTINCT COURSEID
from SELECT_COURSE)
order by rownum
SPL的解决方案：
SPL提供了函数 A.align() 用于对齐分组。
A |
|
1 |
=T("Course.csv") |
2 |
=T("SelectCourse.csv") |
3 |
=A2.align(A1:ID, COURSEID) |
4 |
=A1(A3.pos@a(null)) |
A1：导入课程表。
A2：导入选课表。
A3：使用函数 A.align()，选课表按照课程表的顺序对齐分组。
A4：从课程表中，选出没人选的课程。
【例 2】按部门表的部门顺序统计各部门人数。部门表和员工表部分数据如下：
DEPARTMENT：
DEPT |
MANAGER |
Administration |
1 |
Finance |
4 |
HR |
5 |
Marketing |
6 |
Production |
7 |
… |
… |
EMPLOYEE：
ID |
NAME |
BIRTHDAY |
STATE |
DEPT |
SALARY |
1 |
Rebecca |
1974/11/20 |
California |
R&D |
7000 |
2 |
Ashley |
1980/07/19 |
New York |
Finance |
11000 |
3 |
Rachel |
1970/12/17 |
New Mexico |
Sales |
9000 |
4 |
Emily |
1985/03/07 |
Texas |
HR |
7000 |
5 |
Ashley |
1975/05/13 |
Texas |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
在这个对齐分组问题中，每个组中包含匹配的所有成员。在 SQL 中并不支持这种对齐分组。我们可以先把员工表按照部门分组汇总统计各部门人数，然后将部门表与员工表的分组汇总结果左连接，并查询部门和人数。值得注意的是，两个结果集 JOIN 之后是无法保证次序的，所以我们需要按照部门表的行号排序，否则无法保证查询结果与部门表的顺序一致。SQL 语句如下：
select
t1.DEPT, t2.COUNT
from
(select DEPT,rownum ordernum from DEPARTMENT) t1
left join
(select
DEPT, count(*) COUNT
from EMPLOYEE
group by DEPT) t2
on t1.DEPT=t2.DEPT
order by ordernum
SPL的解决方案：
SPL提供了函数 A.align() 用于对齐分组, 选项 @a 可以返回每组匹配的所有成员。
A |
|
1 |
=T("Department.csv") |
2 |
=T("Employee.csv") |
3 |
=A2.align@a(A1:DEPT, DEPT) |
4 |
=A3.new(DEPT, ~.len():COUNT) |
A1：导入部门表。
A2：导入员工表。
A3：员工表按部门对齐分组，每组返回所有匹配成员。
A4：统计各部门的员工数量。
【例 3】以员工表为例，按照 California, Texas, New York, Florida, OTHER 的顺序统计各州的平均工资，其中”OTHER”组用于存放其他州的员工。部分数据如下：
ID |
NAME |
BIRTHDAY |
STATE |
DEPT |
SALARY |
1 |
Rebecca |
1974/11/20 |
California |
R&D |
7000 |
2 |
Ashley |
1980/07/19 |
New York |
Finance |
11000 |
3 |
Rachel |
1970/12/17 |
New Mexico |
Sales |
9000 |
4 |
Emily |
1985/03/07 |
Texas |
HR |
7000 |
5 |
Ashley |
1975/05/13 |
Texas |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
在这个对齐分组例子中，需要把不匹配的成员分配到新组。SQL 并不支持这种对齐分组，我们需要提前创建好四个州的组和“其他”组，再通过 CASE WHEN 语句，判断员工属于哪个组。SQL 语句如下：
with cte1(ID,STATE) as
(select 1,'California' from DUAL
UNION ALL select 2,'Texas' from DUAL
UNION ALL select 3,'New York' from DUAL
UNION ALL select 4,'Florida' from DUAL
UNION ALL select 5,'OTHER' from DUAL)
select
t1.STATE, t2.AVG_SALARY
from cte1 t1
left join
(select
STATE,avg(SALARY) AVG_SALARY
from
( select
CASE WHEN
STATE IN ('California','Texas','New York','Florida')
THEN STATE
ELSE 'OTHER' END STATE,
SALARY
from EMPLOYEE)
group by STATE) t2
on t1.STATE=t2.STATE
order by t1.ID
SPL的解决方案：
SPL提供了函数 A.align() 用于对齐分组, 选项 @n 时不匹配成员存放到新组。
A |
|
1 |
=T("Employee.csv") |
2 |
[California,Texas,New York,Florida] |
3 |
=A1.align@an(A2,STATE) |
4 |
=A3.new(if (#>A2.len(),"OTHER",STATE):STATE,~.avg(SALARY):AVG_SALARY) |
A1：导入员工表。
A2：定义地区常数集合。
A3：使用函数 A.align() 将员工表按地区对位分组，选项 @a 时每组返回所有匹配成员，选项 @n 时不匹配成员存放到新组。
A4：统计每组的平均工资，定义新组的名称为 OTHER。
因为 SQL 并不支持对齐分组，在 SQL 中我们只能通过 JOIN 等方式来实现。因为 SQL 的结果集是无序的，我们只能使用行号等方式记录原表的顺序。这些原因使得 SQL 实现对齐分组非常复杂。而 SPL 专门提供了用于对齐分组的函数 A.align()，语法更简洁，效率也更高。
二．枚举分组
枚举分组是指，事先指定一组枚举条件，将待分组集合的成员作为参数计算这批条件，条件成立者都被划分到与该条件对应的一个子集中，结果集的子集和事先指定的条件一一对应。
【例 4】 根据中国城市的市区人口，将城市分类并统计数量。其中超大城市 200 万人口以上，特大城市 100-200 万人口，大城市 50-100 万，其他中小城市。部分数据如下：
ID |
CITY |
POPULATION |
PROVINCE |
1 |
Shanghai |
12286274 |
Shanghai |
2 |
Beijing |
9931140 |
Beijing |
3 |
Chongqing |
7421420 |
Chongqing |
4 |
Guangzhou |
7240465 |
Guangdong |
5 |
Hong Kong |
7010000 |
Hong Kong Special Administrative Region |
… |
… |
… |
… |
SQL的解决方案：
在 SQL 中并不支持枚举分组，我们可以通过 CASE WHEN 语句来分段，然后再根据分段值进行分组汇总。CASE WHEN 语句将每条记录只存放到第一个匹配组。SQL 语句如下：
with cte1(ID,CITY_TYPE) as
(select 1,'超大城市 ' from DUAL
UNION ALL select 2,'特大城市 ' from DUAL
UNION ALL select 3,'大城市 ' from DUAL
UNION ALL select 4,'中小城市 ' from DUAL)
select
t1.CITY_TYPE, nvl(t2.COUNT,0) COUNT
from cte1 t1
left join
(select CITY_TYPE, count(*) COUNT from
(select
case when POPULATION>2000000 then '超大城市 '
when POPULATION>1000000 then '特大城市 '
when POPULATION>500000 then '大城市 '
when POPULATION<=500000 then '中小城市 '
else 'OTHER' end CITY_TYPE
from URBAN_POPULATION)
group by CITY_TYPE) t2
on t1.CITY_TYPE=t2.CITY_TYPE
order by t1.ID
SPL的解决方案：
SPL提供了函数 A.enum()，根据枚举条件进行分组。
A |
|
1 |
=T("UrbanPopulation.txt") |
2 |
[?>2000000,?>1000000,?>500000,?<=500000] |
3 |
=A1.enum(A2,POPULATION) |
4 |
[超大城市, 特大城市, 大城市, 中小城市 ] |
5 |
=A3.new(A4(#):CITY_TYPE,~.count():COUNT) |
A1：导入城市人口表。
A2：定义分组条件。
A3：使用函数 A.enum() 将城市人口表按条件进行枚举分组，默认将每个成员只存放到第一个匹配组。
A4：定义每组的名称。
A5：根据枚举分组结果，统计每个组的城市数量。
【例 5】 根据年龄将员工分组统计平均工资，分成 35 岁以下、45 岁以下和其他三个组。员工表部分数据如下：
ID |
NAME |
BIRTHDAY |
STATE |
DEPT |
SALARY |
1 |
Rebecca |
1974/11/20 |
California |
R&D |
7000 |
2 |
Ashley |
1980/07/19 |
New York |
Finance |
11000 |
3 |
Rachel |
1970/12/17 |
New Mexico |
Sales |
9000 |
4 |
Emily |
1985/03/07 |
Texas |
HR |
7000 |
5 |
Ashley |
1975/05/13 |
Texas |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
在这个枚举分组例子中，需要将不满足条件的记录分配到新组。我们可以通过 CASE WHEN … ELSE 语句定义“其他“组来实现，SQL 语句如下：
with cte1(ID,AGE_AREA) as
(select 1,'35岁以下 ' from DUAL
UNION ALL select 2,'45岁以下 ' from DUAL
UNION ALL select 3,'其他 ' from DUAL)
select
t1.AGE_AREA, nvl(t2.AVG_SALARY,0) AVG_SALARY
from cte1 t1
left join
(select
AGE_AREA, avg(SALARY) AVG_SALARY
from
(select
case when TRUNC(months_between(sysdate, BIRTHDAY)/12)<35
then '35岁以下 '
when TRUNC(months_between(sysdate, BIRTHDAY)/12)<45
then '45岁以下 '
else '其他 ' end AGE_AREA,
SALARY
from EMPLOYEE)
group by AGE_AREA) t2
on t1.AGE_AREA=t2.AGE_AREA
order by t1.ID
SPL的解决方案：
函数 A.enum() 的选项 @n，将不匹配成员存放到新组。
A |
|
1 |
=T("Employee.csv") |
2 |
[?<35,?<45] |
3 |
=A1.enum@n(A2, age(BIRTHDAY)) |
4 |
[35岁以下,45 岁以下, 其他 ] |
5 |
=A3.new(A4(#):AGE_AREA,~.avg(SALARY):AVG_SALARY) |
A1：导入员工表。
A2：定义分组条件。
A3：使用函数 A.enum() 按年龄条件枚举分组，使用选项 @n 将不匹配成员存放到新组。
A4：定义每组的名称。
A5：根据枚举分组结果，统计每个员工的平均工资。
【例 6】 根据城市 GDP 表，分别统计直辖市、一线城市、二线城市的人均 GDP。需要注意的是，分组可能会有重复成员，比如北京既是一线城市，又是直辖市。部分数据如下：
ID |
CITY |
GDP |
POPULATION |
1 |
Shanghai |
32679 |
2418 |
2 |
Beijing |
30320 |
2171 |
3 |
Shenzhen |
24691 |
1253 |
4 |
Guangzhou |
23000 |
1450 |
5 |
Chongqing |
20363 |
3372 |
… |
… |
… |
… |
SQL的解决方案：
在本例中，同一条记录可能会同时分配到多个组中。前面我们使用的 CASE WHEN 语句，只会将记录分配到第一个匹配的组，不能重分组。SQL 不支持枚举分组的重分组，我们只能分别统计每个组的人均 GDP，再把所有组的统计结果合并。SQL 语句如下：
select
'直辖市 ' AREA,
SUM(GDP*1000)/SUM(POPULATION) CAPITA_GDP
from CITY_GDP
where
CITY in ('Beijing','Shanghai','Tianjin','Chongqing')
UNION ALL
select
'一线城市 ' AREA,
SUM(GDP*1000)/SUM(POPULATION) CAPITA_GDP
from CITY_GDP
where
CITY in ('Beijing','Shanghai','Guangzhou','Shenzhen')
UNION ALL
select
'二线城市 ' AREA,
SUM(GDP*1000)/SUM(POPULATION) CAPITA_GDP
from CITY_GDP
where
CITY in ('Chengdu','Hangzhou','Chongqing','Wuhan','Xian','Suzhou','Tianjin','Nanjing','Changsha','Zhengzhou','Dongguan','Qingdao','Shenyang','Ningbo','Kunming')
在这个 SQL 语句中，我们把每个组都单独查询统计，再一个一个合并起来。这样导致每增加一个分组，都需要增加一段 SQL 语句。当分组数量很多时，SQL 的编写和维护都会更加复杂。
SPL的解决方案：
函数 A.enum() 的选项 @r，每组检查所有成员是否匹配。
A |
|
1 |
=T("CityGDP.txt") |
2 |
[["Beijing","Shanghai","Tianjin","Chongqing"].pos(?)>0,["Beijing","Shanghai","Guangzhou","Shenzhen"].pos(?)>0,["Chengdu","Hangzhou","Chongqing","Wuhan","Xian","Suzhou","Tianjin","Nanjing","Changsha","Zhengzhou","Dongguan","Qingdao","Shenyang","Ningbo","Kunming"].pos(?)>0] |
3 |
=A1.enum@r(A2,CITY) |
4 |
[直辖市, 一线城市, 二线城市 ] |
5 |
=A3.new(A4(#):AREA,~.sum(GDP)/~.sum(POPULATION)*10000:CAPITA_GDP) |
A1：导入城市 GDP 表。
A2：枚举直辖市、一线城市和二线城市的条件。
A3：使用函数 A.enum() 按枚举条件分组，使用选项 @r 时每组检查所有成员是否匹配。
A4：定义每组的名称。
A5：根据枚举分组结果，统计每组的人均 GDP。
在 SPL 中仅需一个选项就可以解决枚举分组的重分组问题。即便再多分组，也只需要维护枚举条件，而枚举分组及汇总部分代码完全不需要修改。
总结
SQL对于对齐分组和枚举分组极端不适应。一方面原因是 SQL 并没有单独提供语句或函数来支持，另一方面由于 SQL 的的理论基础，也就是关系代数，是以无序集合作为基础的，对于固定顺序的运算非常困难。而 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 的递归对比
