一文讲通SQL数据分析

数据集介绍：

用于数据分析和机器学习的终极足球数据库。

+25,000场比赛

+10,000名球员

11个欧洲国家和他们的主要冠军

2008年至2016年的赛季

球员和球队的属性来源于EA体育的FIFA游戏系列，包括每周的更新。

球队阵容阵型（X，Y坐标）。

来自多达10个供应商的投注赔率

10,000场比赛的详细比赛事件（进球类型、控球、角球、传球、犯规、牌等...）。

*2016年10月16日。新的表格包含了来自FIFA的球队属性!

原始数据来源：

你可以很容易地找到有关足球比赛的数据，但它们通常分散在不同的网站上。为了让你的分析工作更轻松，我们进行了彻底的数据收集和处理。我必须坚持，你不要对这些数据进行任何商业用途。数据的来源是：

http://football-data.mx-api.enetscores.com/：比分、阵容、球队阵型和事件

http://www.football-data.co.uk/：投注赔率

http://sofifa.com/：来自EA Sports FIFA游戏的球员和球队属性。FIFA系列和所有FIFA资产都是EA体育的数据财产。

当你稍微浏览一下数据库，你会注意到球员和比赛的外键与原始数据源相同。我们把这些外键称为 "api_id"。

完善数据库：

你会注意到阵容中缺少一些球员（NULL值）。这是因为我没能从FIFA中获取他们的属性。随着抓取算法的改进，这个问题将逐步得到解决。

数据集还将扩大到包括国际比赛、国家杯赛、冠军联赛和欧洲联赛。如果你在寻找一个特定的比赛，请告诉我们。

如果你想帮助改进这个数据集，请与我联系。

https://github.com/hugomathien/football-data-collection/tree/master/footballData

对有兴趣使用爬虫的人的重要提示：自从我第一次编写爬虫脚本（python版本）以来，sofifa.com似乎改变了它的设计，随之而来的是对脚本的新要求。现有的抓取球员的脚本在我更新之前将无法工作。

探索数据:

现在是有趣的是，你可以用这个数据集做很多事情。我将在这里提供一些想法给你，但请看一下内核，并自己尝试一下!

一、菠菜

最大的应用显然是预测比赛的结果。菠菜公司使用3个等级（主胜、平局、客胜）。他们大约有53%的预测是正确的。这也是我到目前为止使用自己的SVM所取得的成绩。虽然对于这样一个随机的体育比赛来说，这听起来很高，但你必须要知道这其中主队就赢了大约46%。所以基本程序（不断预测主队获胜）确实有46%的精度。

二、概率与赔率

当运行像SVM这样的多类分类器时，你也可以输出一个概率估计，并将其与菠菜赔率进行比较。看看你的方差与赔率，看看你对哪些比赛的预测跟菠菜不同。

三、探索和可视化特征

通过访问球员和球队的属性、球队阵型和比赛中的事件，你应该能够对精彩的比赛产生一些有趣的洞察力。谁知道呢，瓜迪奥拉本人可能会在某一天雇佣你们中的一个人。

用Navicat打开database.sqlite查看：

可以看到数据库中有大量球员和球队数据。

下面我们就用SQL来简单做一下数据分析

使用SQL语言进行数据分析，这应该是每个数据科学家的必备工具，既可以获取数据，又可以作为高级数据分析的简单工具，更加有趣。SQL背后的逻辑与其他用于数据分析的工具或语言（excel、Pandas）非常相似，对于那些习惯于处理数据的人来说，应该是非常直观的。

SQL是一种概念性语言，用于处理存储在数据库中的数据。具体到在本文的案例中，就是用SQL实现SQLite数据库数据的读取。大多数SQL语言共享本文档中的所有功能，差异通常体现在性能和先进的分析功能上（当然还有价格）。最终，我们将使用SQL语言来编写查询语句，从数据库中提取数据，对其进行操作、排序等操作。

数据库最重要的组成部分是它的表，那是所有数据存储的地方。通常情况下，数据会被分到许多表中，而不是全部存储在一个地方（所以正确设计数据结构是非常重要的）。本文的大部分内容将涉及如何处理表的问题。除了表之外，还有一些非常有用的概念/功能，我们在此不作介绍。

• 创建表

• 在数据库中插入/更新数据。

• 函数 - 获得一个输入值，并返回对该值的操作（例如，删除空白的函数）

#Improts 
import numpy as np # linear algebraimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)import sqlite3import matplotlib.pyplot as plt
# Input data files are available in the "../input/" directory.# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory
path = r"C:/Users/Administrator/Desktop/kaggle/"  #Insert path heredatabase = path + 'European Soccer Database.sqlite'

首先，我们将创建与数据库的连接，并查看我们有哪些表

查询的基本结构非常简单。你在SELECT后面定义你想看到的东西，*表示所有可能的列，在FROM后面选择表，在WHERE后面添加你想从表中使用的数据的条件。

结构和顺序很重要，而空格、新行、大写字母和缩进是为了使代码更容易阅读。​​​​​​​

conn = sqlite3.connect(database)
tables = pd.read_sql("""SELECT *                        FROM sqlite_master                        WHERE type='table';""", conn)tables

获取国家列表

这是最基本的查询。查询中唯一必须的部分是SELECT和FROM（假设你想从一个表中提取）。​​​​​​​

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

联赛和国家列表

当你想把两个表互相连接起来时，就可以使用JOIN。当你在每个表中都有一个共同的键时，它就能发挥作用。理解键的概念对于数据集（表）之间的连接（JOIN）至关重要。一个键可以唯一地识别一个表中的每一条记录（行）。它可以由一个值（单元格）组成--通常是ID，或者由表内唯一的值组合而成。

当在不同的表中进行连接时，你必须：

决定使用何种类型的连接。最常见的是：

• (INNER) JOIN - 在两个表中只保留符合条件的记录（在ON之后），两个表中不符合条件的记录不会出现在输出中。

• LEFT JOIN - 保留第一个（左）表的所有值，并联合右表的匹配行。来自右表的列，如果在左表中没有匹配的值，将被赋予NULL值。

指定用于连接这些表的共同值（在这种情况下是国家的ID）。

确保至少有一个值必须是其表中的一个键。在我们的例子中，它是Country.id。League.country_id不是唯一的，因为在同一个国家可能有不止一个联盟。

JOIN的错误使用，是编写复杂查询语句时最常见和最严重的错误。
​​​​​​​

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

球队列表

ORDER BY定义了输出的排序--升序或降序（DESC）。

LIMIT，限制输出中的行数--排序后的行数。​​​​​​​

teams = pd.read_sql("""SELECT *                        FROM Team                        ORDER BY team_long_name                        LIMIT 10;""", conn)teams

比赛列表

接下来我们将只显示我们感兴趣的列，所以我们将使用确切的名称来代替*。

一些单元格有相同的名称（Country.name,League.name）。我们将使用AS重新命名它们。

查询可以有很多的连接，原因是数据库被设计成星形结构--一个表（Match）包含所有的相关 "性能 "和指标，而所有的描述性信息存储在其他表中（国家、联赛、球队）。

请注意，Team表被连接了两次。这是一个棘手的问题，因为虽然我们使用相同的表名，但我们基本上带来了两个不同的副本（并使用AS重新命名）。原因是我们需要带来两个不同的值（home_team_api_id, away_team_api_id）的信息，如果我们把它们连接到同一个表中，就意味着它们是相互平等的。

你也会注意到，Team表是用左键连接的。原因是我更愿意在输出中保留这些比赛--即使其中一个球队由于某种原因在Team表中丢失。

ORDER定义了输出的顺序，位于LIMIT之前和WHERE之后。
​​​​​​​

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

让我们做一些基本的数据分析

在这里，我们开始在更多的聚合层面上看数据。我们不再看原始数据，而是开始把它分组到我们想要审视的不同层次。在这个例子中，我们将以之前的查询为基础，去掉比赛和日期信息，在国家-联赛-赛季层面上看。

我们将使用的功能是GROUP BY，它位于WHERE和ORDER之间。

一旦你选择了你想分析的级别，我们就可以把SELECT语句分成两个部分。

• 维度--这些是我们描述的值，也就是我们以后要分组的值。
   

• 度量 - 所有的度量都要用函数进行汇总。常见的函数有：sum(), count(), count(distinct ...), avg(), min(), max()

注意 - 在SELECT和GROUP BY中使用相同的维度是非常重要的。否则，输出结果可能是错误的。

另一个可以在分组后使用的功能是HAVING。这又增加了一层过滤数据的功能，这次是分组后的表的输出。很多时候，它是用来清理输出的。

​​​​​​​

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', '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

查询语句的运行顺序

现在我们已经熟悉了查询中使用的大部分功能，了解代码的运行顺序是非常重要的。

正如我们提到的，这是代码中出现的顺序：

SELECT

FROM

JOIN

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

你可以把它看成是一个两部分的过程。首先，在内存中创建一个新的临时表。

定义要使用哪些表，并连接它们（FROM + JOIN)

只保留适用于条件的记录（WHERE）。

按所需级别对数据进行分组（如果需要）（GROUP BY）。选择你想在新表中拥有的信息。它可以只有原始数据（如果没有分组），或者是维度的组合（来自分组），以及指标。

对新表的输出进行排序（ORDER BY）。

添加更多的条件，以过滤新创建的表（HAVING）。

限制行数--根据排序和过滤条件来削减行数（LIMIT）。

子查询和函数

使用子查询是SQL的一个重要工具，因为它允许以非常先进的方式操作数据，而不需要任何外部脚本，特别是当你的表以这样的方式断裂而不能直接连接时，这一点尤为重要。

假如试图将一个保存球员基本信息（姓名、身高、体重）的表和一个保存更多属性的表连接起来。问题是，虽然第一个表为每个球员保留了一条记录，但第二个表的关键是球员+赛季，所以如果我们做一个普通的连接，结果将是一个笛卡尔乘积，每个球员的基本细节在属性表中出现的次数将与这个球员出现的次数相同。其结果是，平均数会偏向于在属性表中出现多次的球员。

解决方案是使用一个子查询。我们需要将属性表分组到一个不同的键--只有球员级别（没有赛季）。当然，我们需要首先决定如何将所有的属性合并成一条记录。我使用的是平均数，但也可以使用最大值等。一旦两个表都有相同的键，我们就可以把它们连接在一起（把子查询看作是任何其他的表，只是临时性的），我们在连接后不会有重复的行。

最后，展示两个关于如何使用函数的例子。

条件函数是数据处理的一个重要工具。虽然IF语句在其他语言中非常流行，但SQLite不支持它，它是用CASE + WHEN + ELSE + END语句实现的。正如你所看到的，基于数据的输入，查询将返回不同的结果。

ROUND - 保留小数位数。每种SQL语言默认都有很多有用的函数。

​​​​​​​​​​​​​​

players_height = pd.read_sql("""SELECT CASE                                        WHEN ROUND(height)<165 then 165                                        WHEN ROUND(height)>195 then 195                                        ELSE ROUND(height)                                        END AS calc_height,                                         COUNT(height) AS distribution,                                         (avg(PA_Grouped.avg_overall_rating)) AS avg_overall_rating,                                        (avg(PA_Grouped.avg_potential)) AS avg_potential,                                        AVG(weight) AS avg_weight                             FROM PLAYER                            LEFT JOIN (SELECT Player_Attributes.player_api_id,                                         avg(Player_Attributes.overall_rating) AS avg_overall_rating,                                        avg(Player_Attributes.potential) AS avg_potential                                          FROM Player_Attributes                                        GROUP BY Player_Attributes.player_api_id)                                         AS PA_Grouped ON PLAYER.player_api_id = PA_Grouped.player_api_id                            GROUP BY calc_height                            ORDER BY calc_height                                ;""", conn)players_height