DuckDB Extensions
KLL functions in the Apache DataSketches DuckDB extension
Function category
KLL
14 functions[KLL quantile sketch](https://datasketches.apache.org/docs/KLL/KLLSketch.html) — modern mergeable quantile estimator. Best balance of accuracy, speed, and size for general-purpose quantile work. Default choice unless you have a specific reason to pick TDigest or REQ.
Signature
datasketch_kll(col0: INTEGER, col1: sketch_kll_bigint | sketch_kll_double | sketch_kll_usmallint | …) → <sketch>
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
INTEGER
|
Mode Positional | Description |
Argument
col1
|
Type
sketch_kll_bigint | sketch_kll_double | sketch_kll_usmallint | …
20 concrete typesBIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINTsketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Return type varies by input — 10 concrete types
sketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
Description
Aggregate input values into a KLL quantile sketch. Best balance of accuracy, speed, and size — the modern default for general-purpose quantile estimation. The first argument K controls accuracy: higher K → smaller error and bigger sketch. K = 200 is a sensible production starting point.
1
p50 / p95 / p99 latency from one KLL sketch
WITH agg AS (
SELECT datasketch_kll(200, latency_ms) AS sketch FROM requests
)
SELECT datasketch_kll_quantile(sketch, 0.50, true) AS p50,
datasketch_kll_quantile(sketch, 0.95, true) AS p95,
datasketch_kll_quantile(sketch, 0.99, true) AS p99
FROM agg;Related functions
- datasketch_kll_quantile() — Approximate quantile at a given rank — given a sketch and `r ∈ [0, 1]`, returns the value at that rank in the sorted distribution
- datasketch_kll_rank() — Inverse of `datasketch_kll_quantile` — given a value, return its approximate rank `r ∈ [0, 1]` in the sorted distribution
- datasketch_kll_cdf() — CDF over a list of split points — one call returns the cumulative rank at each
- datasketch_kll_pmf() — PMF (probability mass) over a list of split points — fraction of the distribution falling in each bucket
- datasketch_kll_normalized_rank_error() — Return the normalized rank error of the sketch
Signature
datasketch_kll_cdf(
col0: <sketch>,
col1: <numeric>[],
col2: BOOLEAN
) → <numeric>[]
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Argument
col1
|
Type
<numeric>[]
10 concrete typesBIGINT[]DOUBLE[]FLOAT[]INTEGER[]SMALLINT[]TINYINT[]UBIGINT[]UINTEGER[]USMALLINT[]UTINYINT[]
|
Mode Positional | Description |
Argument
col2
|
Type
BOOLEAN
|
Mode Positional | Description |
Return type varies by input — 10 concrete types
BIGINT[]DOUBLE[]FLOAT[]INTEGER[]SMALLINT[]TINYINT[]UBIGINT[]UINTEGER[]USMALLINT[]UTINYINT[]
Description
CDF over a list of split points — one call returns the cumulative rank at each. Useful for histograms.
1
Example 1
SELECT datasketch_kll_cdf(sketch, points, inclusive);Signature
datasketch_kll_describe(
col0: <sketch>,
col1: BOOLEAN,
col2: BOOLEAN
) → VARCHAR
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Argument
col1
|
Type
BOOLEAN
|
Mode Positional | Description |
Argument
col2
|
Type
BOOLEAN
|
Mode Positional | Description |
Description
Return a description of this sketch
1
Example 1
SELECT datasketch_kll_describe(sketch, include_levels, include_items);Related functions
Signature
datasketch_kll_is_empty(col0: <sketch>) → BOOLEAN
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Description
Return a boolean indicating if the sketch is empty
1
Example 1
SELECT datasketch_kll_is_empty(sketch);Related functions
Signature
datasketch_kll_is_estimation_mode(col0: <sketch>) → BOOLEAN
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Description
Return a boolean indicating if the sketch is in estimation mode
1
Example 1
SELECT datasketch_kll_is_estimation_mode(sketch);Related functions
Signature
datasketch_kll_k(col0: <sketch>) → USMALLINT
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Description
Return the value of K for this sketch
1
Example 1
SELECT datasketch_kll_k(sketch);Related functions
Signature
datasketch_kll_max_item(col0: <sketch>) → <numeric>
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Return type varies by input — 10 concrete types
BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINT
Description
Return the maxium item in the sketch
1
Example 1
SELECT datasketch_kll_max_item(sketch);Signature
datasketch_kll_min_item(col0: <sketch>) → <numeric>
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Return type varies by input — 10 concrete types
BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINT
Description
Return the minimum item in the sketch
1
Example 1
SELECT datasketch_kll_min_item(sketch);Signature
datasketch_kll_n(col0: <sketch>) → UBIGINT
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Description
Return the number of items contained in the sketch
1
Example 1
SELECT datasketch_kll_rank(sketch);Related functions
Signature
datasketch_kll_normalized_rank_error(col0: <sketch>, col1: BOOLEAN) → DOUBLE
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Argument
col1
|
Type
BOOLEAN
|
Mode Positional | Description |
Description
Return the normalized rank error of the sketch
1
Example 1
SELECT datasketch_kll_normalized_rank_error(sketch, is_pmf);Related functions
Signature
datasketch_kll_num_retained(col0: <sketch>) → UBIGINT
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Description
Return the number of retained items in the sketch
1
Example 1
SELECT datasketch_kll_num_retained(sketch);Related functions
Signature
datasketch_kll_pmf(
col0: <sketch>,
col1: <numeric>[],
col2: BOOLEAN
) → <numeric>[]
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Argument
col1
|
Type
<numeric>[]
10 concrete typesBIGINT[]DOUBLE[]FLOAT[]INTEGER[]SMALLINT[]TINYINT[]UBIGINT[]UINTEGER[]USMALLINT[]UTINYINT[]
|
Mode Positional | Description |
Argument
col2
|
Type
BOOLEAN
|
Mode Positional | Description |
Return type varies by input — 10 concrete types
BIGINT[]DOUBLE[]FLOAT[]INTEGER[]SMALLINT[]TINYINT[]UBIGINT[]UINTEGER[]USMALLINT[]UTINYINT[]
Description
PMF (probability mass) over a list of split points — fraction of the distribution falling in each bucket.
1
Example 1
SELECT datasketch_kll_pmf(sketch, points, inclusive);Signature
datasketch_kll_quantile(
col0: <sketch>,
col1: DOUBLE,
col2: BOOLEAN
) → <numeric>
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Argument
col1
|
Type
DOUBLE
|
Mode Positional | Description |
Argument
col2
|
Type
BOOLEAN
|
Mode Positional | Description |
Return type varies by input — 10 concrete types
BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINT
Description
Approximate quantile at a given rank — given a sketch and r ∈ [0, 1], returns the value at that rank in the sorted distribution.
1
Example 1
SELECT datasketch_kll_rank(sketch, rank, inclusive);Related functions
- datasketch_kll() — Aggregate input values into a KLL quantile sketch
- datasketch_kll_rank() — Inverse of `datasketch_kll_quantile` — given a value, return its approximate rank `r ∈ [0, 1]` in the sorted distribution
- datasketch_kll_cdf() — CDF over a list of split points — one call returns the cumulative rank at each
Signature
datasketch_kll_rank(
col0: <sketch>,
col1: <numeric>,
col2: BOOLEAN
) → DOUBLE
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
col0
|
Type
<sketch>
10 concrete typessketch_kll_bigintsketch_kll_doublesketch_kll_floatsketch_kll_integersketch_kll_smallintsketch_kll_tinyintsketch_kll_ubigintsketch_kll_uintegersketch_kll_usmallintsketch_kll_utinyint
|
Mode Positional | Description |
Argument
col1
|
Type
<numeric>
10 concrete typesBIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINT
|
Mode Positional | Description |
Argument
col2
|
Type
BOOLEAN
|
Mode Positional | Description |
Description
Inverse of datasketch_kll_quantile — given a value, return its approximate rank r ∈ [0, 1] in the sorted distribution.
1
Example 1
SELECT datasketch_kll_rank(sketch, item, inclusive);