Aggregate UDF Calling Patterns

The calling patterns for the user-supplied aggregate UDF functions are more complex and varied than the scalar calling patterns.

create table t (a int, b int, c int)
insert into t values (1, 1, 1)
insert into t values (2, 1, 1)
insert into t values (3, 1, 1)
insert into t values (4, 2, 1)
insert into t values (5, 2, 1)
insert into t values (6, 2, 1)

The following abbreviation is used:

RR = a_v3_extfn_aggregate_context. _result_row_offset_from_start_of_partition – This value indicates the current row number inside the current partition for which a value is calculated. The value is set during windowed aggregates and is intended to be used during the evaluation step of unbounded windows; it is available at all evaluate calls.

SAP Sybase IQ is a multi user application. Many users can simultaneously execute the same UDF. Certain OLAP queries excute UDFs multiple times within the same query, sometimes in parallel.

• Simple Aggregate Ungrouped
  The simple aggregate ungrouped calling pattern totals the input values of all rows and produces a result.
• Simple Aggregate Grouped
  The simple aggregate grouped calling pattern totals the input values of all rows in the group and produces a result. _reset_extfn identifies the beginning of a group.
• OLAP-Style Aggregate Calling Pattern with Unbounded Window
  Partitioning on “b” creates the same partitions as grouping on “b”. An unbounded window causes the “a” value to be evaluated for each row of the partition. Because this is an unbounded query, all values are fed to the UDF first, followed by an evaluation cycle. Context indicators are set to 1 for _window_has_unbounded_preceding and _window_has_unbounded_following
• OLAP-Style Unoptimized Cumulative Window Aggregate
  If _evaluate_cumulative_extfn is not supplied, this cumulative sum is evaluated through this calling pattern, which is less efficient than _evaluate_cumulative_extfn.
• OLAP-Style Optimized Cumulative Window Aggregate
  If _evaluate_cumulative_extfn is supplied, this cumulative sum is evaluated where the next_value/evaluate sequence is combined into a single _evaluate_cumulative_extfn call for each row within each partition.
• OLAP-Style Unoptimized Moving Window Aggregate
  If _drop_value_extfn function is not supplied, this moving window sum is evaluated through this significantly less efficient than using _drop_value_extfn.
• OLAP-Style Optimized Moving Window Aggregate
  If the _drop_value_extfn function is supplied, this moving window sum is evaluated using this calling pattern, which is more efficient than using _drop_value_extfn.
• OLAP-Style Unoptimized Moving Window Following Aggregate
  If _drop_value_extfn function is not supplied, this moving window sum is evaluated through the following calling pattern. This case is similar to the previous moving window example, but the row being evaluated is not the last row given by next value function.
• OLAP-Style Optimized Moving Window Following Aggregate
  If _drop_value_extfn function is supplied, this moving window sum is evaluated through the following calling pattern. Again, this case is similar to the previous moving window example, but the row being evaluated is not the last row given by next value function.
• OLAP-Style Unoptimized Moving Window without Current
  Assume the UDF my_sum works like the built-in SUM. If _drop_value_extfn function is not supplied, this moving window count is evaluated through the following calling pattern. This case is similar to the previous moving window examples, but the current row is not part of the window frame.
• OLAP-Style Optimized Moving Window without Current
  If _drop_value_extfn function is supplied, this moving window count is evaluated through the following calling pattern. This case is similar to the previous moving window examples, but the current row is not part of the window frame.
• External Function Prototypes
  Define the API by a header file named extfnapiv3.h (extfnapiv4.h for the v4 API) in the subdirectory of your SAP Sybase IQ installation directory. This header file handles the platform-dependent features of external function prototypes.