min_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.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
Aggregate
- min_n
- Find the smallest values in a set of data
Accessor
- into_array
- Returns an array of the lowest values from a MinN aggregate
- into_values
- Returns the lowest values from a MinN aggregate
Rollup
- rollup
- Combine 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 into_array(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,min_n(price * volume, 10) AS daily_minFROM stock_salesGROUP BY time_bucket('1 day'::interval, ts))SELECTday, as_array(daily_max)FROM t;
Keywords
Found an issue on this page?Report an issue or Edit this page in GitHub.