Get "NORMALIZE" data in a new column.

Best Answer

• Options

  marcothesane - Select Field - Administrator

  May 2020 Answer ✓

  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)
  
  

  5