---
title: "equi_width_bins"
slug: "dev-database-reference-sql-functions-numeric-equi-width-bins"
updated: 2026-10-30T18:18:51Z
published: 2026-10-30T18:18:51Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-numeric-equi-width-bins"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# equi_width_bins

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Numeric and math functions](/develop/docs/dev-database-reference-sql-functions-numeric) › Binning.* Splits the range from `min` to `max` into `n` bins of equal width and returns a list of their upper bounds. With `nice` set to true, it moves the bounds to round numbers, which can change the number of bins. It takes numbers, dates and timestamps.

## Syntax

```
equi_width_bins(min, max, n, nice)
```

| Argument | Type | Description |
| --- | --- | --- |
| `min` | A number, `DATE` or `TIMESTAMP` | The start of the range. |
| `max` | The same type as `min` | The end of the range. It can't be below `min`. |
| `n` | `BIGINT` | The number of bins, 1 or more. |
| `nice` | `BOOLEAN` | `true` to move the bounds to round numbers; `false` to keep exactly `n` bins of equal width. |

**Returns:** a list of the type of `min` and `max`, such as `INTEGER[]`, `BIGINT[]`, `DOUBLE[]`, `DATE[]` or `TIMESTAMP[]`.

## Set up the examples

The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_numeric_equi_width_bins`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the `run()` and `plan()` helpers, and creates a table of five daily readings: a temperature, a visitor count and the day. The last row has no values:

```
import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_numeric_equi_width_bins"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, temp DOUBLE, visitors BIGINT, day DATE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, -3.5, 120, DATE '2026-01-04'), "
    "(2, 4.0, 340, DATE '2026-03-15'), "
    "(3, 12.25, 95, DATE '2026-06-21'), "
    "(4, 21.0, 410, DATE '2026-09-30'), "
    "(5, NULL, NULL, NULL)")
```

```
[]
[]
[(5,)]
```

## What it does

`equi_width_bins(min, max, n, nice)` returns one value per bin: its upper bound. The first bin starts at `min`, and each bin ends where the next one starts.

- **With `nice` false,** the range is split into exactly `n` bins of equal width, and the last bound is `max`. For integer arguments the bounds are integers, cut down: `equi_width_bins(0, 97, 4, false)` is `[24, 48, 72, 97]`.
- **With `nice` true,** the bounds are moved to round numbers, so the bins can cover a little more than the range and their number can differ from `n`: `equi_width_bins(0, 97, 4, true)` is `[20, 40, 60, 80, 100]`, five bins of 20.

| Call | Result |
| --- | --- |
| `equi_width_bins(0, 100, 4, false)` | `[25, 50, 75, 100]` |
| `equi_width_bins(0, 100, 4, true)` | `[20, 40, 60, 80, 100]` |
| `equi_width_bins(0.0, 1.0, 3, false)` | `[0.3333333333333334, 0.6666666666666667, 1.0]` |
| `equi_width_bins(0.0, 1.0, 3, true)` | `[0.5, 1.0]` |
| `equi_width_bins(-7, 13, 5, true)` | `[-5, 0, 5, 10, 15]` |
| `equi_width_bins(DATE &apos;2026-01-01&apos;, DATE &apos;2026-12-31&apos;, 4, true)` | four dates: 2026-04-02, 2026-07-02, 2026-10-01 and 2026-12-31 |

The list has the type of the arguments: integers give `INTEGER[]`, decimals and doubles give `DOUBLE[]`, and dates and timestamps give `DATE[]` and `TIMESTAMP[]`.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| A `NULL` `max`, `n` or `nice` | `NULL` |
| `equi_width_bins(0, 0, 4, true)`, `min` equal to `max` | `[0]` |
| `equi_width_bins(0, 100, 1, true)`, one bin | `[100]` |
| `equi_width_bins(0, 1, 3, false)`, integers narrower than the bins | `[0, 1]`: bounds that cut to the same integer appear once |
| `equi_width_bins(2.45, 99999.99, 3, true)` | `[50000.0, 100000.0]`, a `DOUBLE[]` |
| `equi_width_bins(0, 1000000, 100000, false)` | a list of 100000 bounds |
| `n` of 0 or less | Error: `Invalid input for bin function - there must be &gt; 0 bins` |
| `max` below `min` | Error: `Invalid input for bin function - max value is smaller than min value` |
| Infinity or NaN as `max` | Error: `equi_width_bucket does not support infinite or nan as min/max value` |
| Three arguments, without `nice` | Error: `No function matches the given name and argument types` |
| Two strings | Error: `Could not choose a best candidate function for the function call` |

The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-numeric-equi-width-bins#set-up-the-examples):

```
run("SELECT equi_width_bins(0, NULL, 4, true), equi_width_bins(0, 100, NULL, true), equi_width_bins(0, 100, 4, NULL)")
run("SELECT equi_width_bins(0, 0, 4, true), equi_width_bins(0, 100, 1, true), equi_width_bins(0, 1, 3, false)")
run("SELECT equi_width_bins(2.45, 99999.99, 3, true), typeof(equi_width_bins(2.45, 99999.99, 3, true))")
run("SELECT length(equi_width_bins(0, 1000000, 100000, false))")
run("SELECT equi_width_bins(0, 100, 0, true)")
run("SELECT equi_width_bins(100, 0, 4, true)")
run("SELECT equi_width_bins(0, 'inf'::DOUBLE, 3, false)")
run("SELECT equi_width_bins(0, 100, 4)")
run("SELECT equi_width_bins('a', 'z', 4, true)")
```

```
[(None, None, None)]
[([0], [100], [0, 1])]
[([50000.0, 100000.0], 'DOUBLE[]')]
[(100000,)]
OSError: Invalid input for bin function - there must be > 0 bins
OSError: Invalid input for bin function - max value is smaller than min value
OSError: equi_width_bucket does not support infinite or nan as min/max value
InternalError: Binder Error: No function matches the given name and argument types 'equi_width_bins(INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "equi_width_bins(STRING_LITERAL, STRING_LITERAL, INTEGER_LITERAL, BOOLEAN)". In order to select one, please add explicit type casts.
```

The calls in [What it does](/develop/docs/dev-database-reference-sql-functions-numeric-equi-width-bins#what-it-does), and the types of their results:

```
run("SELECT equi_width_bins(0, 97, 4, false), equi_width_bins(0, 97, 4, true)")
run("SELECT equi_width_bins(0, 100, 4, false), equi_width_bins(0, 100, 4, true), equi_width_bins(-7, 13, 5, true)")
run("SELECT equi_width_bins(0.0, 1.0, 3, false), equi_width_bins(0.0, 1.0, 3, true)")
run("SELECT equi_width_bins(DATE '2026-01-01', DATE '2026-12-31', 4, true)")
run("SELECT typeof(equi_width_bins(0, 100, 4, true)), typeof(equi_width_bins(0.0::DOUBLE, 1.0, 3, true))")
```

```
[([24, 48, 72, 97], [20, 40, 60, 80, 100])]
[([25, 50, 75, 100], [20, 40, 60, 80, 100], [-5, 0, 5, 10, 15])]
[([0.3333333333333334, 0.6666666666666667, 1.0], [0.5, 1.0])]
[([datetime.date(2026, 4, 2), datetime.date(2026, 7, 2), datetime.date(2026, 10, 1), datetime.date(2026, 12, 31)],)]
[('INTEGER[]', 'DOUBLE[]')]
```

## Examples

Bins for the temperature column, from its smallest to its largest value. `min()` and `max()` read the range from the table, and `equi_width_bins()` splits it:

```
run(f"SELECT equi_width_bins(min(temp), max(temp), 4, false), equi_width_bins(min(temp), max(temp), 4, true) FROM {TABLE}")
```

```
[([2.625, 8.75, 14.875, 21.0], [0.0, 5.0, 10.0, 15.0, 20.0, 25.0])]
```

Bins for an integer column and for a date column:

```
run(f"SELECT equi_width_bins(min(visitors), max(visitors), 4, true) FROM {TABLE}")
run(f"SELECT equi_width_bins(min(day), max(day), 4, false) FROM {TABLE}")
```

```
[([100, 200, 300, 400, 500],)]
[([datetime.date(2026, 3, 12), datetime.date(2026, 5, 18), datetime.date(2026, 7, 24), datetime.date(2026, 9, 30)],)]
```

The same, in a `WITH` clause, so that a later query step can use the bounds by name:

```
run(f"WITH b AS (SELECT equi_width_bins(min(visitors), max(visitors), 4, false) AS bounds FROM {TABLE}) SELECT bounds FROM b")
```

```
[([173, 252, 331, 410],)]
```

## How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). With a table, `equi_width_bins()` usually takes the result of aggregates such as `min()` and `max()`: the scan sends the column, an aggregate step (`UNGROUPED_AGGREGATE`) in the Query Engine computes the aggregates, and then `equi_width_bins()` runs once on their result. With constant arguments, it doesn't read a table at all.

The plan of the temperature query shows the aggregate above the scan:

```
plan(f"SELECT equi_width_bins(min(temp), max(temp), 4, true) FROM {TABLE}")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│ equi_width_bins(min(temp),│
│     max(temp), 4, true)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│         min(temp)         │
│         max(temp)         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│     Projections: temp     │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Narrow the rows with a pushed-down condition.** A comparison of a column with a constant, such as `temp &gt; 0`, is pushed down to VAST DataBase, so the rows that don't match never leave storage and aren't part of `min()` and `max()`:

```
plan(f"SELECT equi_width_bins(min(temp), max(temp), 4, true) FROM {TABLE} WHERE temp > 0")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│ equi_width_bins(min(temp),│
│     max(temp), 4, true)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│         min(temp)         │
│         max(temp)         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│     Projections: temp     │
│                           │
└───────────────────────────┘
```

- **Pass the range if you know it.** `equi_width_bins(-10, 30, 4, true)` needs no scan.

## Limitations and errors

- **`max` can't be below `min`, and `n` must be 1 or more.** Otherwise the query fails; see [NULLs and edge cases](/develop/docs/dev-database-reference-sql-functions-numeric-equi-width-bins#nulls-and-edge-cases).
- **No infinity or NaN.** A `max` that is infinite or NaN fails with `equi_width_bucket does not support infinite or nan as min/max value`. Filter those rows out, for example with `WHERE isfinite(temp)`, before `min()` and `max()`.
- **All four arguments.** There is no default for `nice`; pass `true` or `false`.
- **It gives the bounds, not the bin of each row.** There is no `width_bucket`: it fails with `Scalar Function with name width_bucket does not exist!`. To number the bin of a value, compute it with `floor()`: for 4 bins from 0 to 10, `least(greatest(floor((5 - 0) / (10 - 0) * 4) + 1, 0), 5)` is `3.0`.
- **No `GROUP BY` to count the rows per bin.** The Query Engine doesn't run `GROUP BY` ([not supported](/develop/docs/dev-database-reference-sql-not-supported)).

```
run("SELECT width_bucket(5, 0, 10, 4)")
run("SELECT least(greatest(floor((5 - 0) / (10 - 0) * 4) + 1, 0), 5)")
```

```
InternalError: Catalog Error: Scalar Function with name width_bucket does not exist!
[(3.0,)]
```

## Similar functions

| Function | Use it to |
| --- | --- |
| `equi_width_bins(min, max, n, nice)` | split a range into bins of equal width: `equi_width_bins(0, 100, 4, false)` is `[25, 50, 75, 100]` |
| [bar](/develop/docs/dev-database-reference-sql-functions-numeric-bar) | draw one value as a bar of text: `bar(25, 0, 100, 20)` is 5 full blocks, then 15 spaces |
| `floor()` arithmetic | number the bin of each value; see [Limitations and errors](/develop/docs/dev-database-reference-sql-functions-numeric-equi-width-bins#limitations-and-errors) |

```
run("SELECT bar(25, 0, 100, 20), equi_width_bins(0, 100, 4, false)")
```

```
[('█████               ', [25, 50, 75, 100])]
```

Clean up:

```
run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
```

```
[]
[]
```

## See also

- [Numeric and math functions](/develop/docs/dev-database-reference-sql-functions-numeric): all the numeric functions.
- [bar](/develop/docs/dev-database-reference-sql-functions-numeric-bar).
- [min](/develop/docs/dev-database-reference-sql-functions-aggregate-min), [max](/develop/docs/dev-database-reference-sql-functions-aggregate-max) and [aggregate functions](/develop/docs/dev-database-reference-sql-functions-aggregate): the aggregate query shapes that work.
- [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).
- [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.