max_n() functions

The 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 . To get the N largest values with accompanying data, use max_n_by().

• max_n_by()
• min_n_by()

Hide content

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:

1. More efficient because multiple accessors can reuse the same aggregate
2. Easier to reason about performance, because aggregation is separate from final computation
3. Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates

To learn more, see the .

warning

This function group includes some experimental functions. Experimental functions might change or be removed in future releases. We do not recommend using them in production. Experimental functions are marked with an Experimental tag.

Aggregate

ExperimentalFind the largest values in a set of data

Accessor

into_array

Returns an array of the highest values from a MaxN aggregate

into_values

Returns the highest values from a MaxN aggregate

ExperimentalCombine multiple MaxN aggregates

max_n()

Introduced in Toolkit v1.12.0

Hide content

`

    value BIGINT | DOUBLE PRECISION | TIMESTAMPTZ,
    capacity BIGINT

`

) MaxN

`

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

Required arguments

Returns

into_array()

Introduced in Toolkit v1.12.0

Hide content

`

into_array (

`

    agg MaxN

`

`

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

Required arguments

Returns

Examples

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

SELECT toolkit_experimental.into_array(
    toolkit_experimental.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}

Introduced in Toolkit v1.12.0

`

into_values (

`

    agg MaxN

`

) SETOF BIGINT | SETOF DOUBLE PRECISION | SETOF TIMESTAMPTZ

`

Return the N largest values seen by the aggregate.

Required arguments

Returns

Examples

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

rollup()

Introduced in Toolkit v1.12.0

Hide content

`

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

Returns

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
);