データの正規化を行う

1 はじめに
2 NORMALIZE_FIT/APPLY_NORMALIZE
- 2.1 コマンド構文
- 2.2 利用例
3 参考情報
4 検証バージョンについて

はじめに

機械学習において数値データの列を説明変数として利用する場合は、事前に正規化を行うと精度が向上することがあります。
例えば従業員データの内、「給与」列と「勤続年数」列を説明変数として機械学習に利用する場合、給与は「円」や「ドル」、勤続年数は「年」といったように単位が異なります。また単位が異なれば当然数値の範囲も異なります。このようなスケールが異なる列を同じスケールにするために正規化を利用します。

VerticaはNORMALIZE_FIT/APPLY_NORMALIZE関数を利用するとデータの正規化を行うことができます。

NORMALIZE_FIT/APPLY_NORMALIZE

コマンド構文

SELECT NORMALIZE_FIT ( 'model‑name', 'input‑relation', 'input‑columns', 'normalization‑method'
               [ USING PARAMETERS [exclude_columns='excluded‑columns']
                                  [, output_view='output‑view'] ] )

SELECT NORMALIZE_FIT ( 'model‑name', 'input‑relation', 'input‑columns', 'normalization‑method'

[ USING PARAMETERS [exclude_columns='excluded‑columns']

[, output_view='output‑view'] ] )

パラメータ名	内容
model‑name	正規化の計算パラメータを保持するモデル名
input‑relation	正規化対象のテーブル、ビュー
input‑columns	正規化対象の列。複数列指定する場合は「,」カンマで区切ります。
normalization‑method	minmax：0～1の範囲で正規化を実施します。 zscore：平均値0、標準偏差1になるように正規化を実施します。 robust_zscore：正規四分位範囲を用いて正規化を実施します。
exclude_columns	(オプション)　input‑columnsで*(全列)と指定した場合に、対象列から除外する列
output_view	(オプション)　正規化後のデータを保存する場合はそのビュー名を指定します。

利用例

例として従業員データであるsalary_dataテーブルを正規化します。

1)years_worked(勤続年数)、current_salary(給与)列はスケールが異なるため、正規化を実施します。

SELECT * FROM salary_data ORDER BY 1;
 employee_id | first_name  | last_name  | years_worked | current_salary
-------------+-------------+------------+--------------+----------------
         110 | Brenda      | Morrison   |            0 |         111294
         112 | Jerry       | Thomas     |            7 |         121821
         189 | Andrew      | Burns      |            4 |          38798
         189 | Shawn       | Moore      |            7 |         151023
         313 | Ruby        | Black      |           13 |         282291
         426 | Anne        | Green      |           11 |         133469
         518 | Earl        | Shaw       |            2 |         294344
         593 | Lawrence    | Larson     |           19 |         264219
         620 | Mary        | Rose       |           17 |         213873
         630 | Michelle    | Crawford   |            7 |         129393
         718 | Harry       | Larson     |            6 |          48123
         777 | Ruby        | Johnston   |           16 |          51444
        1043 | Diane       | Morris     |           14 |         207271
        1126 | Susan       | Alexander  |            5 |         275869
        1157 | Jack        | Stone      |            2 |         194583
        1198 | Martha      | Cooper     |            4 |         197949
        1202 | Norma       | Stone      |           19 |         110821
        1277 | Scott       | Wagner     |            1 |         155972
        1337 | Bruce       | Fuller     |           15 |          38062
        1372 | Justin      | Edwards    |            1 |         111823
        1436 | Judith      | Parker     |            9 |         100724
　　　　・
　　　　・
　　　　・
(1000 rows)

SELECT * FROM salary_data ORDER BY 1;

employee_id | first_name | last_name | years_worked | current_salary

-------------+-------------+------------+--------------+----------------

110 | Brenda | Morrison | 0 | 111294

112 | Jerry | Thomas | 7 | 121821

189 | Andrew | Burns | 4 | 38798

189 | Shawn | Moore | 7 | 151023

313 | Ruby | Black | 13 | 282291

426 | Anne | Green | 11 | 133469

518 | Earl | Shaw | 2 | 294344

593 | Lawrence | Larson | 19 | 264219

620 | Mary | Rose | 17 | 213873

630 | Michelle | Crawford | 7 | 129393

718 | Harry | Larson | 6 | 48123

777 | Ruby | Johnston | 16 | 51444

1043 | Diane | Morris | 14 | 207271

1126 | Susan | Alexander | 5 | 275869

1157 | Jack | Stone | 2 | 194583

1198 | Martha | Cooper | 4 | 197949

1202 | Norma | Stone | 19 | 110821

1277 | Scott | Wagner | 1 | 155972

1337 | Bruce | Fuller | 15 | 38062

1372 | Justin | Edwards | 1 | 111823

1436 | Judith | Parker | 9 | 100724

　　　　・

(1000 rows)

2)years_worked、current_salary列に対して、NORMALIZE_FIT関数を使用して正規化の計算を行います。
　※本例ではminmax法を用いて正規化を計算します。

SELECT NORMALIZE_FIT('salary_data_normfit', 'salary_data', 'years_worked,current_salary', 'minmax');
 NORMALIZE_FIT
---------------
 Success

(1 row)

SELECT NORMALIZE_FIT('salary_data_normfit', 'salary_data', 'years_worked,current_salary', 'minmax');

NORMALIZE_FIT

---------------

Success

(1 row)

3)GET_MODEL_SUMMARY関数を使用すると、計算時に利用したデータの範囲(min/max)を確認することができます。

SELECT GET_MODEL_SUMMARY(USING PARAMETERS model_name='salary_data_normfit');

=======
details
=======
 column_name  |    min    |    max
--------------+-----------+------------
 years_worked |  0.00000  |  20.00000
current_salary|35321.00000|299936.00000

(1 row)

SELECT GET_MODEL_SUMMARY(USING PARAMETERS model_name='salary_data_normfit');

=======

details

=======

column_name | min | max

--------------+-----------+------------

years_worked | 0.00000 | 20.00000

current_salary|35321.00000|299936.00000

(1 row)

4)NORMALIZE_FIT関数で計算した正規化パラメータはモデルとしてVertica内に保存されています。
　実際に正規化を行う場合はAPPLY_NORMALIZE関数を使用してモデルを適用します。

SELECT APPLY_NORMALIZE ( input‑columns
	           USING PARAMETERS model_name='model‑name') FROM table-name;

1 2	SELECT APPLY_NORMALIZE ( input‑columns USING PARAMETERS model_name='model‑name') FROM table-name;

パラメータ名	内容
input‑columns	正規化対象の列。複数列指定する場合は「,」カンマで区切ります。
model‑name	使用する正規化パラメータのモデル名

APPLY_NORMALIZE関数で実際にsalary_dataテーブルのyears_worked、current_salary列を正規化します。
※手順2)で作成したsalary_data_normfitを使用します。

SELECT APPLY_NORMALIZE(* USING PARAMETERS model_name = 'salary_data_normfit') FROM salary_data ORDER BY 1;
 employee_id | first_name  | last_name  |    years_worked    |   current_salary
-------------+-------------+------------+--------------------+---------------------
         110 | Brenda      | Morrison   |                  0 |   0.287107676267624
         112 | Jerry       | Thomas     |  0.349999994039536 |   0.326890021562576
         189 | Andrew      | Burns      |  0.200000002980232 |  0.0131398448720574
         189 | Shawn       | Moore      |  0.349999994039536 |   0.437246561050415
         313 | Ruby        | Black      |  0.649999976158142 |   0.933318197727203
         426 | Anne        | Green      |  0.550000011920929 |   0.370908677577972
         518 | Earl        | Shaw       |  0.100000001490116 |   0.978867411613464
         593 | Lawrence    | Larson     |  0.949999988079071 |   0.865022778511047
         620 | Mary        | Rose       |  0.850000023841858 |   0.674761474132538
         630 | Michelle    | Crawford   |  0.349999994039536 |   0.355505168437958
         718 | Harry       | Larson     |  0.300000011920929 |   0.048379722982645
         777 | Ruby        | Johnston   |  0.800000011920929 |  0.0609300322830677
        1043 | Diane       | Morris     |  0.699999988079071 |   0.649811983108521
        1126 | Susan       | Alexander  |               0.25 |   0.909048974514008
        1157 | Jack        | Stone      |  0.100000001490116 |   0.601863086223602
        1198 | Martha      | Cooper     |  0.200000002980232 |   0.614583432674408
        1202 | Norma       | Stone      |  0.949999988079071 |   0.285320192575455
        1277 | Scott       | Wagner     | 0.0500000007450581 |    0.45594921708107
        1337 | Bruce       | Fuller     |               0.75 |  0.0103584453463554
        1372 | Justin      | Edwards    | 0.0500000007450581 |   0.289106816053391
        1436 | Judith      | Parker     |  0.449999988079071 |   0.247162863612175
　　　　・
　　　　・
　　　　・
(1000 rows)

SELECT APPLY_NORMALIZE(* USING PARAMETERS model_name = 'salary_data_normfit') FROM salary_data ORDER BY 1;

employee_id | first_name | last_name | years_worked | current_salary

-------------+-------------+------------+--------------------+---------------------

110 | Brenda | Morrison | 0 | 0.287107676267624

112 | Jerry | Thomas | 0.349999994039536 | 0.326890021562576

189 | Andrew | Burns | 0.200000002980232 | 0.0131398448720574

189 | Shawn | Moore | 0.349999994039536 | 0.437246561050415

313 | Ruby | Black | 0.649999976158142 | 0.933318197727203

426 | Anne | Green | 0.550000011920929 | 0.370908677577972

518 | Earl | Shaw | 0.100000001490116 | 0.978867411613464

593 | Lawrence | Larson | 0.949999988079071 | 0.865022778511047

620 | Mary | Rose | 0.850000023841858 | 0.674761474132538

630 | Michelle | Crawford | 0.349999994039536 | 0.355505168437958

718 | Harry | Larson | 0.300000011920929 | 0.048379722982645

777 | Ruby | Johnston | 0.800000011920929 | 0.0609300322830677

1043 | Diane | Morris | 0.699999988079071 | 0.649811983108521

1126 | Susan | Alexander | 0.25 | 0.909048974514008

1157 | Jack | Stone | 0.100000001490116 | 0.601863086223602

1198 | Martha | Cooper | 0.200000002980232 | 0.614583432674408

1202 | Norma | Stone | 0.949999988079071 | 0.285320192575455

1277 | Scott | Wagner | 0.0500000007450581 | 0.45594921708107

1337 | Bruce | Fuller | 0.75 | 0.0103584453463554

1372 | Justin | Edwards | 0.0500000007450581 | 0.289106816053391

1436 | Judith | Parker | 0.449999988079071 | 0.247162863612175

　　　　・

(1000 rows)

これにより、years_worked、current_salary列の値がデータ間の差は維持したまま0～1の範囲に置き換えられました。

例えば、years_workedの最大値は手順2)で20であることが確認できています。
以下のemployee_id 2974のデータはyears_workedが20ですが、正規化後は1に置き換えられています。

SELECT * FROM salary_data WHERE years_worked=20 LIMIT 1;
 employee_id | first_name | last_name | years_worked | current_salary
-------------+------------+-----------+--------------+----------------
        2974 | Janet      | Russell   |           20 |         292120
(1 row)

SELECT * FROM salary_data WHERE years_worked=20 LIMIT 1;

employee_id | first_name | last_name | years_worked | current_salary

-------------+------------+-----------+--------------+----------------

2974 | Janet | Russell | 20 | 292120

(1 row)

SELECT APPLY_NORMALIZE(* USING PARAMETERS model_name = 'salary_data_normfit') FROM salary_data WHERE employee_id=2974;
 employee_id | first_name | last_name | years_worked |  current_salary
-------------+------------+-----------+--------------+-------------------
        2974 | Janet      | Russell   |            1 | 0.970462739467621
(1 row)

SELECT APPLY_NORMALIZE(* USING PARAMETERS model_name = 'salary_data_normfit') FROM salary_data WHERE employee_id=2974;

employee_id | first_name | last_name | years_worked | current_salary

-------------+------------+-----------+--------------+-------------------

2974 | Janet | Russell | 1 | 0.970462739467621

(1 row)

また、employee_id 2659のデータはyears_workedが10で全体の中央値のため、正規化後は0.5に置き換えられています。

SELECT * FROM salary_data WHERE years_worked=10 LIMIT 1;
 employee_id | first_name | last_name | years_worked | current_salary
-------------+------------+-----------+--------------+----------------
        2659 | Gregory    | Mason     |           10 |         253352
(1 row)

SELECT * FROM salary_data WHERE years_worked=10 LIMIT 1;

employee_id | first_name | last_name | years_worked | current_salary

-------------+------------+-----------+--------------+----------------

2659 | Gregory | Mason | 10 | 253352

(1 row)

SELECT APPLY_NORMALIZE(* USING PARAMETERS model_name = 'salary_data_normfit') FROM salary_data WHERE employee_id=2659;
 employee_id | first_name | last_name | years_worked |  current_salary
-------------+------------+-----------+--------------+-------------------
        2659 | Gregory    | Mason     |          0.5 | 0.823955535888672
(1 row)

SELECT APPLY_NORMALIZE(* USING PARAMETERS model_name = 'salary_data_normfit') FROM salary_data WHERE employee_id=2659;

employee_id | first_name | last_name | years_worked | current_salary

-------------+------------+-----------+--------------+-------------------

2659 | Gregory | Mason | 0.5 | 0.823955535888672

(1 row)

なお、NORMALIZE_FIT関数で作成した正規化モデルは、計算時に指定したテーブル以外でも適用可能なため、学習時だけでなく実際の予測時にも前処理で利用可能です。

参考情報

NORMALIZE_FIT
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/MachineLearning/NORMALIZE_FIT.htm

APPLY_NORMALIZE
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/MachineLearning/APPLY_NORMALIZE.htm

検証バージョンについて

この記事の内容はVertica 9.1で確認しています。

投稿タグ: 正規化, データ準備, ml, 標準化, normalize, データプレパレーション, 機械学習

MENU

はじめに

NORMALIZE_FIT/APPLY_NORMALIZE

コマンド構文

利用例

参考情報

検証バージョンについて

カテゴリー

最近の投稿