Skip to content
DuckDB Extensions All extensions Apache DataSketches

Frequent Items functions in the Apache DataSketches DuckDB extension

Function category

Frequent Items

9 functions

[Heavy-hitter sketch](https://datasketches.apache.org/docs/Frequency/FrequentItemsOverview.html) that identifies the most-frequent items in a stream along with confidence-bounded frequency estimates. Use for top-N analysis on high-cardinality streams where exact `GROUP BY count()` is too expensive.

datasketch_frequent_items

Aggregate function Frequent Items
Signature
2 overloaded forms · click to inspect
Arguments (Positional)
Argument col0 Type FLOAT | UINTEGER | SMALLINT | …
12 concrete types
BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINTVARCHARsketch_frequent_items
Mode Positional Description
Description

Aggregate input values into a Frequent Items (heavy-hitter) sketch. The first argument is lg_max_map_size — log₂ of the maximum number of tracked items, so 8 → up to 256 candidates, 12 → up to 4096. Bigger values track more candidates and tighten the per-item error bound. Typical values run 4–12.

1 Top countries by page views 
SELECT datasketch_frequent_items_get_frequent(
         datasketch_frequent_items(8, country_code),
         'NO_FALSE_POSITIVES'
       ) AS heavy_hitters
FROM page_views;
Related functions

datasketch_frequent_items_epsilon

Scalar function Frequent Items
Signature
datasketch_frequent_items_epsilon(col0: sketch_frequent_items) → DOUBLE
Arguments (Positional)
Argument col0 Type sketch_frequent_items Mode Positional Description
Description

Returns the epsilon value (relative error) of the sketch

1 Example 1 
SELECT datasketch_frequent_items_epsilon(sketch);
Related functions

datasketch_frequent_items_estimate

Scalar function Frequent Items
Signature
datasketch_frequent_items_estimate(col0: sketch_frequent_items, col1: UBIGINT | BIGINT | SMALLINT | …) → BIGINT
Arguments (Positional)
Argument col0 Type sketch_frequent_items Mode Positional Description
Argument col1 Type UBIGINT | BIGINT | SMALLINT | …
11 concrete types
BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINTVARCHAR
Mode Positional Description
Description

Estimated frequency for a specific item.

1 Example 1 
SELECT datasketch_frequent_items_estimate(sketch, 'item');
Related functions

datasketch_frequent_items_get_frequent

Scalar function Frequent Items
Signature
datasketch_frequent_items_get_frequent(col0: sketch_frequent_items, col1: VARCHAR) → STRUCT(item VARCHAR, estimate BIGINT, lower_bound BIGINT, upper_bound BIGINT)[]
Arguments (Positional)
Argument col0 Type sketch_frequent_items Mode Positional Description
Argument col1 Type VARCHAR Mode Positional Description
Description

Return the heavy-hitter candidates with per-item estimate, lower bound, and upper bound. The second argument selects the error mode: 'NO_FALSE_POSITIVES' returns only items that are definitely heavy (every returned item really is frequent); 'NO_FALSE_NEGATIVES' returns the union of all candidates that might be — every truly-frequent item is included, plus some that may not be.

1 Example 1 
SELECT datasketch_frequent_items_get_frequent(sketch, 'NO_FALSE_POSITIVES');
Related functions

datasketch_frequent_items_is_empty

Scalar function Frequent Items
Signature
datasketch_frequent_items_is_empty(col0: sketch_frequent_items) → BOOLEAN
Arguments (Positional)
Argument col0 Type sketch_frequent_items Mode Positional Description
Description

Returns true if the sketch is empty

1 Example 1 
SELECT datasketch_frequent_items_is_empty(sketch);
Related functions

datasketch_frequent_items_lower_bound

Scalar function Frequent Items
Signature
datasketch_frequent_items_lower_bound(col0: sketch_frequent_items, col1: TINYINT | FLOAT | UTINYINT | …) → BIGINT
Arguments (Positional)
Argument col0 Type sketch_frequent_items Mode Positional Description
Argument col1 Type TINYINT | FLOAT | UTINYINT | …
11 concrete types
BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINTVARCHAR
Mode Positional Description
Description

Returns the lower bound frequency estimate for a specific item

1 Example 1 
SELECT datasketch_frequent_items_lower_bound(sketch, 'item');
Related functions

datasketch_frequent_items_num_active

Scalar function Frequent Items
Signature
datasketch_frequent_items_num_active(col0: sketch_frequent_items) → BIGINT
Arguments (Positional)
Argument col0 Type sketch_frequent_items Mode Positional Description
Description

Returns the number of active items currently tracked by the sketch

1 Example 1 
SELECT datasketch_frequent_items_num_active(sketch);
Related functions

datasketch_frequent_items_total_weight

Scalar function Frequent Items
Signature
datasketch_frequent_items_total_weight(col0: sketch_frequent_items) → BIGINT
Arguments (Positional)
Argument col0 Type sketch_frequent_items Mode Positional Description
Description

Returns the total weight (sum of all item counts) processed by the sketch

1 Example 1 
SELECT datasketch_frequent_items_total_weight(sketch);
Related functions

datasketch_frequent_items_upper_bound

Scalar function Frequent Items
Signature
datasketch_frequent_items_upper_bound(col0: sketch_frequent_items, col1: UTINYINT | SMALLINT | INTEGER | …) → BIGINT
Arguments (Positional)
Argument col0 Type sketch_frequent_items Mode Positional Description
Argument col1 Type UTINYINT | SMALLINT | INTEGER | …
11 concrete types
BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINTVARCHAR
Mode Positional Description
Description

Returns the upper bound frequency estimate for a specific item

1 Example 1 
SELECT datasketch_frequent_items_upper_bound(sketch, 'item');
Related functions