每日分享，一个不错的数据分析实战案例【数据分析可视化】MySQL+Python_python数据分析视频 百度网盘

最后

按照上面的过程，4个月的时间刚刚好。当然Java的体系是很庞大的，还有很多更高级的技能需要掌握，但不要着急，这些完全可以放到以后工作中边用别学。

学习编程就是一个由混沌到有序的过程，所以你在学习过程中，如果一时碰到理解不了的知识点，大可不必沮丧，更不要气馁，这都是正常的不能再正常的事情了，不过是“人同此心，心同此理”的暂时而已。

“道路是曲折的，前途是光明的！”

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

利用SQL和pandas对11支球队在7个赛季中的25979场比赛数据，分析各球队在每个赛季的主客场得分情况以及联赛积分情况。

PS: 项目本身提供的是sqlite文件，为了更贴近现实中的工作场景，我将其中的表都输出成csv文件，再利用Navicat for MySQL 导入到MySQL当中。（文末附资源链接）
本次项目同样在jupyter上运行。

导入模块

import pymysql
import pandas as pd
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei'] # 设置汉字字体,优先使用黑体
plt.rcParams['font.size'] = 12  # 设置字体大小
plt.rcParams['axes.unicode\_minus'] = False   # 设置正常显示负号

数据库中的表格：

conn = pymysql.connect(
    host = 'localhost',
    user = 'root',
    password = '',
    db = 'data',
    port = 3306
)
df = pd.read_sql("show tables",conn)
df

国家名单：

countries = pd.read_sql("""SELECT \*
 FROM Country;""", conn)
countries

将country表和league表连接起来

leagues = pd.read_sql("""SELECT \*
 FROM League
 JOIN Country ON Country.id = League.country\_id;""", conn)
leagues

按队名排序中的前十名

teams = pd.read_sql("""SELECT \*
 FROM Team
 ORDER BY team\_long\_name
 LIMIT 10;""", conn)
teams

输出spain主客队比赛的信息

detailed_matches = pd.read_sql("""SELECT Match.id, 
 Country.name AS country\_name, 
 League.name AS league\_name, 
 season, 
 stage, 
 date,
 HT.team\_long\_name AS home\_team,
 AT.team\_long\_name AS away\_team,
 home\_team\_goal, 
 away\_team\_goal 
 FROM `match`
 JOIN Country on Country.id = Match.country\_id
 JOIN League on League.id = Match.league\_id
 LEFT JOIN Team AS HT on HT.team\_api\_id = Match.home\_team\_api\_id
 LEFT JOIN Team AS AT on AT.team\_api\_id = Match.away\_team\_api\_id
 WHERE country.name = 'Spain'
 ORDER by date
 LIMIT 10;""", conn)
detailed_matches

统计各个国家的各个联赛的各个赛季中stage大于10的球队主客队平均得分，主客队平均分之和与差，以及总和

leages_by_season = pd.read_sql("""SELECT Country.name AS country\_name, 
 League.name AS league\_name, 
 season,
 count(distinct stage) AS number\_of\_stages,
 count(distinct HT.team\_long\_name) AS number\_of\_teams,
 avg(home\_team\_goal) AS avg\_home\_team\_scors, 
 avg(away\_team\_goal) AS avg\_away\_team\_goals, 
 avg(home\_team\_goal-away\_team\_goal) AS avg\_goal\_dif, 
 avg(home\_team\_goal+away\_team\_goal) AS avg\_goals, 
 sum(home\_team\_goal+away\_team\_goal) AS total\_goals 
 FROM `match`
 JOIN Country on Country.id = Match.country\_id
 JOIN League on League.id = Match.league\_id
 LEFT JOIN Team AS HT on HT.team\_api\_id = Match.home\_team\_api\_id
 LEFT JOIN Team AS AT on AT.team\_api\_id = Match.away\_team\_api\_id
 WHERE country.name in ('Spain', 'Germany', 'France', 'Italy', 'England')
 GROUP BY Country.name, League.name, season
 HAVING count(distinct stage) > 10
 ORDER BY Country.name, League.name, season DESC
 ;""", conn)
leages_by_season.head(10)

列出几支球队的各赛季平均得分趋势图

df = pd.DataFrame(index=np.sort(leages_by_season['season'].unique()), columns=leages_by_season['country\_name'].unique())

df.loc[:,'Germany'] = list(leages_by_season.loc[leages_by_season['country\_name']=='Germany','avg\_goals'])
df.loc[:,'Spain']   = list(leages_by_season.loc[leages_by_season['country\_name']=='Spain','avg\_goals'])
df.loc[:,'France']   = list(leages_by_season.loc[leages_by_season['country\_name']=='France','avg\_goals'])
df.loc[:,'Italy']   = list(leages_by_season.loc[leages_by_season['country\_name']=='Italy','avg\_goals'])
df.loc[:,'England']   = list(leages_by_season.loc[leages_by_season['country\_name']=='England','avg\_goals'])

df.plot(figsize=(12,5),title='各赛季平均得分趋势图')

列出几支球队的各赛季平均主客队分差趋势图

df = pd.DataFrame(index=np.sort(leages_by_season['season'].unique()), columns=leages_by_season['country\_name'].unique())

df.loc[:,'Germany'] = list(leages_by_season.loc[leages_by_season['country\_name']=='Germany','avg\_goal\_dif'])
df.loc[:,'Spain']   = list(leages_by_season.loc[leages_by_season['country\_name']=='Spain','avg\_goal\_dif'])
df.loc[:,'France']   = list(leages_by_season.loc[leages_by_season['country\_name']=='France','avg\_goal\_dif'])
df.loc[:,'Italy']   = list(leages_by_season.loc[leages_by_season['country\_name']=='Italy','avg\_goal\_dif'])
df.loc[:,'England']   = list(leages_by_season.loc[leages_by_season['country\_name']=='England','avg\_goal\_dif'])

df.plot(figsize=(12,5),title='各赛季平均主客队分差趋势图')

求各赛季各球队的积分（赢球得3分，平局得1分，输球不得分）

team_season_score = pd.read_sql("""SELECT 
 m.season,


### 最后

毕竟工作也这么久了 ，除了途虎一轮，也七七八八面试了不少大厂，像阿里、饿了么、美团、滴滴这些面试过程就不一一写在这篇文章上了。我会整理一份详细的面试过程及大家想知道的一些问题细节

### 美团面试经验
![美团面试](https://img-blog.csdnimg.cn/img_convert/e8ce3f1ec23c830c6aa12cd680bd8bd6.webp?x-oss-process=image/format,png)
字节面试经验
![字节面试](https://img-blog.csdnimg.cn/img_convert/79c2f751d0a8e97c68cb67b2ad25042b.webp?x-oss-process=image/format,png)
菜鸟面试经验
![菜鸟面试](https://img-blog.csdnimg.cn/img_convert/6920c28722658072004f2490fb8d24ff.webp?x-oss-process=image/format,png)
蚂蚁金服面试经验
![蚂蚁金服](https://img-blog.csdnimg.cn/img_convert/b6ec2eccfca61eb24d59fb4be9078e0a.webp?x-oss-process=image/format,png)
唯品会面试经验
![唯品会](https://img-blog.csdnimg.cn/img_convert/eca41b3ec0aa1bfe7a5a573b4410d861.webp?x-oss-process=image/format,png)

>因篇幅有限，图文无法详细发出


> **本文已被[CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)收录**

**[需要这份系统化的资料的朋友，可以点击这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**

有限，图文无法详细发出


> **本文已被[CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)收录**

**[需要这份系统化的资料的朋友，可以点击这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**