SPL：TopN 和分组内 TopN
TopN 查询，即从数据中查找前 N 名 / 后 N 名。TopN 查询除了直接取值，还有查询 TopN 所在记录的详细信息，有时候还要查询 TopN 所在的行号位置。另外 TopN 查询还可能在分组中使用，用于查询组内的前 N 名 / 后 N 名。
1. TopN
取前 N 个 / 后 N 个，我们可以分为取值、取所在的行号以及取所在的记录，这三类需求来详细介绍。
以纳斯达克指数为例，部分数据如下：
Date |
Open |
Close |
Amount |
2019/01/02 |
6506.910156 |
6665.939941 |
2261800000 |
2019/01/03 |
6584.77002 |
6463.5 |
2607290000 |
2019/01/04 |
6567.140137 |
6738.859863 |
2579550000 |
2019/01/07 |
6757.529785 |
6823.470215 |
2507550000 |
2019/01/08 |
6893.439941 |
6897.0 |
2380290000 |
… |
… |
… |
… |
1.1 取前 N 个 / 后 N 个值
【例 1】 查询纳斯达克指数 2019 年成交量最高的 3 个量值。
SPL 脚本如下：
A |
|
1 |
=T("IXIC.txt") |
2 |
=A1.select(year(Date)==2019) |
3 |
=A2.top(-3, Amount) |
A1：导入纳斯达克指数数据
A2：选出 2019 年的数据。
A3：使用函数 A.top(n,x) 获取成交量最高的 3 个量值。其中 n 为正数时表示取前 N 个，n 为负数时表示取后 N 个。特殊的，当 n 为±1 时返回单值，类似于取最大值 / 最小值。
我们还可以查询纳斯达克指数 2019 年成交量最低的 4 个量值：
A |
|
3 |
=A2.top(4, Amount) |
A3：使用函数 A.top(n,x) 获取成交量最低的 4 个量值
1.2 取前 N 个 / 后 N 个所在的行号
在有序集合中，我们可以通过取前 N 个 / 后 N 个成员所在的行号，进行行间计算。
【例 2】 查询纳斯达克指数 2019 年收盘价最高的 3 天中，交易量相对前一日的涨幅。
SPL 脚本如下：
A |
|
1 |
=T("IXIC.txt") |
2 |
=A1.select(year(Date)==2019).sort(Date) |
3 |
=A2.ptop(-3, Close) |
4 |
=A3.(A2(~).Amount/A2(~-1).Amount-1) |
A1：导入纳斯达克指数数据。
A2：选出 2019 年的数据，并按日期排序。
A3：使用函数 A.ptop(n,x) 取出最高的 3 个收盘价所在的行号。
A4：根据选出的行号，分别用当日交易量与前一日交易量相比来计算涨幅。
1.3 取前 N 个 / 后 N 个所在的记录
有些时候，我们并不关心前 N 个 / 后 N 个的值具体是多少，而是关心这些值所在的记录。比如查询期末考试数学前 3 名的学生姓名，2020 年销售额前 5 名的客户名称等等。
【例 3】 查询纳斯达克指数 2019 年收盘价最低的 5 个交易日的成交量。
SPL脚本如下：
A |
|
1 |
=T("IXIC.txt") |
2 |
=A1.select(year(Date)==2019) |
3 |
=A2.top(5; Close) |
4 |
=A3.new(Date,Amount) |
A1：导入纳斯达克指数数据。
A2：选出 2019 年的数据。
A3：使用函数 A.top(n; x) 取出收盘价最低的 5 个交易日的记录。
A4：从 5 个交易日记录中取出日期和成交量。
2. 分组内 TopN
查询组内前 N 个 / 后 N 个是很常见的需求。例如查询每个班级数学成绩前两名是多少分，每个月单笔销售额前三名的客户是哪些等等。在本节中我们会分类介绍，如何解决在分组中使用 TopN 的问题。
2.1 分组后进行 TopN 运算
我们可以把 TopN 查询看作一种聚合运算。首先将数据按照一定的条件分组，然后再对每个分组后的结果集进行 TopN 查询。我们分别按照取值和取记录两种情况来讲解。
以学生成绩表为例，部分数据如下：
Class |
StudentID |
Subject |
Score |
1 |
1 |
English |
95 |
1 |
1 |
Math |
90 |
1 |
1 |
PE |
80 |
1 |
2 |
English |
75 |
1 |
2 |
Math |
84 |
… |
… |
… |
… |
【例 4】 查询各班数学前两名的分数。
SPL脚本如下：
A |
|
1 |
=T("Score.txt") |
2 |
=A1.select(Subject:"Math") |
3 |
=A2.group(Class; ~.top(-2, Score):TOP2) |
4 |
=A3.new(Class, TOP2(1):First, TOP2(2):Second) |
A1：导入成绩表数据。
A2：选出数学成绩。
A3：按班级分组，使用函数 A.top() 统计各班数学前两名的分数。
A4：创建结果表，第一列是班级，第二列是第一名，第三列是第二名。
【例 5】 查询各班每科成绩前三名的学生信息。
SPL脚本如下：
A |
|
1 |
=T("Score.txt") |
2 |
=A1.group(Class,Subject;~.top(-3;Score):TOP3) |
3 |
=A2.conj(TOP3) |
A1：导入成绩表数据。
A2：按班级和学科分组，并取出每组分数前三名的记录。
A3：将所有班级各科前两名所在记录合并。
2.2 以累计方式进行 TopN 运算
以累计方式进行 TopN 运算，不会产生分组的结果集，常用于数据量比较大的时候。我们还是按照取值和取记录两种情况来讲解。
以销售表为例，部分数据如下：
OrderID |
Customer |
OrderDate |
SellerId |
Amount |
81182311 |
VINET |
2013/07/04 |
5 |
2440.0 |
98807954 |
TOMSP |
2013/07/05 |
6 |
1863.4 |
65550721 |
HANAR |
2013/07/08 |
4 |
1813.0 |
37311312 |
VICTE |
2013/07/08 |
3 |
670.8 |
80138612 |
SUPRD |
2013/07/09 |
4 |
3730.0 |
… |
… |
… |
… |
… |
【例 6】 查询 2014 年每个月销售额前两名的金额。
SPL脚本如下：
A |
|
1 |
=file("Sales.txt").cursor@t().select(year(OrderDate)==2014) |
2 |
=A1.groups(month(OrderDate):Month; top(-2,Amount):TOP2) |
3 |
=A2.news(TOP2;Month, ~:Amount) |
A1：产生销售表的游标，并选出 2014 年的数据。
A2：按月份分组，并取出每个月销售额前两名的金额。
A3：创建结果表，第一列是月份，第二列是金额。
【例 7】 查询 2014 年每个月销售额前三名的销售记录。
SPL脚本如下：
A |
|
1 |
=file("Sales.txt").cursor@t().select(year(OrderDate)==2014) |
2 |
=A1.groups(month(OrderDate); top(-3;Amount):TOP3) |
3 |
=A2.conj(TOP3) |
A1：产生销售表的游标，并选出 2014 年的数据。
A2：按月份分组，并取出每个月销售额前三名的记录。
A3：把每个月销售额前三名的记录合并。
