APPLY_NORMALIZE

A UDTF function that applies the normalization parameters saved in a model to a set of specified input columns. If any column specified in the function is not in the model, its data passes through unchanged to APPLY_NORMALIZE.

Note: If a column contains only one distinct value, APPLY_NORMALIZE returns NaN for values in that column.

Syntax

APPLY_NORMALIZE ( input‑columns
	           USING PARAMETERS model_name='model‑name');

Arguments

input‑columns

Comma-separated list of columns to use from the input relation, or asterisk (*) to select all columns. If you supply an asterisk, APPLY_NORMALIZE normalizes all columns in the model.

Parameter Settings

Parameter name Set to…
model_name Name of the model (case-insensitive)

Examples

The following example creates a model with NORMALIZE_FIT using the wt and hp columns in table mtcars , and then uses this model in successive calls to APPLY_NORMALIZE and REVERSE_NORMALIZE. 

=> SELECT NORMALIZE_FIT('mtcars_normfit', 'mtcars', 'wt,hp', 'minmax');
NORMALIZE_FIT
---------------
Success
(1 row)

The following call to APPLY_NORMALIZE specifies the hp and cyl columns in table mtcars, where hp is in the normalization model and cyl is not in the normalization model:

=> CREATE TABLE metcars_normalized AS SELECT APPLY_NORMALIZE 
    (hp, cyl USING PARAMETERS model_name = 'mtcars_normfit') FROM mtcars;
CREATE TABLE

=> SELECT * FROM mtcars_normalized;
hp        	    | cyl
-------------------+-----
0.62897527217865   |   8
0.681978821754456  |   8
                0  |   4
0.434628963470459  |   8
0.575971722602844  |   8
                1  |   8
0.204946994781494  |   6
0.204946994781494  |   6
0.201413422822952  |   4
0.681978821754456  |   8
0.434628963470459  |   6
0.0494699664413929 |   4
0.0494699664413929 |   4
0.749116599559784  |   8
0.0353356897830963 |   4
0.452296823263168  |   8
0.159010604023933  |   4
0.346289753913879  |   8
0.54063606262207   |   8
0.144876331090927  |   4
0.346289753913879  |   8
0.204946994781494  |   6
0.215547695755959  |   4
0.15194346010685   |   4
0.250883400440216  |   6
0.250883400440216  |   6
0.452296823263168  |   8
0.452296823263168  |   8
0.434628963470459  |   8
0.137809187173843  |   4
0.045936394482851  |   4
0.187279149889946  |   6
(32 rows)

The following call to REVERSE_NORMALIZE also specifies the hp and cyl columns in table mtcars, where hp is in normalization model mtcars_normfit, and cyl is not in the normalization model.

=> SELECT REVERSE_NORMALIZE (hp, cyl USING PARAMETERS model_name='mtcars_normfit') FROM mtcars;
hp    | cyl
------+-----
42502 |   8
58067 |   8
26371 |   4
42502 |   8
31182 |   6
32031 |   4
26937 |   4
34861 |   6
34861 |   6
50992 |   8
50992 |   8
49577 |   8
25805 |   4
18447 |   4
29767 |   6
65142 |   8
69387 |   8
14768 |   4
49577 |   8
60897 |   8
94857 |   8
31182 |   6
31182 |   6
30899 |   4
69387 |   8
49577 |   6
18730 |   4
18730 |   4
74764 |   8
17598 |   4
50992 |   8
27503 |   4
(32 rows)

See Also