一、表格
建表
CREATE TABLE `countrydata` (
`id` int NOT NULL,
`confirmedCount` varchar(255) NOT NULL,
`confirmedIncr` varchar(255) NOT NULL,
`recordDate` datetime NOT NULL,
`countryName` varchar(255) not NULL,
`countryShotCode` varchar(255) not NULL,
`contient` varchar(255) not NULL
);
导入
load data local infile '/root/ml-1m/countrydata.dat' into table countrydata
CHARACTER SET utf8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n';
1.统计每个国家在数据截止统计时的累计确诊人数。
SELECT countryName,confirmedCount FROM countrydata WHERE recordDate=(
SELECT MAX(recordDate) FROM countrydata GROUP BY countryName LIMIT 1
);
SELECT countryName,SUM(confirmedIncr)
FROM countrydata
GROUP BY countryName;
2.统计全世界在数据截止统计时的总确诊人数。
SELECT SUM(confirmedCount) 总感染人数 FROM countrydata WHERE recordDate=(
SELECT MAX(recordDate) FROM countrydata GROUP BY countryName LIMIT 1
);
3.统计每个大洲中每日新增确诊人数最多的国家及确诊人数,显示 20200408 这一天各大洲当日新增确诊人数最多的国家及确诊人数
SELECT contient,countryName,recordDate,confirmedIncr,confirmedCount
from
(
SELECT contient,countryName,recordDate,confirmedCount,confirmedIncr,
ROW_NUMBER() OVER(PARTITION BY contient ORDER BY confirmedIncr DESC)rn
FROM countrydata
where recordDate='20200408')t
where rn=1;
4.统计每个大洲中每日累计确诊人数最多的国家及确诊人数,显示 20200607 这一天各大洲当日累计确诊人数最多的国家及确诊人数
with t as (
select recordDate,row_number()over(partition by recordDate,continent order by confirmedCount desc) rn,confirmedCount,countryName,continent from countrydata
)
select recordDate,countryName,continent,confirmedcount
from t
where rn=1 and recordDate='20200607';
5.统计每个大洲每月累计确诊人数,显示 202006 这个月每个大洲的累计确诊人数
注意:这里截取日期段时,要配合创建表格时时间的类型,此处是datetime类型,所以是2020-06-01格式,如果是date类型,则是20200601格式,通过格式来确定截取到第几个字符
SELECT contient,SUBSTRING(recordDate,1,7) months,SUM(confirmedIncr)
FROM countrydata
WHERE SUBSTRING(recordDate,1,7)='2020-06'
GROUP BY contient,months;