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

博主：南凝呀
发布时间：2023 年 12 月 06 日
1292 次浏览
暂无评论
7060字数
分类：

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

## SQL数据分析

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

### 项目背景

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

### 数据说明

数据地址：/root/mysql/bicycle.csv（ [bicycle.csv](https://www.zmzaxg.top/usr/uploads/2023/12/1665488629.zip) 下载后重命名去掉 `.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）修改字段类型

```sql
alter table bicycle modify datetime datetime;
```

（2）分别提取日期列和时间列

```sql
# 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.查看所有数据库

```sql
show databases;
```

![image-20231206192732235.png](https://www.zmzaxg.top/usr/uploads/2023/12/3898537695.png)

And 2.创建train数据库并进入

```sql
create database train;
use train;
```

![image-20231206193049050.png](https://www.zmzaxg.top/usr/uploads/2023/12/4058953887.png)

And 3.创建bicycle数据表并查看是否完整

```sql
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;
--查看
```

![image-20231206193633291.png](https://www.zmzaxg.top/usr/uploads/2023/12/2456640622.png)

![image-20231206193707952.png](https://www.zmzaxg.top/usr/uploads/2023/12/3029770297.png)

And 4.导入csv数据表内容，并查看数据导入是否出错

```sql
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;
--查看数据行，检查是否有导入错误
```

![image-20231206194245584.png](https://www.zmzaxg.top/usr/uploads/2023/12/4128781694.png)

![image-20231206194535648.png](https://www.zmzaxg.top/usr/uploads/2023/12/4073161203.png)

And 5.将datatime字段的类型更改成datetime时间串，并将日期和时间独立区分新添字段

```sql
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字段中
```

![image-20231206194949056.png](https://www.zmzaxg.top/usr/uploads/2023/12/2021980362.png)

![image-20231206195513179.png](https://www.zmzaxg.top/usr/uploads/2023/12/3707789156.png)

这里再 `select`检查一下表内数据是否正确完整

![image-20231206195920462.png](https://www.zmzaxg.top/usr/uploads/2023/12/1972029209.png)

---

考核条件如下：

\1. 环境已经安装mysql8，自行开启数据库服务、创建数据库train，创建数据表bicycle,导入数据，计算共享单车年使用次数，结果存入视图table1 。（0.00 / 5分）操作环境: mysql

> 这里题目要求字段 `year`和 `sum`，其中sum可以通过最上方中的字段说明中了解为 `casual`和 `registered`字段相加的值，以年数量排列：
>
> ```sql
> create view table1 as(select year(date) year,sum(casual)+sum(registered) sum from bicycle group by year(date));
>
> select * from table1;
> ```
>
> ![image-20231206200929627.png](https://www.zmzaxg.top/usr/uploads/2023/12/405197777.png)

\2. 对2022年共享单车使用次数进行分析，求注册用户和未注册用户的年使用数，结果存入视图table2。（0 / 5分）操作环境: mysql

> 字段要求： （year , sum_c , sum_r）要创建的视图内容为2022年的数据
>
> ```sql
> 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;
> ```
>
> ![image-20231206201751773.png](https://www.zmzaxg.top/usr/uploads/2023/12/2719925359.png)

\3. 进一步对2022年注册用户共享单车使用数量进行分析，计算该年的单月使用人数，结果存入视图table3。（0 / 5分）操作环境: mysql

> 字段要求： (month , sum_r)  同样时2022年的数据，以单月分析
>
> ```sql
> 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;
> ```
>
> ![image-20231206202438656.png](https://www.zmzaxg.top/usr/uploads/2023/12/4059047911.png)

\4. 对2022年注册用户共享单车数据进行时段分析，计算该年各时间段(24小时制)的单车使用数量（累计值），结果存入视图table4。（0 / 5分）操作环境: mysql

> 这题与第三题一致，改几个字段名就行；字段要求： (hour,sum_r)
>
> ```sql
> 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;
> ```
> ![image-20231206203221226.png](https://www.zmzaxg.top/usr/uploads/2023/12/1798580417.png)

最后修改：2024 年 08 月 30 日

觉得文章有用，可以赞赏请我喝瓶冰露

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，将记录你的ip和窗口信息展示，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

ZDX随身wifi折腾记
浏览次数: 7125
随身wifi刷debian
浏览次数: 3828
centos安装谷歌Chrome浏览器
浏览次数: 3317
办公软件共享
浏览次数: 3073
关于搭建Hadoop集群
浏览次数: 2060

浅草物语
原来是h-t-t-p-s-是敏感词去掉了就能发出来了
浅草物语
麻烦把原浅草博客改为浅草物语，网站添加www.，变更为：www...
clash节点
友链申请网站名称：clash节点网站链接：https://cl...
zz
之前是因为在MacBook的模拟器上搞有问题，后面找了台win...
叭叭叭
想请教一下，用了debian超频版，还能用原来的esim吗？

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

南凝呀 • 2023 年 12 月 06 日

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

## SQL数据分析

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

### 项目背景

### 数据说明

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

### 数据处理

#### 1、数据字段

数据集中已经给出对应的字段，这里无需再做修改。

#### 2、时间日期列处理

（1）修改字段类型

```sql
alter table bicycle modify datetime datetime;
```

（2）分别提取日期列和时间列

```sql
# 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'
```

### 数据分析

---

**在以下操作前需要先创建库，表，将数据导入到表中：**

And 1.查看所有数据库

```sql
show databases;
```

![image-20231206192732235.png](https://www.zmzaxg.top/usr/uploads/2023/12/3898537695.png)

And 2.创建train数据库并进入

```sql
create database train;
use train;
```

![image-20231206193049050.png](https://www.zmzaxg.top/usr/uploads/2023/12/4058953887.png)

And 3.创建bicycle数据表并查看是否完整

desc bicycle;
--查看
```

![image-20231206193633291.png](https://www.zmzaxg.top/usr/uploads/2023/12/2456640622.png)

![image-20231206193707952.png](https://www.zmzaxg.top/usr/uploads/2023/12/3029770297.png)

And 4.导入csv数据表内容，并查看数据导入是否出错

```sql
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;
--查看数据行，检查是否有导入错误
```

![image-20231206194245584.png](https://www.zmzaxg.top/usr/uploads/2023/12/4128781694.png)

![image-20231206194535648.png](https://www.zmzaxg.top/usr/uploads/2023/12/4073161203.png)

And 5.将datatime字段的类型更改成datetime时间串，并将日期和时间独立区分新添字段

```sql
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字段中
```

![image-20231206194949056.png](https://www.zmzaxg.top/usr/uploads/2023/12/2021980362.png)

![image-20231206195513179.png](https://www.zmzaxg.top/usr/uploads/2023/12/3707789156.png)

这里再 `select`检查一下表内数据是否正确完整

![image-20231206195920462.png](https://www.zmzaxg.top/usr/uploads/2023/12/1972029209.png)

---

考核条件如下：

\2. 对2022年共享单车使用次数进行分析，求注册用户和未注册用户的年使用数，结果存入视图table2。（0 / 5分）操作环境: mysql

\3. 进一步对2022年注册用户共享单车使用数量进行分析，计算该年的单月使用人数，结果存入视图table3。（0 / 5分）操作环境: mysql

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

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，将记录你的ip和窗口信息展示，继续评论表示您已同意该条款

ZDX随身wifi折腾记

随身wifi刷debian

centos安装谷歌Chrome浏览器

办公软件共享

关于搭建Hadoop集群

为站点添加支持加端口SSL访问，内外网均可

在Hadoop集群里运行第一个程序脚本

简单搭建属于自己的网页版Chat GPT

sqlmap工具渗透使用

MySql相关

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

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，将记录你的ip和窗口信息展示，继续评论表示您已同意该条款

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

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，将记录你的ip和窗口信息展示，继续评论表示您已同意该条款