min_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 smallest values from a column.
The min_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 largest values, use max_n()
. To get the N smallest
values with accompanying data, use min_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
- min_n
- ExperimentalFind the smallest values in a set of data
Accessor
- into_array
- ExperimentalReturns an array of the lowest values from a MinN aggregate
- into_values
- ExperimentalReturns the lowest values from a MinN aggregate
Rollup
- rollup
- ExperimentalCombine multiple MinN aggregates
min_n(value BIGINT | DOUBLE PRECISION | TIMESTAMPTZ,capacity BIGINT) MinN
Construct an aggregate that keeps track of the smallest 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 |
---|---|---|
min_n | MinN | The compiled aggregate. Note that the exact type is MinInts , MinFloats , or MinTimes depending on the input type |
into_array (agg MinN) BIGINT[] | DOUBLE PRECISION[] | TIMESTAMPTZ[]
Returns the N lowest values seen by the aggregate. The values are formatted as an array in increasing order.
Required arguments
Name | Type | Description |
---|---|---|
agg | MinN | 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_array | BIGINT[] , DOUBLE PRECISION[] , TIMESTAMPTZ[] | The lowest values seen while creating this aggregate. |
Examples
Find the bottom 5 values from i * 13 % 10007
for i = 1 to 10000:
SELECT toolkit_experimental.into_array(toolkit_experimental.min_n(sub.val, 5))FROM (SELECT (i * 13) % 10007 AS valFROM generate_series(1,10000) as i) sub;
into_array---------------------------------{1,2,3,4,5}
into_values (agg MinN) SETOF BIGINT | SETOF DOUBLE PRECISION | SETOF TIMESTAMPTZ
Return the N lowest values seen by the aggregate.
Required arguments
Name | Type | Description |
---|---|---|
agg | MinN | 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 lowest values seen while creating this aggregate. |
Examples
Find the bottom 5 values from i * 13 % 10007
for i = 1 to 10000:
SELECT toolkit_experimental.into_array(toolkit_experimental.min_n(sub.val, 5))FROM (SELECT (i * 13) % 10007 AS valFROM generate_series(1,10000) as i) sub;
into_values---------------------------------12345
rollup(agg MinN) MinN
This aggregate combines the aggregates generated by other min_n
aggregates and returns the minimum values found across all the
aggregated data.
Required arguments
Name | Type | Description |
---|---|---|
agg | MinN | The aggregates being combined |
Returns
Column | Type | Description |
---|---|---|
rollup | MinN | 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 smallest transactions each day:
WITH t as (SELECTtime_bucket('1 day'::interval, ts) as day,toolkit_experimental.min_n(price * volume, 10) AS daily_minFROM 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