IDAX.SUMMARY1000 - Summary of up to 1000 columns

Use this stored procedure to calculate summary statistics of up to 1000 input columns. Null values are counted but are not part of the statistics computation.

Authorization

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

Syntax

IDAX.SUMMARY1000(in parameter_string varchar(32672))

Details

For continuous columns, the following statistics are collected:

  • Missing value count
  • Non-missing value count
  • Average
  • Variance
  • Standard deviation
  • Skewness
  • Excess kurtosis
  • Minimum
  • Maximum

For discrete columns, the following statistics are collected:

  • Number of missing values
  • Number of non-missing values
  • Number of distinct values
  • Frequency of the most frequent value

Parameter descriptions

parameter_string
Mandatory one-string parameter that contains pairs of <parameter>=<value> entries that are separated by a comma.
Data type: VARCHAR(32672)
The following list shows the parameter values:
intable
Mandatory.
The name of the input table.
Data type: VARCHAR(256)
outtable
Mandatory.
The name of the output table to which the summary statistics are written.
Depending on the type of the columns that are analyzed, the following output tables are created in addition:
  • <outtable>_num for numeric columns (boolean, smallint, integer, bigint, float, real, double, decfloat, decimal, numeric)
  • <outtable>_char for character columns (char, nchar, varchar, nvarchar)
  • <outtable>_date for date columns
  • <outtable>_time for time columns (time)
  • <outtable>_timestamp for timestamp columns
Data type: VARCHAR(256)
incolumn
Optional.
The columns of the input table that are separated by a semicolon (;).
If this parameter is not specified, all columns of the input table are analyzed.
Default: none
Data type: VARCHAR(ANY)
by
Optional.
The column of the input table column that splits the data into the groups for which summary statistics are to be collected.
Default: none
Data type: VARCHAR(128)
talk
Optional.
Is deprecated and has no action.
Default: no
Data type: VARCHAR(3)

Returned information

INTEGER the number of rows in the output table.

Example

CALL IDAX.SUMMARY1000('intable=GOSALES.ORDER_HEADER, outtable=ORDER_HEADER_SUM1000,incolumn=RETAILER_NAME;RETAILER_NAME_MB;RETAILER_SITE_CODE;SALES_BRANCH_CODE;ORDER_DATE;ORDER_METHOD_CODE');
select * from ORDER_HEADER_SUM1000;
select * from ORDER_HEADER_SUM1000_CHAR;
select * from ORDER_HEADER_SUM1000_NUM;
select * from ORDER_HEADER_SUM1000_TIMESTAMP;