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

Get the N smallest values from a column, with an associated piece of data per value. For example, you can return an accompanying column, or the full row.

The min_n_by() 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 with accompanying data, use max_n_by(). To get the N smallest values without accompanying data, use min_n().

Related hyperfunction groups

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

min_n_by: Track the smallest values and associated data in a set of values

into_values: Returns the lowest values and associated data from a MinNBy aggregate

rollup: Combine multiple MinNBy aggregates

Function details

min_n_by(
    value BIGINT | DOUBLE PRECISION | TIMESTAMPTZ,
    data ANYELEMENT,
    capacity BIGINT
) MinNBy

Construct an aggregate that keeps track of the smallest values passed through it, as well as some associated data which is passed alongside the value.

Required arguments

Name	Type	Description
`value`	`BIGINT`, `DOUBLE PRECISION`, `TIMESTAMPTZ`	The values passed into the aggregate
`data`	`ANYELEMENT`	The data associated with a particular value
`capacity`	`BIGINT`	The number of values to retain.

Returns

Column	Type	Description
`min_n_by`	`MinNBy`	The compiled aggregate. Note that the exact type is `MinByInts`, `MinByFloats`, or `MinByTimes` depending on the input type

into_values(
  agg MinNBy,
  dummy ANYELEMENT
) TABLE (
  value BIGINT | DOUBLE PRECISION | TIMESTAMPTZ, data ANYELEMENT
)

This returns the smallest values seen by the aggregate and the corresponding values associated with them. Note that PostgresQL requires an input argument with type matching the associated value in order to deterimine the response type.

Required arguments

Name	Type	Description
`agg`	`MinNBy`	The aggregate to return the results from. Note that the exact type here varies based on the type of data stored.
`dummy`	`ANYELEMENT`	This is purely to inform PostgresQL of the response type. A NULL cast to the appropriate type is typical.

Returns

Column	Type	Description
`into_values`	`TABLE (value BIGINT`, `DOUBLE PRECISION`, `TIMESTAMPTZ, data ANYELEMENT)`	The smallest values and associated data seen while creating this aggregate.

Examples

Find the bottom 5 values from i * 13 % 10007 for i = 1 to 10000, and the integer result of the operation that generated that modulus:


SELECT into_values(
    min_n_by(sub.mod, sub.div, 5),
    NULL::INT)
FROM (
  SELECT (i * 13) % 10007 AS mod, (i * 13) / 10007 AS div
  FROM generate_series(1,10000) as i
) sub;


into_values 
-------------
(1,9)
(2,5)
(3,1)
(4,10)
(5,6)

rollup(
    agg MinNBy
) MinNBy

This aggregate combines the aggregates generated by other minnby aggregates and returns the minimum values and associated data found across all the aggregated data.

Required arguments

Name	Type	Description
`agg`	`MinNBy`	The aggregates being combined

Returns

Column	Type	Description
`rollup`	`MinNBy`	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
);

Find the 10 smallest transactions in the table, what time they occurred, and what symbol was being traded.


SELECT 
    (data).time, 
    (data).symbol, 
    value AS transaction 
FROM
    into_values((
        SELECT min_n_by(price * volume, stock_sales, 10)
        FROM stock_sales
    ), 
    NULL::stock_sales);

min_n_by() functions

Related hyperfunction groups

Two-step aggregation

Functions in this group

Aggregate

Accessor

Rollup

Function details

min_n_by()

into_values()

rollup()

Extended examples

min_n_by() functions

Introduction

Related hyperfunction groups

Two-step aggregation

Functions in this group

Aggregate

Accessor

Rollup

Function details

min_n_by()

into_values()

rollup()

Extended examples

Related Content