NORMALIZE

Runs a normalization algorithm on an input relation. The output is a view with the normalized data.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

NORMALIZE ( 'output-view', 'input-relation', 'input-columns', 'normalization-method'
           [ USING PARAMETERS exclude_columns = 'excluded-columns' ] )

Arguments

output-view

The name of the view showing the input relation with normalized data replacing the specified input columns. .

input-relation

The table or view that contains the data to normalize. If the input relation is defined in Hive, use SYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.

input-columns

Comma-separated list of numeric input columns that contain the values to normalize, or asterisk (*) to select all columns.

normalization-method

The normalization method to use, one of the following:

• minmax

• zscore

• robust_zscore

If infinity values appear in the table, the method ignores those values.

Parameters

exclude_columns

Comma-separated list of column names from input-columns to exclude from processing.

Privileges

Non-superusers:

• SELECT privileges on the input relation

• CREATE privileges on the output view schema

Examples

These examples show how you can use the NORMALIZE function on the wt and hp columns in the mtcars table.

Execute the NORMALIZE function, and specify the minmax method:

=> SELECT NORMALIZE('mtcars_norm', 'mtcars',
                    'wt, hp', 'minmax');
        NORMALIZE
--------------------------
 Finished in 1 iteration

(1 row)

Execute the NORMALIZE function, and specify the zscore method:

=> SELECT NORMALIZE('mtcars_normz','mtcars',
                    'wt, hp', 'zscore');
        NORMALIZE
--------------------------
 Finished in 1 iteration

(1 row)

Execute the NORMALIZE function, and specify the robust_zscore method:

=> SELECT NORMALIZE('mtcars_normz', 'mtcars',
                    'wt, hp', 'robust_zscore');
        NORMALIZE
--------------------------
 Finished in 1 iteration

(1 row)

See also