SQL 데이터 전처리 1 : TRIM, IS NULL, COALESCE

by

소프트웨어 설계와 DB 모델링을 마치고 데이터를 수집하면 우리가 원하는 스키마에 원하는 데이터 유형으로 데이터를 받을 수 있습니다. 하지만 데이터를 가공하다 보면 원하지 않은 데이터를 변경하거나 제거해야 하는 경우가 반드시 존재합니다. 하물며 우리가 모델링한 DB에서 나온 데이터가 아닌 공개 DB나 어디서인가 데이터를 받았을 때는 절대 우리가 원하는 형태로 데이터가 존재하지 않습니다. 이럴 때, “데이터 전처리” 를 진행줘야 합니다.

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

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

SQL 데이터 전처리는 2개로 나눠서 정리할 예정입니다.

  1. 중복 제거, 공백 제거, 결측치 제거 및 대체
  2. 변수 변환, 카테고리형 변수 다루기, 표준화, 정규화,

이번 포스트에서는 1. 중복 제거, 공백 제거, 결측치 제거 및 대체에 대해 정리해보겠습니다.

중복된 행 제거

아래와 같이 name과 age가 중복된 내용이 있는 테이블이 있다고 가정해 봅니다.

INSERT INTO your_table(id, name, age) VALUES
  (1, 'John', 25),
  (2, 'Jane', 30),
  (3, 'John', 25),
  (4, 'Alice', 28),
  (5, 'Bob', 30),
  (6, 'Jane', 30);

이 테이블에서 중복된 행을 제거하는 쿼리는 다음과 같이 할 수 있습니다.

-- 테이블에서 중복을 제거
DELETE FROM your_table
  WHERE id NOT IN (
  SELECT MIN(id) 
  FROM your_table
  GROUP BY name, age
);

-- 중복을 제거하여 조회
SELECT MIN(id) AS id, name, age
FROM your_table
GROUP BY name, age;

중복을 제거한 결과는 다음과 같이 됩니다.

+----+-------+-----+
| id | name  | age |
+----+-------+-----+
| 1  | John  | 25  |
| 2  | Jane  | 30  |
| 4  | Alice | 28  |
| 5  | Bob   | 30  |
+----+-------+-----+

공백 제거

공백 제거는 TRIM 함수를 사용합니다.

-- 테이블의 특정 칼럼에서 공백 제거
UPDATE your_table
SET name= TRIM(name);


-- 조회 결과로만 공백 제거
SELECT TRIM(name) AS trimmed_name
FROM your_table;

결측치 제거

IS NULL, IS NOT NULL 혹은 COALESCE 를 통해서 결측치를 제거 혹은 대체 할 수 있습니다.

(결측치를 대체하는 함수는 COALESCE 외에도 NVL, NVL2, NULLIF 등이 존재 합니다. 하지만 NVL, NVL2, NULLIF는 표준 SQL이 아닌 Oracle에서 사용되는 함수이므로 해당 포스트에서는 제외하였습니다.)

IS NOT NULL 키워드를 사용하여 결측치를 제거하고 조회하는 법

SELECT
  id,
  name,
  age
FROM your_table
WHERE name IS NOT NULL AND age IS NOT NULL;

이 쿼리는 your_table에서 name과 age 열이 NULL이 아닌 행만을 선택하여 조회합니다.

또한 COALESCE 함수를 이용해서 결측치를 대체할 수 있습니다.

SELECT
  id,
  COALESCE(name, 'Unknown') AS name,
  COALESCE(age, 0) AS age
FROM your_table;

위의 쿼리에서는 your_table에서 name과 age 열에 대해 결측치를 대체하고, 각각 ‘Unknown’과 0으로 대체했습니다.

동일한 코드를 CASE 문을 이용해서 구현 할 수 있습니다.

SELECT
  id,
  CASE WHEN name IS NULL 
    THEN 'Unknown' 
    ELSE name 
    END AS name,
  CASE WHEN age IS NULL 
    THEN 0 
    ELSE age 
    END AS age
FROM your_table;

참고할 만한 글

SQL 복잡한 연산 : 서브쿼리
수학 연산에서 순서가 복잡한 연산은 괄호”()”로 묶어서 연산하듯, SQL에서 복잡한 연산을 할 때, “서브쿼리” 를 사용합니다. “서브쿼리”는 구조적으로 다른 SQL 문장 내에 포함된 쿼리를 의미합니다… Read more
SQL 데이터 전처리 2 : 이상치 제거, 표준화, 정규화
이번 포스트에서는 2. 이상치 제거, 표준화, 정규화에 대해 정리해보겠습니다. 이상치 제거 이상치를 판단하기 위해서는 데이터에 대한 도메인 지식이 필요합니다…. Read more

Leave a Comment

Content

→ 목차