`tuple_difference_theta_integer` function

Applies to: Databricks Runtime 18.2 and above

Computes the set difference (A minus B) of a TupleSketch with integer summaries and a Theta Sketch. The returned sketch contains only keys that appear in the TupleSketch but not in the Theta Sketch.

Syntax

tuple_difference_theta_integer ( first, second )

Arguments

first: A TupleSketch in binary format with integer summaries (set A).
second: A Theta Sketch in binary format (set B), such as from theta_sketch_agg aggregate function.

Returns

A BINARY value containing the TupleSketch representing the set difference (A - B).

If first or second is NULL, the result is NULL.

Notes

Argument order is fixed: first must be a TupleSketch and second must be a Theta Sketch. Passing the sketch types in the reverse order raises TUPLE_INVALID_INPUT_SKETCH_BUFFER_FAMILY.
Summary values from the TupleSketch are preserved for keys in the result.
This function is not the same as tuple_sketch_theta_integer, which returns the sampling rate of a TupleSketch.

Common error conditions

Examples

SQL
> SELECT tuple_sketch_estimate_integer(
    tuple_difference_theta_integer(
      tuple_sketch_agg_integer(col1, val1),
      theta_sketch_agg(col2)
    )
  ) FROM VALUES (5, 5, 4), (1, 1, 4), (2, 2, 5), (3, 3, 1) tab(col1, val1, col2);
2.0

Syntax​

Arguments​

Returns​

Notes​

Common error conditions​

Examples​

Related​