一带一路金砖赛大数据集群运维管理赛项总决赛赛前练习

SQL数据分析

共享单车数据分析（0 / 20分）

项目背景

本项目数据为共享单车使用的数据信息，数据包含2021、2022年共享单车使用的详细数据，数据包含使用的季节、时间、天气、温度、体感温度、是否注册等信息。

数据说明

数据地址：/root/mysql/bicycle.csv（ bicycle.csv 下载后重命名去掉 .zip ）
数据库：train（自行创建）
数据表：bicycle
基础数据特征：
共享单车数据表（bicycle）包含日期、季节、是否假日、是否工作日、天气、体感温度、相对湿度、未注册用户、注册用户（datetime、season、holiday、workingday、weather、atemp、humidity、casual、registered）

字段说明及建立表格的数据类型如下：

数据处理

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;