stddev_samp

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Spread. An aggregate function that returns the sample standard deviation of a numeric expression across the rows of a query: how far the values typically are from their mean, dividing by n - 1. stddev is the same function.

Syntax

stddev_samp(x)
stddev(x)
stddev_samp(x) FILTER (WHERE condition)

Returns: DOUBLE.

Aliases: stddev.

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_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_stddev_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

stddev_samp(x) reads x on every row the query keeps, skips the NULLs, and computes the sample 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 - 1 (the sample variance, var_samp);
4. the square root of that.

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 stddev_pop, which divides by n. With one value there's nothing to divide by, so stddev_samp() returns NULL where stddev_pop() returns 0.0.

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

[(2.250925735484551, 2.250925735484551)]
[(None,)]
[(None,)]
[(0.0,)]
[(None,)]
[(None,)]
[(None, None)]
[(0.0,)]
[(288.8194360957494, 288.8194360957494)]
OSError: STDDEV_SAMP is out of range!
OSError: STDDEV_SAMP is out of range!
OSError: STDDEV_SAMP 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_samp(BOOLEAN)'. You might need to add explicit type casts.
[(0.408248290463863,)]

Examples

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

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

[(2.250925735484551, 2.250925735484551)]

An INTEGER column returns a DOUBLE:

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

[(8.16496580927726,)]

The mean, the spread and the standard error of the mean in one query. sem isn't available, so the standard error is computed as stddev_samp(x) / sqrt(count(x)):

run(f"SELECT avg(temperature), stddev_samp(temperature), "
    f"stddev_samp(temperature) / sqrt(count(temperature)) FROM {TABLE}")

[(20.666666666666668, 2.250925735484551, 0.9189365834726816)]

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_samp(temperature) FILTER (WHERE sensor = 's1') AS s1, "
    f"stddev_samp(temperature) FILTER (WHERE sensor = 's2') AS s2 FROM {TABLE}")

[(1.7677669529663689, 3.181980515339464)]

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

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

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

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

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

• Compute the mean, the spread and the count 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_samp(...)'. You might need to add explicit type casts. Cast a BOOLEAN to INTEGER first.
• No DISTINCT or ORDER BY inside the call. stddev_samp(DISTINCT x) and stddev_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 STDDEV_SAMP is out of range!. Leave such rows out in WHERE, or scale the values down first.
• No GROUP BY. SELECT sensor, stddev_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. std, stdev, std_dev and stddevp fail with Catalog Error: Scalar Function with name ... does not exist!. Use stddev_samp or stddev, or stddev_pop for the population form.

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

Similar functions

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

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