# 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!