首页 理论教育 SparkSQL窗口函数NBA常规赛数据统计案例

SparkSQL窗口函数NBA常规赛数据统计案例

时间:2023-07-02 理论教育 版权反馈
【摘要】:下面我们以2014~2015和2015~2016赛季NBA常规赛为例,对窗口函数进行数据统计的应用实践。表5-4 球队的分区信息2.在Hive中加载NBA常规赛数据,并使用Spark SQL查询1)将NBA球队的场均数据信息、NBA球队分区信息数据文件上传到HDFS。3)使用Spark SQL统计NBA常规赛各类查询数据。⑥使用rank函数对排名涨幅差异DIFF字段再次进行涨幅幅度排名,分别查询出东部联盟、西部联盟排名涨幅第一的球队。

SparkSQL窗口函数NBA常规赛数据统计案例

下面我们以2014~2015和2015~2016赛季NBA常规赛为例,对窗口函数进行数据统计的应用实践。

1.NBA常规赛数据说明

NBA常规赛数据来源于stat-nba(http://www.stat-nba.com),通过数据查询器抓取了这两个赛季中30支NBA球队的场均数据,数据的信息如表5-3所示。

5-3 数据的信息

978-7-111-59197-9-Chapter05-60.jpg

球队的分区信息如表5-4所示。

5-4 球队的分区信息

978-7-111-59197-9-Chapter05-61.jpg

2.在Hive中加载NBA常规赛数据,并使用Spark SQL查询

1)将NBA球队的场均数据信息、NBA球队分区信息数据文件上传到HDFS。

2)在Hive中创建外部表:NBA球队的场均数据表(PLAYOFFS_1416)、NBA球队的分区信息表(TEAMINFO)。

3)使用Spark SQL统计NBA常规赛各类查询数据。

(1)将数据文件上传到HDFS。

篮球参考网站(www.basketball-reference.com)提供了NBA篮球历届比赛球员、球队、季节赛、领先者、分数、季后赛、篮球指数的详细数据。我们可以从网站上(http://www.basketball-reference.com/leagues/NBA_2017_totals.html)下载NBA篮球运动员历史数据。将数据文件上传到HDFS指定的目录下:

978-7-111-59197-9-Chapter05-62.jpg

(2)启动Spark-Shell,根据上述信息分别创建外部表。

978-7-111-59197-9-Chapter05-63.jpg

在Spark-Shell中,查询Hive数据库NBA球队的场均数据表(PLAYOFFS_1416)、NBA球队的分区信息表(TEAMINFO)中的数据记录数:

978-7-111-59197-9-Chapter05-64.jpg

978-7-111-59197-9-Chapter05-65.jpg

(3)使用Spark SQL统计NBA常规赛各类查询数据。

1)场均数据和球队分区信息关联查询。

对过去的两个赛季,每支球队都具有自己的场均指标,我们可以根据胜负场计算出各支球队的胜率,为了更好地、清晰地进行指标分析,我们将场均数据和球队的分区信息进行关联,保存为一个dataFrame,代码如下:

978-7-111-59197-9-Chapter05-66.jpg

给这个DataFrame注册临时表,后续的统计我们要运用这张临时表:

scala>dataFrame.registerTempTable("PG_DATA");

查询dataFrame的记录数:

978-7-111-59197-9-Chapter05-67.jpg

dataFrame的记录数是60,即将NBA球队的场均数据表(PLAYOFFS_1416)、NBA球队的分区信息表(TEAMINFO)根据球队名称关联以后,共查询的记录数为60。dataFrame每一行的记录包括球队名称、赛季、投篮命中率、投篮命中数、投篮出手次数、三分球命中率、三分球命中数、三分球出手次数、罚球命中率、罚球命中次数、罚球出手次数、篮板、前场篮板、后场篮板、助攻、抢断、盖帽、失误、犯规、场均得分、场均失分、胜场、负场、胜率WR(即胜场/(胜场+负场),结果四舍五入后保留4位小数)、所属分赛区、所属联盟等信息。

我们查询近两个赛季金州勇士队的情况,从DataFrame注册的临时表PG_DATA中查询金州勇士队在NBA 14~15赛季、NBA 15~16赛季比赛记录的相关内容,包括:球队名称、赛季,胜率。

978-7-111-59197-9-Chapter05-68.jpg

978-7-111-59197-9-Chapter05-69.jpg

2)根据胜负场次统计两个赛季能够进入季后赛的球队:按照赛季、所属联盟分组,对胜场场次、投篮命中率进行降序排列,使用窗口函数row_number()查询出球队的排名情况。

978-7-111-59197-9-Chapter05-70.jpg

playoffsRankDF查询结果每行记录的内容包括:球队名称、赛季、所属联盟、胜场、负场、胜率、投篮命中率、使用row_number()函数查询球队排名rn等信息。

将playoffsRankDF注册为临时表PLAYOFFSRANK:

scala>playoffsRankDF.register TempTable("PLAYOFFSRANK");

查询NBA 15-16赛季东西部联盟进入季后赛的球队信息的具体步骤如下:

①查询playoffsRankDF注册的临时表PLAYOFFSRANK;

②根据球队前8强排名rn、赛季SEASON、所属联盟UNION等条件进行查询。

③查询NBA 15~16赛季西部联盟前8强的球队信息,包括球队名称TEAM、胜场W、负场L、球队排名rn。

④查询NBA 15~16赛季东部联盟前8强的球队信息,包括球队名称TEAM、胜场W、负场L、球队排名rn。

在上面的结果中分别查找2015~2016赛季东西部进入季后赛的球队信息。(www.xing528.com)

NBA15~16赛季西部前8强查询结果如下:

978-7-111-59197-9-Chapter05-71.jpg

978-7-111-59197-9-Chapter05-72.jpg

NBA15~16赛季东部联盟前8强查询结果如下:

978-7-111-59197-9-Chapter05-73.jpg

3)针对最近两个赛季常规赛排名,我们可以根据排名的变化,找出东、西部进步幅度最大的球队。首先查找出每支球队的排名涨幅。

具体步骤如下:

①子查询T1:NBA 15~16赛季中PLAYOFFSRANK临时表中的记录,内容包括:球队名称、赛季、所属联盟、胜场、负场、胜率、投篮命中率、使用row_number()函数查询球队排名rn。

②子查询T2:NBA 14~15赛季中PLAYOFFSRANK临时表中的记录,内容包括:球队名称、赛季、所属联盟、胜场、负场、胜率、投篮命中率、使用row_number()函数查询球队排名rn。

③将子查询T1和子查询T2根据球队名称进行关联。

④从关联表中查询NBA球队排名涨幅差异记录结果,内容包括:NBA 14~15赛季球队名称、NBA 14~15赛季球队所属联盟、NBA 14~15赛季排名LAST_RANK、NBA 15~16赛季排名CURRENT_RANK、NBA 15~16赛季排名和NBA 14~15赛季排名相减即排名涨幅差异DIFF等信息。

⑤将查询结果注册临时表RANKDIFFDF。

⑥使用rank函数对排名涨幅差异DIFF字段再次进行涨幅幅度排名,分别查询出东部联盟、西部联盟排名涨幅第一的球队。

NBA球队排名涨幅差异记录查询:

978-7-111-59197-9-Chapter05-74.jpg

将rankDiffDF注册临时表RANKDIFFDF:

scala>rankDiffDF.registerTempTable("RANKDIFFDF");

使用rank函数对排名涨幅差异DIFF字段再次进行涨幅幅度排名:

①从临时表RANKDIFFDF进行子查询,根据球队所属联盟分组,按照排名涨幅差异DIFF字段进行降序排列,使用rank()窗口函数对排名涨幅情况进行排名,查询记录内容包括:NBA 14~15赛季球队名称、NBA 14~15赛季球队所属联盟、NBA 14~15赛季排名LAST_RANK、NBA 15~16赛季排名CURRENT_RANK、NBA 15~16赛季排名-NBA 14~15赛季排名(即排名涨幅差异DIFF)排名涨幅的排名rn。

②查询出东部联盟、西部联盟排名涨幅第一(t.rn=1)的球队,内容包括:球队所属联盟、球队名称。

978-7-111-59197-9-Chapter05-75.jpg

从统计的结果可以看出,较上个赛季而言,本赛季东、西部进步最快的球队是西部的雷霆队和东部的热火队。

4)分析统计出本赛季命中率、三分球命中率、防守能力最强的三支球队。

具体步骤如下:

①根据投篮命中率FG进行降序排序,使用窗口函数rank()对投篮命中率进行排名,作为FG_RANK字段。

②根据三分球命中率3P进行降序排序,使用窗口函数rank()对三分球命中率进行排名,作为3P_RANK字段。

③根据场均失分PTLS进行升序排序,使用窗口函数rank()对场均失分最少进行排名,作为PTLS_RANK字段。

④从NBA球队的场均数据表(PLAYOFFS_1416)中查询NBA 15~16赛季的记录,内容包括:球队名称、投篮命中率排名、三分球命中率排名、场均失分排名。

⑤查询投篮命中率最高的球队,即投篮命中率排名第一的球队(FG_RANK=1)。

⑥查询三分球命中率最高的球队,即三分球命中率排名第一的球队(3P_RANK=1)。

⑦查询防守能力最强的球队,即场均失分最少排名第一的球队(PTLS_RANK=1)。

978-7-111-59197-9-Chapter05-76.jpg

将dataRankDF注册临时表DATARANK。

978-7-111-59197-9-Chapter05-77.jpg

查询投篮命中率最高的球队:

978-7-111-59197-9-Chapter05-78.jpg

查询三分球命中率最高的球队:

978-7-111-59197-9-Chapter05-79.jpg

查询防守能力最强的球队:

978-7-111-59197-9-Chapter05-80.jpg

978-7-111-59197-9-Chapter05-81.jpg

从这3个指标来看本赛季联盟投篮命中率、三分球命中率排名第一的均为勇士队,但防守能力处于中下水平,而防守能力排名第一的马刺队,在投篮命中率方面均为联盟第二,可见马刺队在攻防能力上均有较高的水准。

免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。

我要反馈