HLL_COUNT_ESTIMATE

Extracts a cardinality estimate of a single HLL++ sketch that was previously built using the aggregate function HLL_COUNT_BUILD.

Syntax

HLL_COUNT_ESTIMATE(<expression>)

Parameters

Parameter	Description	Supported input types
`<expression>`	An HLL++ sketch produced by the HLL_COUNT_BUILD function.	`BYTEA`

Return Type

BIGINT

Examples

The following example builds an HLL++ sketch from 1,000 unique integers using HLL_COUNT_BUILD and estimates the distinct count:

SELECT HLL_COUNT_ESTIMATE(HLL_COUNT_BUILD(number)) AS hll_estimate
FROM GENERATE_SERIES(1, 1000) AS t(number);

hll_estimate long
983

Rows: 1Execution time: 6.16ms

HLL_COUNT_ESTIMATE can also be applied to each sketch independently before merging. The following example builds two sketches from overlapping ranges and estimates the cardinality of each:

SELECT HLL_COUNT_ESTIMATE(sketch) AS hll_estimate
FROM (
    SELECT HLL_COUNT_BUILD(n) AS sketch FROM GENERATE_SERIES(1, 500) t(n)
    UNION ALL
    SELECT HLL_COUNT_BUILD(n) AS sketch FROM GENERATE_SERIES(251, 750) t(n)
) sketches;

hll_estimate long
487
497

Rows: 2Execution time: 6.18ms

To estimate the combined count across all sketches, use HLL_COUNT_MERGE to merge them first. The two ranges 1–500 and 251–750 share 250 values, giving 750 unique values in total:

SELECT HLL_COUNT_ESTIMATE(HLL_COUNT_MERGE(sketch)) AS merged_estimate
FROM (
    SELECT HLL_COUNT_BUILD(n) AS sketch FROM GENERATE_SERIES(1, 500) t(n)
    UNION ALL
    SELECT HLL_COUNT_BUILD(n) AS sketch FROM GENERATE_SERIES(251, 750) t(n)
) sketches;

merged_estimate long
730

Rows: 1Execution time: 6.21ms

FLOOR

IS_FINITE

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Examples

​Syntax

​Parameters

​Return Type

​Examples

Syntax

Parameters

Return Type

Examples