geomean

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Sums and averages. Returns the geometric mean of positive values: the n-th root of the product of n values. geometric_mean is the same function.

Syntax

geomean(x)
geometric_mean(x)

Returns: DOUBLE.

Aliases: geometric_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_geomean. 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 years of a fund: its growth factor that year (1.10 is 10% up), the same change as a fraction (0.10), and the number of new investors. The last year has no growth figures yet, and no new investors:

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_geomean"'

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}.fund_years"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, factor DOUBLE, change DOUBLE, investors INTEGER)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 1.10, 0.10, 2), "
    "(2, 0.95, -0.05, 8), "
    "(3, 1.20, 0.20, 4), "
    "(4, NULL, NULL, 0)")

[]
[]
[(4,)]

What it does

geomean(x) is an aggregate: it returns the n-th root of the product of the n non-NULL values of x. It's the right mean for factors that multiply, such as yearly growth rates: the geometric mean of the yearly factors is the constant yearly factor that gives the same total growth.

The plan shows how it's computed, through logarithms:

exp(avg(ln(x)))

So the rules of ln apply to every value: a zero or a negative value stops the query with an error, cannot take logarithm of zero or cannot take logarithm of a negative number. Leave such rows out in WHERE, or measure growth as factors (1.10, 0.95), not as changes (0.10, -0.05), which can be negative.

NULLs are skipped, as by avg. geomean is a macro, a shorthand the Query Engine expands into that expression, not an aggregate of its own, so it can't take FILTER, DISTINCT or ORDER BY (see Limitations and errors).

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 geomean(factor), geometric_mean(factor) FROM {TABLE}")
run(f"SELECT geomean(factor) FROM {TABLE} WHERE id = 4")
run(f"SELECT geomean(factor) FROM {TABLE} WHERE id > 100")
run("SELECT geomean(NULL), geomean(2.5)")
run(f"SELECT geomean(investors) FROM {TABLE}")
run(f"SELECT geomean(change) FROM {TABLE}")
run(f"SELECT geomean(id > 1) FROM {TABLE}")
run(f"SELECT geomean(factor) FILTER (WHERE id < 3) FROM {TABLE}")

[(1.078365153390936, 1.078365153390936)]
[(None,)]
[(None,)]
[(None, 2.5)]
OSError: cannot take logarithm of zero
OSError: cannot take logarithm of a negative number
InternalError: Binder Error: No function matches the given name and argument types 'ln(BOOLEAN)'. You might need to add explicit type casts.
InternalError: Invalid Input Error: Function "geomean" is a Macro Function. "DISTINCT", "FILTER", and "ORDER BY" are only applicable to aggregate functions.

Examples

The average yearly growth factor of the fund, next to the arithmetic mean of the same factors and the formula written out:

run(f"SELECT geomean(factor), avg(factor), exp(avg(ln(factor))) FROM {TABLE}")

[(1.078365153390936, 1.0833333333333333, 1.078365153390936)]

The geometric mean of the number of new investors, over the years that had any. The year with 0 is left out in WHERE, which runs before the aggregate:

run(f"SELECT geomean(investors) FROM {TABLE} WHERE investors > 0")

[(4.0,)]

The average yearly change from changes stored as fractions: add 1 to turn them into factors, and subtract 1 from the result:

run(f"SELECT geomean(1 + change) - 1 FROM {TABLE}")

[(0.07836515339093597,)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. A geomean query has three steps, which EXPLAIN shows:

1. A scan in VAST DataBase (VAST_SCAN), which reads the column and evaluates the WHERE conditions it can push down.
2. One aggregate step (UNGROUPED_AGGREGATE) in the Query Engine, which computes avg(ln(x)) over the rows the scan sends.
3. A PROJECTION step, which applies exp() to the result.

The WHERE condition of the second example compares a column with a constant, so it's evaluated in the scan and doesn't appear in the plan:

plan(f"SELECT geomean(investors) FROM {TABLE} WHERE investors > 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│ exp(avg(ln(CAST(investors │
│        AS DOUBLE))))      │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│  avg(ln(CAST(investors AS │
│          DOUBLE)))        │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│         investors         │
│                           │
└───────────────────────────┘

Make it faster

• Leave out zeros and negatives in WHERE. WHERE x > 0 is pushed down to VAST DataBase, so those rows never leave storage, and the query doesn't fail on them.
• Compute it with other aggregates in one query. They share one scan.
• Store factors rather than changes if you often take their geometric mean: geomean(factor) reads the column as it is, while geomean(1 + change) computes 1 + change on every row.

Limitations and errors

• Positive values only. A zero or a negative value fails the whole query, not just that row. Filter them out, or shift the values (geomean(x + 1) - 1 for changes), knowing that a shift changes the result.
• No FILTER, DISTINCT or ORDER BY. They fail with Function "geomean" is a Macro Function. "DISTINCT", "FILTER", and "ORDER BY" are only applicable to aggregate functions. (geometric_mean in the message when you use that name). Use WHERE, or write exp(avg(ln(x)) FILTER (WHERE ...)) out.
• No window form. geomean(x) OVER (...) fails with Catalog Error: exp is not an aggregate function.
• Numbers only. A string, a date, a BOOLEAN or a list fails with No function matches the given name and argument types, and the message names ln, such as 'ln(VARCHAR)'.
• No GROUP BY. A geometric mean per group fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported.

run(f"SELECT exp(avg(ln(factor)) FILTER (WHERE id < 3)) FROM {TABLE}")
run(f"SELECT id, geomean(factor) OVER (ROWS UNBOUNDED PRECEDING) FROM {TABLE}")

[(1.0222524150130436,)]
InternalError: Catalog Error: exp is not an aggregate function

Similar functions

run(f"SELECT geomean(factor), exp(avg(ln(factor))), avg(factor), product(factor), wavg(factor, 1) FROM {TABLE}")

[(1.078365153390936, 1.078365153390936, 1.0833333333333333, 1.2539999999999998, 1.0833333333333333)]

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.
• avg, product and ln.
• Pushdowns and EXPLAIN.