COVID-19 Open-Data
COVID-19 Open-Data 旨在构建最大规模的 COVID-19 流行病学数据库，并提供一套功能强大且覆盖面广泛的协变量。其内容包括与人口统计、经济、流行病学、地理、健康、住院、流动性、政府响应、天气等相关的开放数据，这些数据来自公开渠道并具有相应许可证。
详细信息请参阅 GitHub 仓库此处。
将这些数据写入 ClickHouse 非常简单……
注意
以下命令是在 ClickHouse Cloud 的 生产 实例上执行的。您也可以轻松地在本地安装的实例中运行这些命令。
- 让我们先来看一下这些数据是什么样子的：
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 行数据。耗时：0.745 秒。
- 现在让我们查看几行数据：
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())─┐
│ 1253 万 │
└─────────────────────────────────┘
- 来看一下共记录了多少例新冠肺炎（COVID-19）病例：
SELECT formatReadableQuantity(sum(new_confirmed))
FROM covid19;
┌─formatReadableQuantity(sum(new_confirmed))─┐
│ 13.9 亿 │
└────────────────────────────────────────────┘
- 你会注意到数据中有很多日期对应的数值为 0——要么是周末，要么是某些天没有按日上报数据。我们可以使用窗口函数来平滑新确诊病例的每日平均值：
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 '增加'
WHEN percent_change = 0 THEN '无变化'
ELSE '减少'
END AS trend
FROM confirmed_percent_change
WHERE location_key = 'US_DC';
结果如下：
┌───────date─┬─new_confirmed─┬─percent_change─┬─trend─────┐
│ 2020-03-08 │ 0 │ nan │ 下降 │
│ 2020-03-09 │ 2 │ inf │ 上升 │
│ 2020-03-10 │ 0 │ -100 │ 下降 │
│ 2020-03-11 │ 6 │ inf │ 上升 │
│ 2020-03-12 │ 0 │ -100 │ 下降 │
│ 2020-03-13 │ 0 │ nan │ 下降 │
│ 2020-03-14 │ 6 │ inf │ 上升 │
│ 2020-03-15 │ 1 │ -83 │ 下降 │
│ 2020-03-16 │ 5 │ 400 │ 上升 │
│ 2020-03-17 │ 9 │ 80 │ 上升 │
│ 2020-03-18 │ 8 │ -11 │ 下降 │
│ 2020-03-19 │ 32 │ 300 │ 上升 │
│ 2020-03-20 │ 6 │ -81 │ 下降 │
│ 2020-03-21 │ 21 │ 250 │ 上升 │
│ 2020-03-22 │ 18 │ -14 │ 下降 │
│ 2020-03-23 │ 21 │ 17 │ 上升 │
│ 2020-03-24 │ 46 │ 119 │ 上升 │
│ 2020-03-25 │ 48 │ 4 │ 上升 │
│ 2020-03-26 │ 36 │ -25 │ 下降 │
│ 2020-03-27 │ 37 │ 3 │ 上升 │
│ 2020-03-28 │ 38 │ 3 │ 上升 │
│ 2020-03-29 │ 59 │ 55 │ 上升 │
│ 2020-03-30 │ 94 │ 59 │ 上升 │
│ 2020-03-31 │ 91 │ -3 │ 下降 │
│ 2020-04-01 │ 67 │ -26 │ 下降 │
│ 2020-04-02 │ 104 │ 55 │ 上升 │
│ 2020-04-03 │ 145 │ 39 │ 上升 │
注意
正如 GitHub 仓库 中所述，自 2022 年 9 月 15 日起，该数据集不再更新。
