equi_width_bins

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › 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)

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

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

The same cases, as the cluster returns them. They use the table and helpers from 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, 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 > 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.
• 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).

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

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: all the numeric functions.
• bar.
• min, max and aggregate functions: the aggregate query shapes that work.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.