12.1.3 데이터 분석하기
데이터 정제 작업까지 마쳤으니 이제 본격적으로 SQL로 코로나 데이터를 분석해 보겠습니다.
2020년 사망자 수 상위 10개국 조회하기
가장 먼저 2020년 코로나로 인한 사망자 수가 많은 상위 10개국을 조회하는 쿼리를 작성해 보겠습니다. 상위 10건 또는 하위 10건을 조회하는 방법은 두 가지입니다. 하나는 ORDER BY와 LIMIT 절을 사용하는 방법이고, 다른 하나는 윈도우 함수인 RANK()를 사용하는 방법입니다.
코드 12-9
SELECT b.countryname, SUM(a.deaths) death_num, SUM(a.cases) case_num
FROM covid19_data a
INNER JOIN covid19_country b
ON a.countrycode = b.countrycode
WHERE YEAR(a.issue_date) = 2020
GROUP BY b.countryname
ORDER BY 2 DESC
LIMIT 10;
실행결과
covid19_data와 covid19_country 테이블을 countrycode 칼럼으로 조인합니다. 그리고 WHERE 절에서 YEAR() 함수로 2020년 데이터를 조회하는 조건을 주고, 국가명인 countryname 칼럼을 GROUP BY 절에 추가합니다. 사망자 수는 deaths, 확진자 수는 cases 칼럼에 있으므로 두 칼럼에 대해 SUM() 함수를 사용합니다. 마지막으로 집계한 사망자 수 기준으로 내림차순 정렬하고 LIMIT 10을 명시해 상위 10개 국가를 조회합니다.
결과를 보면, 2020년 1월 1일부터 12월 31일까지 가장 많은 사망자가 발생한 국가는 미국, 브라질, 인도, 멕시코, 이탈리아, 영국, 프랑스, 러시아, 이란, 스페인 순입니다. 미국은 35만 명 이상 사망했는데, 확진자 수도 2천만 명이 넘었네요. 그리고 전반적으로 확진자 수가 많으면 사망자 수도 많은 것을 확인할 수 있습니다.
2020년 인구 대비 확진자 수와 사망자 수 비율 조회하기
미국은 인구가 많으니 확진자와 사망자가 많이 나온 것이 이상한 일은 아닙니다. 하지만 사망자 수가 가장 많다는 것은 좀 이상한 일이죠. 미국보다 인구가 많은 국가도 있기 때문입니다. 그럼 이번에는 인구 대비 확진자 수와 사망자 수의 비율을 구해 봅시다.
코드 12-10
SELECT countryname, death_num, case_num, population, population_density,
ROUND(death_num / population * 100, 5) death_popul_rate,
ROUND(case_num / population * 100, 5) case_popul_rate
FROM (SELECT b.countryname, b.population, b.population_density,
SUM(a.deaths) death_num, SUM(a.cases) case_num
FROM covid19_data a
INNER JOIN covid19_country b
ON a.countrycode = b.countrycode
WHERE YEAR(a.issue_date) = 2020
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 10
) t
ORDER BY 6 DESC, 7 DESC;
실행결과
코드 12-10은 코드 12-9에 인구수와 인구 밀도인 population, population_density 칼럼을 추가하고 파생 테이블로 만듭니다. 그리고 메인쿼리에서 사망자 수와 확진자 수를 인구수로 나눠 비율을 구합니다. 또한, 사망자 수보다 인구수가 많으므로 ROUND() 함수로 반올림해 소수점 아래 다섯째 자리까지 보여 줍니다.
결과를 보면, 인구수 대비 사망률이 높은 나라는 이탈리아, 스페인, 영국, 미국, 프랑스 순입니다. 이탈리아는 미국보다 사망자 수가 상당히 적지만, 인구수도 적어서 인구 대비 사망자 비율은 약 0.12%로 가장 높습니다.
그러나 인구 대비 확진자 수 비율인 case_popul_rate 값을 보면 미국이 역시 가장 높게 나왔습니다. 더구나 이탈리아, 영국, 프랑스는 인구 밀도(population_density)가 높은 편인 데 비해 미국은 인구 밀도가 낮은데도 확진자 수나 사망자 수가 많습니다. 왜 이런 결과가 나왔는지는 우리가 실습하는 데이터만으로는 파악할 수 없습니다.
우리나라의 월별 확진자 수와 사망자 수 조회하기
이번에는 전체 대상 기간(2020.01~2021.02) 동안 우리나라의 확진자 수와 사망자 수를 월별로 조회해 보겠습니다.
코드 12-11
SELECT EXTRACT(YEAR_MONTH FROM issue_date) months,
SUM(cases) case_num, SUM(deaths) death_num
FROM covid19_data
WHERE countrycode = 'KOR'
GROUP BY 1
ORDER BY 1;
실행결과
WHERE 절에 countrycode 값이 ‘KOR’과 같은 건을 조회하라는 조건을 줘서 우리나라 데이터만 조회합니다. 여기서는 우리나라의 국가 코드가 KOR임을 알고 있다고 가정해 covid19_country 테이블과는 조인하지 않았습니다. 그리고 SELECT 절에서 EXTRACT() 함수로 날짜에서 연월을 추출해 그룹을 만들어서 월별로 확진자 수와 사망자 수의 합계를 구합니다.
다음 쿼리를 실행해 보죠.
코드 12-12
SELECT EXTRACT(YEAR_MONTH FROM issue_date) months,
SUM(cases) case_num, SUM(deaths) death_num
FROM covid19_data
WHERE countrycode = 'KOR'
GROUP BY 1 WITH ROLLUP
ORDER BY 1;
실행결과
코드 12-12는 코드 12-11의 GROUP BY 절에 WITH ROLLUP 구문을 추가해 소계를 구합니다. 결과를 보면 2020년 1월부터 2021년 2월까지 누적 확진자 수는 90,030명, 누적 사망자 수는 1,605명입니다. 이렇게 월이 로우 형태로 나오게 할 수도 있지만, 칼럼 형태로도 만들 수 있습니다.
그럼 2020년 1월부터 2021년 2월까지 월별 확진자 수를 칼럼 형태로 조회해 봅시다.
코드 12-13
WITH raw_data1 AS
(SELECT EXTRACT(YEAR_MONTH FROM issue_date) months,
SUM(cases) case_num
FROM covid19_data
WHERE countrycode = 'KOR'
GROUP BY 1
)
SELECT CASE WHEN months = 202001 THEN case_num ELSE 0 END "202001",
CASE WHEN months = 202002 THEN case_num ELSE 0 END "202002",
CASE WHEN months = 202003 THEN case_num ELSE 0 END "202003",
CASE WHEN months = 202004 THEN case_num ELSE 0 END "202004",
CASE WHEN months = 202005 THEN case_num ELSE 0 END "202005",
CASE WHEN months = 202006 THEN case_num ELSE 0 END "202006",
CASE WHEN months = 202007 THEN case_num ELSE 0 END "202007",
CASE WHEN months = 202008 THEN case_num ELSE 0 END "202008",
CASE WHEN months = 202009 THEN case_num ELSE 0 END "202009",
CASE WHEN months = 202010 THEN case_num ELSE 0 END "202010",
CASE WHEN months = 202011 THEN case_num ELSE 0 END "202011",
CASE WHEN months = 202012 THEN case_num ELSE 0 END "202012",
CASE WHEN months = 202101 THEN case_num ELSE 0 END "202101",
CASE WHEN months = 202102 THEN case_num ELSE 0 END "202102"
FROM raw_data1;
실행결과
코드 12-13에서는 코드 12-11을 raw_data1이라는 CTE 서브쿼리로 만들고 메인쿼리에서 참조합니다. SELECT 절을 보면 연월을 담은 months 칼럼 값이 202001인지 CASE 연산자로 확인해 맞으면 확진자 수인 case_num을, 아니면 0을 반환합니다. 202001부터 202102까지 같은 방식으로 작성합니다.
결과를 보면 마치 내려가는 계단 모양으로 데이터가 조회됩니다. 대상 기간은 2020년 1월부터 2021년 2월까지 14개월이므로 조회된 로우는 총 14건이며, 월마다 확진자 수를 가져오니 이처럼 보입니다.
그런데 원래 의도했던 형태는 이런 형태가 아닙니다. 결과에서 0을 없애고 한 건으로 보여 줘야 합니다. SUM() 함수로 각 월의 칼럼 데이터를 모두 더하면 마치 0을 없앤 효과를 줄 수 있습니다. 다음을 실행해 봅시다.
코드 12-14
WITH raw_data1 AS
(SELECT EXTRACT(YEAR_MONTH FROM issue_date) months,
SUM(cases) case_num
FROM covid19_data
WHERE countrycode = 'KOR'
GROUP BY 1
)
SELECT SUM(CASE WHEN months = 202001 THEN case_num ELSE 0 END) "202001",
SUM(CASE WHEN months = 202002 THEN case_num ELSE 0 END) "202002",
SUM(CASE WHEN months = 202003 THEN case_num ELSE 0 END) "202003",
SUM(CASE WHEN months = 202004 THEN case_num ELSE 0 END) "202004",
SUM(CASE WHEN months = 202005 THEN case_num ELSE 0 END) "202005",
SUM(CASE WHEN months = 202006 THEN case_num ELSE 0 END) "202006",
SUM(CASE WHEN months = 202007 THEN case_num ELSE 0 END) "202007",
SUM(CASE WHEN months = 202008 THEN case_num ELSE 0 END) "202008",
SUM(CASE WHEN months = 202009 THEN case_num ELSE 0 END) "202009",
SUM(CASE WHEN months = 202010 THEN case_num ELSE 0 END) "202010",
SUM(CASE WHEN months = 202011 THEN case_num ELSE 0 END) "202011",
SUM(CASE WHEN months = 202012 THEN case_num ELSE 0 END) "202012",
SUM(CASE WHEN months = 202101 THEN case_num ELSE 0 END) "202101",
SUM(CASE WHEN months = 202102 THEN case_num ELSE 0 END) "202102"
FROM raw_data1;
실행결과
코드 12-14는 코드 12-13의 메인쿼리에 있는 SELECT 절의 CASE 연산자로 만든 표현식을 SUM() 함수의 매개변수로 넘겨 칼럼 값을 모두 더합니다. 이렇게 하면 0이 모두 사라지고 결과처럼 데이터가 한 건으로 줄어 조회됩니다. 원하던 대로 2020년 1월부터 2021년 2월까지 월별 확진자 수를 칼럼 형태로 볼 수 있습니다. 이렇게 로우를 칼럼 형태로 변경하면 데이터를 좀 더 쉽게 파악할 수 있습니다.
국가별, 월별 확진자 수와 사망자 수 조회하기
앞에서는 우리나라의 코로나 확진자 수를 월별 칼럼 형태로 볼 수 있는 쿼리를 작성했습니다. 이번에는 국가별 확진자 수와 사망자 수까지 조회하는 쿼리를 작성해 보죠. 국가별로 조회해야 하므로 covid19_country 테이블과 조인해 국가명을 가져오고 사망자 수도 추가해야 합니다.
코드 12-15
WITH raw_data1 AS
(SELECT b.countryname,
EXTRACT(YEAR_MONTH FROM a.issue_date) months,
SUM(a.cases) case_num, SUM(a.deaths) death_num
FROM covid19_data a
INNER JOIN covid19_country b
ON a.countrycode = b.countrycode
GROUP BY 1, 2
)
SELECT countryname,
'1.확진' gubun,
SUM(CASE WHEN months = 202001 THEN case_num ELSE 0 END) "202001",
SUM(CASE WHEN months = 202002 THEN case_num ELSE 0 END) "202002",
SUM(CASE WHEN months = 202003 THEN case_num ELSE 0 END) "202003",
SUM(CASE WHEN months = 202004 THEN case_num ELSE 0 END) "202004",
SUM(CASE WHEN months = 202005 THEN case_num ELSE 0 END) "202005",
SUM(CASE WHEN months = 202006 THEN case_num ELSE 0 END) "202006",
SUM(CASE WHEN months = 202007 THEN case_num ELSE 0 END) "202007",
SUM(CASE WHEN months = 202008 THEN case_num ELSE 0 END) "202008",
SUM(CASE WHEN months = 202009 THEN case_num ELSE 0 END) "202009",
SUM(CASE WHEN months = 202010 THEN case_num ELSE 0 END) "202010",
SUM(CASE WHEN months = 202011 THEN case_num ELSE 0 END) "202011",
SUM(CASE WHEN months = 202012 THEN case_num ELSE 0 END) "202012",
SUM(CASE WHEN months = 202101 THEN case_num ELSE 0 END) "202101",
SUM(CASE WHEN months = 202102 THEN case_num ELSE 0 END) "202102"
FROM raw_data1
GROUP BY 1, 2
UNION ALL
SELECT countryname,
'2.사망' gubun,
SUM(CASE WHEN months = 202001 THEN death_num ELSE 0 END) "202001",
SUM(CASE WHEN months = 202002 THEN death_num ELSE 0 END) "202002",
SUM(CASE WHEN months = 202003 THEN death_num ELSE 0 END) "202003",
SUM(CASE WHEN months = 202004 THEN death_num ELSE 0 END) "202004",
SUM(CASE WHEN months = 202005 THEN death_num ELSE 0 END) "202005",
SUM(CASE WHEN months = 202006 THEN death_num ELSE 0 END) "202006",
SUM(CASE WHEN months = 202007 THEN death_num ELSE 0 END) "202007",
SUM(CASE WHEN months = 202008 THEN death_num ELSE 0 END) "202008",
SUM(CASE WHEN months = 202009 THEN death_num ELSE 0 END) "202009",
SUM(CASE WHEN months = 202010 THEN death_num ELSE 0 END) "202010",
SUM(CASE WHEN months = 202011 THEN death_num ELSE 0 END) "202011",
SUM(CASE WHEN months = 202012 THEN death_num ELSE 0 END) "202012",
SUM(CASE WHEN months = 202101 THEN death_num ELSE 0 END) "202101",
SUM(CASE WHEN months = 202102 THEN death_num ELSE 0 END) "202102"
FROM raw_data1
GROUP BY 1, 2
ORDER BY 1, 2;
실행결과
쿼리가 꽤 복잡합니다. 코드 12-15는 코드 12-14와 거의 같으니 변경된 부분 위주로 살펴보죠.
첫째, raw_data1 서브쿼리에서 covid19_country 테이블과 조인해 국가명인 countryname 칼럼을 추가하고 GROUP BY 절에는 국가명과 연월을 명시합니다. 모든 국가를 조회하므로 기존에 있던 WHERE 절은 삭제합니다.
둘째, 사망자 수를 추가해야 하므로 메인쿼리에서 사망자 수인 death_num을 사용한 SELECT 문을 추가해 확진자 수인 case_num을 사용한 SELECT 문과 UNION ALL로 연결합니다.
셋째, 국가별로 확진자 수와 사망자 수를 보여 줘야 하므로 2개의 SELECT 문에 countryname과 gubun이라는 가상 칼럼을 추가하고 GROUP BY 절에 이 둘을 추가합니다. 여기서 gubun은 첫 번째 SELECT 문에서는 확진자 수이므로 '1.확진', 두 번째 SELECT 문에서는 사망자 수이므로 '2.사망'이라고 명시합니다.
쿼리를 실행하면 결과처럼 국가별로 확진자 수와 사망자 수를 2020년 1월부터 2021년 2월까지 월별로 볼 수 있습니다. 쿼리가 복잡하지만, 결과는 한눈에 보기 편합니다.
그런데 여기서 특정 국가의 데이터만 보고 싶다면 어떻게 해야 할까요? 코드 12-14처럼 raw_data1 서브쿼리의 WHERE 절에 조건을 추가해도 되지만, 매번 긴 쿼리를 수정해야 하므로 좀 불편합니다. 이럴 때 쿼리를 다음과 같이 뷰로 만들면 훨씬 간편하게 사용할 수 있습니다.
코드 12-16
CREATE OR REPLACE VIEW covid19_summary1_v AS
WITH raw_data1 AS
(SELECT b.countryname,
EXTRACT(YEAR_MONTH FROM a.issue_date) months,
SUM(a.cases) case_num, SUM(a.deaths) death_num
FROM covid19_data a
INNER JOIN covid19_country b
ON a.countrycode = b.countrycode
GROUP BY 1, 2
)
SELECT countryname,
'1.확진' gubun,
SUM(CASE WHEN months = 202001 THEN case_num ELSE 0 END) "202001",
SUM(CASE WHEN months = 202002 THEN case_num ELSE 0 END) "202002",
SUM(CASE WHEN months = 202003 THEN case_num ELSE 0 END) "202003",
SUM(CASE WHEN months = 202004 THEN case_num ELSE 0 END) "202004",
SUM(CASE WHEN months = 202005 THEN case_num ELSE 0 END) "202005",
SUM(CASE WHEN months = 202006 THEN case_num ELSE 0 END) "202006",
SUM(CASE WHEN months = 202007 THEN case_num ELSE 0 END) "202007",
SUM(CASE WHEN months = 202008 THEN case_num ELSE 0 END) "202008",
SUM(CASE WHEN months = 202009 THEN case_num ELSE 0 END) "202009",
SUM(CASE WHEN months = 202010 THEN case_num ELSE 0 END) "202010",
SUM(CASE WHEN months = 202011 THEN case_num ELSE 0 END) "202011",
SUM(CASE WHEN months = 202012 THEN case_num ELSE 0 END) "202012",
SUM(CASE WHEN months = 202101 THEN case_num ELSE 0 END) "202101",
SUM(CASE WHEN months = 202102 THEN case_num ELSE 0 END) "202102"
FROM raw_data1
GROUP BY 1, 2
UNION ALL
SELECT countryname,
'2.사망' gubun,
SUM(CASE WHEN months = 202001 THEN death_num ELSE 0 END) "202001",
SUM(CASE WHEN months = 202002 THEN death_num ELSE 0 END) "202002",
SUM(CASE WHEN months = 202003 THEN death_num ELSE 0 END) "202003",
SUM(CASE WHEN months = 202004 THEN death_num ELSE 0 END) "202004",
SUM(CASE WHEN months = 202005 THEN death_num ELSE 0 END) "202005",
SUM(CASE WHEN months = 202006 THEN death_num ELSE 0 END) "202006",
SUM(CASE WHEN months = 202007 THEN death_num ELSE 0 END) "202007",
SUM(CASE WHEN months = 202008 THEN death_num ELSE 0 END) "202008",
SUM(CASE WHEN months = 202009 THEN death_num ELSE 0 END) "202009",
SUM(CASE WHEN months = 202010 THEN death_num ELSE 0 END) "202010",
SUM(CASE WHEN months = 202011 THEN death_num ELSE 0 END) "202011",
SUM(CASE WHEN months = 202012 THEN death_num ELSE 0 END) "202012",
SUM(CASE WHEN months = 202101 THEN death_num ELSE 0 END) "202101",
SUM(CASE WHEN months = 202102 THEN death_num ELSE 0 END) "202102"
FROM raw_data1
GROUP BY 1, 2
ORDER BY 1, 2;
-- 미국 현황 조회
SELECT *
FROM covid19_summary1_v
WHERE countryname = 'United States';
실행결과
코드 12-16은 CREATE OR REPLACE VIEW 구문으로 코드 12-15를 covid19_summary1_v라는 뷰로 만듭니다. 그다음 covid19_summary1_v 뷰를 조회해 미국의 코로나 확진자 수와 사망자 수를 조회합니다.
이렇게 자주 사용하는 복잡한 쿼리를 뷰로 만들어 놓으면 필요할 때마다 편리하게 사용할 수 있습니다.
우리나라의 월별 누적 확진자 수와 사망자 수 조회하기
이번에는 우리나라의 월별 누적 확진자 수와 사망자 수를 조회해 보겠습니다. 예를 들어 2020년 1월에 10명, 2월에 20명이 확진됐다면 누적 확진자 수는 202001에는 10명, 202002에는 30(10 + 20)명이 되겠죠. 월별로 확진자 수를 더하면 됩니다. 이는 윈도우로 처리할 수 있습니다.
코드 12-17
WITH raw_data1 AS
(SELECT EXTRACT(YEAR_MONTH FROM issue_date) months,
SUM(cases) case_num, SUM(deaths) death_num
FROM covid19_data
WHERE countrycode = 'KOR'
GROUP BY 1
)
SELECT months, case_num, death_num,
SUM(case_num) OVER (ORDER BY months) cum_case_num,
SUM(death_num) OVER (ORDER BY months) cum_death_num
FROM raw_data1
ORDER BY 1;
실행결과
raw_data1 서브쿼리는 코드 12-13의 서브쿼리와 같고, 나머지 부분을 메인쿼리에서 처리합니다. 누적 합계를 구해야 하므로 집계 함수인 SUM()을 사용하고, OVER 다음에 ORDER BY 절을 추가합니다. 월별 누적이므로 ORDER BY months라고 명시해야 월이 바뀌면서 누적 합계를 구할 수 있습니다. 여기서 PARTITION BY 절을 사용하지 않은 이유는, raw_data1 서브쿼리가 반환하는 결과 집합은 1개 로우가 하나의 월을 나타내므로 추가로 파티션 그룹을 지정할 필요가 없기 때문입니다.
결과를 보면 cum_case_num은 누적 확진자 수, cum_death_num은 누적 사망자 수를 보여 줍니다. 따라서 마지막 로우인 202102건의 누적 합계는 각각 전체 대상 기간의 누적 확진자 수와 누적 사망자 수를 나타냅니다.
대륙별 사망자 수 상위 3개국 조회하기
전 기간을 대상으로 대륙별로 사망자가 가장 많은 3개 국가와 해당 국가의 누적 사망자 수를 조회해 보겠습니다.
코드 12-18
WITH raw_data1 AS
(SELECT b.continent, b.countryname,
SUM(a.deaths) death_num, SUM(a.cases) case_num
FROM covid19_data a
INNER JOIN covid19_country b
ON a.countrycode = b.countrycode
GROUP BY 1, 2
),
raw_data2 AS
(SELECT continent, countryname, death_num,
RANK() OVER (PARTITION BY continent
ORDER BY death_num DESC) ranks
FROM raw_data1
)
SELECT *
FROM raw_data2
WHERE ranks <= 3;
실행결과
raw_data1 서브쿼리에서 covid19_data와 covid19_country 테이블을 조인해 대륙명과 국가명을 가져옵니다. 그리고 raw_data2 서브쿼리에서 RANK() 윈도우 함수로 사망자 수 순위를 구합니다. 대륙별 순위이므로 PARTITION BY 절에 대륙명(continent)을 명시해 파티션을 지정하고, ORDER BY 절에서는 사망자 수로 내림차순 정렬합니다. 이렇게 하면 대륙별로 사망자가 많은 순서대로 순위가 매겨지죠. 마지막으로 메인쿼리에서는 raw_data1 서브쿼리를 조회하는데, 3위까지만 가져오므로 WHERE 조건에 순위에 대한 가상 칼럼인 ranks 값이 3보다 작거나 같다는 조건을 주었습니다.
결과를 보면 대륙별로 사망자가 많은 3개 국가가 순서대로 조회됐습니다.