TopN 系列函数

在分组计算中，常常不需要对分组中的所有数据做计算，有时只需要对组中 topn 个元素做计算即可。举个例子，如想要统计每天每只股票 Volume 值小于第一四分位数的平均 Volume 值，是不可以用 percentile 函数直接求值的，因为 sql 语句是先整体进行条件过滤再分组计算，因此无法将每个分组的 top 元素取出。

假设存在表 tb 是两支股票的数据表，要按照股票和日期分组后用 percentile 筛选，统计每天每只股票因子位于前 40% 的 value 的平均值。

trade_date=sort(take(2017.01.11..2017.01.12,20))
secu_code=take(`600570`600000,20)
value=1..20
tb=table(trade_date,secu_code,value)   

常规的做法编写自定义聚合函数来实现：

defg percentile_40(x){
	ret = NULL
	y = percentile(x,40)
	cc = sum(x<y) 
	if (cc > 0){
		ret = sum(iif( x<y,x,0))\cc
	}
	return ret
}
select percentile_40(value) as factor_value from tb group by trade_date,secu_code

#output
trade_date secu_code factor_value
---------- --------- ------------
2017.01.11 600000    3           
2017.01.11 600570    2           
2017.01.12 600000    13          
2017.01.12 600570    12          

针对类似的情况，DolphinDB 推出了 topN 系列函数来解决这样的问题。topN 函数能够使得数据按照某个指标进行排序，并取排序后前 top 个元素或者前多少百分比的元素进行计算，能够显著提升脚本的可读性。

select aggrTopN(avg, funcArgs=value, sortingCol=value, top=0.4, ascending=true) as factor_value from tb group by trade_date,secu_code

#output
trade_date secu_code factor_value
---------- --------- ------------
2017.01.11 600000    3           
2017.01.11 600570    2           
2017.01.12 600000    13          
2017.01.12 600570    12

除了聚合函数 aggrTopN 之外，DolphinDB 还推出了 mTopN，cumTopN，tmTopN。对于 topN 的排序列，我们通常会将涨跌幅、交易量等指标作为排序键。在接下来的第 2、3 章中，将会具体举例 mTopN，cumTopN，tmTopN 的应用场景。

TopN 系列函数基本上可以归纳为以下几种类型：mfuncTopN、tmfuncTopN 以及 cumfuncTopN。此外，还有与之对应的高阶函数 aggrTopN。

以 mfuncTopN(X, S, window, top, [ascending=true], [tiesMethod]) 为例，其计算过程为：

1. 将 X 根据 S 进行稳定排序（排序方式由 ascending 指定，默认 true 为升序）
2. 取排序结果的前 top 个元素进行计算。如果有多个具有相同值的元素无法全部进入前 top，可通过 tiesMethod 参数设置对这些值的选取规则。简单的说，tiesMethod 为 latest 时，优先选取最新的数据，为 oldest 时，优先选取最老的数据，为 all 时，选取全部数据。具体的用法规则可参考 TopN 的用户手册 TopN。

目前支持 TopN 的计算函数共有 36 个：

滑动窗口的 TopN 计算的场景很多，比如计算每只股票窗口内交易量最大的 3 条记录的平均价格，亦或是计算每种仪器的窗口内温度最高的 5 条记录的平均能耗等。具体的应用场景会在本教程的第二章 mTopN、tmTopN 系列的应用场景中介绍。

累计窗口的 TopN 计算更多的涉及比如计算每只股票历史涨幅最大的几天的成交量之和等。具体的应用场景会在本教程的第三章 cumTopN 系列的应用场景。

除此之外，如果用户想要自定义取出 TopN 之后的计算规则，DolphinDB 也开放了 aggrTopN 函数，用户可以在此函数搭配其他高阶函数如 moving， tmoving 等，实现自己的 mfuncTopN 函数。这一部分的场景和应用会在第四章自定义 TopN 的实现方法中展开。

最后，TopN 系列函数都支持了批流一体，在第五章 TopN 的批流一体场景，会介绍如何将批计算中的 TopN 系列因子运用到实时计算流引擎中。

mTopN 可以按记录数滑动，窗口长度计算既可以按记录数，也可以按时间长度。具体滑动的规则可以参考窗口计算综述教程： window_cal.md

对于模拟数据中的分钟表 t，要得到每支股票每 5 条记录内交易量最大的 3 条记录的平均价格，可以用 mavgTopN 函数搭配 context by 解决：

select windCode, tradingTime, mavgTopN(close, volume, 5, 3, false) as mavgTop3Close from t context by windCode
//output
windCode  tradingTime             mavgTop3Close
--------- ----------------------- ------------------
600001.SH 2023.04.30T09:30:00.000 20                
600001.SH 2023.04.30T09:31:00.000 20.005
600001.SH 2023.04.30T09:32:00.000 20.01
600001.SH 2023.04.30T09:33:00.000 20.02
600001.SH 2023.04.30T09:34:00.000 20.03
...

一般来说，写因子的时候，计算的值不带单位，比如计算窗口大小为 100 分钟的交易量最大的十条记录的平均涨幅：

select windCode, tradingTime, mavgTopN(ratios(close), volume, 100, 10, false) as mavgTop10RatioClose from t context by windCode, date(tradingTime)
//output
windCode  tradingTime             mavgTop10RatioClose
--------- ----------------------- -----------------
600001.SH 2023.04.30T09:30:00.000                  
600001.SH 2023.04.30T09:31:00.000 1.0005           
600001.SH 2023.04.30T09:32:00.000 1.000499875
600001.SH 2023.04.30T09:33:00.000 1.000499750
600001.SH 2023.04.30T09:34:00.000 1.000499625
...

除了单目的 TopN 之外，DolphinDB 也支持了双目运算，如 beta、corr、covar 等的 TopN 算子。例如，计算每支股票每 5 条记录价格最高的 3 条记录的两个因子的相关性，可以用 mcorrTopN 函数搭配 context by 解决：

select windCode, tradingTime, mcorrTopN(low, close * volume,  log(ratios(close)), 5, 3, false) as mcorrTop3CloseVol from t context by windCode, date(tradingTime)
//output （由于是模拟数据，比较失真）
windCode  tradingTime             mcorrTop3CloseVol
--------- ----------------------- -----------------
600001.SH 2023.04.30T09:30:00.000                  
600001.SH 2023.04.30T09:31:00.000                  
600001.SH 2023.04.30T09:32:00.000 1.00000
600001.SH 2023.04.30T09:33:00.000 0.99999
600001.SH 2023.04.30T09:34:00.000 0.99999

mTopN 的窗口计算是根据记录数取窗口大小。而 tmTopN 函数的窗口大小可以是一个时间间隔，既可以是 5 分钟，也可以为 20 秒，以此类推。tmTopN 系列函数中，top 可以是一个 0 和 1 之间的浮点数，表示百分比，譬如 0.2，表示选择窗口内 20% 的记录。

例如对上述数据的处理中，计算时间窗口为 3 分钟，交易量最大的两条记录的平均涨幅：

select windCode, tradingTime, tmavgTopN(tradingTime, ratios(close), volume, 3m, 2, false) as tmavgTop2RatioClose from t context by windCode, date(tradingTime)
//output
windCode  tradingTime             tmavgTop2RatioClose
--------- ----------------------- -----------------
600001.SH 2023.04.30T09:30:00.000                  
600001.SH 2023.04.30T09:31:00.000 1.0005           
600001.SH 2023.04.30T09:32:00.000 1.000499875
600001.SH 2023.04.30T09:33:00.000 1.000499625
600001.SH 2023.04.30T09:34:00.000 1.0004993758
...

TopN 系列函数也实现了相关性函数的计算，因此可以计算例如 5 分钟窗口期内交易量最大的三条记录的 close 和 volume 相关性：

select windCode, tradingTime, tmcorrTopN(tradingTime, close, volume, volume, 5m, 3, false) as tmavgTop3CorrCloseVolume from t context by windCode, date(tradingTime)

//output
windCode  tradingTime             tmavgTop3CorrCloseVolume
--------- ----------------------- ------------------------
600001.SH 2023.04.30T09:30:00.000                         
600001.SH 2023.04.30T09:31:00.000 0.999999990552169       
600001.SH 2023.04.30T09:32:00.000 1.000000001625267       
600001.SH 2023.04.30T09:33:00.000 1.000000006877599       
600001.SH 2023.04.30T09:34:00.000 1.000000012129931       
...

TopN 系列对应的高阶函数是 aggrTopN(func, funcArgs, sortingCol, top, [ascending=true])，func 参数可以接受聚合函数。因此，如果用户希望用复杂的自定义聚合函数计算 TopN，例如计算交易量最大的前 40% 的记录中 OHLC 的平均值，可以这样实现：

//用 defg 自定义聚合函数 avgOHLC，定义取出 TopN 之后的聚合行为
defg avgOHLC(price){ 
	return avg(price)
}

select aggrTopN(avgOHLC, funcArgs =(open + high + low + close) , sortingCol=volume, top=0.4, ascending=true) as factor_value from t group by windCode

//output
windCode  factor_value       
--------- -------------------
600001.SH 80.94 
600002.SH 116.56583
600003.SH 160.74
600004.SH 196.40
600005.SH 240.76167

同样，mTopN、tmTopN 系列的窗口函数也支持自定义。例如上述的 avgOHLC 函数，用户可以将其应用到 moving、tmoving 函数中：

select windCode, tradingTime, moving(aggrTopN{avgOHLC,,,0.4,true},(open + high + low + close, volume),10,1) as udfmTopN from t context by windCode

//output
windCode  tradingTime             udfmTopN          
--------- ----------------------- ------------------
600001.SH 2023.04.30T09:30:00.000 80 
...
600001.SH 2023.04.30T09:39:00.000 80.06
600001.SH 2023.04.30T09:40:00.000 80.10
600001.SH 2023.04.30T09:41:00.000 80.14
...

m 系列函数为各自的计算场景进行了优化，因此比 moving 高阶函数有更好的性能。上述的计算也可以用内置函数的 mavgTopN 完成：

select windCode, tradingTime, mavgTopN(open + high + low + close, volume,10,4) as udfmTopN from t context by windCode

//output
windCode  tradingTime             udfmTopN          
--------- ----------------------- ------------------
600001.SH 2023.04.30T09:30:00.000 80 
...
600001.SH 2023.04.30T09:39:00.000 80.06
600001.SH 2023.04.30T09:40:00.000 80.10
600001.SH 2023.04.30T09:41:00.000 80.14
...

性能上来说，内置 TopN 系列函数的计算效率远高于自定义 TopN 函数：

• 测试设备
  • CPU：Intel(R) Xeon(R) Silver 4216 CPU @ 2.10GHz
  • 操作系统：64 位 CentOS Linux 7 (Core)
  • DolphinDB 部署类型：2.00.10 版本，单节点

//moving+aggrTopN+avgOHLC
timer(10000)select windCode, tradingTime, moving(aggrTopN{avgOHLC,,,0.4,true},(open + high + low + close, volume),10,1) as udfmTopN from t context by windCode
//8394 ms

//moving+aggrTopN+avg
timer(10000)select windCode, tradingTime, moving(aggrTopN{avg,,,0.4,true},(open + high + low + close, volume),10,1) as udfmTopN from t context by windCode
//6812 ms

//mavgTopN
timer(10000)select windCode, tradingTime, mavgTopN(open + high + low + close, volume,10,4) as udfmTopN from t context by windCode
//1394 ms