count_min_sketch aggregate function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns a count-min sketch of all values in the group in expr with the epsilon, confidence and seed.


count_min_sketch ( [ALL | DISTINCT] expr, epsilon, confidence, seed ) [FILTER ( WHERE cond ) ]

This function can also be invoked as a window function using the OVER clause.


  • expr: An expression that evaluates to an integral numeric, STRING, or BINARY.

  • epsilon: A DOUBLE literal greater than 0 describing the relative error.

  • confidence: A DOUBLE literal greater than 0 and less than 1.

  • seed: An INTEGER literal.

  • cond: An optional boolean expression filtering the rows used for aggregation.



Count-min sketch is a probabilistic data structure used for cardinality estimation using sub-linear space.

If DISTINCT is specified the function operates only on a unique set of expr values.


> SELECT hex(count_min_sketch(col, 0.5d, 0.5d, 1)) FROM VALUES (1), (2), (1) AS tab(col);

> SELECT hex(count_min_sketch(DISTINCT col, 0.5d, 0.5d, 1)) FROM VALUES (1), (2), (1) AS tab(col);