min_n() functions

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().

Two-step aggregation

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:

More efficient because multiple accessors can reuse the same aggregate
Easier to reason about performance, because aggregation is separate from final computation
Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
Can perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result

To learn more, see the blog post on two-step aggregates.

Functions in this group

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

Function details

min_n()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

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

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

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 val 
  FROM generate_series(1,10000) as i
) sub;
into_array            
---------------------------------
{1,2,3,4,5}

into_values()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

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 val 
  FROM generate_series(1,10000) as i
) sub;
into_values            
---------------------------------
1
2
3
4
5

rollup()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

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.

Extended examples

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