Нормализация данных через запрос в SQL

Главный принцип анализа данных GIGO (от англ. garbage in — garbage out, дословный перевод «мусор на входе — мусор на выходе») говорит нам о том, что ошибки во входных данных всегда приводят к неверным результатам анализа. От того, насколько хорошо подготовлены данные, зависят результаты всей вашей работы.

Например, перед нами стоит задача подготовить выборку для использования в алгоритме машинного обучения (модели k-NN, k-means, логической регрессии и др). Признаки в исходном наборе данных могут быть в разном масштабе, как, например, возраст и рост человека. Это может привести к некорректной работе алгоритма. Такого рода данные нужно предварительно масштабировать.

В данном материале мы рассмотрим способы масштабирования данных через запрос в SQL: масштабирование методом min-max, min-max для произвольного диапазона и z-score нормализация. Для каждого из методов мы подготовили по два примера написания запроса — один с помощью подзапроса SELECT, а второй используя оконную функцию OVER().

Для работы возьмем таблицу students с данными о росте учащихся.

——
 |name|height|
 |Иван| 174|
 |Петр| 181|
 |Денис| 199|
 |Ксения| 158|
 |Сергей| 179|
 |Ольга| 165|
 |Юлия| 152|
 |Кирилл| 188|
 |Антон| 177|
 |Софья| 165|
 ——

Min-Max масштабирование

Подход min-max масштабирования заключается в том, что данные масштабируются до фиксированного диапазона, который обычно составляет от 0 до 1. В данном случае мы получим все данные в одном масштабе, что исключит влияние выбросов на выводы.

Выполним масштабирование по формуле:

Умножаем числитель на 1.0, чтобы в результате получилось число с плавающей точкой.

SQL-запрос с подзапросом:

SELECT height,

       1.0 * (height-t1.min_height)/(t1.max_height — t1.min_height) AS scaled_minmax

  FROM students,

      (SELECT min(height) as min_height,

              max(height) as max_height

         FROM students

      ) as t1;

SQL-запрос с оконной функцией:

SELECT height,

       (height — MIN(height) OVER ()) * 1.0 / (MAX(height) OVER () — MIN(height) OVER ()) AS scaled_minmax

  FROM students;

В результате мы получим переменные в диапазоне [0…1], где за 0 принят рост самого невысокого учащегося, а 1 рост самого высокого.

——
 |name|height|scaled_minmax|
 |Иван| 174|0.46809|
 |Петр| 181|0.61702|
 |Денис| 199|1|
 |Ксения| 158|0.12766|
 |Сергей| 179|0.57447|
 |Ольга| 165|0.2766|
 |Юлия| 152|0|
 |Кирилл| 188|0.76596|
 |Антон| 177|0.53191|
 |Софья| 165|0.2766|
 ——

Масштабирование для заданного диапазона

Вариант min-max нормализации для произвольных значений. Не всегда, когда речь идет о масштабировании данных, диапазон значений находится в промежутке между 0 и 1.
Формула для вычисления в этом случае такая:

Это даст нам возможность масштабировать данные к произвольной шкале. В нашем примере пусть а=10.0, а b=20.0.

SQL-запрос с подзапросом:

SELECT height,

       ((height — min_height) * (20.0 — 10.0) / (max_height — min_height)) + 10 AS scaled_ab

  FROM students,

      (SELECT MAX(height) as max_height,

              MIN(height) as min_height

         FROM students

      ) t1;

SQL-запрос с оконной функцией:

SELECT height,

       ((height — MIN(height) OVER() ) * (20.0 — 10.0) / (MAX(height) OVER() — MIN(height) OVER())) + 10.0 AS scaled_ab

  FROM students;

Получаем аналогичные результаты, что и в предыдущем методе, но данные распределены в диапазоне от 10 до 20.

——
 |name|height|scaled_ab|
 |Иван| 174|14.68085|
 |Петр| 181|16.17021|
 |Денис| 199|20|
 |Ксения| 158|11.2766|
 |Сергей| 179|15.74468|
 |Ольга| 165|12.76596|
 |Юлия| 152|10|
 |Кирилл| 188|17.65957|
 |Антон| 177|15.31915|
 |Софья| 165|12.76596|
 ——

Нормализация с помощью z-score

В результате z-score нормализации данные будут масштабированы таким образом, чтобы они имели свойства стандартного нормального распределения — среднее (μ) равно 0, а стандартное отклонение (σ) равно 1.

Вычисляется z-score по формуле:

SQL-запрос с подзапросом:

SELECT height,

       (height — t1.mean) * 1.0 / t1.sigma AS zscore

  FROM students,

      (SELECT AVG(height) AS mean,

              STDDEV(height) AS sigma

         FROM students

        ) t1;

SQL-запрос с оконной функцией:

SELECT height,

       (height — AVG(height) OVER()) * 1.0 / STDDEV(height) OVER() AS z-score

  FROM students;

В результате мы сразу заметим выбросы, которые выходят за пределы стандартного отклонения.

——
 |name|height|zscore|
 |Иван| 174|0.01488|
 |Петр| 181|0.53582|
 |Денис| 199|1.87538|
 |Ксения| 158|-1.17583|
 |Сергей| 179|0.38698|
 |Ольга| 165|-0.65489|
 |Юлия| 152|-1.62235|
 |Кирилл| 188|1.05676|
 |Антон| 177|0.23814|
 |Софья| 165|-0.65489|
 ——