var_samp

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Spread. An aggregate function that returns the sample variance of a numeric expression across the rows of a query: the mean squared distance of the values from their mean, dividing by n - 1. variance is the same function.

Syntax

var_samp(x)
variance(x)
var_samp(x) FILTER (WHERE condition)

Returns: DOUBLE.

Aliases: variance.

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_aggregate_var_samp. 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 two tables: seven sensor readings, one of which has no values, and seven special floating-point values (NaN, inf, 1.0 and values near the DOUBLE limits):

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_aggregate_var_samp"'

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"
SPECIAL = f"{SCHEMA}.special"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, sensor VARCHAR, temperature DOUBLE, humidity INTEGER, ok BOOLEAN)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 's1', 18.5, 40, true), (2, 's1', 21.0, 45, true), (3, 's2', 19.5, 50, false), "
    "(4, 's2', 24.0, 55, true), (5, 's3', 22.5, 60, true), (6, 's3', NULL, NULL, NULL), "
    "(7, 's3', 18.5, 40, true)")
run(f"CREATE TABLE IF NOT EXISTS {SPECIAL} (id BIGINT, x DOUBLE)")
for row in ["(1, 'NaN'::DOUBLE)", "(2, 'inf'::DOUBLE)", "(3, 1.0)", "(4, 1.0e308)", "(5, 1.0e308)",
            "(6, 5e-324)", "(7, -1.0e308)"]:
    run(f"INSERT INTO {SPECIAL} VALUES {row}")

[]
[]
[(7,)]
[]
[(1,)]
[(1,)]
[(1,)]
[(1,)]
[(1,)]
[(1,)]
[(1,)]

What it does

var_samp(x) reads x on every row the query keeps, skips the NULLs, and computes the sample variance of the n values that remain:

1. the mean of the values;
2. for each value, the square of its difference from the mean;
3. the sum of those squares, divided by n - 1.

Dividing by n - 1 rather than n is what makes it the sample form: use it when the rows are a sample of a larger population. When the rows are the whole population, use var_pop, which divides by n. With one value there's nothing to divide by, so var_samp() returns NULL where var_pop() returns 0.0.

The variance is in the square of the values' unit (degrees squared, for temperatures). Its square root, in the values' own unit, is the standard deviation, stddev_samp.

Every input type is converted to DOUBLE, so integer and DECIMAL columns return a DOUBLE too.

NULLs and edge cases

The same cases, as the cluster returns them. They use the tables and helpers from Set up the examples:

run(f"SELECT var_samp(temperature), var_samp(temperature) FILTER (WHERE id <> 6) FROM {TABLE}")
run(f"SELECT var_samp(temperature) FROM {TABLE} WHERE id = 1")
run(f"SELECT var_samp(temperature) FROM {TABLE} WHERE id IN (1, 6)")
run(f"SELECT var_samp(temperature) FROM {TABLE} WHERE id IN (1, 7)")
run(f"SELECT var_samp(temperature) FROM {TABLE} WHERE id = 6")
run(f"SELECT var_samp(temperature) FROM {TABLE} WHERE id > 100")
run("SELECT var_samp(NULL), var_samp(2.5)")
run(f"SELECT var_samp(5) FROM {TABLE}")
run("SELECT var_samp(1e8 + x), var_samp(1e15 + x) FROM range(1000) t(x)")
run(f"SELECT var_samp(x) FILTER (WHERE id IN (2, 3)) FROM {SPECIAL}")
run(f"SELECT var_samp(x) FILTER (WHERE id IN (1, 3)) FROM {SPECIAL}")
run(f"SELECT var_samp(x) FILTER (WHERE id >= 4) FROM {SPECIAL}")
run(f"SELECT var_samp(x) FILTER (WHERE id IN (4, 5)) FROM {SPECIAL}")
run(f"SELECT var_samp(DISTINCT temperature) FROM {TABLE}")
run(f"SELECT var_samp(ok) FROM {TABLE}")
run(f"SELECT var_samp(ok::INTEGER) FROM {TABLE}")

[(5.066666666666667, 5.066666666666667)]
[(None,)]
[(None,)]
[(0.0,)]
[(None,)]
[(None,)]
[(None, None)]
[(0.0,)]
[(83416.66666666667, 83416.66666666667)]
OSError: VARSAMP is out of range!
OSError: VARSAMP is out of range!
OSError: VARSAMP is out of range!
[(0.0,)]
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
InternalError: Binder Error: No function matches the given name and argument types 'var_samp(BOOLEAN)'. You might need to add explicit type casts.
[(0.16666666666666669,)]

Examples

The sample variance of the temperatures, with both names of the function. The empty reading is skipped:

run(f"SELECT var_samp(temperature), variance(temperature) FROM {TABLE}")

[(5.066666666666667, 5.066666666666667)]

An INTEGER column returns a DOUBLE:

run(f"SELECT var_samp(humidity) FROM {TABLE}")

[(66.66666666666667,)]

The variance and its square root, the standard deviation, side by side:

run(f"SELECT var_samp(temperature), sqrt(var_samp(temperature)), stddev_samp(temperature) FROM {TABLE}")

[(5.066666666666667, 2.250925735484551, 2.250925735484551)]

The variance of each sensor, in one query. GROUP BY isn't available, so each sensor gets its own FILTER clause:

run(f"SELECT var_samp(temperature) FILTER (WHERE sensor = 's1') AS s1, "
    f"var_samp(temperature) FILTER (WHERE sensor = 's2') AS s2 FROM {TABLE}")

[(3.125, 10.125)]

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). An aggregate can't go in WHERE, so what matters is the plan of the aggregate query:

• The scan reads the columns the query uses. A WHERE condition that VAST DataBase can evaluate, such as a comparison of a column with a constant, is applied in the scan, so the rows it rejects never reach the Query Engine. It doesn't appear as a step in the plan.
• A WHERE condition the scan can't evaluate, such as one on a function of a column, appears as a FILTER step above the scan.
• One UNGROUPED_AGGREGATE step in the Query Engine computes var_samp(), and every other aggregate in the SELECT list, over the rows it receives, and returns one row.

The plan of a var_samp() with a pushed-down WHERE condition has only the aggregate step and the scan:

plan(f"SELECT var_samp(temperature) FROM {TABLE} WHERE id > 1")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│   var_samp(temperature)   │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│        temperature        │
│                           │
└───────────────────────────┘

Make it faster

• Restrict the rows in WHERE, not in FILTER, when every aggregate needs the same rows. A FILTER (WHERE ...) condition is evaluated in the aggregate step, after the scan has sent every row and the column the condition reads. The plan shows it inside the UNGROUPED_AGGREGATE step:

plan(f"SELECT var_samp(temperature) FILTER (WHERE id > 1) FROM {TABLE}")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│   var_samp(temperature)   │
│   FILTER (WHERE (id > 1)) │
│      Filter: (id > 1)     │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│        temperature        │
│                           │
└───────────────────────────┘

• Compute the variance, the standard deviation and the mean in one query. All the aggregates of a query share one scan and one aggregate step.
• Keep functions of a column out of WHERE when a plain comparison does the job. A condition such as abs(temperature) > 5 adds a FILTER step in the Query Engine; temperature > 5 is applied in the scan.

Limitations and errors

• Numbers only. A BOOLEAN, VARCHAR, BLOB, DATE, TIME, TIMESTAMP, INTERVAL or list fails with Binder Error: No function matches the given name and argument types 'var_samp(...)'. You might need to add explicit type casts. Cast a BOOLEAN to INTEGER first.
• No DISTINCT or ORDER BY inside the call. var_samp(DISTINCT x) and var_samp(x ORDER BY y) fail with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
• Infinite, NaN and very large values stop the query. If the values include inf, -inf or NaN, or are so large that the computation overflows DOUBLE, the query fails with VARSAMP is out of range!. Leave such rows out in WHERE, or scale the values down first.
• No GROUP BY. SELECT sensor, var_samp(temperature) ... GROUP BY sensor fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. Use one FILTER clause per group, as in Examples, or run the query in Trino or Spark.
• As a window function, only OVER (ROWS UNBOUNDED PRECEDING) runs, over the rows in the order the scan returns them. OVER (), OVER (ORDER BY ...) and OVER (PARTITION BY ...) fail with The operator 'WINDOW' type=6 is currently not supported. See window functions.
• Other SQL dialects' names aren't available. var, varp, variance_samp and variance_pop fail with Catalog Error: Scalar Function with name ... does not exist!. Use var_samp or variance, or var_pop for the population form.

run(f"SELECT var_samp(temperature ORDER BY id) FROM {TABLE}")
run(f"SELECT sensor, var_samp(temperature) FROM {TABLE} GROUP BY sensor")
run(f"SELECT var(temperature) FROM {TABLE}")
run(f"SELECT variance_samp(temperature) FROM {TABLE}")

InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: Catalog Error: Scalar Function with name var does not exist!
InternalError: Catalog Error: Scalar Function with name variance_samp does not exist!

Similar functions

run(f"SELECT var_samp(temperature), var_pop(temperature), stddev_samp(temperature), "
    f"stddev_pop(temperature) FROM {TABLE}")
run(f"SELECT var_samp(temperature), var_pop(temperature), stddev_samp(temperature), "
    f"stddev_pop(temperature) FROM {TABLE} WHERE id = 1")

[(5.066666666666667, 4.222222222222222, 2.250925735484551, 2.0548046676563256)]
[(None, 0.0, None, 0.0)]

Clean up:

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

[]
[]
[]

See also

• Aggregate and analytic functions: all the aggregates.
• SELECT: aggregates in a query, and EXPLAIN: how to read a plan.
• Pushdowns: which WHERE conditions VAST DataBase evaluates.