摸鱼大数据——Spark SQL——Spark SQL的综合案例

本文链接：https://blog.youkuaiyun.com/weixin_65694308/article/details/140061469

1、常见DSL代码整理

分类	格式	含义	示例
API/方法	select	查询字段	select('id1', 'id2')
	where	对数据过滤	where('avg_score>3')
	groupBy	对数据分组	groupBy('userid')
	orderBy	对数据排序	orderBy('cnt', ascending=False)
	limit	取前几条数据	orderBy('cnt', ascending=False).limit(1)
	agg	聚合操作，里面可以写多个聚合表达式	agg(F.round(F.avg('score'), 2).alias('avg_score'))
	show	打印数据	init_df.show()
	printSchema	打印数据的schema信息，也就是元数据信息	init_df.printSchema()
	alias	对字段取别名	F.count('movieid').alias('cnt')
	join	关联2个DataFrame	etl_df.join(avg_score_dsl_df, 'movieid')
	withColumn	基于目前的数据产生一个新列	init_df.withColumn('word',F.explode(F.split('value', ' ')))
	dropDuplicates	删除重复数据	init_df.dropDuplicates(subset=["id","name"])
	dropna	删除缺失值	init_df.dropna(thresh=2,subset=["name","age","address"])
	fillna	替换缺失值	init_df.fillna(value={"id":111,"name":"未知姓名","age":100,"address":"北京"})
	first	取DataFrame中的第一行数据
	over	创建一个窗口列


窗口	partitionBy	对数据分区
	orderBy	对数据排序	orderBy(F.desc('pv'))


函数	row_number	行号。从1开始编号
	desc	降序排序
	avg	计算均值
	count	计数
	round	保留小数位
	col	将字段包装成Column对象，一般用于对新列的包装

 1- 什么使用使用select()，什么时候使用groupBy()+agg()/select()实现聚合?:如果不需要对数据分组，那么可以直接使用select()实现聚合；如果有分组操作，需要使用groupBy()+agg()/select()，推荐使用agg()
         
 2- first(): 如果某个DataFrame中只有一行数据，并且不使用join来对比数据，那么一般需要使用first()明确指定和第一行进行比较
     
 3- F.col(): 对于在计算过程中临时产生的字段，需要使用F.col()封装成Column对象,然后去使用

API/方法：是由DataFrame来调用
函数：需要先通过import pyspark.sql.functions as F导入，使用F调用。Spark SQL内置提供的函数Spark SQL, Built-in Functions
窗口：需要先通过from pyspark.sql import Window导入

2、电影分析案例

需求说明:

数据集的介绍:

数据说明 : userid,movieid,score,datestr

字段的分隔符号为: \t

需求分析:

需求一: 查询用户平均分

需求分析：

维度：用户指标：平均分
需求二: 查询每部电影的平均分
需求三: 查询大于平均分的电影的数量

需求分析：

1- 统计所有打分的平均分，这个结果就是一个数字

2- 统计每部电影各自的平均分

3- 查询大于平均分的电影的数量
需求四: 查询高分电影中(电影平均分大于3)打分次数最多的用户, 并且求出此人所有的打分记录中, 打的平均分是多少

需求分析：

1- 筛选出高分电影。统计每部电影的平均分，再过滤出>3分的电影信息

2- 从高分电影中，统计每个用户分别打了多少次分。再选择TOP1的用户

3- 统计该用户所有打分记录的平均分
需求五: 查询每个用户的平均打分, 最低打分, 最高打分（课后作业）
需求六: 查询被评分超过100次的电影的平均分排名 TOP10（课后作业）

一三四需求实现代码:

 # 导包
 import os
 from pyspark.sql import SparkSession,functions as F
 
 # 绑定指定的python解释器
 os.environ['SPARK_HOME'] = '/export/server/spark'
 os.environ['PYSPARK_PYTHON'] = '/root/anaconda3/bin/python3'
 os.environ['PYSPARK_DRIVER_PYTHON'] = '/root/anaconda3/bin/python3'
 
 
 def demo1_get_user_avg_score():
     # 方式1: SQL方式
     spark.sql(
         'select userid,round(avg(score),3) as user_avg_score from movie group by userid'
     ).show()
     # 方式2: DSL方式
     etldf.groupBy('userid').agg(
         F.round(F.avg('score'), 3).alias('user_avg_score')
     ).show()
 
 
 def demo2_get_lag_avg_movie_cnt():
     # 方式1: SQL
     spark.sql(
         """
         select count(1) as cnt from (
             select movieid,avg(score) as movie_avg_score from movie group by movieid
             having movie_avg_score > (select avg(score) as all_avg_score from movie)  
         ) t
         """
     ).show()
     # 方式2: DSL
     # col():把临时结果作为新列使用   first():取第一个值
     etldf.groupBy('movieid').agg(
         F.avg('score').alias('movie_avg_score')
     ).where(
         F.col('movie_avg_score') > etldf.select(F.avg('score').alias('all_avg_score')).first()['all_avg_score']
     ).agg(
         F.count('movieid').alias('cnt')
     ).show()
 
 
 def demo3_get_top1_user_avg_sql():
     # 方式1: SQL
     # ①先查询高分电影:
     spark.sql(
         "select movieid,avg(score) as movie_avg_score from movie group by movieid having movie_avg_score > 3"
     ).createTempView('hight_score_tb')
     # ②再求打分次数最多的用户(先不考虑并列,只取最大1个)
     spark.sql(
         "select userid,count(1) as cnt from hight_score_tb h join movie m on h.movieid = m.movieid group by userid order by cnt desc limit 1"
     ).createTempView('top1_user_tb')
     # ③最后求此人所有打分的平均分
     spark.sql(
         "select avg(score) as top1_user_avg from movie where userid = (select userid from top1_user_tb)"
     ).show()
 
 
 def demo3_get_top1_user_avg_dsl():
     # ①先查询高分电影:
     hight_score_df = etldf.groupBy('movieid').agg(
         F.avg('score').alias('movie_avg_score')
     ).where('movie_avg_score>3')
     # ②再求打分次数最多的用户(先不考虑并列,只取最大1个)
     top1_user_df = hight_score_df.join(etldf, on=hight_score_df['movieid'] == etldf['movieid']) \
         .groupBy('userid').agg(F.count('userid').alias('cnt')) \
         .orderBy('cnt', ascending=False).limit(1)
     # ③最后求此人所有打分的平均分
     etldf.where(
         etldf['userid'] == top1_user_df.first()['userid']
     ).agg(
         F.avg('score').alias('top1_user_avg')
     ).show()
 
 
 # 创建main函数
 if __name__ == '__main__':
     # 1.创建SparkContext对象
     spark = SparkSession.builder.appName('pyspark_demo').master('local[*]').getOrCreate()
 
     # 2.数据输入
     df = spark.read.csv(
         schema='userid string,movieid string,score int,datestr string',
         sep='\t',
         path='file:///export/data/spark_project/spark_sql/data/u.data'
     )
     print(df.count())
     # 3.数据处理(切分,转换,分组聚合)
     etldf = df.dropDuplicates().dropna()
     print(etldf.count())
     # 4.数据分析
     # 方便后续所有SQL方式使用,提前创建临时视图作为表
     etldf.createTempView('movie')
     # 需求1: 查询用户的平均分
     # demo1_get_user_avg_score()
 
     # 需求3: 查询大于平均分的电影的数量
     # demo2_get_lag_avg_movie_cnt()
 
     # 需求4: 查询高分电影(平均分>3)中,打分次数最多的用户,并求出此人所有打分的平均分
     # 方式1: SQL
     demo3_get_top1_user_avg_sql()
     # 方式2: DSL
     demo3_get_top1_user_avg_dsl()
 
     # 5.数据输出
 
     # 6.关闭资源
     spark.stop()

附录: 问题

可能出现的错误一：

 原因: 是使用withColumn产生新列，但是表达式中有聚合的操作。缺少groupBy调用

可能出现的错误二：

错误原因：DataFrame结果是单行的情况，列值获取错误

解决办法：

 将df_total_avg_score['total_avg_score']改成df_total_avg_score.first()['total_avg_score']

可能遇到的错误三：

原因：对于在计算过程中临时产生的字段，需要使用F.col封装成Column对象

解决办法：F.col('avg_score')