目录

1. 背景与项目目标

1.1 背景描述

伦敦作为全球重要的大都市之一，交通拥堵和环境污染问题日益严重。为应对这些挑战，伦敦交通局推出了自行车租赁计划，旨在提供便捷、环保的交通解决方案，同时促进市民的健康生活方式

1.2 项目目标设定

基于Hadoop和Hive实现自行车租赁数据统计分析，构建自行车租赁数据分析报表：
在这里插入图片描述

1.3 项目需求

1.3.1 日出行次数统计表格

通过统计每天的出行次数，我们可以分析出行量的日常波动和潜在的周期性变化，帮助理解市民的出行习惯和租赁需求的季节性变化

1.3.2 平均出行时间表格

分析不同日期的平均出行时间可以揭示出行时间的变化趋势，例如工作日和周末的对比，帮助优化服务响应时间和自行车配备

1.3.3 热门起始站点表格

识别和统计最常用的起始站点可以帮助优化自行车的调度和站点布局，提高服务效率和用户体验

1.3.4 热门终点站点表格

分析最常用的终点站点可以帮助理解市民的出行目的和流量分布，为站点资源分配和交通规划提供依据

1.3.5 高频使用自行车表格

跟踪和分析高频使用的自行车可以帮助优化自行车的维护计划和替换策略，确保自行车的高效使用和服务寿命

1.3.6 出行时间段分布表格

分析出行时间段的分布可以帮助预测高峰时段和低谷时段，指导调整服务策略和市场推广活动

1.3.7 周末 vs 工作日出行模式表格

比较工作日和周末的出行模式可以帮助理解市民的生活方式和出行习惯，为服务优化和政策制定提供数据支持

2. 数据收集与准备

2.1 数据集简介

伦敦交通局的自行车租赁计划为市民提供了一种环保、便捷的公共交通选择，促进了城市的可持续发展和市民的健康生活方式。2023年8月份的数据集记录了在伦敦各个自行车租赁站点租借和归还自行车的详细信息

2.2 数据来源

数据来源链接: 伦敦共享单车数据集.

在本项目中，数据的来源是2023年8月份伦敦交通局（TfL）自行车租赁系统的详细记录。具体来说，数据来自于伦敦市内各个自行车租赁站点的租借和归还信息，包括但不限于以下几个关键属性：

在这里插入图片描述

2.3 加载数据

1、创建库表

-- 如果数据库已存在就删除
drop database if exists bike_msg cascade;

-- 创建数据库
CREATE database bike_msg;

-- 选择数据库
use bike_msg;

--如果表已存在就删除
drop table if exists bike_msg.BikeRentals;

-- 建表
create table BikeRentals(
    TripID bigint COMMENT '每次出行的唯一标识符 (Trip ID)',
    StartDate string COMMENT '出行开始的日期和时间',
    StartStationNumber string COMMENT '起始站的标识符',
    StartStation_1 string COMMENT '起始站的名称',
    StartStation_2 string COMMENT '起始站的名称',
    EndDate string COMMENT '出行结束的日期和时间',
    EndStationNumber string COMMENT '终点站的标识符',
    EndStation_1 string COMMENT '终点站的名称',
    EndStation_2 string COMMENT '终点站的名称',
    BikeNumber string COMMENT '所使用自行车的唯一标识符',
    BikeModel string COMMENT '所使用自行车的型号',
    TotalDuration string COMMENT '出行的总时间 (时分秒)',
    TotalDurationMs BIGINT COMMENT '出行的总时间 (毫秒)'
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
TBLPROPERTIES ("skip.header.line.count"="1");

2、数据导入

# 切换工作目录
cd /home/hadoop

# 在HDFS系统中创建/chatdemo/data目录
hadoop fs -mkdir -p /bikedemo/data

# 将chat_data-30W.csv文件从Linux上传到HDFS系统中
hadoop fs -put LondonBikeJourneyAug2023.csv /bikedemo/data

在DataGrip中执行以下命令：

-- 从HDFS系统中加载数据到Hive表
load data inpath '/bikedemo/data/LondonBikeJourneyAug2023.csv' into table BikeRentals;

-- 验证数据加载
select * from BikeRentals tablesample ( 1 percent );

-- 验证表中的数据条数
SELECT COUNT(*) from BikeRentals tms ;

结果展示：在这里插入图片描述
共有776527条数据

2.4 ETL数据清洗与转换

1、原始数据存在的问题
问题1：需求中，需要统计每天、每个小时的出行量，但是数据中没有天和小时字段，只有整体时间字段，不好处理
2、数据清洗需求
需求1：通过时间字段构建天和小时字段
需求2：将ETL以后的结果保存到一张新的Hive表中

清洗过程：

-- 创建存储清洗后数据的表
create table BikeRentals_etl(
    TripID bigint COMMENT '每次出行的唯一标识符 (Trip ID)',
    StartDate string COMMENT '出行开始的日期和时间',
    StartStationNumber string COMMENT '起始站的标识符',
    StartStation_1 string COMMENT '起始站的名称',
    StartStation_2 string COMMENT '起始站的名称',
    EndDate string COMMENT '出行结束的日期和时间',
    EndStationNumber string COMMENT '终点站的标识符',
    EndStation_1 string COMMENT '终点站的名称',
    EndStation_2 string COMMENT '终点站的名称',
    BikeNumber string COMMENT '所使用自行车的唯一标识符',
    BikeModel string COMMENT '所使用自行车的型号',
    TotalDuration string COMMENT '出行的总时间 (时分秒)',
    TotalDurationMs BIGINT COMMENT '出行的总时间 (毫秒)',
    start_day string comment '出行时间（日）',
    start_hour string comment '出行时间（时）'
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
TBLPROPERTIES ("skip.header.line.count"="1");

-- 按照需求对原始数据表中的数据进行过滤，然后插入新建的表中
INSERT OVERWRITE TABLE bike_msg.Bikerentals_etl
SELECT
    *,
    DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(StartDate, 'M/d/yyyy H:mm'))) AS start_day,
    HOUR(FROM_UNIXTIME(UNIX_TIMESTAMP(StartDate, 'M/d/yyyy H:mm'))) AS start_hour
FROM bike_msg.BikeRentals;

清洗后的数据：
在这里插入图片描述

3. 需求表格制作

3.1 每日出行次数统计表格

-- 统计每日出行次数
create table DailyTripCounts comment '每日出行次数' as
SELECT
    start_day,
    COUNT(*) AS daily_trip_count
FROM
    BikeRentals_etl
GROUP BY
    start_day
ORDER BY
    start_day;

3.2 每日平均出行时间表格

-- 统计每日平均出行时间
CREATE table AverageTripDuration  comment '每日平均出行时间' AS
SELECT
    start_day,
     ROUND(AVG(TotalDurationMs / 60000), 2) AS average_trip_duration_minutes
FROM
    BikeRentals_etl
GROUP BY
    start_day
ORDER BY
    start_day;
select *
from AverageTripDuration;

3.3 热门起始站点表格

-- 统计热门起始站点

CREATE table PopularStartStations comment '热门起始站点' AS
SELECT
    StartStation_2,
    COUNT(*) AS departure_count
FROM
    BikeRentals_etl
GROUP BY
    StartStation_2
ORDER BY
    departure_count DESC;

3.4 热门终点站点表格

-- 统计热门终点站点
CREATE table PopularEndStations comment '热门终点站点' AS
SELECT
    EndStation_2,
    COUNT(*) AS arrival_count
FROM
    BikeRentals
GROUP BY
    EndStation_2
ORDER BY
    arrival_count DESC;

3.5 高频使用自行车表格

--统计识别最受欢迎的自行车
CREATE table HighUsageBikes comment '最受欢迎的自行车' AS
SELECT
    BikeNumber,
    usage_count
FROM (
    SELECT
        BikeNumber,
        COUNT(*) AS usage_count,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS row_num
    FROM
        BikeRentals_etl
    GROUP BY
        BikeNumber
) AS ranked_bikes
WHERE
    row_num > 2 
    AND row_num <= 12
ORDER BY
    usage_count DESC;

3.6 出行时间段分布表格

-- 计算出行时间段分布
CREATE table TripTimeDistribution comment '出行时间段分布' AS
SELECT
    start_day,
    CASE
        WHEN start_hour >= 0 AND start_hour < 6 THEN '凌晨'
        WHEN start_hour >= 6 AND start_hour < 12 THEN '上午'
        WHEN start_hour >= 12 AND start_hour < 18 THEN '下午'
        ELSE '晚上'
    END AS time_period,
    COUNT(*) AS trip_count
FROM
    BikeRentals_etl
GROUP BY
    start_day,
    CASE
        WHEN start_hour >= 0 AND start_hour < 6 THEN '凌晨'
        WHEN start_hour >= 6 AND start_hour < 12 THEN '上午'
        WHEN start_hour >= 12 AND start_hour < 18 THEN '下午'
        ELSE '晚上'
    END;

3.7 周末 vs 工作日出行模式表格

CREATE table WeekdayWeekendTripPattern comment '周末 vs 工作日出行模式' AS
SELECT
    weekday_weekend,
    COUNT(*) AS trip_count,
    ROUND(AVG(TotalDurationMs / 60000), 2) AS avg_trip_duration_minutes
FROM (
    SELECT
        CASE
            WHEN DAYOFWEEK(start_day) >= 2 AND DAYOFWEEK(start_day) <= 6 THEN '工作日'
            ELSE '周末'
        END AS weekday_weekend,
        TotalDurationMs
    FROM
        BikeRentals_etl
) AS trips_with_weekday_weekend
GROUP BY
    weekday_weekend;

4. FineBI可视化呈现和分析

4.1 2023年八月伦敦共享单车数据分析报表

在这里插入图片描述

4.2 报表各组件

4.2.1 2023年八月伦敦每日共享单车出行次数变化（折线图）

在这里插入图片描述
结果分析：可以看出8.5这天的共享单车出行次数是最低的，且整个月的共享单车出现次数呈波动变化，最低值也不低于10000次，最高值不高于35000次

4.2.2 每日平均出行时间（散点图）

在这里插入图片描述
结果分析：整个月份的每日平均出行时间分布在20分钟~40分钟之间，分布不规律，整体偏向在20分钟到30分钟之间，且每日平均出行时间和每日出行次数不存在明显的正相关性和负相关性，

4.2.3 热门起始站点（文字云）

在这里插入图片描述
结果分析：Hyde Park（海德公园）是最为热门的起始地点，其次为Marylebone，Westminster等

4.2.4 热门终点站点（气泡图）

在这里插入图片描述
结果分析：和热门的起始地点相同，Hyde Park（海德公园）也为最为热门的终点站点，其次为Marylebone，Westminster等

4.2.5 最受欢迎的共享单车型号TOP10（雷达图）

在这里插入图片描述
结果分析：最受欢迎的自行车型号是58819，它的使用次数达到208次，位居榜首，但top10成员之间的差距不会特别大，第一受欢迎的自行车型号的骑行次数和第十受欢迎的自行车型号的骑行次数的差异为26（次）

4.2.6 每日出行次数最多的时间段分布（圆环图）

在这里插入图片描述
结果分析：整个月份有28八天的每日出行次数最多的时间段都为下午，可见，大众更喜欢下午出行，8.8和8.31出行次数最多的时间段则为上午，8.2出行次数最多的时间段则为晚上

4.2.7 工作日和周末共享单车平均使用时间对比（玫瑰图）

在这里插入图片描述
结果分析：工作日的共享单车平均使用时间（24.34m）要小于周末共享单车平均使用时间（29.99m），人们在周末使用共享单车的时间更长

备注：如有错误，还望多多指正，共同学习交流进步

# 数据分析 # 数据挖掘 # hdfs # hadoop # hive # finebi # sql
Logo
腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

终极指南：Flink SQL连接器版本管理从混乱到有序的升级之路

Apache Flink作为流处理领域的佼佼者，其SQL连接器的版本管理一直是开发者面临的核心挑战。本文将系统讲解Flink SQL连接器版本管理的最佳实践，帮助你轻松应对版本兼容性问题，实现从混乱到有序的升级之旅。## 连接器版本管理的常见痛点 😫在Flink应用开发中，连接器版本管理常常让开发者头疼不已。不同版本的连接器可能导致各种兼容性问题，例如API变更、功能差异甚至运行时错误。

avatar 腾讯云开发者社区

Elasticsearch复杂数据类型终极指南：从入门到精通

Elasticsearch作为功能强大的搜索引擎，支持多种复杂数据类型，让开发者能够灵活处理各种结构化和非结构化数据。本文将带你全面了解Elasticsearch中的复杂数据类型，从基础概念到实际应用，助你轻松掌握数据建模的核心技巧。## 内部对象：构建层级化数据结构在Elasticsearch中，对象类型（Object）是最基础的复杂数据类型之一，用于表示具有嵌套关系的数据。例如，我们可

avatar 腾讯云开发者社区

如何快速搭建Neon无服务器PostgreSQL：面向初学者的完整指南

Neon是一款革命性的无服务器PostgreSQL解决方案，它通过分离存储和计算层，实现了自动扩缩容、类代码式数据库分支以及零级扩展能力。本指南将帮助你从零开始搭建Neon开发环境，体验这款创新数据库的强大功能。## 准备工作：环境要求与依赖项在开始搭建Neon环境前，请确保你的系统满足以下要求：- Linux操作系统（推荐Ubuntu 20.04+或Debian 11+）- Git

avatar 腾讯云开发者社区