max_n() functions

ToolkitTimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale, but must be installed separately for self-hosted TimescaleDB. Click to learn more.

Get the N largest values from a column.

The max_n() functions give the same results as the regular SQL query SELECT ... ORDER BY ... LIMIT n. But unlike the SQL query, they can be composed and combined like other aggregate hyperfunctions.

To get the N smallest values, use min_n(). To get the N largest values with accompanying data, use max_n_by().

Related hyperfunction groups

This group of functions uses the two-step aggregation pattern.

Rather than calculating the final result in one step, you first create an intermediate aggregate by using the aggregate function.

Then, use any of the accessors on the intermediate aggregate to calculate a final result. You can also roll up multiple intermediate aggregates with the rollup functions.

The two-step aggregation pattern has several advantages:

More efficient because multiple accessors can reuse the same aggregate
Easier to reason about performance, because aggregation is separate from final computation
Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
Can perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result

To learn more, see the blog post on two-step aggregates.

Functions in this group

max_n: Find the largest values in a set of data

into_array: Returns an array of the highest values from a MaxN aggregate
into_values: Returns the highest values from a MaxN aggregate

rollup: Combine multiple MaxN aggregates

Function details

max_n(
    value BIGINT | DOUBLE PRECISION | TIMESTAMPTZ,
    capacity BIGINT
) MaxN

Construct an aggregate which will keep track of the largest values passed through it.

Required arguments

Name	Type	Description
`value`	`BIGINT`, `DOUBLE PRECISION`, `TIMESTAMPTZ`	The values passed into the aggregate
`capacity`	`BIGINT`	The number of values to retain.

Returns

Column	Type	Description
`max_n`	`MaxN`	The compiled aggregate. Note that the exact type will be `MaxInts`, `MaxFloats`, or `MaxTimes` depending on the input type

into_array (
    agg MaxN
) BIGINT[] | DOUBLE PRECISION[] | TIMESTAMPTZ[]

Return the N largest values seen by the aggregate. The values are formatted as an array in decreasing order.

Required arguments

Name	Type	Description
`agg`	`MaxN`	The aggregate to return the results from. Note that the exact type here varies based on the type of data stored in the aggregate.

Returns

Column	Type	Description
`into_array`	`BIGINT[]`, `DOUBLE PRECISION[]`, `TIMESTAMPTZ[]`	The largest values seen while creating this aggregate.

Examples

Find the top 5 values from i * 13 % 10007 for i = 1 to 10000:


SELECT into_array(
    max_n(sub.val, 5))
FROM (
  SELECT (i * 13) % 10007 AS val 
  FROM generate_series(1,10000) as i
) sub;


into_array            
---------------------------------
{10006,10005,10004,10003,10002}

into_values (
    agg MaxN
) SETOF BIGINT | SETOF DOUBLE PRECISION | SETOF TIMESTAMPTZ

Return the N largest values seen by the aggregate.

Required arguments

Name	Type	Description
`agg`	`MaxN`	The aggregate to return the results from. Note that the exact type here varies based on the type of data stored.

Returns

Column	Type	Description
`into_values`	`SETOF BIGINT`, `SETOF DOUBLE PRECISION`, `SETOF TIMESTAMPTZ`	The largest values seen while creating this aggregate.

Examples

Find the top 5 values from i * 13 % 10007 for i = 1 to 10000:


SELECT into_values(
    max_n(sub.val, 5))
FROM (
  SELECT (i * 13) % 10007 AS val 
  FROM generate_series(1,10000) as i
) sub;


into_values 
-------------
10006
10005
10004
10003
10002

rollup(
    agg MaxN
) MaxN

This aggregate combines the aggregates generated by other max_n aggregates. Combined with an accessor, it returns the maximum values found across all the aggregated data.

Required arguments

Name	Type	Description
`agg`	`MaxN`	The aggregates being combined

Returns

Column	Type	Description
`rollup`	`MaxN`	An aggregate over all of the contributing values.

Extended examples

Get the 10 largest transactions from a table of stock trades

This example assumes that you have a table of stock trades in this format:


CREATE TABLE stock_sales(
    ts TIMESTAMPTZ,
    symbol TEXT,
    price FLOAT,
    volume INT
);

You can query for the 10 largest transactions each day:


WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as day,
        max_n(price * volume, 10) AS daily_max
    FROM stock_sales
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    day, as_array(daily_max)
FROM t;

max_n() functions

Related hyperfunction groups

Two-step aggregation

Functions in this group

Aggregate

Accessor

Rollup

Function details

max_n()

into_array()

into_values()

rollup()

Extended examples

Get the 10 largest transactions from a table of stock trades

max_n() functions

Introduction

Related hyperfunction groups

Two-step aggregation

Functions in this group

Aggregate

Accessor

Rollup

Function details

max_n()

into_array()

into_values()

rollup()

Extended examples

Get the 10 largest transactions from a table of stock trades

Related Content