The TopN Functions

The TopN functions are designed to sort data based on a specific metric (such as price changes, trading volumes, etc.) and select the top N elements (or top N%) for further calculation. These functions can be categorized as follows:

• mTopN Functions: Used for calculations within a sliding window, where the window size can be based on the number of elements or time interval. Examples include msumTopN, mcorrTopN, etc.
• tmTopN Functions: Used for calculations within a time-based sliding window. Examples include tmsumTopN, tmcorrTopN, etc. N can also be a floating-point number between 0 and 1, representing the top N% of elements.
• cumTopN Functions: Used for calculations within a cumulative window, such as calculating the sum of trading volumes for the top N days with the highest historical price changes for each stock. Examples include cumsumTopN, cumcorrTopN, etc.
• aggrTopN Higher-Order Function: Used for calculation logic not covered by the TopN functions above. It can be combined with a user-defined aggregate function and higher-order window functions (such as moving, tmoving) to create customized TopN functions. N can be a floating-point number between 0 and 1, representing the top N% of elements.

The table below shows the functions categorized under the TopN functions:

Calculation Rules of the mTopN Functions

Diving into TopN functions, we'll use the mTopN functions as an example to unpack their core principles. In later sections, we'll zoom in on the specific applications of different TopN function categories, revealing their distinctive characteristics and shared traits. This will enable you to find the right function for for real-world problems.

The mTopN functions follow this syntax:

mTopN(X, S, window, top, [ascending=true], [tiesMethod])

The calculation proceeds as follows:

1. The function sorts X based on S (ascending by default).
2. Then, it picks the top N values. If there are equal values, the tiesMethod parameter decides how to handle them:
   • latest – Keeps the most recent data.
   • oldest – Keeps the oldest data.
   • all – Includes all tied values.

For full details, check the mTopN User Manual.

The calculation in a sliding window involves sorting records within the window, selecting the top N records (or top N% of records), and then performing an aggregation.

This section illustrates the concept with practical examples. The following script generates minute-level OHLC data for demonstration:

n = 5*121
timeVector = 2023.04.30T09:30:00.000 + 0..120 * 60000
tradingTime = take(timeVector,n)
windCode = stretch(format(000001..000005, "000000") + "AA", n)
open = (20.00+0.01*0..120) join (30.00-0.01*0..120) join (40.00+0.01*0..120) join (50.00-0.01*0..120) join (60.00+0.01*0..120)
high = (20.50+0.01*0..120) join (31.00-0.01*0..120) join (40.80+0.01*0..120) join (50.90-0.01*0..120) join (60.70+0.01*0..120)
low = (19.50+0.01*0..120) join (29.00-0.01*0..120) join (39.00+0.01*0..120) join (48.00-0.01*0..120) join (59.00+0.01*0..120)
close = (20.00+0.01*0..120) join (30.00-0.01*0..120) join (40.00+0.01*0..120) join (50.00-0.01*0..120) join (60.00+0.01*0..120)
volume= 10000+ take(-100..100,n)
t = table(tradingTime, windCode, open, high, low, close, volume)

Unlike the sliding window calculations of mTopN and tmTopN functions, the cumTopN functions focus on cumulative windows.

For example, to calculate the cumulative trading volume for the top 3 records with the largest price increases in history, you can use cumsumTopN:

select windCode, tradingTime, cumsumTopN(volume, ratios(close), 3, false) as cumsumTop3Volume from t context by windCode
//output
windCode tradingTime             cumsumTop3Volume
-------- ----------------------- ----------------
000001AA 2023.04.30T09:30:00.000
000001AA 2023.04.30T09:31:00.000 9901
000001AA 2023.04.30T09:32:00.000 19803
000001AA 2023.04.30T09:33:00.000 29706
000001AA 2023.04.30T09:34:00.000 29706
...

In the above script, we can see that the second parameter of cumsumTopN, the sorting metric S, is not a column but rather the result of a function applied to a column. This demonstrates the flexibility of the TopN functions.

We've now covered the built-in TopN functions. Additionally, DolphinDB provides an interface for custom implementations, allowing you to define TopN functions that fit your specific requirements.

Note: The built-in TopN functions are optimized for their respective computation scenarios and generally outperform user-defined TopN functions.

The TopN functions also support stream computing scenarios. For example, in a reactive state engine, to calculate the average price change of the top 10 records with the highest trading volume within a 100-minute sliding window:

//Factor expressions used in batch computation can be directly used in a streaming engine
factor = <mavgTopN(ratios(close), volume, 100, 10, false)>

// Define the input and output table schemas
share streamTable(1:0, `tradingTime`windCode`open`high`low`close`volume, [TIMESTAMP,STRING,DOUBLE,DOUBLE,DOUBLE,DOUBLE,INT]) as tickStream
result = table(1000:0, `windCode`tradingTime`mavgTop10RatioClose, [STRING,TIMESTAMP,DOUBLE])

//Define the streaming engine
rse = createReactiveStateEngine(name="streamTopN", metrics =[<tradingTime>, factor], dummyTable=t, outputTable=result, keyColumn="windCode")

//Subscribe to the stream table and replay data
subscribeTable(tableName=`tickStream, actionName="mTopN", handler=tableInsert{rse})
replay(inputTables=t.copy().sortBy!(`tradingTime), outputTables=tickStream, timeColumn=`tradingTime)

//Query the stream computation result
select * from result

//To run the script above again, use the following script to unsubscribe from the stream table first
unsubscribeTable(tableName=`tickStream, actionName="mTopN")
dropStreamEngine(`streamTopN)
undef(`tickStream, SHARED)

Starting from version 2.00.10, the TopN functions support the use of DECIMAL types (including DECIMAL32, DECIMAL64, and DECIMAL128) for the sorting field (S parameter) and the calculation fields (X and Y).

If the calculation fields are of DECIMAL type, the functions msumTopN, tmsumTopN, and cumsumTopN will return results in DECIMAL type, while other TopN functions will return results in DOUBLE type.

A special note for the TopN versions of var, varp, std, stp, corr, covar, beta, and wsum: These functions perform intermediate calculations in DECIMAL128 when processing DECIMAL inputs to maintain precision, though they ultimately return DOUBLE results. However, using DECIMAL for intermediate results comes with trade-offs: computations take longer, and there is a risk of overflow. For performance optimization, the system doesn't throw overflow exceptions, so users should exercise additional caution with extreme values.

DECIMAL128 supports up to 38 significant digits (including both integer and decimal parts). For example, when calculating the variance of price data like 18.2345 (6 digits), squaring it results in 12 digits. With 100 million data points, the total precision used is about 20 (12+8) digits—well within the 38-digit limit, so overflow won't occur. However, if the number of decimal places of 18.2345 increases significantly—for instance, from 4 to 15—the number becomes 17 digits long, and squaring it gives 34 digits. Just 10,000 such numbers could exceed the 38-digit limit of DECIMAL128.

To prevent such issues, in cases where data has many decimal places, consider either converting it to DOUBLE for processing or using functions like decimal32, decimal64, or decimal128 to reduce precision before calculation.