[SQL] 캐글 데이터 활용/타이타닉 호 데이터 분석

 

데이터 딕셔너리

• PassengerId: 번호
• Survived : 생존=1, 죽음=0
• pclass : 승객 등급. 1등급=1, 2등급=2, 3등급=3
• name: 이름
• sex: 성별
• sibsp : 함께 탑승한 형제 또는 배우자 수
• parch : 함께 탑승한 부모 또는 자녀 수
• ticket : 티켓 번호
• fare: 요금
• cabin : 선실 번호
• embarked : 탑승장소 S=Southhampton, C=Cherbourg, Q=Queenstown
• (영국 Southhampthon -> 프랑스 Cherbourg -> 아일랜드 Queenstown(현 코브))

 

자료는 아래 링크에서 확인 가능하다.

https://www.kaggle.com/datasets/pavlofesenko/titanic-extended

Titanic extended dataset (Kaggle + Wikipedia)

---

 

헤더 포함 / 박스 모양으로 출력 / 10번까지 출력

sqlite> .headers on
sqlite> .mode box
sqlite> select * from titanic where PassengerId < 11;
┌─────────────┬──────────┬────────┬─────────────────────────────────────────────────────┬────────┬──────┬───────┬───────┬──────────────────┬─────────┬───────┬──────────┐
│ PassengerId │ Survived │ Pclass │                        Name                         │  Sex   │ Age  │ SibSp │ Parch │      Ticket      │  Fare   │ Cabin │ Embarked │
├─────────────┼──────────┼────────┼─────────────────────────────────────────────────────┼────────┼──────┼───────┼───────┼──────────────────┼─────────┼───────┼──────────┤
│ 1           │ 0        │ 3      │ Braund, Mr. Owen Harris                             │ male   │ 22.0 │ 1     │ 0     │ A/5 21171        │ 7.25    │       │ S        │
│ 2           │ 1        │ 1      │ Cumings, Mrs. John Bradley (Florence Briggs Thayer) │ female │ 38.0 │ 1     │ 0     │ PC 17599         │ 71.2833 │ C85   │ C        │
│ 3           │ 1        │ 3      │ Heikkinen, Miss. Laina                              │ female │ 26.0 │ 0     │ 0     │ STON/O2. 3101282 │ 7.925   │       │ S        │
│ 4           │ 1        │ 1      │ Futrelle, Mrs. Jacques Heath (Lily May Peel)        │ female │ 35.0 │ 1     │ 0     │ 113803           │ 53.1    │ C123  │ S        │
│ 5           │ 0        │ 3      │ Allen, Mr. William Henry                            │ male   │ 35.0 │ 0     │ 0     │ 373450           │ 8.05    │       │ S        │
│ 6           │ 0        │ 3      │ Moran, Mr. James                                    │ male   │      │ 0     │ 0     │ 330877           │ 8.4583  │       │ Q        │
│ 7           │ 0        │ 1      │ McCarthy, Mr. Timothy J                             │ male   │ 54.0 │ 0     │ 0     │ 17463            │ 51.8625 │ E46   │ S        │
│ 8           │ 0        │ 3      │ Palsson, Master. Gosta Leonard                      │ male   │ 2.0  │ 3     │ 1     │ 349909           │ 21.075  │       │ S        │
│ 9           │ 1        │ 3      │ Johnson, Mrs. Oscar W (Elisabeth Vilhelmina Berg)   │ female │ 27.0 │ 0     │ 2     │ 347742           │ 11.1333 │       │ S        │
│ 10          │ 1        │ 2      │ Nasser, Mrs. Nicholas (Adele Achem)                 │ female │ 14.0 │ 1     │ 0     │ 237736           │ 30.0708 │       │ C        │
└─────────────┴──────────┴────────┴─────────────────────────────────────────────────────┴────────┴──────┴───────┴───────┴──────────────────┴─────────┴───────┴──────────┘

 

---

 

몇명의 생존자가 있었는가?

sqlite> select count(Survived) from titanic where Survived = 1;
┌─────────────────┐
│ count(Survived) │
├─────────────────┤
│ 342             │
└─────────────────┘

총 342명의 생존자가 있었다.

 

 

생존자 중 여자는 몇 명인가?

select count(Survived) as survived_female 
from titanic 
where Survived = 1 and sex = 'female';

생존된 사람을 세는데(count) survived_female로 열 제목을 만든다(as)

타이타닉 테이블에서(from)

생존하고(survived= 1), 여자인 사람(sex = 'female'), 

sqlite> select count(Survived) as survived_female from titanic where Survived = 1 and sex = 'female';
┌─────────────────┐
│ survived_female │
├─────────────────┤
│ 233             │
└─────────────────┘

총 233명이 있었다. 

 

어떤 성별이 타이타닉에 더 많이 탑승했나? 생존률은 어떻게 다른가?

SELECT sex, 
COUNT(PassengerId), SUM(Survived), (CAST(SUM(Survived) AS FLOAT) / COUNT(PassengerId)) 
FROM titanic
GROUP BY 1;

┌────────┬────────────────────┬───────────────┬─────────────────────────────────────────────────────┐
│  Sex   │ count(PassengerId) │ sum(Survived) │ (cast(sum(Survived) as float) / count(PassengerID)) │
├────────┼────────────────────┼───────────────┼─────────────────────────────────────────────────────┤
│ female │ 314                │ 233           │ 0.74203821656051                                    │
│ male   │ 577                │ 109           │ 0.188908145580589                                   │
└────────┴────────────────────┴───────────────┴─────────────────────────────────────────────────────┘

-> 남자가 여자보다 많이 탑승했고, 생존률은 더 낮았다.

 

 

 

각 등급별 승객은 몇 명씩인가?

sqlite> select Pclass, count(Pclass) from titanic group by Pclass;
┌────────┬───────────────┐
│ Pclass │ count(Pclass) │
├────────┼───────────────┤
│ 1      │ 216           │
│ 2      │ 184           │
│ 3      │ 491           │
└────────┴───────────────┘

Pclass와 Pclass의 개수를 세고, 이를 그룹화 했다.

1등급은 216명, 2등급은 184명, 3등급은 491명이다. 

 

 

등급별 생존률은 몇 퍼센트일까? 등급이 높을수록 생존률이 높을까?

sqlite> select Pclass, count(Pclass) from titanic where Survived = 1 group by Pclass;
┌────────┬───────────────┐
│ Pclass │ count(Pclass) │
├────────┼───────────────┤
│ 1      │ 136           │
│ 2      │ 87            │
│ 3      │ 119           │
└────────┴───────────────┘

일단 클래스별 생존자들의 수 세기

SELECT Pclass, 
ROUND( CAST(SUM(Survived) AS FLOAT) / COUNT(survived), 2) 
AS probability 
FROM titanic GROUP BY Pclass;
┌────────┬─────────────┐
│ Pclass │ probability │
├────────┼─────────────┤
│ 1      │ 0.63        │
│ 2      │ 0.47        │
│ 3      │ 0.24        │
└────────┴─────────────┘

Pclass를 선택하고,  

생존자를 분모로 두고 생존자를 더한 수를 소수로 바꿔준다.

그리고 반올림한다.

가능성으로 표시를 바꿔주고

Pclass 별로 그룹화한다.

 

-> 등급이 높을수록 생존 확률이 더 높았다.

 

 

각 나이대별 생존 명수 구하기

floor: 소수점 이하를 무조건 버리는 함수

floor(숫자/10)을 하면 10의 자리 숫자만 반환되므로 10을 곱해 주면 각 나이대별의 수를 구할 수 있다.

산 사람은 1로 반환되므로 *10을 해 준다.

 

select floor(age/10)*10 as ageband, 
count(PassengerId) as passengers, 
sum(Survived) as survived_cnt 
from titanic 
group by 1;


┌─────────┬────────────┬──────────────┐
│ ageband │ passengers │ survived_cnt │
├─────────┼────────────┼──────────────┤
│         │ 177        │ 52           │
│ 0.0     │ 62         │ 38           │
│ 10.0    │ 102        │ 41           │
│ 20.0    │ 220        │ 77           │
│ 30.0    │ 167        │ 73           │
│ 40.0    │ 89         │ 34           │
│ 50.0    │ 48         │ 20           │
│ 60.0    │ 19         │ 6            │
│ 70.0    │ 6          │ 0            │
│ 80.0    │ 1          │ 1            │
└─────────┴────────────┴──────────────┘

생존률도 같이 구해 보았다.

 select 
 floor(age/10)*10 as ageband, 
 count(PassengerId) as passengers, 
 sum(Survived) as survived_cnt, 
 cast(sum(Survived) as float) / count(PassengerId) as survived_rate 
 from titanic group by 1;
┌─────────┬────────────┬──────────────┬───────────────────┐
│ ageband │ passengers │ survived_cnt │   survived_rate   │
├─────────┼────────────┼──────────────┼───────────────────┤
│         │ 177        │ 52           │ 0.293785310734463 │
│ 0.0     │ 62         │ 38           │ 0.612903225806452 │
│ 10.0    │ 102        │ 41           │ 0.401960784313726 │
│ 20.0    │ 220        │ 77           │ 0.35              │
│ 30.0    │ 167        │ 73           │ 0.437125748502994 │
│ 40.0    │ 89         │ 34           │ 0.382022471910112 │
│ 50.0    │ 48         │ 20           │ 0.416666666666667 │
│ 60.0    │ 19         │ 6            │ 0.315789473684211 │
│ 70.0    │ 6          │ 0            │ 0.0               │
│ 80.0    │ 1          │ 1            │ 1.0               │
└─────────┴────────────┴──────────────┴───────────────────┘

생존비율은 0세가 가장 많았다. 80대중 생존 인원은 1명이었다.

20대 탑승객 수가 가장 많았다.

나이가 어릴수록 생존 확률이 높게 나타났다.

 

---

승선 항구별 승객 수

 select embarked, count(PassengerId) from titanic group by 1;
┌──────────┬────────────────────┐
│ Embarked │ count(PassengerId) │
├──────────┼────────────────────┤
│          │ 2                  │
│ C        │ 168                │
│ Q        │ 77                 │
│ S        │ 644                │
└──────────┴────────────────────┘

프랑스에서 탑승한 사람은 168명, 영국은 644명, 아일랜드는 77명이었다. 

 

승선 항구별, 성별 승객 수

1) group by 1, 2를 했으므로 첫번째와 두번째 열로 그룹화하는 것을 의미한다. 

즉 승선 항구별과 성별로 그룹화 했다.

2) order by 1, 2 : 첫번째 열과 두번째 열로 정렬하는 것을 의미한다. 기본적으로 오름차순이며, 내림차순을 하기 위해서는 desc를 뒤에 붙여줘야 한다.

select embarked, sex, count(PassengerId) from titanic group by 1,2 order by 1, 2;
┌──────────┬────────┬────────────────────┐
│ Embarked │  Sex   │ count(PassengerId) │
├──────────┼────────┼────────────────────┤
│          │ female │ 2                  │
│ C        │ female │ 73                 │
│ C        │ male   │ 95                 │
│ Q        │ female │ 36                 │
│ Q        │ male   │ 41                 │
│ S        │ female │ 203                │
│ S        │ male   │ 441                │
└──────────┴────────┴────────────────────┘

 

 

se

---

교수님의 추가 문제

 

1. 가장 돈을 많이 낸 순으로 정렬해서 10개만 테이블 출력

select * from titanic order by fare desc limit 10;

출력결과

 

2. 나이가 16살보다 적은 사람들에 대한 이름과 나이를 출력

select name, age from titanic where age <16;

나이로도 정렬해 봤다.

 

 

3. 나이가 16살보다 적은 사람들 중 생존한 사람들의 이름과 나이를 출력

select name, age from titanic where Survived = 0 and age <16;

order by 1 해서 이름별로 정렬해 봄

 

4. 가장 나이가 많은 사람의 이름과 나이, 객실 등급을 출력

select name, age, Pclass from titanic order by age desc limit 1;

select name, age, Pclass from titanic where age = (select max(age) from titanic);

 

같은 결과가 나온다.

 

 

5. 셰르부르 사람 중 가장 돈을 많이 낸 사람의 이름과 성별, 금액을 출력

select name, sex, fare from titanic where embarked = 'C' order by fare desc limit 1;

가장 적은 가격을 낸 사람은 4달러였고

가장 돈을 많이 낸 사람은 512달러였다.

 

다른 방법

 SELECT name, sex, fare from titanic where embarked = 'C' and fare=(select max(fare)from titanic);