조회한 데이터 문제가 있다면?(결측값/이상치(Outlier))+이상치 탐지 방법(IQR, Z-score)

데이터가 없을 때(결측값)의 연산 결과 변화 케이스

 

방법1. 없는 값을 연산에서 제외해 주기

• 계산에 사용할 수 없는 값은 NULL로 변환하여 집계에서 제외 → 0으로 간주
  • SQL의 집계 함수(AVG, SUM 등)는 NULL을 자동으로 제외
  • 따라서 잘못된 값을 NULL로 바꾸는 것
• 분석 정확도 중요 → 제외 (NULL 처리)

// 예시.

SELECT
    restaurant_name,
    -- 원본 평균 (문자 포함 시 문제 가능)
    AVG(rating) average_of_rating,
    -- 'Not given'은 NULL로 처리 후 평균 계산
    AVG(IF(rating <> 'Not given', rating, NULL)) average_of_rating2
FROM
    food_orders
GROUP BY 1;

 

→ JOIN에서 NULL 제거(INNER JOIN 효과)

• LEFT JOIN 후 NULL 제거하면 INNER JOIN과 동일한 결과

SELECT
    a.order_id,
    a.customer_id,
    a.restaurant_name,
    a.price,
    b.name,
    b.age,
    b.gender
FROM
    food_orders a LEFT JOIN customers b
    ON a.customer_id = b.customer_id        -- 고객 ID 기준으로 연결
WHERE
    b.customer_id IS NOT NULL;              -- 매칭 안 된 데이터 제거(INNER JOIN 효과)

NULL 제거 안 했을 때	NULL 제거 했을 때(JOIN시에는 INNER JOIN과 동일)

 

 

 

방법2. 결측값을 다른 값으로 대체하기

• NULL 또는 이상값을 대표값 또는 특정 값으로 치환
• 데이터 부족 → 대체 (평균, 중앙값 등)

-- 1. 조건문으로 값 대체(IF)
-- 정상 값이면 그대로 사용/아니면 대체값 사용

IF(rating >= 1, rating, 대체값)



-- 2. NULL 대체(COALESCE)
-- NULL이면 지정한 값으로 변경

COALESCE(컬럼, 대체값)

// 예시.

SELECT
    a.order_id,
    a.customer_id,
    a.restaurant_name,
    a.price,
    b.name,
    b.age,
    COALESCE(b.age, 20) "age_대체값",   -- age가 NULL이면 20으로 대체
    b.gender
FROM
    food_orders a LEFT JOIN customers b
    ON a.customer_id = b.customer_id
WHERE
    b.age IS NULL;                     -- age가 NULL인 데이터만 확인

 

 

 

이상치(Outlier) 데이터 처리 정리

• 데이터가 비어있는 경우(NULL)도 문제지만, 값이 존재하더라도 상식적으로 맞지 않는 경우도 존재
• 대표적인 이상치 사례
  • case1. 고객 나이
    • ex. 예: 2세, 120세 → 실제 서비스 사용자로 보기 어려움
  • case2. 결제 일자
    • ex. 1970년대 → 서비스 시작 시점과 맞지 않음
• 해결 방법: 범위 제한(Clipping)
  • 조건문으로 최솟값 / 최댓값을 제한하여 정상 범위로 보정(너무 작으면 최솟값, 너무 크면 최댓값)
• 왜 필요한가?
  • 분석 왜곡 방지 : 평균, 분포가 깨지는 문제 해결
  • 서비스 현실 반영 : 실제 사용자 범위에 맞춤
  • 데이터 안정성 확보 : 이상치로 인한 오류 방지
• 언제 사용하는가?
  • 나이, 금액, 시간 등 범위가 명확한 데이터
  • 센서/로그 데이터 이상값 처리
  • ML/통계 분석 전 전처리
• 주의사항
  • 범위는 ‘데이터가 쓰이는 현실 상황’ 기준으로 정해야 함(기준은 도메인 기반)
  • 무조건 보정이 정답은 아님
    • 제거 : 완전히 잘못된 데이터
    • 보정 : 일부만 비정상
    • 유지 : 의미 있는 극단값

-- 예시.

SELECT
    customer_id,
    name,
    email,
    gender,
    age,
    CASE
        WHEN age < 15 THEN 15     -- 최소값 제한
        WHEN age > 80 THEN 80     -- 최대값 제한
        ELSE age                  -- 정상 범위 유지
    END AS adjusted_age
FROM
    customers;

 

 

 

방법1. IQR(사분위수 기반)

• 데이터를 정렬해서 중간 50% 범위(IQR)를 기준으로 너무 벗어난 값을 이상치로 판단
• 특징
  • 이상치에 강함 (robust)
  • 평균 영향 거의 없음
• 언제 사용하는가?
  • 데이터 분포가 비대칭(한쪽으로 치우침)일 때
  • 금액, 주문 수 등

-- 공식.

IQR = Q3 - Q1

이상치 기준:
Q1 - 1.5 * IQR  (하한)
Q3 + 1.5 * IQR  (상한)

-- 예시.

SELECT
    *
FROM
    (
    SELECT
        price,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price) OVER() AS Q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price) OVER() AS Q3
    FROM food_orders
    ) t
WHERE
    price < (Q1 - 1.5 * (Q3 - Q1))
    OR price > (Q3 + 1.5 * (Q3 - Q1));

 

 

 

방법2. Z-score(평균 기반)

• 각 데이터가 평균에서 얼마나 떨어져 있는지 → 표준편차 기준으로 판단
• 특징
  • 계산 간단
  • 이상치에 민감 (평균이 흔들림)
• 언제 사용하는가?
  • 데이터가 정규분포(종 모양)일 때
  • 통계 분석 / 머신러닝

-- 공식.

Z = (값 - 평균) / 표준편차

 

구분	IQR	Z-score
기준	중앙값	평균
분포	상관 없음	정규분포 가정
이상치 영향	적음	큼
추천 상황	실무 데이터	통계/모델
데이터 성격	금액, 주문수	시험 점수, 센서값
데이터 분포 확	한쪽으로 쏠림	종 모양

 

 

※ 사용 흐름

   • 분포 확인(히스토그램) → IQR로 1차 이상치 제거 → 필요하면 Z-score로 추가 필터링