`tuple_intersection_theta_integer` function

Applies to: Databricks Runtime 18.2 and above

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

Syntax

tuple_intersection_theta_integer ( first, second [, mode ] )

Arguments

first: A TupleSketch in binary format with integer 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 integer summary based on mode: 0 for 'sum', the maximum INTEGER value for 'min', the minimum INTEGER value for 'max', or 1 for 'alwaysone'.

Common error conditions

Examples

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

Syntax​

Arguments​

Returns​

Notes​

Common error conditions​

Examples​

Related​