list_stddev_samp

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Aggregate a list. Returns the sample standard deviation of the numbers in a list, ignoring NULL elements. For the standard deviation of a whole population, use list_stddev_pop.

Syntax

list_stddev_samp(l)

Returns: DOUBLE, for a list of integers too.

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_lists_list_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 a table of five rows of lists: numbers, words, measurements, flags and lists of lists. Row 3 has empty lists and row 4 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_lists_list_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}.samples"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, nums BIGINT[], words VARCHAR[], vals DOUBLE[], "
    "flags BOOLEAN[], nested BIGINT[][])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], ['a', 'b', 'c'], [0.5, 2.5, -1.0], [true, false, true], [[1, 2], [3]]), "
    "(2, [3, NULL, 3], ['b', NULL, 'B'], [2.0, NULL], [NULL, true], [[], NULL, [4]]), "
    "(3, [], [], [], [], []), "
    "(4, NULL, NULL, NULL, NULL, NULL), "
    "(5, [-5, 0, -5, 10], ['\u00e9', 'Zo\u00eb', '\U0001F1EB\U0001F1F7', ''], [1e308, 1e308, -1e-308], "
    "[false, false], [[5, NULL], [], [6, 7, 8]])")

[]
[]
[(5,)]

What it does

list_stddev_samp(l) computes the sample standard deviation of the non-NULL elements of l: the square root of the sum of the squared differences from their mean, divided by one less than the number of values. Use it when the list holds a sample of a larger set of values.

• list_stddev_samp([1.0, 2.0, 3.0]) is 1.0, and list_stddev_samp([1.0, 2.0, 3.0, 10.0]) is 4.08248290463863.
• Equal values give 0.0: list_stddev_samp([5, 5, 5, 5]).
• It needs at least two values: a list of one value returns NULL. So do an empty list, a list of only NULLs and a NULL list.
• NaN, infinity, or values near the DOUBLE limit such as 1e308, fail the query with STDDEV_SAMP is out of range!.

NULLs and edge cases

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

run("SELECT list_stddev_samp(NULL::BIGINT[]), list_stddev_samp([]::DOUBLE[]), "
    "list_stddev_samp([NULL, NULL]::DOUBLE[]), list_stddev_samp([1.0])")
run("SELECT list_stddev_samp([1.0, 2.0]), list_stddev_samp([5, 5, 5, 5])")
run("SELECT list_stddev_samp(['NaN'::DOUBLE, 1.0, 2.0])")
run("SELECT list_stddev_samp([1e308, -1e308, 1e308])")
run("SELECT list_stddev_samp(['a'])")
run("SELECT list_stddev_samp(123)")

[(None, None, None, None)]
[(0.7071067811865476, 0.0)]
OSError: STDDEV_SAMP is out of range!
OSError: STDDEV_SAMP is out of range!
InternalError: Binder Error: No matching aggregate function
InternalError: Binder Error: No function matches the given name and argument types 'list_aggr(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

The sample standard deviation of each row's numbers. Row 2's NULL is ignored, which leaves two equal values and a result of 0.0; rows 3 and 4 return NULL:

run(f"SELECT id, nums, list_stddev_samp(nums) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2, 3], 1.0), (2, [3, None, 3], 0.0), (3, [], None), (4, None, None), (5, [-5, 0, -5, 10], 7.0710678118654755)]

The rows whose numbers are spread out:

run(f"SELECT id, nums FROM {TABLE} WHERE list_stddev_samp(nums) > 0.5 ORDER BY id LIMIT 10")

[(1, [1, 2, 3]), (5, [-5, 0, -5, 10])]

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). The scan reads a list column whole, and list_stddev_samp() always runs in the Query Engine:

• In the SELECT list, a projection step computes list_stddev_samp() for each row the scan returns.
• In WHERE, the scan sends the list for every row, and a FILTER step computes list_stddev_samp() and keeps the rows that match. No rows are skipped in storage.

The plan of a WHERE condition on list_stddev_samp() shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_stddev_samp(nums) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (list_aggr(CAST(nums AS  │
│  DOUBLE[]), 'stddev_samp')│
│        IS NOT NULL)       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            nums           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Add a condition on a plain column. A comparison of a column with a constant, such as id > 1, is evaluated in VAST DataBase, so list_stddev_samp() runs only on the rows that pass it. The plan for id > 1 alone has no FILTER step:

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

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Store the result if you filter on it often. Compute it when you write the rows and keep it in its own column: spread > 0.5 is pushed down, list_stddev_samp(nums) > 0.5 isn't.

Limitations and errors

• One out-of-range list fails the whole query. Row 5's measurements hold 1e308, so list_stddev_samp(vals) over the table fails with STDDEV_SAMP is out of range! and returns no rows. Keep NaN, infinities and values near the DOUBLE limit out of the lists you pass.
• Numbers only. A list of text fails with No matching aggregate function. Any argument that isn't a list fails with No function matches the given name and argument types; the error names list_aggr, the function list_stddev_samp calls.
• ORDER BY needs a LIMIT. Without one, the query fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

run(f"SELECT id, list_stddev_samp(vals) FROM {TABLE} ORDER BY id LIMIT 10")

OSError: STDDEV_SAMP is out of range!

Similar functions

run("SELECT list_stddev_samp(l), list_stddev_pop(l), list_var_samp(l), list_var_pop(l), list_sem(l) "
    "FROM (SELECT [1.0, 2.0, 3.0, 10.0]::DOUBLE[] AS l)")

[(4.08248290463863, 3.5355339059327378, 16.666666666666668, 12.5, 1.7677669529663689)]

Clean up:

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

[]
[]

See also

• List, struct and map functions: all the list functions.
• list_avg: the mean of a list.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.