var_pop

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Spread. Returns the population variance of the values in a numeric column: the average of the squared differences from their mean, dividing by the number of values (n). NULLs are skipped.

Syntax

var_pop(x)
var_pop(x) FILTER (WHERE condition)

Returns: DOUBLE, whatever the numeric input type.

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_pop. 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: six sensor readings (the last has no values) and a table of extreme DOUBLE 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_aggregate_var_pop"'

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"
EXTREMES = f"{SCHEMA}.extremes"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, sensor VARCHAR, temperature DOUBLE, qty INTEGER, price DECIMAL(10,2), ok BOOLEAN)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 's1', 18.5, 3, 2.50, true), (2, 's1', 21.0, 5, 4.00, true), "
    "(3, 's2', 19.5, 2, 2.50, false), (4, 's2', 24.0, 8, 6.25, true), "
    "(5, 's3', 22.5, 4, 3.75, true), (6, 's3', NULL, NULL, NULL, NULL)")
run(f"CREATE TABLE IF NOT EXISTS {EXTREMES} (id BIGINT, x DOUBLE)")
run(f"INSERT INTO {EXTREMES} VALUES "
    "(1, 1.0e308), (2, 1.0e308), (3, 5e-324), (4, -1.0e308), "
    "(5, 'inf'::DOUBLE), (6, 'NaN'::DOUBLE), (7, 1.0)")

[]
[]
[(6,)]
[]
[(7,)]

What it does

var_pop(x) takes the non-NULL values of x (n of them), computes their mean, and returns the sum of the squared differences from the mean divided by n. It's the variance of the values taken as a whole population. To estimate the variance of a larger population from a sample, divide by n − 1 instead: that's var_samp.

Integers, unsigned integers, FLOAT and DECIMAL values are converted to DOUBLE, and the result is always a DOUBLE. The square root of var_pop(x) is the population standard deviation, stddev_pop.

Like every aggregate in the VAST Query Engine, var_pop returns one row for the whole query: there's no GROUP BY.

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_pop(temperature), count(*), count(temperature) FROM {TABLE}")
run(f"SELECT var_pop(temperature) FROM {TABLE} WHERE id = 6")
run(f"SELECT var_pop(temperature) FROM {TABLE} WHERE id > 100")
run(f"SELECT var_pop(temperature), var_samp(temperature) FROM {TABLE} WHERE id = 1")
run(f"SELECT var_pop(5) FROM {TABLE}")
run("SELECT var_pop(NULL)")
run("SELECT var_pop(x), var_pop(1e15 + x) FROM range(1000) t(x)")
run(f"SELECT var_pop(x) FILTER (WHERE id IN (1, 2)) FROM {EXTREMES}")
run(f"SELECT var_pop(x) FILTER (WHERE id IN (1, 2, 3, 4)) FROM {EXTREMES}")
run(f"SELECT var_pop(x) FILTER (WHERE id IN (5, 7)) FROM {EXTREMES}")
run(f"SELECT var_pop(x) FILTER (WHERE id IN (6, 7)) FROM {EXTREMES}")
run(f"SELECT var_pop(sensor) FROM {TABLE}")
run(f"SELECT var_pop(ok) FROM {TABLE}")

[(3.939999999999999, 6, 5)]
[(None,)]
[(None,)]
[(0.0, None)]
[(0.0,)]
[(None,)]
[(83333.25, 83333.25)]
[(0.0,)]
OSError: VARPOP is out of range!
OSError: VARPOP is out of range!
OSError: VARPOP is out of range!
InternalError: Binder Error: No function matches the given name and argument types 'var_pop(VARCHAR)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'var_pop(BOOLEAN)'. You might need to add explicit type casts.

Examples

The population variance of each numeric column. The INTEGER and DECIMAL columns give a DOUBLE too:

run(f"SELECT var_pop(temperature), var_pop(qty), var_pop(price) FROM {TABLE}")

[(3.939999999999999, 4.24, 1.8850000000000002)]

The variance of one sensor's readings, with the condition in WHERE:

run(f"SELECT var_pop(temperature) FROM {TABLE} WHERE sensor = 's2'")

[(5.0625,)]

The variance of several subsets in one query, with FILTER:

run(f"SELECT var_pop(temperature) FILTER (WHERE ok), "
    f"var_pop(temperature) FILTER (WHERE NOT ok), var_pop(temperature) FROM {TABLE}")

[(4.124999999999998, 0.0, 3.939999999999999)]

A BOOLEAN column needs a cast. The variance of ok::INTEGER measures how mixed the true and false values are:

run(f"SELECT var_pop(ok::INTEGER) FROM {TABLE}")

[(0.16,)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. An aggregate query has two steps, which EXPLAIN shows:

1. A scan in VAST DataBase (VAST_SCAN) reads only the columns the query uses, and evaluates the WHERE conditions it can push down, so rows that don't match never leave storage. A pushed-down condition doesn't appear in the plan.
2. An aggregate step (UNGROUPED_AGGREGATE) in the Query Engine computes var_pop over the rows the scan returns, and returns one row.

With the condition in WHERE, the scan reads only temperature and evaluates sensor = 's2' itself:

plan(f"SELECT var_pop(temperature) FROM {TABLE} WHERE sensor = 's2'")

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

With the condition in FILTER, the scan sends every row, with the ok column as well, and the aggregate step applies the condition:

plan(f"SELECT var_pop(temperature) FILTER (WHERE ok) FROM {TABLE}")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│    var_pop(temperature)   │
│  FILTER (WHERE ok) Filter:│
│             ok            │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ok            │
│        temperature        │
│                           │
└───────────────────────────┘

Make it faster

• Put a condition shared by every aggregate in WHERE. A comparison of a column with a constant is pushed down to VAST DataBase, so fewer rows reach the Query Engine. FILTER is evaluated in the aggregate step, after the scan has sent every row; use it when different aggregates need different conditions.
• Compute the spread statistics together. var_pop, var_samp, stddev_pop, avg and count in one SELECT share one scan.
• Keep the condition on the raw column. A function of a column in WHERE, such as abs(qty) > 5, isn't pushed down: it adds a FILTER step in the Query Engine. See Where filters run.

Limitations and errors

• Numbers only. A VARCHAR, BOOLEAN, date or time, BLOB, INTERVAL or list fails with No function matches the given name and argument types 'var_pop(VARCHAR)' (with the type you passed). The error lists the one form: var_pop(DOUBLE) -> DOUBLE. Cast a BOOLEAN to INTEGER.
• One argument. var_pop(x, 1) fails with the same error, for 'var_pop(DOUBLE, INTEGER)'.
• No DISTINCT or ORDER BY inside. var_pop(DISTINCT x) and var_pop(x ORDER BY id) fail with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
• No GROUP BY. A grouped query fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. Run one query per group with WHERE, use FILTER for a few groups, or use Trino or Spark.
• Not in WHERE. An aggregate in WHERE fails with WHERE clause cannot contain aggregates!. To test the result, use HAVING.
• As a window function, only a running variance. var_pop(x) OVER (ROWS UNBOUNDED PRECEDING) runs, in the order rows reach the Query Engine; OVER () and windows with ORDER BY or PARTITION BY fail with The operator 'WINDOW' type=6 is currently not supported. See window functions.
• Infinite results. If the variance is beyond the DOUBLE range, or the values include inf, -inf or NaN, the query fails with VARPOP is out of range!. Exclude those rows in WHERE.
• Other SQL dialects' names aren't available. var and varp fail with Scalar Function with name ... does not exist!. Use var_pop.

run(f"SELECT var_pop(temperature, 1) FROM {TABLE}")
run(f"SELECT var_pop(DISTINCT temperature) FROM {TABLE}")
run(f"SELECT sensor, var_pop(temperature) FROM {TABLE} GROUP BY sensor")
run(f"SELECT id FROM {TABLE} WHERE var_pop(temperature) > 1")
run(f"SELECT var_pop(temperature) FROM {TABLE} HAVING var_pop(temperature) > 1")
run(f"SELECT id, var_pop(temperature) OVER () FROM {TABLE}")
run(f"SELECT varp(temperature) FROM {TABLE}")

InternalError: Binder Error: No function matches the given name and argument types 'var_pop(DOUBLE, INTEGER)'. You might need to add explicit type casts.
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: Binder Error: WHERE clause cannot contain aggregates!
[(3.939999999999999,)]
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: Catalog Error: Scalar Function with name varp does not exist!

Similar functions

The four on the readings, and on one reading:

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

[(3.939999999999999, 4.924999999999999, 4.924999999999999, 1.9849433241279206, 1.9849433241279206, 2.2192341021172144)]
[(0.0, None, 0.0, None)]

Clean up:

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

[]
[]
[]

See also

• Aggregate and analytic functions: all the aggregates.
• FILTER (WHERE ...): restrict one aggregate.
• SELECT, EXPLAIN and pushdowns.