Skip to content
DuckDB Extensions All extensions Apache DataSketches

Theta functions in the Apache DataSketches DuckDB extension

Function category

Theta

13 functions

[Theta sketch](https://datasketches.apache.org/docs/Theta/ThetaSketches.html) — the only distinct-count family that supports set operations beyond union. Combine cohorts with `union`, `intersect`, and `a_not_b` directly on sketch state, without rescanning the source data.

datasketch_theta

Aggregate function Theta
Signature
2 overloaded forms · click to inspect
Arguments (Positional)
Argument col0 Type VARCHAR | FLOAT | TINYINT | …
8 concrete types
BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTVARCHARsketch_theta
Mode Positional Description
Description

Aggregate input values into a Theta sketch — the distinct-count family that supports set operations (union, intersect, A-not-B) beyond simple merge. The leading lg_k is base-2 log of the nominal sketch entries.

1 Build per-cohort sketches 
SELECT cohort,
       datasketch_theta(12, user_id) AS sketch
FROM events
GROUP BY cohort;
Related functions

datasketch_theta_a_not_b

Scalar function Theta
Signature
datasketch_theta_a_not_b(col0: sketch_theta, col1: sketch_theta) → sketch_theta
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Argument col1 Type sketch_theta Mode Positional Description
Description

Approximate |A \ B| — distinct items present in A but not in B. Useful for churn (in-Jan, not-in-Feb) and new-user (in-Feb, not-in-Jan) measurements directly from sketch state.

1 Example 1 
SELECT datasketch_theta_a_not_b(sketch_a, sketch_b);
Related functions

datasketch_theta_describe

Scalar function Theta
Signature
datasketch_theta_describe(col0: sketch_theta) → VARCHAR
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Description

Returns a human-readable description of the Theta sketch

1 Example 1 
SELECT datasketch_theta_describe(sketch);
Related functions

datasketch_theta_estimate

Scalar function Theta
Signature
datasketch_theta_estimate(col0: sketch_theta) → DOUBLE
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Description

Read the estimated distinct count from a Theta sketch.

1 Example 1 
SELECT datasketch_theta_estimate(sketch);
Related functions

datasketch_theta_get_seed

Scalar function Theta
Signature
datasketch_theta_get_seed(col0: sketch_theta) → BIGINT
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Description

Returns the seed hash used by the sketch

1 Example 1 
SELECT datasketch_theta_get_seed(sketch);
Related functions

datasketch_theta_get_theta

Scalar function Theta
Signature
datasketch_theta_get_theta(col0: sketch_theta) → DOUBLE
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Description

Returns the theta value of the sketch (sampling probability)

1 Example 1 
SELECT datasketch_theta_get_theta(sketch);
Related functions

datasketch_theta_intersect

Scalar function Theta
Signature
datasketch_theta_intersect(col0: sketch_theta, col1: sketch_theta) → sketch_theta
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Argument col1 Type sketch_theta Mode Positional Description
Description

Approximate |A ∩ B| — distinct items present in both cohorts. The classic use case is funnel and retention analysis: how many users in segment A also appear in segment B, computed from the sketches alone.

1 Retention — users active in Jan AND Feb 
WITH a AS (SELECT datasketch_theta(12, user_id) AS s FROM events_jan),
     b AS (SELECT datasketch_theta(12, user_id) AS s FROM events_feb)
SELECT datasketch_theta_estimate(
         datasketch_theta_intersect(a.s, b.s)
       ) AS retained_users
FROM a, b;
Related functions

datasketch_theta_is_empty

Scalar function Theta
Signature
datasketch_theta_is_empty(col0: sketch_theta) → BOOLEAN
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Description

Returns true if the Theta sketch is empty

1 Example 1 
SELECT datasketch_theta_is_empty(sketch);
Related functions

datasketch_theta_is_estimation_mode

Scalar function Theta
Signature
datasketch_theta_is_estimation_mode(col0: sketch_theta) → BOOLEAN
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Description

Returns true if the sketch is in estimation mode (has exceeded exact counting capacity)

1 Example 1 
SELECT datasketch_theta_is_estimation_mode(sketch);
Related functions

datasketch_theta_lower_bound

Scalar function Theta
Signature
datasketch_theta_lower_bound(col0: sketch_theta, col1: INTEGER) → DOUBLE
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Argument col1 Type INTEGER Mode Positional Description
Description

Returns the lower bound estimate at the given number of standard deviations (1, 2, or 3)

1 Example 1 
SELECT datasketch_theta_lower_bound(sketch, 2);
Related functions

datasketch_theta_num_retained

Scalar function Theta
Signature
datasketch_theta_num_retained(col0: sketch_theta) → BIGINT
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Description

Returns the number of hash values retained in the sketch

1 Example 1 
SELECT datasketch_theta_num_retained(sketch);
Related functions

datasketch_theta_union

Scalar function Theta
Signature
datasketch_theta_union(col0: sketch_theta, col1: sketch_theta) → sketch_theta
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Argument col1 Type sketch_theta Mode Positional Description
Description

Approximate |A ∪ B| — distinct count across the union of two cohorts. Aggregate over a sketch_theta column to merge per-day or per-shard sketches.

1 Example 1 
SELECT datasketch_theta_union(sketch1, sketch2);
Related functions

datasketch_theta_upper_bound

Scalar function Theta
Signature
datasketch_theta_upper_bound(col0: sketch_theta, col1: INTEGER) → DOUBLE
Arguments (Positional)
Argument col0 Type sketch_theta Mode Positional Description
Argument col1 Type INTEGER Mode Positional Description
Description

Returns the upper bound estimate at the given number of standard deviations (1, 2, or 3)

1 Example 1 
SELECT datasketch_theta_upper_bound(sketch, 2);
Related functions