avg

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Sums and averages. Returns the arithmetic mean of the non-NULL values: their sum divided by how many there are. mean is the same function.

Syntax

avg(x)
mean(x)

Returns: DOUBLE, for every input type, DECIMAL included.

Aliases: mean.

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_avg. 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 four sensor readings: a temperature, a level, a price and an OK flag. The last row 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_aggregate_avg"'

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"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, temperature DOUBLE, level INTEGER, price DECIMAL(10,2), ok BOOLEAN)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 18.5, 10, 1.25, true), "
    "(2, 21.0, 20, 2.50, true), "
    "(3, 19.5, 30, 3.75, false), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

avg(x) is an aggregate: it adds up the non-NULL values of x over the rows of the query and divides by how many there are. It's the same as sum(x) / count(x), not sum(x) / count(*): a row where x is NULL counts neither in the sum nor in the number of values.

The result is a DOUBLE for every input type. An average of INTEGER values has a fractional part, and an average of DECIMAL values is a DOUBLE too, not a DECIMAL. Round it for display with round.

NULLs and edge cases

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

run(f"SELECT avg(temperature), sum(temperature) / count(temperature), sum(temperature) / count(*) FROM {TABLE}")
run(f"SELECT avg(temperature) FROM {TABLE} WHERE id = 4")
run(f"SELECT avg(temperature) FROM {TABLE} WHERE id > 100")
run("SELECT avg(NULL), avg(42)")
run("SELECT avg(x) FROM (VALUES ('NaN'::DOUBLE), (1.0::DOUBLE)) v(x)")
run("SELECT avg(x) FROM (VALUES (1.0e308::DOUBLE), (1.0e308::DOUBLE)) v(x)")
run(f"SELECT avg(ok) FROM {TABLE}")
run(f"SELECT avg(ok::INTEGER) FROM {TABLE}")
run(f"SELECT avg(DISTINCT level) FROM {TABLE}")

[(19.666666666666668, 19.666666666666668, 14.75)]
[(None,)]
[(None,)]
[(None, 42.0)]
[(nan,)]
[(inf,)]
InternalError: Binder Error: No function matches the given name and argument types 'avg(BOOLEAN)'. You might need to add explicit type casts.
[(0.6666666666666666,)]
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.

Examples

The average temperature, level and price. mean gives the same result as avg, and every result is a DOUBLE:

run(f"SELECT avg(temperature), mean(temperature), avg(level), avg(price) FROM {TABLE}")

[(19.666666666666668, 19.666666666666668, 20.0, 2.5)]

The average temperature of the OK readings, rounded to one decimal, and the share of readings that are OK:

run(f"SELECT round(avg(temperature) FILTER (WHERE ok), 1), avg(ok::INTEGER) FROM {TABLE}")

[(19.8, 0.6666666666666666)]

How it runs in VAST

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

1. A scan in VAST DataBase (VAST_SCAN). It reads only the columns the average uses, and evaluates the WHERE conditions it can push down, so rows that don't match never leave storage.
2. One aggregate step (UNGROUPED_AGGREGATE) in the Query Engine, which computes the average of the values the scan sends and returns one row.

The plan of an average with a pushed-down WHERE condition. The condition doesn't appear in the plan, because the scan evaluates it:

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

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

Make it faster

• Filter in WHERE, not in FILTER (WHERE ...). A WHERE condition that compares a column with a constant is evaluated in VAST DataBase; a FILTER condition is evaluated in the aggregate step, after the scan has sent every row. Its plan shows the condition inside UNGROUPED_AGGREGATE:

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

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

• Compute several aggregates in one query. avg, sum, count and the others in one SELECT list share one scan and one aggregate step.
• Keep functions out of the WHERE condition. A function of a column, such as abs(temperature) > 20, isn't pushed down, so the scan sends every row.

Limitations and errors

• Numbers only. A BOOLEAN, a string, a date, a timestamp, an interval or a list fails with No function matches the given name and argument types; the error lists the types avg takes. Cast a BOOLEAN to INTEGER to get the share of true values.
• No average of dates or times. avg(day) on a DATE or TIMESTAMP fails, and so does an average of an INTERVAL. Use min and max for the range.
• No comparison with the average in the same query. WHERE x > (SELECT avg(x) FROM t) fails, with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. (as below) or, for some queries, The operator 'NESTED_LOOP_JOIN' type=31 is currently not supported.; SELECT (SELECT avg(x) FROM t) fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. Run the average as its own query and put the value in the second one.
• No GROUP BY, DISTINCT or ORDER BY inside. An average per group fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported.; avg(DISTINCT x) and avg(x ORDER BY y) fail with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. favg, a compensated average, isn't available either.
• Window forms. avg(x) OVER (ROWS UNBOUNDED PRECEDING) runs, as a running average in the order the scan returns the rows. Other OVER (...) forms fail with The operator 'WINDOW' type=6 is currently not supported. See window functions.

run(f"SELECT id FROM {TABLE} WHERE temperature > (SELECT avg(temperature) FROM {TABLE})")
run(f"SELECT ok, avg(temperature) FROM {TABLE} GROUP BY ok")
run(f"SELECT avg(temperature ORDER BY id) 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: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.

Similar functions

run(f"SELECT avg(level), sum(level) / count(level), sum(level) / count(*), wavg(level, 1), geomean(level) FROM {TABLE}")
run("SELECT list_avg([10, 20, 30])")

[(20.0, 20.0, 15.0, 20.0, 18.171205928321406)]
[(20.0,)]

Clean up:

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

[]
[]

See also

• Aggregate and analytic functions: all the aggregates, and the ones that aren't available.
• sum, wavg, geomean and FILTER.
• Pushdowns and EXPLAIN.