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

1. 데이터 딕셔너리

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)

Additional features from Wikipedia Titanic passenger list

www.kaggle.com

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


      
      
      
    
    
      <sql />
    
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        │
└─────────────┴──────────┴────────┴─────────────────────────────────────────────────────┴────────┴──────┴───────┴───────┴──────────────────┴─────────┴───────┴──────────┘

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


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

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

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


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

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

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

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


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

총 233명이 있었다.

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


      
      
      
    
    
      <sql />
    
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                                   │
└────────┴────────────────────┴───────────────┴─────────────────────────────────────────────────────┘

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

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


      
      
      
    
    
      <sql />
    
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명이다.

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


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

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


      
      
      
    
    
      <sql />
    
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을 해 준다.


      
      
      
    
    
      <sql />
    
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            │
└─────────┴────────────┴──────────────┘

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


      
      
      
    
    
      <sql />
    
 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대 탑승객 수가 가장 많았다.

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

승선 항구별 승객 수


      
      
      
    
    
      <sql />
    
 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를 뒤에 붙여줘야 한다.


      
      
      
    
    
      <sql />
    
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                │
└──────────┴────────┴────────────────────┘

교수님의 추가 문제

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


      
      
      
    
    
      <sql />
    
select * from titanic order by fare desc limit 10;

출력결과

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


      
      
      
    
    
      <sql />
    
select name, age from titanic where age <16;

나이로도 정렬해 봤다.

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


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

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

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


      
      
      
    
    
      <sql />
    
select name, age, Pclass from titanic order by age desc limit 1;


      
      
      
    
    
      <python />
    
select name, age, Pclass from titanic where age = (select max(age) from titanic);

같은 결과가 나온다.

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


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

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

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

다른 방법


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

저작자표시

'개발공부 > SQL & DB' 카테고리의 다른 글

[SQL] CSV 파일 SQLite 이용해 DB파일로 바꾸기 (0)	2023.06.03
[SQL] 4가지 모드(box, column, table, markdown) (0)	2023.06.01
[SQL] SQL 기초지식 공부(식별자, 주석, DB백업, 데이터타입, 이스케이프, 테이블 스키마 확인, DEFAULT, 인덱스) (0)	2023.05.31
[SQL] 명령어 공부하기_INSERT, DELETE, UPDATE, SELECT (0)	2023.05.29
[SQL] SQLite3 다운로드 및 실행하기, 값추가 및 조회 (0)	2023.05.29

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

1. 데이터 딕셔너리

'개발공부 > SQL & DB' 카테고리의 다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

1. 데이터 딕셔너리

'개발공부 > SQL & DB' 카테고리의 다른 글

검색 태그

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역