11.26mysql测试

一、表格

建表

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;