结构化数据上的 TopN 运算

【摘要】
    TopN 查询,即从数据中查找前 N 名 / 后 N 名。TopN 查询除了直接取值,还有查询 TopN 所在记录的详细信息,有时候还要查询 TopN 所在的位置。另外 TopN 查询还可能在分组中使用,用于查询组内的前 N 名 / 后 N 名。这么多种 TopN 查询如何处理?这里将为你全程剖析,并提供 esProc SPL 示例代码。结构化数据上的 TopN 运算

1.     最大值 / 最小值

最大值 / 最小值可以理解为 TopN 查询中,N 等于 1 时的情况,因为很常用所以单独拿出来讲一下。取最大值 / 最小值是很常见的需求,例如一班数学最高分是多少,员工年龄最小的是几岁等等。但是有时候我们并不关心具体的值,而是关心最大值 / 最小值出现的位置,这种需求常用于跨行计算。例如公司销售额最高的那个月比上个月的销售额增加了多少?此时我们需要知道销售额最高月份所在记录的行号,再取出上个月的销售额与之比较。还有时候我们关心的是最大值 / 最小值所在记录的详细信息。例如取一班数学最高分的同学姓名,公司年龄最小的员工在哪个部门等等。

本节将从以上三种情况来讲解如何处理最大值 / 最小值的各种情况。以纳斯达克指数为例,部分数据如下:

Date

Open

Close

Volume

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    取最大值 / 最小值

【例 1】 求纳斯达克指数 2019 年最高收盘价。

【SPL 脚本】


A

B

1

=file("IXIC.txt").import@t()

/导入纳斯达克指数数据

2

=A1.select(year(Date)==2019)

/选出 2019 年数据

3

=A2.max(Close)

/使用函数 A.max() 获取最高收盘价

同样的例子,求纳斯达克指数 2019 年最低收盘价:


A

B

3

=A2.min(Close)

/使用函数 A.min() 获取最低收盘价

 

1.2    取最大值 / 最小值所在的行号

【例 2】 求 2019 年收盘价最高日,相比前一日的收盘价涨幅。

【SPL 脚本】


A

B

1

=file("IXIC.txt").import@t()

/导入纳斯达克指数数据

2

=A1.select(year(Date)==2019).sort(Date)

/选出 2019 年数据并按日期排序

3

=A2.pmax(Close)

/使用函数 A.pmax() 取出收盘价最高点所在的行号

4

=A2.calc(A3,Close/Close[-1]-1)

/使用收盘价最大值与前日收盘价计算涨幅

最大值不一定是唯一的,如果想返回所有的行号,可以使用函数 A.pmax() 的 @a 选项:


A

B

3

=A2.pmax@a(Close)

/取出所有收盘价最高点记录所在行号

    如果希望从后向前定位,可以使用函数 A.pmax() 的 @z 选项:


A

B

3

=A2.pmax@z(Close)

/从后向前取出收盘价最高点记录所在行号

 

1.3    取最大值 / 最小值所在的记录

【例 3】 求纳斯达克指数 2019 年最高点的日期。

【SPL 脚本】


A

B

1

=file("IXIC.txt").import@t()

/导入纳斯达克指数数据

2

=A1.select(year(Date)==2019)

/选出 2019 年数据

3

=A2.maxp(Close)

/使用函数 A.maxp() 取出收盘价最高点所在的记录

4

=A3.Date

/取出收盘价最高点的日期

    同样可以使用函数 A.minp() 来取最小值所在记录:


A

B

3

=A2.minp(Close)

/使用函数 A.minp() 取出收盘价最低点所在的记录

    函数 A.maxp()和 A.minp() 同样支持 @a 和 @z 选项,就不再逐一列举了。

2.     前 N 个 / 后 N 个

取前 N 个 / 后 N 个的需求,与取最大值 / 最小值是类似的。我们同样分为三类需求来详细介绍。还是以纳斯达克指数为例,部分数据如下:

Date

Open

Close

Volume

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

 

2.1    取前 N 个 / 后 N 个值

【例 4】 查询纳斯达克指数 2019 年成交量最高的 3 个量值。

【SPL 脚本】


A

B

1

=file("IXIC.txt").import@t()

/导入纳斯达克指数数据

2

=A1.select(year(Date)==2019)

/选出 2019 年数据

3

=A2.top(-3, Volume)

/使用函数 A.top(n,x) 获取成交量最高的 3 个量值

同样的例子,查询纳斯达克指数 2019 年成交量最低的 4 个量值:


A

B

3

=A2.top(4, Volume)

/使用函数 A.top(n,x) 获取成交量最低的 4 个量值

 

2.2    取前 N 个 / 后 N 个所在的行号

【例 5】 查询纳斯达克指数 2019 年收盘价最高的 3 天中,交易量相对前一日的涨幅。

【SPL 脚本】


A

B

1

=file("IXIC.txt").import@t()

/导入纳斯达克指数数据

2

=A1.select(year(Date)==2019).sort(Date)

/选出 2019 年数据并按日期排序

3

=A2.ptop(-3, Close)

/使用函数 A.ptop(n,x) 取出最高的 3 个收盘价所在的行号

4

=A3.run(~=A2(~).Volume/A2(~-1).Volume-1)

/循环使用当日交易量与前日交易量计算涨幅

 

2.3    取前 N 个 / 后 N 个所在的记录

【例 6】 查询纳斯达克指数 2019 年成交量最低的 5 个交易日的交易信息。

【SPL 脚本】


A

B

1

=file("IXIC.txt").import@t()

/导入纳斯达克指数数据

2

=A1.select(year(Date)==2019).sort(Date)

/选出 2019 年数据

3

=A2.top(5; Close)

/使用函数 A.top(n; x) 取出成交量最低的 5 个交易日的记录

 

3.     分组中的使用

除了分组汇总计算每组的最大值 / 最小值,查询每组前 N 个 / 后 N 个也是很常见的需求。例如每个月卖的最好的 5 款商品是哪些,每年总销售额前三名的客户是哪些等等。本节我们会分类介绍,如何解决在分组中使用 TopN 的问题。

3.1    分组聚合中的最大值

【例 7】 查询各班数学最高分。成绩表部分数据如下:

CLASS

STUDENTID

SUBJECT

SCORE

1

1

English

95

1

1

Math

90

1

1

PE

80

1

2

English

75

1

2

Math

84

SPL脚本】


A

B

1

=file("Score.txt").import@t()

/导入成绩表数据

2

=A1.select(Subject:"Math")

/选出数学成绩

3

=A2.groups(Class; max(Score):BestScore)

/按班级分组,使用 max() 函数统计各班数学最高分

 

3.2    分组后进行 TopN 运算

我们也可以把 TopN 查询看作一种聚合运算。首先将数据按照一定的条件分组,然后再对每个分组后的结果集进行 TopN 查询。我们分别按照取值和取记录两种情况来讲解。

【例 8】 查询各班数学前两名的分数。成绩表部分数据如下:

CLASS

STUDENTID

SUBJECT

SCORE

1

1

English

95

1

1

Math

90

1

1

PE

80

1

2

English

75

1

2

Math

84

SPL脚本】


A

B

1

=file("Score.txt").import@t()

/导入成绩表数据

2

=A1.select(Subject:"Math")

/选出数学成绩

3

=A2.group(Class; ~.top(-2, Score):top2)

/按班级分组,使用函数 A.top() 统计各班数学前两名的分数

4

=A3.new(Class, top2(1):First,   top2(2):Second)

/创建结果表,第一列是班级,第二列是第一名,第三列是第二名

 

【例 9】 查询各班每科成绩前三名的学生信息。成绩表部分数据如下:

CLASS

STUDENTID

SUBJECT

SCORE

1

1

English

95

1

1

Math

90

1

1

PE

80

1

2

English

75

1

2

Math

84

SPL脚本】


A

B

1

=file("Score.txt").import@t()

/导入成绩表数据

2

=A1.group(Class,Subject;~.top(-3;Score):top3)

/按班级和学科分组并取出每组分数前两名

3

=A2.conj(top3)

/将所有班级各科前两名对应的记录合并

 

3.3    以累计方式进行 TopN 运算

以累计方式进行 TopN 运算,不会产生分组的结果集,常用于数据量比较大的时候。我们还是按照取值和取记录两种情况来讲解。

【例 10】 求每个部门入职最早的两个人的入职日期。雇员表的部分数据如下:

EID

NAME

DEPT

EntryDate

1

Rebecca

R&D

2005/03/11

2

Ashley

Finance

2008/03/16

3

Rachel

Sales

2010/12/01

4

Emily

HR

2006/08/15

5

Ryan

R&D

2004/07/30

 

【SPL 脚本】


A

B

1

=file("Employee.txt").cursor@t()

/产生雇员表的游标

2

=A1.groups(Department;   top(2,EntryDate):Top2)

/按部门分组并取出每组入职时间最早的两个日期

3

=A2.news(Top2;Department, ~:EntryDate)

/创建新表,第一列是部门,第二列是入职日期

 

【例 11】 求每个部门薪水前三高的员工信息。雇员表的部分数据如下:

EID

NAME

DEPT

SALARY

1

Rebecca

R&D

7000

2

Ashley

Finance

11000

3

Rachel

Sales

9000

4

Emily

HR

7000

5

Ryan

R&D

13000

 

【SPL 脚本】


A

B

1

=file("Employee.txt").cursor@t()

/产生雇员表的游标

2

=A1.groups(Department; top(-3;Salary):Top3)

/按部门分组并取出每组薪水前三的记录

3

=A2.conj(Top3)

/把各部门薪水前三的记录合并

 

SPL CookBook》中还有更多相关计算示例。