一带一路金砖赛大数据集群运维管理赛项总决赛赛前练习
SQL数据分析
共享单车数据分析(0 / 20分)
项目背景
本项目数据为共享单车使用的数据信息,数据包含2021、2022年共享单车使用的详细数据,数据包含使用的季节、时间、天气、温度、体感温度、是否注册等信息。
数据说明
数据地址:/root/mysql/bicycle.csv( bicycle.csv 下载后重命名去掉 .zip
)
数据库:train(自行创建)
数据表:bicycle
基础数据特征:
共享单车数据表(bicycle)包含日期、季节、是否假日、是否工作日、天气、体感温度、相对湿度、未注册用户、注册用户(datetime、season、holiday、workingday、weather、atemp、humidity、casual、registered)
datetime | season | holiday | workingday | weather | atemp | humidity | casual | registered |
---|---|---|---|---|---|---|---|---|
2021/1/1 0:00 | 1 | 0 | 0 | 1 | 14.395 | 81 | 3 | 13 |
字段说明及建立表格的数据类型如下:
字段名 | 字段中文名 | 数据类型 | 数据说明 | 示例数据 |
---|---|---|---|---|
datetime | 日期 | varchar(255) | 共享单车使用的时间段数据,数据为:日期 + 时间(小时:分钟)。注意:时间最小颗粒度为小时 | 2021/1/1 0:00 |
season | 季节 | varchar(255) | 共享单车使用的季节数据,1:春季,2:夏季,3:秋季,4:冬季 | 1 |
holiday | 是否假日 | varchar(255) | 标识当前日期是否为被视为假日,其中1表示为假日,0表示为非假日。注意:假日不包含正常休假的周末 | 0 |
workingday | 是否工作日 | varchar(255) | 表示当前日期既不是周末也不是节假日,其中0表示非工作日、1表示工作日 | 0 |
weather | 天气 | varchar(255) | 表示当前日期的天气,1、晴天,2、雾/多云,3、小雪,4、大雨/雷暴 | 1 |
atemp | 体感温度 | double | 表示当前日期的体感温度,温度的数据范围为1-45摄氏度。注意:对温度字段进行分析时,需要对温度字段进行分段,具体分为[0-20)、[20-30)、30以上 | 14.395 |
humidity | 相对湿度 | int | 表示当前日期的相对湿度,湿度的范围为0-100。注意:在分析时需要对对湿度进行分段,具体分为[0-30)、[30-60)、[60-80)、80以上 | 81 |
casual | 未注册用户 | int | 未注册用户的使用人数,也表示该时间段的使用次数。 | 3 |
registered | 注册用户 | int | 注册用户的使用人数,也表示该时间段的使用次数。 | 13 |
数据处理
1、数据字段
数据集中已经给出对应的字段,这里无需再做修改。
2、时间日期列处理
(1)修改字段类型
alter table bicycle modify datetime datetime;
(2)分别提取日期列和时间列
# a、添加日期列date
alter TABLE bicycle add date date;
# 获取time_of_payment中日期数据命令:
update bicycle set date = DATE_FORMAT(datetime,'%Y-%m-%d');
# b、添加时间列time(自行操作)
# 获取time_of_payment中时间数据,格式为'%H:%i:%s'
数据分析
1. 计算全量数据,求共享单车年使用次数,结果存入视图table1。
字段要求:(year,sum)
2. 对2022年共享单车使用次数进行分析,求注册用户和未注册用户的年使用数,结果存入视图table2。
字段要求: (year , sum_c , sum_r)
注意:其中sum_c表示未注册用户,sum_r表示注册用户。
3. 进一步对2022年注册用户共享单车使用数量进行分析,计算该年的单月使用人数,结果存入视图table3。
字段要求: (month , sum_r)
4. 对2022年注册用户共享单车数据进行时段分析,计算该年各时间段(24小时制)的单车使用数量(累计值),结果存入视图table4。
字段要求: (hour,sum_r)
在以下操作前需要先创建库,表,将数据导入到表中:
And 1.查看所有数据库
show databases;
And 2.创建train数据库并进入
create database train;
use train;
And 3.创建bicycle数据表并查看是否完整
create table bicycle(
datetime varchar(255),
season varchar(255),
holiday varchar(255),
workingday varchar(255),
weather varchar(255),
atemp double,
humidity int,
casual int,
registered int);
--创建
desc bicycle;
--查看
And 4.导入csv数据表内容,并查看数据导入是否出错
load data local infile '/root/mysql/bicycle.csv' into TABLE bicycle fields TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES[ROWS];
--导入
select * from bicycle limit 10;
--查看数据行,检查是否有导入错误
And 5.将datatime字段的类型更改成datetime时间串,并将日期和时间独立区分新添字段
alter table bicycle modify datetime datetime;
--修改datetime字段类型
alter table bicycle add date date;
--add添加data字段,类型为date
update bicycle set date=DATE_FORMAT(datetime,"%Y-%m-%d");
--将datetime字段中的年月日格式填入到date字段中
alter table bicycle add time time;
--add添加time字段,类型为time
update bicycle set time=DATE_FORMAT(datetime,"%H:%i:%s");
--将datetime字段中的时分秒格式填入到time字段中
这里再 select
检查一下表内数据是否正确完整
考核条件如下:
\1. 环境已经安装mysql8,自行开启数据库服务、创建数据库train,创建数据表bicycle,导入数据,计算共享单车年使用次数,结果存入视图table1 。(0.00 / 5分)操作环境: mysql
这里题目要求字段
year
和sum
,其中sum可以通过最上方中的字段说明中了解为casual
和registered
字段相加的值,以年数量排列:create view table1 as(select year(date) year,sum(casual)+sum(registered) sum from bicycle group by year(date)); select * from table1;
\2. 对2022年共享单车使用次数进行分析,求注册用户和未注册用户的年使用数,结果存入视图table2。(0 / 5分)操作环境: mysql
字段要求: (year , sum_c , sum_r)要创建的视图内容为2022年的数据
create view table2 as(select year(date) year, sum(casual) sum_c, sum(registered) sum_r from bicycle where year(date)=2022 group by year(date)); select * from table2;
\3. 进一步对2022年注册用户共享单车使用数量进行分析,计算该年的单月使用人数,结果存入视图table3。(0 / 5分)操作环境: mysql
字段要求: (month , sum_r) 同样时2022年的数据,以单月分析
create view table3 as(select month(date) month,sum(registered) sum_r from bicycle where year(date)=2022 group by month(date)); select * from table3;
\4. 对2022年注册用户共享单车数据进行时段分析,计算该年各时间段(24小时制)的单车使用数量(累计值),结果存入视图table4。(0 / 5分)操作环境: mysql
这题与第三题一致,改几个字段名就行;字段要求: (hour,sum_r)
create view table4 as(select hour(time) hour, sum(registered) sum_r from bicycle where year(date)=2022 group by hour(time)); select * from table4;