Преамбула
15 апреля 1912 года в Северной Атлантике столкнулся с айсбергом и затонул пароход «Титаник».
На борту было 1309 пассажиров.
Спаслось 500 человек, погибло 809.
В этом цикле статей мы исследуем вероятности выживаемости
пассажиров в зависимости от различных, хорошо задокументированных
факторов. Здесь можно найти источники данных, роликов и кода.
Первая статья посвящена получению данных, переносу их в СУБД Postgres
и изучению их в psql - терминале Postgres.
Ссылка на ролик: http://an2k.ru/data/level2/201-titanic/201-titanic.mp4
Далее мы будем работать (на своём ПК или на сервере не имеет значение - отличий нет), в терминале с командной строкой Linux и с SQL-консолью postgres.
Чтобы не путаться: первые мы будем выделять жирным шрифтом, а вторые нет.
Консоль psql принимает для исполнения два вида строк:
- мета-команды,
- SQL-запросы.
Мета-команды сразу видно по лидирующему знаку \ (обратный слэш).
SQL-запросы всегда заканчиваются точкой с запятой. Без нее команда не начинает исполняться - интерпретатор ожидает именно ее, чтобы начать выполнение.
Часто SQL-запросы бывают многострочными.
Получение данных
Следующие две команды выполняется в командной строке терминала Linux.
1. Скачать архивный файл с помощью wget (30 Кб)
wget an2k.ru/data/level2/201-titanic/titanic3.csv.zip
2. Распаковать архив
unzip titanic3.csv.zip
В файле titanic3.csv содержатся все необходимые данные. Их можно смотреть в текстовом редакторе или как электронную таблицу типа Excel. Но, гораздо удобнее работать с такого рода информацией в SQL базе данных.
Данные go to SQL
Файл titanic3.csv содержит следующие поля:
id — идентификатор, который был присвоен в базе
pclass — класс купленного билета (1-й, 2-й, или 3-й)
survived — выжил 1 или нет 0 пассажир
name — имя пассажира
sex — пол пассажира
age — возраст пассажира
sibsp — количество братьев или супругов у пассажира на борту
parch — количество родителей или детей у пассажира на борту
ticket — номер билета
fare — сумма денег, которую заплатили за билет
cabin — номер каюты, в которой находился пассажир
embarked — порт (город), где пассажир сел на титаник
boat — номер лодки куда попал выживший
body — номер лодки куда было поднято тело погибшего
home — домашний адрес
Все 17 следующих команд выполняются в консоли Postgres, которая в терминале запускается так:
psql
1. Создать базу titanic
create database titanic;
2. Перейти в созданную базу
\c titanic
3. Создать таблицу tit0
create table tit0 (
pclass int,
survived int,
name varchar,
sex varchar,
age varchar,
sibsp int,
parch int,
ticket varchar,
fare varchar,
cabin varchar,
embarked varchar,
boat varchar,
body varchar,
home varchar
);
4. Залить файл titanic3.csv в таблицу tit0
COPY tit0 FROM '/home/an2k/py/lessons/Titanic/titanic3.csv'
DELIMITER 't\' CSV HEADER;
5. Создать таблицу tit1
create table tit1 (
id serial,
pclass int,
survived int,
name varchar,
sex varchar,
age varchar,
sibsp int,
parch int,
ticket varchar,
fare varchar,
cabin varchar,
embarked varchar,
boat varchar,
body varchar,
home varchar
);
6. Проиндексировать таблицу tit1
create UNIQUE index on tit1 (id);
7. Залить данные в таблицу tit1 из tit0
insert into tit1 (
pclass,
survived,
name,
sex,
age,
sibsp,
parch,
ticket,
fare,
cabin,
embarked,
boat,
body,
home
)
select * from tit0;
8. Откомментоировать поля таблицы tit1
comment on column tit1.id is 'Идентификатор, который был присвоен в базе';
comment on column tit1.pclass is 'Класс купленного билета (1-й, 2-й, или 3-й)';
comment on column tit1.survived is 'Выжил 1 или нет 0 пассажир';
comment on column tit1.name is 'Имя пассажира';
comment on column tit1.sex is 'Пол пассажира';
comment on column tit1.age is 'Возраст пассажира';
comment on column tit1.sibsp is 'Количество братьев или супругов у пассажира на борту';
comment on column tit1.parch is 'Количество родителей или детей у пассажира на борту';
comment on column tit1.ticket is 'Номер билета';
comment on column tit1.fare is 'Сумма денег, которую заплатили за билет';
comment on column tit1.cabin is 'Номер каюты, в которой находился пассажир';
comment on column tit1.embarked is 'Порт (город), где пассажир сел на титаник';
comment on column tit1.boat is 'Номер лодки куда попал выживший';
comment on column tit1.body is 'Номер лодки куда было поднято тело погибшего';
comment on column tit1.home is 'Домашний адрес';
Данные в базе и готовы к использованию. Исследуем их.
9. Посмотреть структуру таблицы tit1 + комментарии
\d+ tit1
10. Посмотреть первые 10 строк таблицы tit1
Строки в SQL-таблицах называются записями
select * from tit1 limit 10;
11. Подсчитать количество записей в таблице tit1
select count(*) from tit1 ;
count
-------
1309
12. Подсчитать количество выживших
select count(*) from tit1 where survived = 1;
count
-------
500
13. Подсчитать количество погибших
select count(*) from tit1 where survived = 0;
count
-------
809
14. Сгруппировать записей по признаку выживаемости
select survived, count(*) from tit1 group by survived;
survived | count
----------+-------
0 | 809 -- Погибли
1 | 500 -- Выжили
15. Сгруппировать записей по признаку пола
select sex, count(*) from tit1 group by sex;
sex | count
--------+-------
male | 843 -- Мужчины
female | 466 -- Женщины
16. Сгруппировать по двум признакам: пола и выживаемости
select sex, survived, count(*) from tit1
group by sex, survived order by sex, survived;
sex | survived | count
--------+----------+-------
female | 0 | 127
female | 1 | 339
male | 0 | 682
male | 1 | 161
17. То же, плюс проценты
SELECT
sex,
survived,
count,
ROUND(
100.0 * count /
SUM(count) OVER (PARTITION BY sex),
2
) as percent
FROM (
SELECT
sex,
survived,
count(*) as count
FROM tit1
GROUP BY sex, survived
) subquery
ORDER BY sex, survived;
sex | survived | count | percent
--------+----------+-------+---------
female | 0 | 127 | 27.25
female | 1 | 339 | 72.75
male | 0 | 682 | 80.90
male | 1 | 161 | 19.10
Для первого знакомства достаточно. Продолжение следует.