tuple_difference_theta_double function
Applies to: 
Databricks Runtime 18.2 and above
Computes the set difference (A minus B) of a TupleSketch with double 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_double ( first, second )
Arguments
- first: A TupleSketch in binary format with double summaries (set A).
- second: A Theta Sketch in binary format (set B), such as from
theta_sketch_aggaggregate 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:
firstmust be a TupleSketch andsecondmust 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_double, which returns the sampling rate of a TupleSketch.
Common error conditions
- TUPLE_INVALID_INPUT_SKETCH_BUFFER
- TUPLE_INVALID_INPUT_SKETCH_BUFFER_FAMILY
- THETA_INVALID_INPUT_SKETCH_BUFFER
- THETA_INVALID_INPUT_SKETCH_BUFFER_FAMILY
Examples
SQL
> SELECT tuple_sketch_estimate_double(
tuple_difference_theta_double(
tuple_sketch_agg_double(col1, val1),
theta_sketch_agg(col2)
)
) FROM VALUES (5, 5.0D, 4), (1, 1.0D, 4), (2, 2.0D, 5), (3, 3.0D, 1) tab(col1, val1, col2);
2.0