SQL (조회하기 실습 및 One-hot Encoding)

캐글 타이타닉

오늘은 캐글에 있는 타이타닉 데이터를 가지고, 실습을 해보았다.

 

/*
passengerid - 승객 id (PK)
survied - 생존 여부 (0:사망, 1:생존)
pclass - 객실 등급 (1, 2, 3)
name - 이름
gender - 성별 (male: 남성, female: 여성)
sibsp - 동반한 형제 또는 자매 또는 배우자 수
parch - 동반한 부모 또는 자식의 수
ticket - 티켓번호
fare - 요금
cabin - 객실번호
embarked - 탑승한 항구 (C: 프랑스 항국, Q: 아일랜드 항구, S: 영국 항구)
*/

 

이것은 칼럼들에 대한 설명을 적어놓은 것이다.

 

이제부터 조회를 해보겠다.

 

-- 컬럼을 선택해서 조회할 수 있다.
select name,age from titanic_raw;

-- 컬럼명에 대하여 별칭도 줄 수 있다. (원본에 영향 X)
select gender as "성별", fare as "요금" from titanic_raw;

# where 이용하여 조건줘서 조회하기
select * 	-- 산 사람만 조회
from titanic_raw
where survived = 1;

select *	-- 죽은사람만 조회
from titanic_raw
where survived != 1;

 

 

데이터를 조회해보면 죽은사람은 0으로 되어있고, 산 사람은 1로 되어있는 것을 확인할 수 있다.

 

이를 이용해서 where이라는 조건문을 추가해 산사람과 죽은사람을 조회했다.

 

-- is null
select * 
from titanic_raw
where age is null;

-- is not null
select *
from titanic_raw
where age is not null;

-- 특정 문자열이 포함되어 있는지 조회
select *
from titanic_raw
where name like '%miss%'; # 양 옆에 어떤 글자가 와도 상관없다.

select *
from titanic_raw
where binary(name) not like '%miss%';	# 대소문자 구분 = binary

 

이 후 Null 값과 특정 문자열에 대하여도 조회하였다.

 

-- and
select *
from titanic_raw
where survived = 1 and gender = "female";

select *
from titanic_raw
where survived = 1 and gender = "male";

-- or
select *
from titanic_raw
where name like "%mrs%" or name like "miss";

-- in, not in
select * from titanic_raw
where embarked in("C","S");

select * from titanic_raw
where embarked not in("Q");

 

마찬가지로 and, or, in, not in도 조건을 걸어 조회할 수 있다.

 

-- 구간 조회
select *
from titanic_raw
where age between 20 and 40; # 20살 이상 ~ 40살 이하

select *
from titanic_raw
where pclass between 0 and 2 and ticket like "%1";

-- 정렬하기
select *
from titanic_raw
where survived = 1
order by fare desc; # 정렬이 되고자하는 컬럼명 넣기 (오름차순(asc)이 기본) 내림차순은 desc

 

위 코드를 보면 내가 원하는 구간의 데이터를 조회하고, 정렬할 수 있다.

 

---

산술연산

 

select name, sibsp + parch as add_sibsp_parch
from titanic_raw;

select sibsp - parch as add_sibsp_parch
from titanic_raw;

select sibsp * parch as add_sibsp_parch
from titanic_raw;

select sibsp / parch as add_sibsp_parch
from titanic_raw; # 나누기의 경우 나누는 수가 0일 경우 Null

select sibsp % parch as add_sibsp_parch
from titanic_raw; # 나머지를 구할 때 나누는 수가 0일 경우 Null

 

다음과 같이 산술연산도 가능하다. 직접해보시면서 결과를 확인하는 것을 추천드립니다.

 

---

함수 사용하기

 

-- 중복 제거하기
select distinct(cabin) from titanic_raw;

-- 총 개수 확인하기
	-- count 함수는 Null값 무시!
select count(passengerid) as cnt # Null 값이 없는 PK를 주로 넣는다.
from titanic_raw;

select count(passengerid) as cnt	# 객실 번호는 Null값이 많다.
from titanic_raw
where cabin is null;

-- sum 함수는 Null값 무시
select sum(fare) as sum_fare
from titanic_raw;

select avg(fare)/std(fare) as sum_fare
from titanic_raw;

-- 평균 운임료 확인해보기
	-- avg 함수는 null값 무시
select avg(fare) as sum_fare
from titanic_raw;    

-- 운임료에 대한 표준편차 확인해보기
	-- std 함수는 null값 무시
select std(fare) as sum_fare
from titanic_raw;

-- 분산 구하기
	-- variance 함수는 Null값 무시
select variance(fare) as var_fare
from titanic_raw;

-- 거듭제곱하기
	-- pow 함수
select pow(std(fare),2) as pow_std_fare
from titanic_raw;

-- pow 함수와 variance 함수를 이용해서 표준편차 구해보기
select pow(variance(fare),0.5) as pow_variance_fare
from titanic_raw;

 -- 최대값 구하기
 select max(fare) as max_fare
 from titanic_raw;
 
 -- 최소값 구하기
  select min(fare) as max_fare
 from titanic_raw;
 
 -- 생존자의 나이에 대한 평균과 표준편차를 구해보세요.	# where을 생각 못함;;
 select  avg(age) as avg_age, std(age) as std_age
 from titanic_raw
 where survived = 1;
 
 -- 사망자의 나이에 대한 평균과 표준편차를 구해보세요.
  select avg(age) as avg_age, std(age) as std_age
 from titanic_raw
 where survived = 0;	# 생존자의 나이의 편차가 크다

 

함수는 간단하게 이런게 있구나를 기억하고, 나중에 필요할 때 써먹으면 된다.

 

한번 문제들을 읽고, 혼자서 풀어보시길 바란다. 예상치 못한 곳에서 막힐 때가 있다.

 

---

select 절에 조건주기

-- ifnull 첫번째 값이 null일 경우 두번쨰 값으로 채워넣습니다.
select ifnull(cabin,"알수없음") as result
from titanic_raw;

-- if 첫번째 값이 참일 경우 두번쨰 값이 들어가고, 거짓일 경우 세번째 값이 들어간다.
select if(cabin is not null,cabin,"알수없음") as result
from titanic_raw;

-- gender에 대하여 male일 경우 남자, female일 경우 여자로 변경하여 조회하시오.
select if(gender = "male", "남자","여자") as gender_name
from titanic_raw;

 

 select 절에 조건을 주는 방법은 ifnull, if가 있다.

 

---

one - hot encoding

 

one - hot encoding은 문자를 숫자로 변경하는 것이다.

 

예시

 

select * from titanic_raw;
select if(embarked = "C","1",0) as C, if(embarked = "Q","1","0") as Q, if(embarked = "S","1","0") as S
from titanic_raw;

 

위 코드를 사용하면 다음과 같이 결과가 나온다.

 

여기서 s에 포함이 되면 1이 나오고 나머지는 0이 되는 구조이다.

 

이것은 처음에 이해가 안되었는데 문제를 풀고난 후 이해가 확실히 되었다.

 

 

---

case 문

select case
	when embarked = "c" then "프랑스 항구" # if 와 동일하다.
        when embarked = "q" then "아일랜드 항구"	# else if 와 동일하다
        else "영국 항구"
    end as result
from titanic_raw;

select * from titanic_raw;

-- count encoding (성능이 좋게 나온다.) 개수를 세는 것!
set @embarked_c = (select count(embarked) from titanic_raw where embarked="c");
set @embarked_q = (select count(embarked) from titanic_raw where embarked="q");
set @embarked_s = (select count(embarked) from titanic_raw where embarked="s");
select @embarked_s;

-- 예시
select embarked,
		case
			when embarked = "c"  then @embarked_c
			when embarked = "q"  then @embarked_q
			else @embarked_s
		end as result
from titanic_raw;

 

 

case문은 우리가 아는 if문과 구조가 비슷하다.

 

코드를 한번씩 타이핑해보면서 이해가 되었다.

 

count encoding은 오늘 처음 들어본 개념으로 솔직히 이해가 잘 안되었다.

 

지금은 이해가 안되도 나중에 또 가르쳐주신다고 하니 일단 이런게 있구나하고 넘어가자!

 

---

group by

- 데이터를 그룹화, 특정 컬럼으로 데이터 묶는다.
- 특정 컬럼을 기준으로 데이터를 묶고 집계함수를 이용해서 집계한다.

 

select * from titanic_raw;

-- pclass별로 평균 나이를 알고 싶다면?
select avg(age) from titanic_raw where pclass = 1;
select avg(age) from titanic_raw where pclass = 2;
select avg(age) from titanic_raw where pclass = 3;

-- pclass 별 평균 나이구하기
select pclass, avg(age) # 1,2,3의 값들을 집계(avg,sum....)하여 묶음
from titanic_raw
group by pclass;

 

이로써 개념은 끝이났다. 이제부터는 개념을 활용하여 분석을 해보자!

 

---

분석하기

여기부터는 더보기를 하지않고, 다 풀 수 있다면 완벽하게 이해했다고 볼 수 있다.

 

- 각 pclass에 대하여 gender 별로 생존률로 알고 싶다면?

 

select pclass, gender, avg(survived) as result
from titanic_raw
group by pclass,gender;

 

- 각 embarked에 대하여 pclass 별로 생존률을 구하시오.

 

select embarked, pclass, avg(survived) as result
from titanic_raw
group by embarked,pclass order by embarked, pclass;

 

# 가설 1: c항구가 생존율이 높으므로 c항구가 더 잘산다!

# 가설 2: 2등급 Q와 3등급 Q를 비교하였을 때 생존율이 3등급이  더 높으므로 3등급 Q에는 여자와 어린아이가 2등급 Q보다 많았을 것으로 예상된다.

 

증명

 

- Q항구에 대해서 pclass별로 여자의 비율과 나이의 평균을 구하세요.

 

select pclass, avg(gender = "female") as lady_rate, avg(age) as age_avg
from titanic_raw
where embarked = "Q"
group by pclass, embarked order by pclass;

 

- embarked별로 운임료의 평균을 구하세요.

 

select embarked, fare, avg(fare) as fare_avg
from titanic_raw
group by embarked;

# c의 운임료가 제일 비싸다!

 

---

오늘은 데이터 조회하는 방법에 대해서 배웠다.

 

처음엔 솔직히 조금 지루하긴 했으나 이것을 통해서 데이터를 분석할 수 있다는 것이 매우 재미있었다.

 

마지막에 강사님의 뿌듯한 표정을 아직 잊지 못한다...

 

앞으로도 많은 데이터분석을 하고 싶다는 생각이 들었다!