HLL_COUNT_BUILD

Counts the approximate number of unique not NULL values, aggregating the values to HLL++ sketches represented as the BYTEA data type. Multiple sketches can be merged to a single sketch using the aggregate function HLL_COUNT_MERGE. To estimate the final distinct count value, the scalar function HLL_COUNT_ESTIMATE can be used. HLL_COUNT_BUILD uses the HLL++ algorithm and allows you to control the set sketch size precision, similar to HLL_COUNT_DISTINCT. HLL_COUNT_BUILD requires less memory than exact count distinct aggregation, but also introduces statistical uncertainty. The default precision is 12, with a maximum of 20 set optionally.

Higher precision comes at a memory and performance cost.

Syntax

HLL_COUNT_BUILD(<expression> [, <precision> ]) [FILTER ([WHERE] <condition>)]

Parameters

Parameter	Description	Supported input types
`<expression>`	Any column name or function that return a column name.	Any type
`<precision>`	Optional literal integer value to set precision. If not included, the default precision is 12. Precision range: 12-20.	`INTEGER`, `BIGINT`
`<condition>`	An optional boolean expression to filter rows used in aggregation	`BOOL`

Return Type

BYTEA

Example

CREATE TABLE data_to_count AS
SELECT *
FROM generate_series(0, 10000000, 3) a;

SELECT count(distinct a) AS accurate_count
FROM data_to_count;

accurate_count (BIGINT)
3333334

CREATE TABLE data_to_count2 AS
SELECT *
FROM generate_series(0, 10000000, 2) a;

SELECT count(distinct a) AS accurate_count
FROM data_to_count2;

accurate_count (BIGINT)
5000001

CREATE TABLE sketch_of_data_to_count AS
SELECT hll_count_build(a) a
FROM data_to_count;

INSERT INTO sketch_of_data_to_count
SELECT hll_count_build(a)
FROM data_to_count2;

SELECT hll_count_estimate(a) AS hll_estimate, a AS sketch
FROM sketch_of_data_to_count
ORDER BY 1;

hll_estimate (BIGINT)	sketch (BYTEA)
3291008	\x2f41676772656761746546…
4948957	\x2f41676772656761746546…

SELECT hll_count_estimate(hll_count_merge(a)) AS hll_estimate
FROM sketch_of_data_to_count;

hll_estimate (BIGINT)
6606880

HASH_AGG

HLL_COUNT_DISTINCT

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Example

​Syntax

​Parameters

​Return Type

​Example

Syntax

Parameters

Return Type

Example