SQL 和 SPL 的有序分组对比
【摘要】
对于有序集合来说，有时候数据集的次序在分组时是有意义的。我们有时需要把具有相同字段值或者同时满足某些条件的相邻记录分到同一组。比如 2020 年蝉联月销售冠军的最多是几次，北京气温连续上升的日子最长有几天等等。这就需要用到有序分组了。SQL 和 SPL 是大家比较熟悉的程序语言，本文将探讨对于分组问题，这两种语言的解决方案和基本原理。如何简便快捷的处理分组运算，这里为你全程解析，并提供 SQL 和 SPL 示例代码。SQL 和 SPL 的有序分组对比
【例 1】 上证指数 2020 年收盘价最长连续上涨了多少天？（首个交易日指数上涨）。部分数据如下：
DATE |
CLOSE |
OPEN |
VOLUME |
AMOUNT |
2020/01/02 |
3085.1976 |
3066.3357 |
292470208 |
3.27197122606E11 |
2020/01/03 |
3083.7858 |
3089.022 |
261496667 |
2.89991708382E11 |
2020/01/06 |
3083.4083 |
3070.9088 |
312575842 |
3.31182549906E11 |
2020/01/07 |
3104.8015 |
3085.4882 |
276583111 |
2.88159227657E11 |
2020/01/08 |
3066.8925 |
3094.2389 |
297872553 |
3.06517394459E11 |
… |
… |
… |
… |
… |
SQL的解决方案：
这个问题有点难想了。基本的思路是把按日期有序的指数记录分成若干组，连续上涨的记录分成同一组。也就是说，某天的收盘价比上一天是上涨的，则和上一天记录分到同一组，如果下跌了，则开始一个新组。最后看所有分组中最大的成员数量，也就是最多连续上涨的天数。
这种分组比较特殊，和记录的次序有关，而 SQL 里只支持等值分组，就需要把这种有序分组转换成常规的等值分组来实现。过程是这样：
（1) 按日期排序，用窗口函数取出每天的前一天股价。
（2）对比，如果上涨了的标记为 0，下跌的标记为 1。
（3）累加当前行以前的标记，累加的结果类似 0,0,1,1,1,1,2,2,3,3,3…，这些就是我们需要的组号了。
（4）现在可以用 SQL 常规的等值分组了。
SQL 语句如下：
SELECT
MAX(ContinuousDays) MAX_DAYS
FROM (
SELECT COUNT(*) ContinuousDays
FROM (
SELECT SUM(RisingFlag) OVER (ORDER BY "DATE") NoRisingDays
FROM (
SELECT
"DATE", CASE WHEN CLOSE>
LAG(CLOSE) OVER (ORDER BY "DATE")
THEN 0 ELSE 1 END RisingFlag
FROM SSEC
WHERE EXTRACT (YEAR FROM "DATE")=2020
)
)
GROUP BY NoRisingDays
)
SPL的解决方案：
在 SPL 中函数 A.group() 的选项 @i，当条件发生变化时重新分组。
A |
|
1 |
=T("SSEC.csv") |
2 |
=A1.select(year(DATE)==2020).sort(DATE) |
3 |
=A2.group@i(CLOSE<CLOSE[-1]) |
4 |
=A3.max(~.len()) |
A1：导入上证指数表。
A2：选出 2020 年的记录，并按日期升序排列。
A3：当收盘价小于前日收盘价时重新分组。
A4：计算连续上涨的最大天数。
与让人眼晕的 SQL 语句相比，SPL 语句十分简洁。一方面原因，是由于 SQL 对于有序分组并不支持。另外一方面原因前面已经介绍过，SQL 的分组子集无法保留，不能使用上次的分组结果继续进行分组等运算。而 SPL 不但可以保留分组子集，还支持有序分组。
【例 2】 根据奥运会奖牌榜统计表，求总成绩蝉联第一名届数最长的国家及其奖牌信息。部分数据如下：
Game |
Nation |
Gold |
Silver |
Copper |
30 |
USA |
46 |
29 |
29 |
30 |
China |
38 |
27 |
23 |
30 |
UK |
29 |
17 |
19 |
30 |
Russia |
24 |
26 |
32 |
30 |
Korea |
13 |
8 |
7 |
… |
… |
… |
… |
… |
SQL的解决方案：
这个题目主要是分组子集和有序分组，但是由于 SQL 对这两种分组方式都不支持，实现起来非常复杂。我们还是需要通过增加一些标记，再通过等值分组来实现。首先根据总成绩选出每一届的冠军，然后再按照连续的国家名称分组，选出每组成员最多的国家，并选出从第几届开始、连续几届等信息。最后根据这些信息在奥运会奖牌榜中选出这些记录。SQL 语句如下：
with cte1 as
(select GAME, max(NATION) keep (dense_rank first order by 1000000*GOLD+1000*SILVER+COPPER desc) NATION
from OLYMPIC
group by GAME),
cte2 as
(select min(NATION) NATION, min(GAME) GAMESTART, count(*) GAMECOUNT
from
(select GAME, NATION, row_number() over(ORDER by GAME) rowno, ROW_NUMBER()OVER(PARTITION BY NATION ORDER BY GAME) groupno
from cte1)
group by NATION, groupno-rowno),
cte3 as
(select min(NATION) keep (dense_rank first order by GAMECOUNT desc) NATION, min(GAMESTART) keep (dense_rank first order by GAMECOUNT desc) GAMESTART, max(GAMECOUNT) GAMECOUNT
from cte2
select t1.GAME,t1.NATION,t1.GOLD,t1.SILVER,t1.COPPER
from OLYMPIC t1
right join cte3
on t1.nation=cte3.nation and t1.game>=cte3.GAMESTART and t1.game<(cte3.GAMESTART+cte3.GAMECOUNT)
这个 SQL 已经很难看懂了。另外这里是以 ORACLE 为例，ORACLE 中有分析函数 KEEP 可以解决子集合中查询最值的问题。如果是其他数据库，又需要增加一层查询来取最值所在的记录。
SPL的解决方案：
在 SPL 中函数 A.group() 的选项 @o，当字段值发生变化时重新分组。
A |
|
1 |
=T("Olympic.txt") |
2 |
=A1.sort@z(GAME, 1000000*GOLD+1000*SILVER+COPPER) |
3 |
=A2.group@o1(GAME) |
4 |
=A3.group@o(NATION) |
5 |
=A4.maxp(~.len()) |
A1：导入奥运会奖牌榜。
A2：按第几届和总成绩降序排列。
A3：每届取一名，因为有序也就是每届的第一名。
A4：当国家改变时重新分组。
A5：选出成员数量最多的一组，也就是蝉联次数最多的。
与 SQL 相比，这个问题的 SPL 脚本还是很简单的。主要是因为 SPL 的分组保留了分组子集，接下来可以对分组子集进行有序分组运算。这个题目的 SPL 脚本逻辑清晰，只要按思路顺序编写就可以了。
有些时候，我们能够直接或者间接的获取分组序号（成员应该分配到第几组），这种情况下我们可以直接按照分组序号进行分组。
【例 3】根据员工的入职时间平均分成三组（有余数的顺序分配到某一组），并统计每组的平均工资。部分数据如下：
ID |
NAME |
BIRTHDAY |
ENTRYDATE |
DEPT |
SALARY |
1 |
Rebecca |
1974/11/20 |
2005/03/11 |
R&D |
7000 |
2 |
Ashley |
1980/07/19 |
2008/03/16 |
Finance |
11000 |
3 |
Rachel |
1970/12/17 |
2010/12/01 |
Sales |
9000 |
4 |
Emily |
1985/03/07 |
2006/08/15 |
HR |
7000 |
5 |
Ashley |
1975/05/13 |
2004/07/30 |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
这个题目是将员工按照入职时间的顺序来分组，前三分之一分配到第一组，中间三分之一分配到第二组，剩下的三分之一分配到第三组。这实际上是一个按序号分组的例子，将具有相同序号的成员分配到同一组。SQL 语句并不支持序号分组，我们可以先计算出序号，再根据序号进行等值分组，最后按序号排序。SQL 语句如下：
with cte1 as(
select count(*) COUNT
from EMPLOYEE
)
select
GROUP_NO, avg(SALARY) AVG_SALARY
from (
select
TRUNC((rn-1)*3/(select COUNT from cte1))+1 GROUP_NO, SALARY
from (
select
SALARY, ROW_NUMBER()OVER(ORDER BY ENTRYDATE) rn
from EMPLOYEE
)
)
group by GROUP_NO
order by GROUP_NO
SPL的解决方案：
在 SPL 中函数 A.group() 的选项 @n，用于按序号分组，具有相同序号的记录被分到同一组（序号为 N 的成员分配到第 N 组，N 从 1 开始）。
A |
|
1 |
=T("Employee.csv").sort(ENTRYDATE) |
2 |
=A1.group@n((#-1)*3\A1.len()+ 1) |
3 |
=A2.new(#:GROUP_NO, ~.avg(SALARY):AVG_SALARY) |
A1：导入员工表，并按照入职日期排序。
A2：通过排序后的行号计算出所属的分组序号，并按序号分组。
A3：统计每组的平均工资。
总结
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 的递归对比
