`tuple_intersection_theta_double` function

Applies to: Databricks Runtime 18.2 and above

Computes the set intersection of a TupleSketch with double summaries and a Theta Sketch.

Syntax

tuple_intersection_theta_double ( first, second [, mode ] )

Arguments

first: A TupleSketch in binary format with double summaries.
second: A Theta Sketch in binary format, such as from theta_sketch_agg aggregate function.
mode: An optional STRING literal specifying the aggregation mode. Valid values: 'sum', 'min', 'max', 'alwaysone'. The default is 'sum'.

Returns

A BINARY value containing the TupleSketch representing the intersection.

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.
The result contains only keys that appear in both input sketches.
Theta Sketch keys are assigned a default double summary based on mode: 0.0 for 'sum', +Infinity for 'min', -Infinity for 'max', or 1.0 for 'alwaysone'.

Common error conditions

Examples

SQL
> SELECT tuple_sketch_estimate_double(
    tuple_intersection_theta_double(
      tuple_sketch_agg_double(col1, val1),
      theta_sketch_agg(col2)
    )
  ) FROM VALUES (1, 1.0D, 1), (2, 2.0D, 2), (3, 3.0D, 4) tab(col1, val1, col2);
2.0

Syntax​

Arguments​

Returns​

Notes​

Common error conditions​

Examples​

Related​