stddev_pop

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Spread. An aggregate function that returns the population standard deviation of a numeric expression across the rows of a query: how far the values typically are from their mean, dividing by n. Use it when the rows are the whole population; for a sample, use stddev_samp.

Syntax

stddev_pop(x)
stddev_pop(x) FILTER (WHERE condition)

Returns: DOUBLE.

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_stddev_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: 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_stddev_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"
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

stddev_pop(x) reads x on every row the query keeps, skips the NULLs, and computes the population standard deviation 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 (the population variance, var_pop);
4. the square root of that.

Dividing by n is what makes it the population form: use it when the rows are every member of the group you're describing. When they're a sample of a larger population, use stddev_samp, which divides by n - 1 and so returns a larger value whenever the values differ. With one value, stddev_pop() returns 0.0 where stddev_samp() returns NULL.

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

[(2.0548046676563256, 2.0548046676563256)]
[(0.0,)]
[(0.0,)]
[(0.0,)]
[(None,)]
[(None,)]
[(None, 0.0)]
[(0.0,)]
[(288.6749902572095, 288.6749902572095)]
OSError: STDDEV_POP is out of range!
OSError: STDDEV_POP is out of range!
OSError: STDDEV_POP 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 'stddev_pop(BOOLEAN)'. You might need to add explicit type casts.
[(0.37267799624996495,)]

Examples

The population standard deviation of the temperatures. The empty reading is skipped:

run(f"SELECT stddev_pop(temperature) FROM {TABLE}")

[(2.0548046676563256,)]

An INTEGER column returns a DOUBLE:

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

[(7.4535599249993,)]

The argument can be an expression. The spread in degrees Fahrenheit is 1.8 times the spread in degrees Celsius, next to the mean of each:

run(f"SELECT avg(temperature), stddev_pop(temperature), "
    f"avg(temperature * 1.8 + 32), stddev_pop(temperature * 1.8 + 32) FROM {TABLE}")

[(20.666666666666668, 2.0548046676563256, 69.2, 3.6986484017813823)]

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

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

[(1.25, 2.25)]

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 stddev_pop(), and every other aggregate in the SELECT list, over the rows it receives, and returns one row.

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

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

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│  stddev_pop(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 stddev_pop(temperature) FILTER (WHERE id > 1) FROM {TABLE}")

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

• Compute the mean and the spread in one query, as in Examples. 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 'stddev_pop(...)'. You might need to add explicit type casts. Cast a BOOLEAN to INTEGER first.
• No DISTINCT or ORDER BY inside the call. stddev_pop(DISTINCT x) and stddev_pop(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 STDDEV_POP is out of range!. Leave such rows out in WHERE, or scale the values down first.
• No GROUP BY. SELECT sensor, stddev_pop(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. stddevp fails with Catalog Error: Scalar Function with name stddevp does not exist!. Use stddev_pop.

run(f"SELECT stddev_pop(temperature ORDER BY id) FROM {TABLE}")
run(f"SELECT sensor, stddev_pop(temperature) FROM {TABLE} GROUP BY sensor")
run(f"SELECT stddevp(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 stddevp does not exist!

Similar functions

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

[(2.0548046676563256, 2.250925735484551, 4.222222222222222, 5.066666666666667, 2.0548046676563256, 5.5)]
[(0.0, None, 0.0, None, 0.0, 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.