疫情数据分析
1.前期数据准备
创建hive表,并导入数据:
create table covid_19_data(
dt String comment "日期",
confirmed_case Int comment "确诊病例",
new_confirmed_case Int comment "新增病例",
cure_case Int comment "累计治愈",
new_cure_case Int comment "新增治愈",
death_case Int comment "死亡病例",
new_death_case Int comment "新增死亡",
state String comment "洲",
country String comment "国家"
)
row format delimited
fields terminated by ",";
load data inpath "/covid_19.csv" into table covid_19_data;
2.数据分析
2.1 死亡人数最多的国家以及其所在洲
select state,
country,
sum(new_death_case) sum_death_case
from covid_19_data
group by state, country
order by sum_death_case desc
limit 1;
2.2 连续三日新增病例在10000人以上的国家及其所连续天数
select country,
count(1) as days,
concat_ws("--",cast(min(dt) as string),cast(max(dt) as string)) as duration_dt
from (select country,
new_confirmed_case,
dt,
row_number() over (partition by country order by dt) rank
from (select country,
to_date(from_unixtime(UNIX_TIMESTAMP(dt, "yyyyMMdd"))) dt,
new_confirmed_case
from covid_19_data
where new_confirmed_case >= 10000) t1) t2
group by country, date_sub(dt, rank)
having days >= 20;
2.3 找出新增确诊病例连续十天未减少的国家及其时间,一个国家出现多次,保留距离现在时间最近的一次,并按持续天数降序排序.
select country,
days,
duration_dt
from (select country,
days,
duration_dt,
row_number() over (partition by country order by min_dt desc) rank
from (select country,
count(1) as days,
concat_ws("--", cast(min(dt) as string), cast(max(dt) as string)) as duration_dt,
min(dt) as min_dt
from (select country,
to_date(from_unixtime(UNIX_TIMESTAMP(dt, "yyyyMMdd"))) as dt,
new_confirmed_case,
row_number() over (partition by country order by new_confirmed_case) rank
from covid_19_data) t1
group by country, date_sub(dt, rank)
having days >= 20) t2) t3
where rank = 1
order by days desc;