本文就adventure项目做一个总结,主要记录了,对项目需求分析,需求建模以及实现过程,还有hive SQL,shell脚本和python代码实现自动ETL过程,最后用power bi实现可视化dashboard,具体体现了个人对项目分析的思路和一些想法。
以下是dashboard部分内容展示:
本文目录:
1.业务分析报告制作
- 业务需求分析
- pyhton代码实现
- powerbi分析报告制作
2.数据仓库实现可视化看板
- 业务需求分析
- python代码实现
- 查询优化
- 自动化ETL和定时任务实现
一,业务分析报告制作
1.1业务需求分析
通过数据库可视化软件navicat观察相关表的字段信息,并制作数据字典便于分析
根据业务实际情况制定分析框架和分析指标,主要体现adventure11月自行车产品的销售表现,具体如下:
通过对数据字典和分析框架之间的逻辑梳理,并整理出分析思路具体如下:
1.2.pyhton代码实现
分析思路和分析指标已建立,本文用jupyter实现python代码
1.2.1整体销售表现
导入聚合表,筛选出自行车的销售记录
处理缺失值和日期字段,筛选出1至11月份销售记录
汇总1至11月销量和销售额
计算1-11月自行车的月销量和月销售额的环比
将表gather_territory_diff_lyh存储到数据库中
1.2.2区域销售表现
计算区域11月销量和销售额环比
计算11月销量TOP10城市的销量和销售额环比
1.2.3产品销售表现
1—11月自行车各类别的销量占比情况呈现
在每个类别中,求1—11月各产品型号在类别中的销量占比情况
- 公路自行车中,产品型号占比
- 山地自行车中,产品型号占比
- 旅游自行车中,产品型号占比
- 存储数据
求每个产品型号11月销量环比和1—11月的累计销量
1.2.4热销产品销售表现
11月TOP10产品销量榜
11月TOP10销量增速榜(环比)
1.2.5用户行为分析
导入数据
用户年龄分析
连接聚合表和用户信息表,并删除缺失值和得到用户年龄
年龄分组[30,35,40,45,50,60,65]和['<=29', '30-39', '>=40']
用户性别分析
1.3powerbi分析报告制作
通过pyhton代码将分析指标所需数据表存储在数据仓库中,然后通过powerbi连接mysql数据库制作可视化报告,报告部分内容如下:
二,数据仓库实现可视化看板
业务分析报告已整理完成,但因业务需要,需要每天更新可视化看板,因此需要实现自动ETL更新数据仓库数据,从而更新每日Dashboard
2.1需求分析
Dashboard可视化需要如下数据表:
根据数据字典观察可知上述表生成逻辑如下:
输入与输出如下:
2.2python代码实现
代码编写思维导图如下:
代码附件:
2.2查询优化
数据量较大,查询读取存储数据时间较长,因此需要对代码进行优化,优化主要分两方面:
- 在mysql追加索引优化查询速度
- 在python读取sql和存储至sql增添多进程
在mysql追加索引优化查询速度
给order_by_day_lyh表增加一个index_date索引,长度为7,将日期列字段前7个字符作为索引,这样查询时,就不用遍历整个数据表
在python读取sql和存储至sql增添多进程
多进程代码思维导图:
多进程代码附件:
2.4自动化ETL和定时任务实现
主要任务:
- 定时执行命令
- 能够跳出命令窗口输入 python xxx.py 文件使其运行
- 将这个文件挂在后台,等时间一到,自动执行
为了实现功能,前两个可以通过python模块库实现:
- 定时执行模块:import schedule
- 与命令窗口交互模块: import os 的 os.system()
- 第三个挂在后台功能则是利用linux系统自带的 & 挂后台。
代码思维导图如下: