Skip to main content
Last updated on

tuple_union_theta_integer function

Applies to: check marked yes Databricks Runtime 18.2 and above

Merges a TupleSketch with integer summaries and a Theta Sketch using set union.

Syntax​

tuple_union_theta_integer ( first, second [, lgNomEntries [, 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.
  • lgNomEntries: An optional INTEGER literal specifying the log-base-2 of nominal entries. Must be between 4 and 26, inclusive. The default is 12.
  • 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 merged TupleSketch with integer summaries.

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.
  • 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'.
  • When a key appears in both sketches, summary values are combined according to mode.

Common error conditions​

Examples​

SQL
> SELECT tuple_sketch_estimate_integer(
    tuple_union_theta_integer(
      tuple_sketch_agg_integer(col1, val1),
      theta_sketch_agg(col2)
    )
  ) FROM VALUES (1, 1, 4), (2, 2, 5), (3, 3, 6) tab(col1, val1, col2);
6.0
On this page