Get "NORMALIZE" data in a new column.

ManjitPathak

I am relatively new to Vertica and I am exploring the "NORMALIZE" function to get some numeric columns normalized reading the docs however, I also want the actual column in my table together with normalized columns. So, having the "NORMALIZE"ed columns in new columns will help but I am not able to figure it out. Appreciate suggestion.
Doc link: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/MachineLearning/DataPreparation/NormalizingData.htm?Highlight=normalize

marcothesane

If you look at the example from the link you added - it always works if you have a primary key, or a primary key candidate, in your source table.

-- primary key candidate seems to be employee_id *and* first_name ...
-- out sbx=> select employee_id,first_name from salary_data group by 1,2 having count(*) > 1;
-- out employee_id | first_name 
-- out-------------+------------
-- out(0 rows)
-- So, after running:
-- SELECT NORMALIZE('normalized_salary_data', 'salary_data', 'current_salary, years_worked', 'minmax');
-- I join the two over a unique key:
 SELECT
   s.*
 , n.years_worked AS norma_years_worked
 , n.current_salary AS norma_salary
 FROM salary_data s
 JOIN normalized_salary_data n USING(employee_id,first_name)
 LIMIT 10;
 employee_id | first_name | last_name | years_worked | current_salary |  norma_years_worked  |     norma_salary     
-------------+------------+-----------+--------------+----------------+----------------------+----------------------
         110 | Brenda     | Morrison  |            0 |         111294 | 0.000000000000000000 | 0.287107684749541787
         112 | Jerry      | Thomas    |            7 |         121821 | 0.350000000000000000 | 0.326890010014549440
         189 | Andrew     | Burns     |            4 |          38798 | 0.200000000000000000 | 0.013139844680006802
         189 | Shawn      | Moore     |            7 |         151023 | 0.350000000000000000 | 0.437246565765357217
         313 | Ruby       | Black     |           13 |         282291 | 0.650000000000000000 | 0.933318217032292198
         426 | Anne       | Green     |           11 |         133469 | 0.550000000000000000 | 0.370908678646335242
         518 | Earl       | Shaw      |            2 |         294344 | 0.100000000000000000 | 0.978867411144492943
         593 | Lawrence   | Larson    |           19 |         264219 | 0.950000000000000000 | 0.865022768928443210
         620 | Mary       | Rose      |           17 |         213873 | 0.850000000000000000 | 0.674761445874194585
         630 | Michelle   | Crawford  |            7 |         129393 | 0.350000000000000000 | 0.355505167885418438
(10 rows)

ManjitPathak

Thanks @marcothesane for your help, I see your point.