Skip to main content
Last updated on

kll_sketch_get_quantile_bigint function

Applies to: check marked yes Databricks Runtime 18.0 and later

Estimates the value at a given quantile rank (or multiple ranks) from an integer KLL sketch.

Syntax​

kll_sketch_get_quantile_bigint ( sketch, rank )

Arguments​

  • sketch: A BINARY expression containing a serialized integer KLL sketch.
  • rank: A DOUBLE expression or ARRAY<DOUBLE> of quantile ranks between 0.0 and 1.0, where:
    • 0.0 is the minimum,
    • 0.5 is the median, and
    • 1.0 is the maximum.

Returns​

  • If rank is DOUBLE: returns a BIGINT value representing the estimated quantile.
  • If rank is ARRAY<DOUBLE>: returns ARRAY<BIGINT> with quantile estimates for each rank.

Notes​

  • Rank must be between 0.0 and 1.0 inclusive.
  • Returns NULL if the sketch is empty.
  • Common quantiles: 0.25 (Q1), 0.5 (median), 0.75 (Q3), 0.95 (P95), 0.99 (P99).

Examples​

SQL
-- Get median from a sketch
> WITH sketch_data AS (
    SELECT kll_sketch_agg_bigint(value) AS sketch
    FROM VALUES (1), (2), (3), (4), (5) AS T(value)
  )
  SELECT kll_sketch_get_quantile_bigint(sketch, 0.5) FROM sketch_data
  3
-- Get multiple quantiles (Q1, median, Q3)
> WITH sketch_data AS (
    SELECT kll_sketch_agg_bigint(value) AS sketch
    FROM VALUES (1), (2), (3), (4), (5) AS T(value)
  )
  SELECT kll_sketch_get_quantile_bigint(sketch, array(0.25, 0.5, 0.75)) FROM sketch_data
  [2, 3, 4]
-- Get P95 and P99 for latency monitoring
> WITH sketch_data AS (
    SELECT kll_sketch_agg_bigint(latency_ms) AS sketch FROM latency_table
  )
  SELECT kll_sketch_get_quantile_bigint(sketch, array(0.95, 0.99)) FROM sketch_data
  [145, 234]