list_histogram

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Aggregate a list. Counts how often each distinct value occurs in a list and returns the counts as a map from value to count. NULL elements aren't counted.

Syntax

list_histogram(l)

Argument	Type	Description
`l`	a list of any type whose values can be compared	The values to count.

Returns: MAP(element type, UBIGINT): each distinct value of l is a key, and its count (an unsigned 64-bit integer) is the value. For example typeof(list_histogram([1, 2])) is 'MAP(INTEGER, UBIGINT)'.

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_histogram. 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: whole numbers (nums), measurements (vals) and words (words). Row 2 has NULL elements, row 3 has empty lists, row 4 has no values, and row 5 has non-ASCII words and an empty string:

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

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[], vals DOUBLE[], words VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], [0.5, 2.5, -1.0], ['a', 'b', 'c']), "
    "(2, [3, NULL, 3], [2.0, NULL], ['b', NULL, 'B']), "
    "(3, [], [], []), "
    "(4, NULL, NULL, NULL), "
    "(5, [-5, 0, -5, 10], [1e308, 1e308, -1e-308], ['\u00e9', 'Zo\u00eb', '\U0001F1EB\U0001F1F7', ''])")

[]
[]
[(5,)]

What it does

list_histogram(l) returns one map entry per distinct non-NULL value of l, holding how many times it occurs:

[1, 2, 2] gives {1: 1, 2: 2}, and ['b', 'a', 'b'] gives {'a': 1, 'b': 2}.
In every result returned for this page's cases, the keys come in ascending order: numbers by value, text by character code.
Text is compared exactly: 'b' and 'B' are counted separately.
A list of lists counts each inner list as a whole: [[1], [1]] gives {[1]: 2}.

Python receives a map as a list of (key, count) tuples, for example [(1, 1), (2, 2)].

Use the map functions on the result. list_histogram(l)[v] returns the count of the value v, and map_keys returns the distinct values in key order.

NULLs and edge cases

Input	Result
`NULL`, or `NULL::BIGINT[]`	`NULL`
`[]::BIGINT[]` (empty list)	`NULL`, not an empty map
`[NULL, 1, NULL]`	`{1: 1}` (NULL elements aren't counted)
`[[1], [1]]`	`{[1]: 2}`
`list_histogram([1, 2, 2])[2]`	`2`
`map_keys(list_histogram(['b', 'a', 'b']))`	`['a', 'b']`
`typeof(list_histogram([1.5::DOUBLE]))`	`'MAP(DOUBLE, UBIGINT)'`
A number, string, `BLOB`, date or boolean	Error: `No function matches the given name and argument types`, for example `'list_aggr(INTEGER_LITERAL, STRING_LITERAL)'`

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

run("SELECT list_histogram(NULL), list_histogram(NULL::BIGINT[]), list_histogram([]::BIGINT[]), list_histogram([NULL, 1, NULL])")
run("SELECT list_histogram([[1], [1]]), list_histogram([1, 2, 2])[2], map_keys(list_histogram(['b', 'a', 'b'])), "
    "typeof(list_histogram([1.5::DOUBLE]))")
run(f"SELECT list_histogram(123) FROM {TABLE} LIMIT 1")

[(None, None, None, [(1, 1)])]
[([([1], 2)], 2, ['a', 'b'], 'MAP(DOUBLE, UBIGINT)')]
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 counts in each row's numbers and words. Row 5's numbers [-5, 0, -5, 10] give -5 a count of 2. Row 2's words ['b', NULL, 'B'] give 'B' and 'b' one each and skip the NULL. The empty lists of row 3 and the NULL lists of row 4 give NULL:

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

[(1, [(1, 1), (2, 1), (3, 1)], [('a', 1), ('b', 1), ('c', 1)]), (2, [(3, 2)], [('B', 1), ('b', 1)]), (3, None, None), (4, None, None), (5, [(-5, 2), (0, 1), (10, 1)], [('', 1), ('Zoë', 1), ('é', 1), ('🇫🇷', 1)])]

How many times 3 occurs in each row's numbers:

run(f"SELECT id, nums, list_histogram(nums)[3] FROM {TABLE} ORDER BY id LIMIT 10")

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

The rows whose numbers aren't NULL or empty:

run(f"SELECT id FROM {TABLE} WHERE list_histogram(nums) IS NOT NULL ORDER BY id LIMIT 10")

[(1,), (2,), (5,)]

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_histogram() always runs in the Query Engine:

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

The plan of the last example shows the FILTER step above the scan:

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

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (list_aggr(nums,      │
│ 'histogram') 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_histogram() 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 counts you filter on. If you often filter on how many times a value occurs, compute that count when you write the rows and keep it in its own column: threes > 1 is pushed down, list_histogram(nums)[3] > 1 isn't.

Limitations and errors

An empty list gives NULL, not an empty map. You can't tell an empty list from a NULL list by the result; test len(l) = 0 if you need to.
Lists only. Any other type fails with No function matches the given name and argument types. The error names list_aggr, the list aggregate that list_histogram runs on, and its accepted form list_aggr(ANY[], VARCHAR, [ANY...]) -> ANY.
ORDER BY needs a LIMIT. Without one, the query fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

Similar functions

Function	What it returns	Result for `[3, 1, NULL, 3, 10]`
`list_histogram`	the count of each distinct value, as a map	`{1: 1, 3: 2, 10: 1}`
list_distinct	the distinct values, as a list in no set order	`[10, 3, 1]`
list_unique	the number of distinct non-NULL values	`3`
list_entropy	entropy of the value counts, in bits	`1.5`

run("SELECT list_histogram(l), list_distinct(l), list_unique(l), list_entropy(l) FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[([(1, 1), (3, 2), (10, 1)], [10, 3, 1], 3, 1.5)]

Clean up:

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

[]
[]