SparkSQL案例练习
1、数据集介绍
注:数据分隔符:\t
2、需求
- 查询用户平均分
- 查询电影平均分
- 查询大于电影平均分的电影数量
- 查询高分电影(大于3分)中打分次数最多的用户,并求出此人打的平均分
- 查询每个用户的平均打分,最低打分,最高打分
- 查询被评分超过100次的电影的平均分,排名TOP10
3、实现
3.0、准备工作
代码铺垫
from pyspark.sql import SparkSession
import os
# 锁定远端环境, 确保环境统一
os.environ['SPARK_HOME'] = '/export/server/spark'
os.environ['PYSPARK_PYTHON'] = '/root/anaconda3/bin/python3'
os.environ['PYSPARK_DRIVER_PYTHON'] = '/root/anaconda3/bin/python3'
if __name__ == '__main__':
print("演示: Spark SQL的综合案例 ---> 电影评分案例")
# 1. 创建SparkSession对象
spark = SparkSession.builder.appName('movie_demo').master('local[*]').getOrCreate()
# 2- 读取外部文件数据
df = spark.read.csv(
path='file:///export/data/workspace/gz11_pyspark_parent/_03_spark_sql/data/u.data',
schema='userid string,movieid string,score int,datestr string',
sep='\t'
)
# 3- 处理相关的核心数据
df.printSchema()
df.show()
3.1、查询用户平均分
- SQL
spark.sql('''
select userid, round(avg(score),2) as score_avg from t group by userid
''').show()
- DSL
import pyspark.sql.functions as F
df.groupBy('userid').agg(
F.round(F.avg("score"),2).alias("score_avg")
).show()
3.2、查询电影平均分
- SQL
spark.sql('''
select movieid, round(avg(score),2) as score_avg from t group by movieid
''').show()
- DSL
import pyspark.sql.functions as F
df.groupBy('movieid').agg(
F.round(F.avg("score"),2).alias("score_avg")
).show()
3.3、查询大于电影平均分的电影数量
- SQL
spark.sql('''
select count(1) as cnt from t where score > (select avg(score) from t)
''').show()
3.4、查询高分电影(大于3分)中打分次数最多的用户,并求出此人打的平均分
- SQL
spark.sql("""
with high_movie as (
select movieid from t group by movieid having round(avg(score),2) > 3
),
movie_user as (
select userid from t where movieid in (select movieid from high_movie)
)
select userid, round(avg(score),2) as u_avg from t group by userid having userid in (select userid from movie_user group by userid)
""").show()
3.5、查询每个用户的平均打分,最低打分,最高打分
- SQL
spark.sql("""
select userid, round(avg(score), 2) as s_avg, round(min(score),2) as s_min, round(max(score), 2) as s_max from t group by userid
""").show()
3.6、查询被评分超过100次的电影的平均分,排名TOP10
- SQL
spark.sql("""
select
movieid,
count(1) as cnt,
round(avg(score),2) as s_svg
from t
group by movieid
having cnt > 100 order by s_svg limit 10
""").show()