[MySQL] SQL로 titanic 데이터 처리하기 예시들

사용한 데이터는 아래의 train.csv를 사용

https://www.kaggle.com/competitions/titanic

Titanic - Machine Learning from Disaster | Kaggle

 

이 글은 코드로 여러가지 예시를 설명하는 글.

혼자 학습용으로 기록해둔거라 가독성이 좋지 않고, 그냥 읽기엔 불친절한 글.

 

먼저 csv의 자료형을 살펴본 뒤 그에 맞춰서 CREATE TABLE을 하거나, 

MySQL Workbench를 통해서 csv 파일을 Import해주기.

table 이름은 datas로 해줬음.

 

 

---

 

 

1. 결측치 개수 출력 방법

-- 결측치 개수 출력하기
SELECT
    SUM(CASE WHEN PassengerId IS NULL THEN 1 ELSE 0 END) passenger_id,
    SUM(CASE WHEN Survived IS NULL THEN 1 ELSE 0 END) Survived,
    SUM(CASE WHEN Pclass IS NULL THEN 1 ELSE 0 END) Pclass,
    SUM(CASE WHEN "Name" = "" THEN 1 ELSE 0 END) Name,
    SUM(CASE WHEN Sex = "" THEN 1 ELSE 0 END) Sex,
    SUM(CASE WHEN Age IS NULL THEN 1 ELSE 0 END) Age,
    SUM(CASE WHEN SibSp IS NULL THEN 1 ELSE 0 END) SibSp,
    SUM(CASE WHEN Parch IS NULL THEN 1 ELSE 0 END) Parch,
    SUM(CASE WHEN Ticket = "" THEN 1 ELSE 0 END) Ticket,
    SUM(CASE WHEN Fare IS NULL THEN 1 ELSE 0 END) Fare,
    SUM(CASE WHEN Cabin = "" THEN 1 ELSE 0 END) Cabin,
    SUM(CASE WHEN Embarked = "" THEN 1 ELSE 0 END) Embarked
FROM datas;

* SUM() 대신에 COUNT()를 사용해도 무방 ( ex : COUNT(CASE WHEN AGE IS NULL THEN 1 END) as Age)

* Name, Sex, Cabin 등은 VARCHAR나 Text같은 문자열 자료형으로 넣었으므로 IS NULL대신 = "" 빈공백으로 체크. 데이터가 NULL 대신 빈 문자열로 결측치가 채워졌었기 때문.

* SELECT 절에서 Name에 큰 따옴표 붙인건 Name이 예약어라서, Field를 명확히 지정해준다는 의미로 사용.

 

결과

 

 

2. 결측치 비율 출력 방법

-- 각 컬럼별 결측치 비율 출력
-- WITH - AS를 이용한 서브쿼리문 작성
WITH data_ratio AS (
SELECT
    SUM(CASE WHEN PassengerId IS NULL THEN 1 ELSE 0 END) passenger_id,
    SUM(CASE WHEN Survived IS NULL THEN 1 ELSE 0 END) Survived,
    SUM(CASE WHEN Pclass IS NULL THEN 1 ELSE 0 END) Pclass,
    SUM(CASE WHEN "Name" = "" THEN 1 ELSE 0 END) Name,
    SUM(CASE WHEN Sex = "" THEN 1 ELSE 0 END) Sex,
    SUM(CASE WHEN Age IS NULL THEN 1 ELSE 0 END) Age,
    SUM(CASE WHEN SibSp IS NULL THEN 1 ELSE 0 END) SibSp,
    SUM(CASE WHEN Parch IS NULL THEN 1 ELSE 0 END) Parch,
    SUM(CASE WHEN Ticket = "" THEN 1 ELSE 0 END) Ticket,
    SUM(CASE WHEN Fare IS NULL THEN 1 ELSE 0 END) Fare,
    SUM(CASE WHEN Cabin = "" THEN 1 ELSE 0 END) Cabin,
    SUM(CASE WHEN Embarked = "" THEN 1 ELSE 0 END) Embarked,
    COUNT(1) total
FROM datas
)
SELECT
	( age/ total ) * 100 as age_ratio,
    ( Cabin/ total ) * 100 as Cabin_ratio,
    ( Embarked/ total ) * 100 as Embarked_ratio
FROM data_ratio;

* WITH - AS 방법으로 사용가능한 임시테이블을 만든 뒤 사용.

* WITH - AS 방법은 아래처럼 사용.

* 소수점이 늘어지는게 보기 싫을 경우 ROUND, FLOOR 등을 이용하여 반올림하거나 내림처리.

WITH (테이블 이름) AS (

... 테이블 구조 ...

)
SELECT *
FROM (테이블 이름);

 

 

3. 순위 출력

SELECT
    Fare,
    RANK() OVER(ORDER BY Fare DESC),
    DENSE_RANK() OVER(ORDER BY Fare DESC),
    ROW_NUMBER() OVER(ORDER BY Fare DESC)
FROM datas;

결과

 

* RANK() OVER([ORDER BY 등..]) 으로 써주면 됨.

* RANK() = 동순위자만큼 다음 순위가 밀림.

* DENSE_RANK() = 동순위자가 있으나, 다음 순위가 밀리지 않음. 헷갈리면 다음 순위자가 순위가 밀리지 않는다는 것에 신이 나서 DANSE(춤...)를 춘다라고 외워버릴 것. 난 능지 이슈로 그냥 이렇게 외움.

* ROW_NUMBER() = 동순위 없음.

 

 

4. Z-Score 계산하기

Z-score Formula

Formula는 저렇습니다.

통계에서 데이터의 분포를 파악할 때 씁니다.

Age와 Fare의 Z-score를 알아봅니다.

 

SELECT Fare, (Fare-avg_f)/std_f as "z-score"
FROM
(
    SELECT avg(Fare) as "avg_f", std(Fare) as "std_f"
    FROM datas
) a, datas;

결과

 

* 서브쿼리 같은 경우 서브쿼리를 사용하고 꼭 뒤에 이름을 붙여줘야 문법 오류가 안납니다. 위 테이블에서는 a를 붙였죠?

* Age도 똑같이 필드 명만 바꿔주면 끝.

 

 

5. 호칭추출

SELECT name,
    case
    	WHEN locate('Master', name) THEN "Master"
        WHEN locate("Mrs", name) THEN "Mrs"
        WHEN locate("Mr", name) THEN "Mr"
        WHEN locate("Miss", name) THEN "Miss"
        ELSE NULL END as title
FROM datas;

with titles as
(SELECT
	name,
	substring(name, locate(',', name)+2, (locate('.', name)-2 - locate(',', name))) as title
FROM datas
)
SELECT name, title
FROM titles
WHERE title Not in ("Mr", "Mrs", "Miss", "Master");

 

* locate함수로 name 필드에 Mr, Miss 등의 호칭이 들어가면 title로 추출할 수 있습니다.

* 아래 코드로, 저 4개 호칭이 들어가지 않은 이름도 WITH-AS, substring, locate를 이용해 추출했습니다. substring의 활용법은 다음과 같습니다. RIGHT, LEFT의 사용법과 비슷합니다.

SUBSTRING(문자열, 시작위치, 길이)

결과

6. View 만들기

-- 뷰 만들기
DROP VIEW IF EXISTS pre_data;
CREATE VIEW pre_data as (
	SELECT
	    PassengerId,
        Survived,
        CASE WHEN Survived = 1 THEN "생존" ELSE "사망" END as "Survived_cat",
        Pclass,
        Name,
		case
			WHEN locate('Master', name) THEN "Master"
			WHEN locate("Mrs", name) THEN "Mrs"
			WHEN locate("Mr", name) THEN "Mr"
			WHEN locate("Miss", name) THEN "Miss"
			ELSE NULL END as title,
		CASE WHEN Sex = "male" THEN "남성" ELSE "여성" END as "sex",
		Age,
        SibSp,
        Parch,
        Ticket,
        Fare,
        Cabin,
		CASE WHEN Embarked = "S" THEN "영국"
		WHEN Embarked = "Q" THEN "아일랜드"
        WHEN Embarked = "C" THEN "프랑스"
        ELSE "unknown" END as "Embarked"
	FROM datas
);

SELECT * FROM pre_data;

 

* 위에서 활용한 코드들을 바탕으로 뷰를 만들기

 

 

7. 데이터 분석 일부

-- Pclass별 평균 나이와 평균 생존률
SELECT Pclass, avg(age), avg(Survived) * 100 as "survival_rate"
FROM pre_data
GROUP BY Pclass;

-- 결측치 채운 View 만들기
DROP VIEW IF EXISTS final_data;
CREATE VIEW final_data AS
(
SELECT *
FROM
	(
    SELECT
		PassengerId, Survived, Pclass, Name, a.title, sex, SibSp, Parch, Fare, Cabin,
		FLOOR(CASE WHEN Age IS NULL THEN b.avg_age ELSE a.Age END) as "new_age"
	FROM pre_data a
    JOIN
		(SELECT title, avg(age) as "avg_age"
        FROM pre_data
        GROUP BY title) b ON a.title = b.title
    ) c
);

SELECT * FROM final_data;

결과