hyperloglog() and approx_count_distinct() functions

hyperloglog(
    buckets INTEGER,
    value AnyElement
) RETURNS Hyperloglog

This is the first step for estimating the approximate number of distinct values using the hyperloglog algorithm. Use hyperloglog to create an intermediate aggregate from your raw data. This intermediate form can then be used by one or more accessors in this group to compute final results.

Optionally, multiple such intermediate aggregate objects can be combined using rollup() before an accessor is applied.

If you're not sure what value to set for buckets, try using the alternate aggregate function, approx_count_distinct(). approx_count_distinct also creates a hyperloglog, but it sets a default bucket value that should work for many use cases.

Examples

Given a table called samples, with a column called weights, return a hyperloglog over the weights column:

SELECT hyperloglog(32768, weights) FROM samples;

Copy

Using the same data, build a view from the aggregate that you can pass to other hyperloglog functions:

CREATE VIEW hll AS SELECT hyperloglog(32768, data) FROM samples;

Copy

approx_count_distinct(
    value AnyElement
) RETURNS Hyperloglog

This is an alternate first step for approximating the number of distinct values. It provides some added convenience by using some sensible default parameters to create a hyperloglog.

Use approx_count_distinct to create an intermediate aggregate from your raw data. This intermediate form can then be used by one or more accessors in this group to compute final results.

Optionally, multiple such intermediate aggregate objects can be combined using rollup() before an accessor is applied.

Examples

Given a table called samples, with a column called weights, return a hyperloglog over the weights column::

SELECT toolkit_experimental.approx_count_distinct(weights) FROM samples;

Copy

Using the same data, build a view from the aggregate that you can pass to other hyperloglog functions:

CREATE VIEW hll AS SELECT toolkit_experimental.approx_count_distinct(data) FROM samples;

Copy

distinct_count(
    hyperloglog Hyperloglog
) RETURNS BIGINT

Estimate the number of distinct values from a hyperloglog

Examples

Estimate the number of distinct values from a hyperloglog named hyperloglog. The expected output is 98,814:

SELECT distinct_count(hyperloglog(8192, data))
  FROM generate_series(1, 100000) data

Copy

distinct_count
----------------
        98814

stderror(
    hyperloglog Hyperloglog
) RETURNS DOUBLE PRECISION

Estimate the relative standard error of a Hyperloglog. For approximate relative errors by number of buckets, see the relative errors section.

Examples

Estimate the relative standard error of a hyperloglog named hyperloglog. The expected output is 0.011490485194281396:

SELECT stderror(hyperloglog(8192, data))
  FROM generate_series(1, 100000) data

Copy

stderror       
----------------------
0.011490485194281396

rollup(
  hyperloglog Hyperloglog
) RETURNS Hyperloglog

Combine multiple intermediate hyperloglog aggregates, produced by hyperloglog, into a single intermediate hyperloglog aggregate. For example, you can use rollup to combine hyperloglog from 15-minute buckets into daily buckets.