Approximate Functions
GreptimeDB provides functions for approximate distinct counts and quantiles.
These functions are experimental and may change in future releases.
Approximate Count Distinct (HLL)
GreptimeDB implements HyperLogLog (HLL) for memory-efficient, fast approximate distinct counts. Three functions create a sketch, merge sketches, and read the approximate count.
HLL returns an estimate rather than an exact count. Its relative standard error is approximately 1.04 / sqrt(m), where m is the number of registers. GreptimeDB uses 16,384 registers, corresponding to approximately 0.8125% relative standard error.
hll
hll(value) coerces each value to STRING and aggregates it into a binary HLL state. The state can be stored in a BINARY column, merged with other states, or passed to hll_count. Persisting the state makes it possible to calculate later rollups without retaining or scanning every original value.
hll_merge
hll_merge(hll_state) aggregates binary states produced by hll into one state. Use it to combine sketches from different groups, time windows, or data sources. This supports staged aggregation: a query can create and store states at one granularity, then merge them into larger rollups later.
hll_count
hll_count(hll_state) returns the approximate distinct count from a state produced by hll or hll_merge. HLL states are binary intermediate values rather than human-readable counts, so apply hll_count when returning the estimate to a query.
Full Usage Example
This example calculates approximate distinct user counts by time window. It first creates compact states from raw access records, stores those states, and then reads or merges them for different reporting windows.
Create access_log for source rows and access_log_10s for one binary HLL state per 10-second window. The BINARY state column preserves the sketch needed for later counts and rollups.
CREATE TABLE access_log (
`url` STRING,
user_id BIGINT,
ts TIMESTAMP TIME INDEX,
PRIMARY KEY (`url`, `user_id`)
);
CREATE TABLE access_log_10s (
`url` STRING,
time_window timestamp time INDEX,
state BINARY,
PRIMARY KEY (`url`)
);
Insert sample data into access_log:
INSERT INTO access_log VALUES
("/dashboard", 1, "2025-03-04 00:00:00"),
("/dashboard", 1, "2025-03-04 00:00:01"),
("/dashboard", 2, "2025-03-04 00:00:05"),
("/dashboard", 2, "2025-03-04 00:00:10"),
("/dashboard", 2, "2025-03-04 00:00:13"),
("/dashboard", 4, "2025-03-04 00:00:15"),
("/not_found", 1, "2025-03-04 00:00:10"),
("/not_found", 3, "2025-03-04 00:00:11"),
("/not_found", 4, "2025-03-04 00:00:12");
Group rows into 10-second windows and store one HLL state for each URL and window. date_bin assigns each row to a window, and hll aggregates the user IDs in that group:
-- Use a 10-second windowed query to calculate the HyperLogLog states
INSERT INTO
access_log_10s
SELECT
`url`,
date_bin("10s" :: INTERVAL, ts) AS time_window,
hll(`user_id`) AS state
FROM
access_log
GROUP BY
`url`,
time_window;
-- results will be similar to this:
-- Query OK, 3 rows affected (0.05 sec)
Read the approximate distinct count from each stored state. For very small groups, an exact count can be more appropriate because HLL is designed for large cardinalities and returns an estimate:
-- use hll_count to query approximate data in access_log_10s, notice for small datasets, the results may not be very accurate.
SELECT `url`, `time_window`, hll_count(state) FROM access_log_10s;
-- results as follows:
-- +------------+---------------------+---------------------------------+
-- | url | time_window | hll_count(access_log_10s.state) |
-- +------------+---------------------+---------------------------------+
-- | /dashboard | 2025-03-04 00:00:00 | 2 |
-- | /dashboard | 2025-03-04 00:00:10 | 2 |
-- | /not_found | 2025-03-04 00:00:10 | 3 |
-- +------------+---------------------+---------------------------------+
Merge the 10-second states to calculate a distinct count for each one-minute window. Because HLL states are composable, this rollup does not need to scan the original access records again and is useful for trend analysis:
-- aggregate the 10-second data to a 1-minute level by merging the HyperLogLog states using `hll_merge`.
SELECT
`url`,
date_bin('1 minute' :: INTERVAL, `time_window`) AS time_window_1m,
hll_count(hll_merge(state)) as uv_per_min
FROM
access_log_10s
GROUP BY
`url`,
date_bin('1 minute' :: INTERVAL, `time_window`);
-- results as follows:
-- +------------+---------------------+------------+
-- | url | time_window_1m | uv_per_min |
-- +------------+---------------------+------------+
-- | /dashboard | 2025-03-04 00:00:00 | 3 |
-- | /not_found | 2025-03-04 00:00:00 | 3 |
-- +------------+---------------------+------------+
hll_merge returns another binary state. Apply hll_count to that state to obtain the estimate.
The following diagram shows the state creation, count, and merge operations:
Approximate Quantile (UDDSketch)
GreptimeDB implements UDDSketch with functions to create, merge, and query sketch states.
UDDSketch provides fast approximate quantiles with bounded memory use. Its memory use and error depend on bucket_num, error_rate, and the input value range, as described below.
uddsketch_state
uddsketch_state(bucket_num, error_rate, value) aggregates DOUBLE values into a binary state. The state is a compact summary of the value distribution, organized into logarithmic buckets rather than retaining every input value.
bucket_num: Maximum number of buckets in the sketch.error_rate: Initial relative-error bound.value:DOUBLEexpression to aggregate.
The state can be stored in a BINARY column, merged with uddsketch_merge, or queried with uddsketch_calc. Storing states is useful when later queries need quantiles at several time granularities.
uddsketch_merge
uddsketch_merge(bucket_num, error_rate, udd_state) aggregates binary UDDSketch states into one state. Use it to combine distributions from different groups, time windows, or data sources without rereading the original values. bucket_num and error_rate must match the parameters used to create the input states; otherwise, the merge fails.
uddsketch_calc
uddsketch_calc(quantile, udd_state) returns a quantile estimate from a state created by uddsketch_state or uddsketch_merge. It reads the state without changing it, so the same stored state can be used to calculate several quantiles.
quantile: Value from 0 through 1; for example,0.99requests the 99th percentile.udd_state: Binary UDDSketch state.
See UDDSketch Full Usage Example for an example that combines these functions.
How to determine bucket_num and error_rate
The bucket_num parameter sets the maximum number of internal buckets and therefore bounds the sketch's memory use. A larger value can represent a wider ratio between the minimum and maximum values before compaction. When the sketch reaches this limit, it merges buckets at one end of the value range and loses accuracy. The recommended value is 128, which balances accuracy and memory use for most workloads.
The error_rate sets the initial relative-error bound used to map values to buckets. A smaller value creates more granular buckets. If the value range requires more than bucket_num buckets, UDDSketch compacts buckets and the effective maximum error increases. Therefore, an error_rate of 0.01 is not an unconditional guarantee that every result remains within 1% of the exact value.
These parameters trade memory for accuracy. A small error_rate requires enough buckets for the data's dynamic range. If bucket_num is too small, decreasing error_rate does not prevent compaction or the resulting increase in maximum error.
UDDSketch Full Usage Example
This example combines the three uddsketch functions to calculate approximate quantiles. It stores one state per five-second window, calculates p99 from each state, and then merges those states for a one-minute rollup.
Create percentile_base for the raw data and percentile_5s for the UDDSketch states in each five-second window. The percentile_state column stores the binary sketch state so that later queries can calculate or merge quantiles without scanning the raw table.
CREATE TABLE percentile_base (
`id` INT PRIMARY KEY,
`value` DOUBLE,
`ts` timestamp(0) time index
);
CREATE TABLE percentile_5s (
`percentile_state` BINARY,
`time_window` timestamp(0) time index
);
Insert some sample data into percentile_base :
INSERT INTO percentile_base (`id`, `value`, `ts`) VALUES
(1, 10.0, 1),
(2, 20.0, 2),
(3, 30.0, 3),
(4, 40.0, 4),
(5, 50.0, 5),
(6, 60.0, 6),
(7, 70.0, 7),
(8, 80.0, 8),
(9, 90.0, 9),
(10, 100.0, 10);
Group rows into five-second windows and store one UDDSketch state for each window. date_bin assigns input values to a window, and uddsketch_state summarizes the distribution in that group:
INSERT INTO
percentile_5s
SELECT
uddsketch_state(128, 0.01, `value`) AS percentile_state,
date_bin('5 seconds' :: INTERVAL, `ts`) AS time_window
FROM
percentile_base
GROUP BY
time_window;
-- results will be similar to this:
-- Query OK, 3 rows affected (0.05 sec)
Calculate p99 for each stored state. The quantile argument 0.99 asks for an estimate of the value below which approximately 99% of the observations fall:
-- query percentile_5s to get the approximate 99th percentile
SELECT
time_window,
uddsketch_calc(0.99, `percentile_state`) AS p99
FROM
percentile_5s;
-- results as follows:
-- +---------------------+--------------------+
-- | time_window | p99 |
-- +---------------------+--------------------+
-- | 1970-01-01 00:00:00 | 40.04777053326359 |
-- | 1970-01-01 00:00:05 | 89.13032933635911 |
-- | 1970-01-01 00:00:10 | 100.49456770856492 |
-- +---------------------+--------------------+
Merge the five-second states to calculate p99 for each one-minute window. uddsketch_merge combines the stored distributions, and the outer uddsketch_calc reads p99 from the merged state. This avoids rebuilding the one-minute distribution from raw rows:
-- in addition, we can aggregate the 5-second data to a 1-minute level by merging the UDDSketch states using `uddsketch_merge`.
SELECT
date_bin('1 minute' :: INTERVAL, `time_window`) AS time_window_1m,
uddsketch_calc(0.99, uddsketch_merge(128, 0.01, `percentile_state`)) AS p99
FROM
percentile_5s
GROUP BY
time_window_1m;
-- results as follows:
-- +---------------------+--------------------+
-- | time_window_1m | p99 |
-- +---------------------+--------------------+
-- | 1970-01-01 00:00:00 | 100.49456770856492 |
-- +---------------------+--------------------+
The following diagram shows the state creation, quantile calculation, and merge operations:
