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:把每个月销售额前三名的记录合并。

IXIC.txt

Score.txt

Sales.txt