下面我们以2014~2015和2015~2016赛季NBA常规赛为例,对窗口函数进行数据统计的应用实践。
1.NBA常规赛数据说明
NBA常规赛数据来源于stat-nba(http://www.stat-nba.com),通过数据查询器抓取了这两个赛季中30支NBA球队的场均数据,数据的信息如表5-3所示。
表5-3 数据的信息
球队的分区信息如表5-4所示。
表5-4 球队的分区信息
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指定的目录下:
(2)启动Spark-Shell,根据上述信息分别创建外部表。
在Spark-Shell中,查询Hive数据库NBA球队的场均数据表(PLAYOFFS_1416)、NBA球队的分区信息表(TEAMINFO)中的数据记录数:
(3)使用Spark SQL统计NBA常规赛各类查询数据。
1)场均数据和球队分区信息关联查询。
对过去的两个赛季,每支球队都具有自己的场均指标,我们可以根据胜负场计算出各支球队的胜率,为了更好地、清晰地进行指标分析,我们将场均数据和球队的分区信息进行关联,保存为一个dataFrame,代码如下:
给这个DataFrame注册临时表,后续的统计我们要运用这张临时表:
scala>dataFrame.registerTempTable("PG_DATA");
查询dataFrame的记录数:
dataFrame的记录数是60,即将NBA球队的场均数据表(PLAYOFFS_1416)、NBA球队的分区信息表(TEAMINFO)根据球队名称关联以后,共查询的记录数为60。dataFrame每一行的记录包括球队名称、赛季、投篮命中率、投篮命中数、投篮出手次数、三分球命中率、三分球命中数、三分球出手次数、罚球命中率、罚球命中次数、罚球出手次数、篮板、前场篮板、后场篮板、助攻、抢断、盖帽、失误、犯规、场均得分、场均失分、胜场、负场、胜率WR(即胜场/(胜场+负场),结果四舍五入后保留4位小数)、所属分赛区、所属联盟等信息。
我们查询近两个赛季金州勇士队的情况,从DataFrame注册的临时表PG_DATA中查询金州勇士队在NBA 14~15赛季、NBA 15~16赛季比赛记录的相关内容,包括:球队名称、赛季,胜率。
2)根据胜负场次统计两个赛季能够进入季后赛的球队:按照赛季、所属联盟分组,对胜场场次、投篮命中率进行降序排列,使用窗口函数row_number()查询出球队的排名情况。
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强查询结果如下:
NBA15~16赛季东部联盟前8强查询结果如下:
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球队排名涨幅差异记录查询:
将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)的球队,内容包括:球队所属联盟、球队名称。
从统计的结果可以看出,较上个赛季而言,本赛季东、西部进步最快的球队是西部的雷霆队和东部的热火队。
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)。
将dataRankDF注册临时表DATARANK。
查询投篮命中率最高的球队:
查询三分球命中率最高的球队:
查询防守能力最强的球队:
从这3个指标来看本赛季联盟投篮命中率、三分球命中率排名第一的均为勇士队,但防守能力处于中下水平,而防守能力排名第一的马刺队,在投篮命中率方面均为联盟第二,可见马刺队在攻防能力上均有较高的水准。
免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。