max_n() functions
ToolkitTimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale Cloud. Click to learn more.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
| 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 toolkit_experimental.into_array(toolkit_experimental.max_n(sub.val, 5))FROM (SELECT (i * 13) % 10007 AS valFROM 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 toolkit_experimental.into_values(toolkit_experimental.max_n(sub.val, 5))FROM (SELECT (i * 13) % 10007 AS valFROM generate_series(1,10000) as i) sub;
into_values-------------1000610005100041000310002
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. |
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 (SELECTtime_bucket('1 day'::interval, ts) as day,toolkit_experimental.max_n(price * volume, 10) AS daily_maxFROM stock_salesGROUP BY time_bucket('1 day'::interval, ts))SELECTday, toolkit_experimental.as_array(daily_max)FROM t;
Found an issue on this page?
Report an issue!Keywords