1.???? 最大值 / 最小值

最大值 / 最小值可以理解為 TopN 查詢中，N 等于 1 時(shí)的情況，因?yàn)楹艹Ｓ盟詥为?dú)拿出來講一下。取最大值 / 最小值是很常見的需求，例如一班數(shù)學(xué)最高分是多少，員工年齡最小的是幾歲等等。但是有時(shí)候我們并不關(guān)心具體的值，而是關(guān)心最大值 / 最小值出現(xiàn)的位置，這種需求常用于跨行計(jì)算。例如公司銷售額最高的那個(gè)月比上個(gè)月的銷售額增加了多少？此時(shí)我們需要知道銷售額最高月份所在記錄的行號，再取出上個(gè)月的銷售額與之比較。還有時(shí)候我們關(guān)心的是最大值 / 最小值所在記錄的詳細(xì)信息。例如取一班數(shù)學(xué)最高分的同學(xué)姓名，公司年齡最小的員工在哪個(gè)部門等等。

本節(jié)將從以上三種情況來講解如何處理最大值 / 最小值的各種情況。以納斯達(dá)克指數(shù)為例，部分?jǐn)?shù)據(jù)如下：

DateOpenCloseVolume

2019/01/026506.9101566665.9399412261800000

2019/01/036584.770026463.52607290000

2019/01/046567.1401376738.8598632579550000

2019/01/076757.5297856823.4702152507550000

2019/01/086893.4399416897.02380290000

…………

1.1??? 取最大值 / 最小值

【例 1】 求納斯達(dá)克指數(shù) 2019 年最高收盤價(jià)。

【SPL 腳本】

AB

1=file("IXIC.txt").import@t()/導(dǎo)入納斯達(dá)克指數(shù)數(shù)據(jù)

2=A1.select(year(Date)==2019)/選出 2019 年數(shù)據(jù)

3=A2.max(Close)/使用函數(shù) A.max() 獲取最高收盤價(jià)

同樣的例子，求納斯達(dá)克指數(shù) 2019 年最低收盤價(jià)：

AB

3=A2.min(Close)/使用函數(shù) A.min() 獲取最低收盤價(jià)

1.2??? 取最大值 / 最小值所在的行號

【例 2】 求 2019 年收盤價(jià)最高日，相比前一日的收盤價(jià)漲幅。

【SPL 腳本】

AB

1=file("IXIC.txt").import@t()/導(dǎo)入納斯達(dá)克指數(shù)數(shù)據(jù)

2=A1.select(year(Date)==2019).sort(Date)/選出 2019 年數(shù)據(jù)并按日期排序

3=A2.pmax(Close)/使用函數(shù) A.pmax() 取出收盤價(jià)最高點(diǎn)所在的行號

4=A2.calc(A3,Close/Close[-1]-1)/使用收盤價(jià)最大值與前日收盤價(jià)計(jì)算漲幅

最大值不一定是唯一的，如果想返回所有的行號，可以使用函數(shù) A.pmax() 的 @a 選項(xiàng)：

AB

3=A2.pmax@a(Close)/取出所有收盤價(jià)最高點(diǎn)記錄所在行號

??? 如果希望從后向前定位，可以使用函數(shù) A.pmax() 的 @z 選項(xiàng)：

AB

3=A2.pmax@z(Close)/從后向前取出收盤價(jià)最高點(diǎn)記錄所在行號

1.3??? 取最大值 / 最小值所在的記錄

【例 3】 求納斯達(dá)克指數(shù) 2019 年最高點(diǎn)的日期。

【SPL 腳本】

AB

1=file("IXIC.txt").import@t()/導(dǎo)入納斯達(dá)克指數(shù)數(shù)據(jù)

2=A1.select(year(Date)==2019)/選出 2019 年數(shù)據(jù)

3=A2.maxp(Close)/使用函數(shù) A.maxp() 取出收盤價(jià)最高點(diǎn)所在的記錄

4=A3.Date/取出收盤價(jià)最高點(diǎn)的日期

??? 同樣可以使用函數(shù) A.minp() 來取最小值所在記錄：

AB

3=A2.minp(Close)/使用函數(shù) A.minp() 取出收盤價(jià)最低點(diǎn)所在的記錄

??? 函數(shù) A.maxp()和 A.minp() 同樣支持 @a 和 @z 選項(xiàng)，就不再逐一列舉了。

2.???? 前 N 個(gè) / 后 N 個(gè)

取前 N 個(gè) / 后 N 個(gè)的需求，與取最大值 / 最小值是類似的。我們同樣分為三類需求來詳細(xì)介紹。還是以納斯達(dá)克指數(shù)為例，部分?jǐn)?shù)據(jù)如下：

DateOpenCloseVolume

2019/01/026506.9101566665.9399412261800000

2019/01/036584.770026463.52607290000

2019/01/046567.1401376738.8598632579550000

2019/01/076757.5297856823.4702152507550000

2019/01/086893.4399416897.02380290000

…………

2.1??? 取前 N 個(gè) / 后 N 個(gè)值

【例 4】 查詢納斯達(dá)克指數(shù) 2019 年成交量最高的 3 個(gè)量值。

【SPL 腳本】

AB

1=file("IXIC.txt").import@t()/導(dǎo)入納斯達(dá)克指數(shù)數(shù)據(jù)

2=A1.select(year(Date)==2019)/選出 2019 年數(shù)據(jù)

3=A2.top(-3, Volume)/使用函數(shù) A.top(n,x) 獲取成交量最高的 3 個(gè)量值

同樣的例子，查詢納斯達(dá)克指數(shù) 2019 年成交量最低的 4 個(gè)量值：

AB

3=A2.top(4, Volume)/使用函數(shù) A.top(n,x) 獲取成交量最低的 4 個(gè)量值

2.2??? 取前 N 個(gè) / 后 N 個(gè)所在的行號

【例 5】 查詢納斯達(dá)克指數(shù) 2019 年收盤價(jià)最高的 3 天中，交易量相對前一日的漲幅。

【SPL 腳本】

AB

1=file("IXIC.txt").import@t()/導(dǎo)入納斯達(dá)克指數(shù)數(shù)據(jù)

2=A1.select(year(Date)==2019).sort(Date)/選出 2019 年數(shù)據(jù)并按日期排序

3=A2.ptop(-3, Close)/使用函數(shù) A.ptop(n,x) 取出最高的 3 個(gè)收盤價(jià)所在的行號

4=A3.run(~=A2(~).Volume/A2(~-1).Volume-1)/循環(huán)使用當(dāng)日交易量與前日交易量計(jì)算漲幅

2.3??? 取前 N 個(gè) / 后 N 個(gè)所在的記錄

【例 6】 查詢納斯達(dá)克指數(shù) 2019 年成交量最低的 5 個(gè)交易日的交易信息。

【SPL 腳本】

AB

1=file("IXIC.txt").import@t()/導(dǎo)入納斯達(dá)克指數(shù)數(shù)據(jù)

2=A1.select(year(Date)==2019).sort(Date)/選出 2019 年數(shù)據(jù)

3=A2.top(5; Close)/使用函數(shù) A.top(n; x) 取出成交量最低的 5 個(gè)交易日的記錄

3.???? 分組中的使用

除了分組匯總計(jì)算每組的最大值 / 最小值，查詢每組前 N 個(gè) / 后 N 個(gè)也是很常見的需求。例如每個(gè)月賣的最好的 5 款商品是哪些，每年總銷售額前三名的客戶是哪些等等。本節(jié)我們會分類介紹，如何解決在分組中使用 TopN 的問題。

3.1??? 分組聚合中的最大值

【例 7】 查詢各班數(shù)學(xué)最高分。成績表部分?jǐn)?shù)據(jù)如下：

CLASSSTUDENTIDSUBJECTSCORE

11English95

11Math90

11PE80

12English75

12Math84

…………

【SPL腳本】

AB

1=file("Score.txt").import@t()/導(dǎo)入成績表數(shù)據(jù)

2=A1.select(Subject:"Math")/選出數(shù)學(xué)成績

3=A2.groups(Class; max(Score):BestScore)/按班級分組，使用 max() 函數(shù)統(tǒng)計(jì)各班數(shù)學(xué)最高分

3.2??? 分組后進(jìn)行 TopN 運(yùn)算

我們也可以把 TopN 查詢看作一種聚合運(yùn)算。首先將數(shù)據(jù)按照一定的條件分組，然后再對每個(gè)分組后的結(jié)果集進(jìn)行 TopN 查詢。我們分別按照取值和取記錄兩種情況來講解。

【例 8】 查詢各班數(shù)學(xué)前兩名的分?jǐn)?shù)。成績表部分?jǐn)?shù)據(jù)如下：

CLASSSTUDENTIDSUBJECTSCORE

11English95

11Math90

11PE80

12English75

12Math84

…………

【SPL腳本】

AB

1=file("Score.txt").import@t()/導(dǎo)入成績表數(shù)據(jù)

2=A1.select(Subject:"Math")/選出數(shù)學(xué)成績

3=A2.group(Class; ~.top(-2, Score):top2)/按班級分組，使用函數(shù) A.top() 統(tǒng)計(jì)各班數(shù)學(xué)前兩名的分?jǐn)?shù)

4=A3.new(Class, top2(1):First, ? top2(2):Second)/創(chuàng)建結(jié)果表，第一列是班級，第二列是第一名，第三列是第二名

【例 9】 查詢各班每科成績前三名的學(xué)生信息。成績表部分?jǐn)?shù)據(jù)如下：

CLASSSTUDENTIDSUBJECTSCORE

11English95

11Math90

11PE80

12English75

12Math84

…………

【SPL腳本】

AB

1=file("Score.txt").import@t()/導(dǎo)入成績表數(shù)據(jù)

2=A1.group(Class,Subject;~.top(-3;Score):top3)/按班級和學(xué)科分組并取出每組分?jǐn)?shù)前兩名

3=A2.conj(top3)/將所有班級各科前兩名對應(yīng)的記錄合并

3.3??? 以累計(jì)方式進(jìn)行 TopN 運(yùn)算

以累計(jì)方式進(jìn)行 TopN 運(yùn)算，不會產(chǎn)生分組的結(jié)果集，常用于數(shù)據(jù)量比較大的時(shí)候。我們還是按照取值和取記錄兩種情況來講解。

【例 10】 求每個(gè)部門入職最早的兩個(gè)人的入職日期。雇員表的部分?jǐn)?shù)據(jù)如下：

EIDNAMEDEPTEntryDate

1RebeccaR&D2005/03/11

2AshleyFinance2008/03/16

3RachelSales2010/12/01

4EmilyHR2006/08/15

5RyanR&D2004/07/30

…………

?

【SPL 腳本】

AB

1=file("Employee.txt").cursor@t()/產(chǎn)生雇員表的游標(biāo)

2=A1.groups(Department; ? top(2,EntryDate):Top2)/按部門分組并取出每組入職時(shí)間最早的兩個(gè)日期

3=A2.news(Top2;Department, ~:EntryDate)/創(chuàng)建新表，第一列是部門，第二列是入職日期

【例 11】 求每個(gè)部門薪水前三高的員工信息。雇員表的部分?jǐn)?shù)據(jù)如下：

EIDNAMEDEPTSALARY

1RebeccaR&D7000

2AshleyFinance11000

3RachelSales9000

4EmilyHR7000

5RyanR&D13000

…………

?

【SPL 腳本】

AB

1=file("Employee.txt").cursor@t()/產(chǎn)生雇員表的游標(biāo)

2=A1.groups(Department; top(-3;Salary):Top3)/按部門分組并取出每組薪水前三的記錄

3=A2.conj(Top3)/把各部門薪水前三的記錄合并

《SPL CookBook》中還有更多相關(guān)計(jì)算示例。