SQL 데이터 전처리 2 : 이상치 제거, 표준화, 정규화

by

“데이터 전처리“는 원시 데이터를 분석이나 모델링에 활용하기 적합하도록 정제하고 가공하는 과정으로, 결측치 처리, 이상치 제거, 변수 스케일링과 같은 단계를 포함하여 데이터의 품질을 향상시키는 작업을 의미합니다.

이는 정확하고 신뢰성 있는 결과를 얻기 위해 필수적인 단계로 여겨집니다.

SQL 데이터 전처리는 2개로 나눠서 정리하고 있습니다.

  1. 중복 제거, 공백 제거, 결측치 제거 및 대체
  2. 이상치 제거, 표준화, 정규화

이번 포스트에서는 2. 이상치 제거, 표준화, 정규화에 대해 정리해보겠습니다.

이상치 제거

이상치를 판단하기 위해서는 데이터에 대한 도메인 지식이 필요합니다.

이번에는 여러 방법 중 Z-Score를 사용한 간단한 이상치 제거를 예로 들겠습니다.

다음과 같은 테이블이 존재한다고 가정해보겠습니다.

Z-Score 란?

특정 값이 평균에서 얼마나 떨어져 있는지를 표준편차의 단위로 나타내는 지표입니다

$Z=\frac{X – 평균}{표준편차}$

INSERT INTO your_table (id, value) VALUES
  (1, 10),
  (2, 15),
  (3, 8),
  (4, 12),
  (5, 25),
  (6, 100);

Z-score를 이용한 이상치를 제거하고 조회하는 쿼리는 다음과 같습니다.

WITH z_scored_data AS (
  SELECT
    id,
    value,
    (value - AVG(value) OVER ()) / STDDEV(value) OVER () AS z_score
  FROM your_table
)
SELECT id, value
FROM z_scored_data
WHERE ABS(z_score) < 2; -- 이상치 기준을 Z-Score 2로 설정

  • WITH문은 일시적으로 사용되는 결과 집합을 생성하는데 사용되며, 위의 쿼리에서는 이를 통해 각 행의 Z-Score를 계산 하였습니다.
  • OVER()는 전체 데이터 셋을 의미합니다.
  • AVG(value) OVER (): AVG(value) 함수를 사용하여 value 열의 평균을 계산하고, OVER ()는 전체 데이터셋에 대한 윈도우를 나타냅니다. 즉, 전체 데이터셋의 평균을 계산합니다.
  • STDDEV(value) OVER (): STDDEV(value) 함수를 사용하여 value 열의 표준편차를 계산하고, OVER ()는 전체 데이터셋에 대한 윈도우를 나타냅니다. 즉, 전체 데이터셋의 표준편차를 계산합니다.

데이터 정규화

데이터 정규화는 데이터 범위를 (0,1) 또는 (-1,1) 등으로 조절하는 것을 의미합니다.

최소-최대 정규화(Min-Max Normalization)는 일반적으로 사용되며, 다음과 같이 쿼리를 작성할 수 있습니다.

SELECT
  id,
  (value - MIN(value) OVER ()) / 
  (MAX(value) OVER () - MIN(value) OVER ()) AS normalized_value
FROM your_table;

데이터 표준화

데이터 표준화는 데이터를 평균이 0이고 표준편차가 1인 분포로 조절하는 것을 의미합니다.

데이터를 표준화 시키면 각 변수의 단위와 스케일이 일치하기 때문에 해석이 용이하게 됩니다.

Z-Score 표준화는 일반적으로 사용되며, 다음과 같이 쿼리를 작성할 수 있습니다.

SELECT
  id,
  (value - AVG(value) OVER ()) /
  STDDEV(value) OVER () AS standardized_value
FROM your_table;
SQL 복잡한 연산 : 서브쿼리
수학 연산에서 순서가 복잡한 연산은 괄호”()”로 묶어서 연산하듯, SQL에서 복잡한 연산을 할 때, “서브쿼리” 를 사용합니다. “서브쿼리”는 구조적으로 다른 SQL 문장 내에 포함된 쿼리를 의미합니다… Read more
SQL 데이터 전처리 1 : TRIM, IS NULL, COALESCE
데이터 전처리는 원시 데이터를 분석이나 모델링에 활용하기 적합하도록 정제하고 가공하는 과정으로, 결측치 처리, 이상치 제거, 변수 스케일링과 같은 단계를 포함하여 데이터의 품질을 향상시키는 작업을 의미합니다. 이번 포스트에서는 1. 중복 제거, 공백 제거, 결측치 제거 및 대체에 대해 정리해보겠습니다…. Read more

Leave a Comment

Content

→ 목차