topn()

Toolkit

TimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale Cloud. Click to learn more.

Experimental

Experimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Returns the most common values accumulated in a frequency aggregate or top N aggregate.

topn (
    agg FrequencyAggregate,
    n INTEGER
) RETURNS topn AnyElement

topn (
    agg TopnAggregate,
    n INTEGER
) RETURNS topn AnyElement

Both frequency aggregates and top N aggregates can be used to calculate topn. Top N aggregates allow you to specify the target number of values you want returned, without estimating their threshold frequency. Frequency aggregates allow you to store all values that surpass a threshold frequency. They are useful if you want to store and use frequency information, and not just calculate top N.

Required arguments

Optional arguments

Returns

In some cases, the function might return fewer than n values. This happens if:

• The underlying frequency aggregate doesn't contain n elements with the minimum frequency
• The data isn't skewed enough to support n values from a top N aggregate

Sample usage

This test uses a table of randomly generated data. The values used are the integer square roots of a random number in the range (0,400).

CREATE TABLE value_test(value INTEGER);
INSERT INTO value_test SELECT floor(sqrt(random() * 400)) FROM generate_series(1,100000);

This returns the 5 most common values seen in the table:

SELECT toolkit_experimental.topn(
    toolkit_experimental.freq_agg(0.05, value), 
    5) 
FROM value_test;

The output for this query:

topn 
------
   19
   18
   17
   16
   15