結構化資料上的 TopN 運算

【摘要】
    TopN 查詢，即從資料中查詢前 N 名 / 後 N 名。TopN 查詢除了直接取值，還有查詢 TopN 所在記錄的詳細資訊，有時候還要查詢 TopN 所在的位置。另外 TopN 查詢還可能在分組中使用，用於查詢組內的前 N 名 / 後 N 名。這麼多種 TopN 查詢如何處理？這裡將為你全程剖析，並提供 esProc SPL 示例程式碼。

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)	/把各部門薪水前三的記錄合併

 

《 》中還有更多相關計算示例。