IDAX.AGGDISC - Aggregate data in outliers and bins

Use this stored procedure to aggregate the input data in outliers and bins, and to count the number of records in each bin during one data pass. For each bin, the minimum value, the maximum value, the average value, and the count of values are gathered and returned.

Authorization

The privileges held by the authorization ID of the statement must include the IDAX_USER role.

Syntax

IDAX.AGGDISC (in intable VARCHAR(256), in incolumn VARCHAR(32000), in validitypredicates VARCHAR(32000), in N INTEGER, in S INTEGER, in quantiles VARCHAR(32000))

Parameter descriptions

intable: Mandatory.; The name of the input table.; Data type: VARCHAR(256)
incolumn: Mandatory.; The columns of the input table that are to be discretized. The columns are separated by a semi-colon (;).; Data type: VARCHAR(A)
validitypredicates: Optional.; The list of predicates that are to be applied on each row to determine the validity of the corresponding column value. The predicates are separated by a semi-colon (;).; The predicates and the names of the columns are listed in the same order.; If a predicate is empty, all non-null values of the corresponding column are valid.; If the validitypredicates parameter is set to NULL, all non-null values of all columns are valid.; Default: NULL; Data type: VARCHAR(3200)
N: Mandatory.; The number of bins including outliers.; Min: 1; Max: 1000; Data type: INTEGER
S: Optional.; The number of outliers (single values) that are to be kept on the left side and on the right side of the aggregated bins.; The N parameter N must be greater than twice the number of the S parameter.; Min: 0; Max: (N-1)/2; Default: NULL; Data type: INTEGER
quantiles: The list of quantile percentage that is to be calculated for each column. The quantiles are separated by a semi-colon (;).; Each percentage is an integer value in the range of 0 - 100, for example, quartiles are calculated with string '25;50;75'.; If the quantiles parameter is set to NULL, no quantiles are calculated and returned.; Default: NULL; Data type: VARCHAR(3200)

Returned information

TABLE(COLUMNNAME varchar(128), MINIMUM double, MAXIMUM double, MEAN double, COUNT bigint) that contains the outliers and aggregated bins as result set.

If the quantiles parameter is set, a second result set is returned. It contains the quantiles as TABLE(COLUMNNAME varchar(128), QUANTILE integer, VALUE double).

Example

CALL IDAX.AGGDISC ('SAMPLES.CUSTOMER_CHURN', 'AVG_SPENT_RETAIN_PM;ANNUAL_REVENUE_MIL', null, 10, 2, '25;50;75')