Versions:

Introduction

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

max_n
ExperimentalFind the largest values in a set of data

Accessor

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

Rollup

rollup
ExperimentalCombine multiple MaxN aggregates
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
NameTypeDescription
valueBIGINT, DOUBLE PRECISION, TIMESTAMPTZThe values passed into the aggregate
capacityBIGINTThe number of values to retain.
Returns
ColumnTypeDescription
max_nMaxNThe 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
NameTypeDescription
aggMaxNThe aggregate to return the results from. Note that the exact type here varies based on the type of data stored in the aggregate.
Returns
ColumnTypeDescription
into_arrayBIGINT[], 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 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}
into_values (
agg MaxN
) SETOF BIGINT | SETOF DOUBLE PRECISION | SETOF TIMESTAMPTZ

Return the N largest values seen by the aggregate.

Required arguments
NameTypeDescription
aggMaxNThe aggregate to return the results from. Note that the exact type here varies based on the type of data stored.
Returns
ColumnTypeDescription
into_valuesSETOF BIGINT, SETOF DOUBLE PRECISION, SETOF TIMESTAMPTZThe largest values seen while creating this aggregate.
Examples

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

SELECT toolkit_experimental.into_values(
toolkit_experimental.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
NameTypeDescription
aggMaxNThe aggregates being combined
Returns
ColumnTypeDescription
rollupMaxNAn aggregate over all of the contributing values.

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,
toolkit_experimental.max_n(price * volume, 10) AS daily_max
FROM stock_sales
GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
day, toolkit_experimental.as_array(daily_max)
FROM t;

Found an issue on this page?

Report an issue!

Keywords

Related Content