12.1.1 데이터 수집하기
우리가 사용할 코로나 데이터는 OWID(Our World In Data)에서 제공하는 데이터입니다. 데이터는 https://github.com/owid/covid-19-data/tree/master/public/data에서 구할 수 있고, XLSX(엑셀), CSV(쉼표로 구분된 데이터), JSON 형식으로 파일을 내려받을 수 있습니다. 이 중에서 owid-covid-data.csv 파일을 기준으로 실습 데이터를 제공합니다. 이 책에서는 원본 파일 데이터를 그대로 사용하지 않고 covid19_country와 covid19_data라는 2개 테이블로 분리하고 일부 항목만 사용합니다. 분리 작업은 상당히 복잡하고 범위를 벗어나므로 자세한 설명은 생략하겠습니다. 여기서는 2020년 1월 1일부터 2021년 2월 28일까지의 데이터를 대상으로 분석합니다.
먼저 MySQL Workbench 프로그램을 실행해 다음과 같이 2개의 테이블을 만듭니다.
코드 12-1
USE mywork;
-- 국가 정보 테이블
CREATE TABLE covid19_country
(
countrycode VARCHAR(10) NOT NULL,
countryname VARCHAR(80) NOT NULL,
continent VARCHAR(50),
population DOUBLE,
population_density DOUBLE,
median_age DOUBLE,
aged_65_older DOUBLE,
aged_70_older DOUBLE,
hospital_beds_per_thousand INT,
PRIMARY KEY (countrycode)
);
-- 코로나 데이터 테이블
CREATE TABLE covid19_data
(
countrycode VARCHAR(10) NOT NULL,
issue_date DATE NOT NULL,
cases INT,
new_cases_per_million DOUBLE,
deaths INT,
icu_patients INT,
hosp_patients INT,
tests INT,
reproduction_rate DOUBLE,
new_vaccinations INT,
stringency_index DOUBLE,
PRIMARY KEY (countrycode, issue_date)
);
실행결과
CREATE TABLE 문으로 테이블 2개를 생성합니다. covid19_country는 국가 정보를, covid19_data는 2020년 1월 1일부터 2021년 2월 28일까지 코로나 데이터를 담을 테이블입니다. 두 테이블의 상세 내역은 다음 표와 같습니다.
▼ 표 12-1 covid19_country 테이블 항목
|
칼럼명 |
데이터 타입 |
칼럼 설명 |
|
countrycode |
VARCHAR(10) |
국가 코드(기본 키) |
|
countryname |
VARCHAR(80) |
국가명 |
|
continent |
VARCHAR(50) |
대륙명 |
|
population |
DOUBLE |
인구 |
|
population_density |
DOUBLE |
인구 밀도 |
|
median_age |
DOUBLE |
평균 연령 |
|
aged_65_older |
DOUBLE |
65세 이상 인구 비율 |
|
aged_70_older |
DOUBLE |
70세 이상 인구 비율 |
|
hospital_beds_per_thousand |
INT |
1000명당 병실 침대 수 |
▼ 표 12-2 covid19_data 테이블 항목
|
칼럼명 |
데이터 타입 |
칼럼 설명 |
|
countrycode |
VARCHAR(10) |
국가 코드(기본 키) |
|
issue_date |
DATE |
발생일(기본 키) |
|
cases |
INT |
확진자 수 |
|
new_cases_per_million |
DOUBLE |
100만 명당 확진자 수 |
|
deaths |
INT |
사망자 수 |
|
icu_patients |
INT |
중환자 수 |
|
hosp_patients |
INT |
병원 입원 환자 수 |
|
tests |
INT |
검사자 수 |
|
reproduction_rate |
DOUBLE |
감염재생산지수 |
|
new_vaccinations |
INT |
백신 접종자 수 |
|
stringency_index |
DOUBLE |
방역 지수로, 0에서 100까지의 값이 저장되어 있고 100이 가장 높음 |
covid19_country 테이블에는 국가 정보가 들어 있으므로 기본 키 칼럼은 국가 코드인 countrycode입니다. covid19_data 테이블은 날짜별, 국가별 코로나 데이터를 담고 있으므로 기본 키 칼럼은 국가 코드인 countrycode와 날짜인 issue_date입니다. 두 칼럼을 조합하면 covid19_data 테이블에서 유일한 값이 됩니다. 그리고 covid19_country와 covid19_data 테이블은 countrycode 칼럼을 이용해 조인할 수 있습니다.
테이블을 만들었으니 데이터를 입력해 보죠. 먼저 covid19_country 테이블의 데이터는 이 책의 자료실에서 받은 스크립트 파일에 들어 있습니다. ch12 폴더로 가서 01.covid19_country_insert.sql 파일을 열고 안에 있는 모든 내용을 복사합니다. 그런 다음 MySQL Workbench 프로그램의 SQL 입력창에 있는 내용을 모두 지우고 복사한 내용을 붙여 넣습니다. 전체 문장을 실행해야 하므로 Ctrl + Shift + Enter키를 눌러 실행합니다.
코드 12-2 01.covid19_country_insert.sql(일부)
-- covid19_country INSERT
INSERT INTO covid19_country VALUES
('AFG', 'Afghanistan', 'Asia', 38928341, 54.422, 18.6, 2.581, 1.337, 0.5),
('AGO', 'Angola', 'Africa', 32866268, 23.89, 16.8, 2.405, 1.362, null),
('AIA', 'Anguilla', 'North America', 15002, null, null, null, null, null),
('ALB', 'Albania', 'Europe', 2877800, 104.871, 38, 13.188, 8.643, 2.9),
('AND', 'Andorra', 'Europe', 77265, 163.755, null, null, null, null),
('ARE', 'United Arab Emirates', 'Asia', 9890400, 112.442, 34, 1.144, 0.526, 1.2),
('ARG', 'Argentina', 'South America', 45195777, 16.177, 31.9, 11.198, 7.441, 5),
('ARM', 'Armenia', 'Asia', 2963234, 102.931, 35.7, 11.232, 7.571, 4.2),
('ATG', 'Antigua and Barbuda', 'North America', 97928, 231.845, 32.1, 6.933, 4.631, 3.8),
('AUS', 'Australia', 'Oceania', 25499881, 3.202, 37.9, 15.504, 10.129, 3.8),
('AUT', 'Austria', 'Europe', 9006400, 106.749, 44.4, 19.202, 13.748, 7.4),
('AZE', 'Azerbaijan', 'Asia', 10139175, 119.309, 32.4, 6.018, 3.871, 4.7),
('BDI', 'Burundi', 'Africa', 11890781, 423.062, 17.5, 2.562, 1.504, 0.8),
...
실행결과
이번에는 02.covid19_data_insert.sql 파일을 열고 안에 있는 모든 내용을 복사합니다. SQL 입력창에 있는 내용을 모두 지우고 복사한 내용을 붙여 넣습니다. 그리고 Ctrl + Shift + Enter 키를 눌러 실행합니다.
코드 12-3 02.covid19_data_insert.sql(일부)
-- covid19_data INSERT
INSERT INTO covid19_data VALUES
('ALB', '2020-04-11', 17, 5.907, 0, null, null, '259', 0.94, null, 84.26),
('ALB', '2020-04-12', 13, 4.517, 0, null, null, '233', 0.96, null, 84.26),
('ALB', '2020-04-13', 21, 7.297, 0, null, null, '193', 1, null, 84.26),
('ALB', '2020-04-14', 8, 2.78, 1, null, null, '236', 1.01, null, 84.26),
('ALB', '2020-04-15', 19, 6.602, 1, null, null, '252', 1.06, null, 84.26),
('ALB', '2020-04-16', 24, 8.34, 1, null, null, '269', 1.09, null, 84.26),
('ALB', '2020-04-17', 21, 7.297, 0, null, null, '253', 1.08, null, 84.26),
('ALB', '2020-04-18', 9, 3.127, 0, null, null, '231', 1.06, null, 89.81),
...
실행결과
두 테이블 데이터 입력이 끝나면 COUNT() 함수로 두 테이블에 입력된 데이터 건수를 확인합니다.
코드 12-4
SELECT COUNT(*)
FROM covid19_country;
SELECT COUNT(*)
FROM covid19_data;
실행결과
covid19_country 테이블 데이터 건수
covid19_data 테이블 데이터 건수
covid19_country 테이블은 215건, covid19_data 테이블은 71957건이 입력됐습니다.