COVID-19 공개 데이터 데이터셋
COVID-19 공개 데이터 데이터셋
COVID-19 Open-Data는 가장 큰 Covid-19 역학 데이터베이스를 조립하려는 프로젝트로, 광범위한 공변량(covariate) 집합을 함께 제공해요. 인구통계, 경제, 역학, 지리, 건강, 입원, 이동성, 정부 대응, 날씨 등을 포함한 공개·공공 소스·라이선스 데이터가 담겨 있어요. 이 페이지에서 이 데이터를 ClickHouse에 쉽게 삽입하는 방법을 보여줘요.
본문
COVID-19 Open-Data는 광범위한 공변량 집합과 함께 가장 큰 Covid-19 역학 데이터베이스를 조립하려고 해요. 인구통계, 경제, 역학, 지리, 건강, 입원, 이동성, 정부 대응, 날씨 등에 관한 공개적이고 공공 소스로부터 얻은 라이선스 데이터를 포함해요. 자세한 내용은 GitHub 여기에서 확인할 수 있어요. 이 데이터를 ClickHouse에 삽입하는 건 쉬워요…
다음 명령들은 ClickHouse Cloud의 Production 인스턴스에서 실행됐어요. 로컬 설치에서도 쉽게 실행할 수 있어요.
- 데이터의 모습을 살펴볼게요:
DESCRIBE url(
'https://storage.googleapis.com/covid19-open-data/v3/epidemiology.csv',
'CSVWithNames'
);
CSV 파일은 10개 컬럼을 가져요:
┌─name─────────────────┬─type─────────────┐
│ date │ Nullable(Date) │
│ location_key │ Nullable(String) │
│ new_confirmed │ Nullable(Int64) │
│ new_deceased │ Nullable(Int64) │
│ new_recovered │ Nullable(Int64) │
│ new_tested │ Nullable(Int64) │
│ cumulative_confirmed │ Nullable(Int64) │
│ cumulative_deceased │ Nullable(Int64) │
│ cumulative_recovered │ Nullable(Int64) │
│ cumulative_tested │ Nullable(Int64) │
└──────────────────────┴──────────────────┘
10 rows in set. Elapsed: 0.745 sec.
- 이제 몇 개 행을 살펴볼게요:
SELECT *
FROM url('https://storage.googleapis.com/covid19-open-data/v3/epidemiology.csv')
LIMIT 100;
url 함수가 CSV 파일의 데이터를 쉽게 읽는 걸 볼 수 있어요:
┌─c1─────────┬─c2───────────┬─c3────────────┬─c4───────────┬─c5────────────┬─c6─────────┬─c7───────────────────┬─c8──────────────────┬─c9───────────────────┬─c10───────────────┐
│ date │ location_key │ new_confirmed │ new_deceased │ new_recovered │ new_tested │ cumulative_confirmed │ cumulative_deceased │ cumulative_recovered │ cumulative_tested │
│ 2020-04-03 │ AD │ 24 │ 1 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 466 │ 17 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
│ 2020-04-04 │ AD │ 57 │ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 523 │ 17 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
│ 2020-04-05 │ AD │ 17 │ 4 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 540 │ 21 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
│ 2020-04-06 │ AD │ 11 │ 1 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 551 │ 22 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
│ 2020-04-07 │ AD │ 15 │ 2 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 566 │ 24 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
│ 2020-04-08 │ AD │ 23 │ 2 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 589 │ 26 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
└────────────┴──────────────┴───────────────┴──────────────┴───────────────┴────────────┴──────────────────────┴─────────────────────┴──────────────────────┴───────────────────┘
- 데이터의 모습을 알았으니 이제 테이블을 만들게요:
CREATE TABLE covid19 (
date Date,
location_key LowCardinality(String),
new_confirmed Int32,
new_deceased Int32,
new_recovered Int32,
new_tested Int32,
cumulative_confirmed Int32,
cumulative_deceased Int32,
cumulative_recovered Int32,
cumulative_tested Int32
)
ENGINE = MergeTree
ORDER BY (location_key, date);
- 다음 명령은 전체 데이터셋을
covid19테이블에 삽입해요:
INSERT INTO covid19
SELECT *
FROM
url(
'https://storage.googleapis.com/covid19-open-data/v3/epidemiology.csv',
CSVWithNames,
'date Date,
location_key LowCardinality(String),
new_confirmed Int32,
new_deceased Int32,
new_recovered Int32,
new_tested Int32,
cumulative_confirmed Int32,
cumulative_deceased Int32,
cumulative_recovered Int32,
cumulative_tested Int32'
);
- 꽤 빠르게 진행돼요 — 몇 행이 삽입됐는지 확인해 볼게요:
SELECT formatReadableQuantity(count())
FROM covid19;
┌─formatReadableQuantity(count())─┐
│ 12.53 million │
└─────────────────────────────────┘
- 기록된 Covid-19 총 확진자 수를 확인해 볼게요:
SELECT formatReadableQuantity(sum(new_confirmed))
FROM covid19;
┌─formatReadableQuantity(sum(new_confirmed))─┐
│ 1.39 billion │
└────────────────────────────────────────────┘
- 데이터에는 주말이나 매일 숫자가 보고되지 않은 날에 0이 많다는 걸 볼 수 있어요. 윈도우 함수로 신규 확진자의 일일 평균을 평활화(smooth)할 수 있어요:
SELECT
AVG(new_confirmed) OVER (PARTITION BY location_key ORDER BY date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS cases_smoothed,
new_confirmed,
location_key,
date
FROM covid19;
- 이 쿼리는 각 위치의 최신 값을 결정해요. 모든 국가가 매일 보고하지 않으므로
max(date)를 사용할 수 없어서,ROW_NUMBER로 마지막 행을 가져와요:
WITH latest_deaths_data AS
( SELECT location_key,
date,
new_deceased,
new_confirmed,
ROW_NUMBER() OVER (PARTITION BY location_key ORDER BY date DESC) AS rn
FROM covid19)
SELECT location_key,
date,
new_deceased,
new_confirmed,
rn
FROM latest_deaths_data
WHERE rn=1;
lagInFrame으로 매일 신규 확진의LAG를 결정할 수 있어요. 이 쿼리에서는US_DC위치로 필터링해요:
SELECT
new_confirmed - lagInFrame(new_confirmed,1) OVER (PARTITION BY location_key ORDER BY date) AS confirmed_cases_delta,
new_confirmed,
location_key,
date
FROM covid19
WHERE location_key = 'US_DC';
응답은 이렇게 생겼어요:
┌─confirmed_cases_delta─┬─new_confirmed─┬─location_key─┬───────date─┐
│ 0 │ 0 │ US_DC │ 2020-03-08 │
│ 2 │ 2 │ US_DC │ 2020-03-09 │
│ -2 │ 0 │ US_DC │ 2020-03-10 │
│ 6 │ 6 │ US_DC │ 2020-03-11 │
│ -6 │ 0 │ US_DC │ 2020-03-12 │
│ 0 │ 0 │ US_DC │ 2020-03-13 │
│ 6 │ 6 │ US_DC │ 2020-03-14 │
│ -5 │ 1 │ US_DC │ 2020-03-15 │
│ 4 │ 5 │ US_DC │ 2020-03-16 │
│ 4 │ 9 │ US_DC │ 2020-03-17 │
│ -1 │ 8 │ US_DC │ 2020-03-18 │
│ 24 │ 32 │ US_DC │ 2020-03-19 │
│ -26 │ 6 │ US_DC │ 2020-03-20 │
│ 15 │ 21 │ US_DC │ 2020-03-21 │
│ -3 │ 18 │ US_DC │ 2020-03-22 │
│ 3 │ 21 │ US_DC │ 2020-03-23 │
- 이 쿼리는 매일 신규 확진의 변화율을 계산하고, 결과 집합에 간단한
increase또는decrease컬럼을 포함해요:
WITH confirmed_lag AS (
SELECT
*,
lagInFrame(new_confirmed) OVER(
PARTITION BY location_key
ORDER BY date
) AS confirmed_previous_day
FROM covid19
),
confirmed_percent_change AS (
SELECT
*,
COALESCE(ROUND((new_confirmed - confirmed_previous_day) / confirmed_previous_day * 100), 0) AS percent_change
FROM confirmed_lag
)
SELECT
date,
new_confirmed,
percent_change,
CASE
WHEN percent_change > 0 THEN 'increase'
WHEN percent_change = 0 THEN 'no change'
ELSE 'decrease'
END AS trend
FROM confirmed_percent_change
WHERE location_key = 'US_DC';
결과는 이렇게 생겼어요:
┌───────date─┬─new_confirmed─┬─percent_change─┬─trend─────┐
│ 2020-03-08 │ 0 │ nan │ decrease │
│ 2020-03-09 │ 2 │ inf │ increase │
│ 2020-03-10 │ 0 │ -100 │ decrease │
│ 2020-03-11 │ 6 │ inf │ increase │
│ 2020-03-12 │ 0 │ -100 │ decrease │
│ 2020-03-13 │ 0 │ nan │ decrease │
│ 2020-03-14 │ 6 │ inf │ increase │
│ 2020-03-15 │ 1 │ -83 │ decrease │
│ 2020-03-16 │ 5 │ 400 │ increase │
│ 2020-03-17 │ 9 │ 80 │ increase │
│ 2020-03-18 │ 8 │ -11 │ decrease │
│ 2020-03-19 │ 32 │ 300 │ increase │
│ 2020-03-20 │ 6 │ -81 │ decrease │
│ 2020-03-21 │ 21 │ 250 │ increase │
│ 2020-03-22 │ 18 │ -14 │ decrease │
│ 2020-03-23 │ 21 │ 17 │ increase │
│ 2020-03-24 │ 46 │ 119 │ increase │
│ 2020-03-25 │ 48 │ 4 │ increase │
│ 2020-03-26 │ 36 │ -25 │ decrease │
│ 2020-03-27 │ 37 │ 3 │ increase │
│ 2020-03-28 │ 38 │ 3 │ increase │
│ 2020-03-29 │ 59 │ 55 │ increase │
│ 2020-03-30 │ 94 │ 59 │ increase │
│ 2020-03-31 │ 91 │ -3 │ decrease │
│ 2020-04-01 │ 67 │ -26 │ decrease │
│ 2020-04-02 │ 104 │ 55 │ increase │
│ 2020-04-03 │ 145 │ 39 │ increase │
GitHub 저장소에서 언급했듯이, 이 데이터셋은 2022년 9월 15일부터 더 이상 업데이트되지 않아요.