theta_sketch_estimate function

Applies to: Databricks SQL Databricks Runtime 18.0 and above

Returns the estimated number of unique values from a Theta Sketch binary representation.

Syntax

theta_sketch_estimate ( sketch )

Arguments

• sketch: A Theta Sketch in binary format (such as from theta_sketch_agg aggregate function, theta_union_agg aggregate function, theta_intersection_agg aggregate function, theta_union function, theta_difference function, or theta_intersection function).

Returns

A LONG value representing the estimated distinct count.

Notes

• The result is rounded to the nearest integer.
• The estimate is probabilistic; accuracy depends on the lgNomEntries parameter used when creating the sketch.

Error messages

• THETA_INVALID_INPUT_SKETCH_BUFFER

Examples

SQL

-- Get estimated distinct count from a sketch
> SELECT theta_sketch_estimate(theta_sketch_agg(col)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
3

Related

• theta_sketch_agg aggregate function
• theta_union_agg aggregate function
• theta_intersection_agg aggregate function