SparkSQL实战小项目之热门商品top3（无需自定义UDAF函数） 一、说明及需求分析
参考我的老师的博客 SparkSQL实战小项目之热门商品top3_若兰幽竹-CSDN博客
二、准备测试数据参考我的老师的博客 SparkSQL实战小项目之热门商品top3_若兰幽竹-CSDN博客
三、思路分析- 测试数据分别位于user_visit_action、city_info、product_info表中，需多表联合查询，保留可用字段，筛选出可用数据。
- 统计某地区某城市某商品出现的次数即为某地区某城市某商品的点击量
- 统计出某地区、某商品总点击量（为后面求百分比做准备）
- 为了容易操作，将第2步的临时视图和第3步的临时视图联合在一起(为后面求百分比做准备）
- 求百分比，并拼接字符串，获得“城市备注”的数据
- 排序、展示结果
package com.sql
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object Top3 {
def main(args: Array[String]): Unit = {
val conf = new SparkConf().setAppName("Top3").setMaster("local[*]")
val spark = SparkSession.builder().enableHiveSupport().config(conf).getOrCreate()
//把三表连接起来
val df1 = spark.sql(
"""
|select
|area as `地区`,
|city_name as `城市`,
|user_id as `用户`,
|product_name as `商品`
|from
|(user_visit_action
|left join
|city_info
|on
|user_visit_action.city_id=city_info.city_id)
|left join product_info on user_visit_action.click_product_id=product_info.product_id
|""".stripMargin)
df1.createOrReplaceTempView("ACUP") //临时表1：ACUP----Area City User Product
df1.show()
//根据`地区`,`城市`,`商品分组并统计个数即点击量
val df2 = spark.sql(
"""
|select
|`地区`,`城市`,`商品`,count(*) as `点击量`
|from ACUP where `商品` is not null group by `地区`,`城市`,`商品`
|""".stripMargin)
df2.createOrReplaceTempView("ACUC")//临时表2：ACUC----Area City User Count
//根据`地区`,`商品`分组，统计出点击量的总和
val df3 = spark.sql(
"""
|select
|`地区`,`商品`,sum(`点击量`) as `总点击量`
|from ACUC
|group by `地区`,`商品`
|ORDER BY `地区`,`商品` DESC
|""".stripMargin)
df3.createOrReplaceTempView("APD")//临时表3：APD----Area Product Dianjiliang
//将ACUC临时表和APD临时表联接起来，达到创建有 “点击量” 和 “总点击量” 的列，为后面 “点击量”/“总点击量”做打算
val df4 = spark.sql(
"""
|select
|ACUC.`地区`,ACUC.`城市`,ACUC.`商品`,ACUC.`点击量`,APD.`总点击量`
|from
|(ACUC
|left join
|APD
| on ACUC.`商品`=APD.`商品`and ACUC.`地区`=APD.`地区`)
|""".stripMargin)
df4.createOrReplaceTempView("ACPDD")//临时表4：ACPDD----Area City Product Dianjiliang Dianjiliang总和
//计算每个地区每种商品的每个城市的百分比
val df5 = spark.sql(
"""
|select *,concat((round(`点击量`/`总点击量`*100,3)),'%') as `百分比` from ACPDD
|""".stripMargin)
df5.createOrReplaceTempView("ACPDDB")//临时表5：ACPDDB----Area City Product Dianjiliang Dianjiliang总和 Baifenbi
//合并城市和百分比这俩列，实现效果
val df6 = spark.sql(
"""
|select `地区`,`商品`,concat(`城市`,`百分比`) as `城市备注` from ACPDDB
|""".stripMargin)
df6.createOrReplaceTempView("APB")//临时表6：APB----Area Product Beizhu
//分组拼接城市备注这一列，达到效果
val df7 = spark.sql(
"""
|select `地区`,`商品`,concat_ws(',',collect_set(`城市备注`)) as `城市备注`
| from APB group by `地区`,`商品`
|""".stripMargin)
df7.createOrReplaceTempView("APBEND")//临时表7：APBEND----Area Product Beizhu END(最后)
//合并APD（里面有总点击量）和 APBEND（里面有城市备注聚合效果）
val df8 = spark.sql(
"""
|select APD.`地区`,APD.`商品`,APD.`总点击量`,APBEND.`城市备注`
|from (APD left join APBEND on APD.`商品`=APBEND.`商品`and APD.`地区`=APBEND.`地区`)
|""".stripMargin)
df8.createOrReplaceTempView("APDB")//临时表8：APDB----Area Product Dianjiliang总和 Beizhu
//分组排序取前三（包括重复）
spark.sql(
"""
|SELECT *
|FROM
|(SELECt *, rank() OVER (PARTITION BY `地区` ORDER BY `总点击量` DESC) rank FROM APDB ) as apd
|WHERe rank <= 3
|""".stripMargin).show
}
}
五、测试结果
六、注明
"城市备注"字段需要用自定义UDAF函数的请参考其他博客，
例如 SparkSQL实战小项目之热门商品top3_若兰幽竹-CSDN博客
代码中的sql操作有多处可以合并操作，可优化（没必要创这么多个临时视图）。
该博文是出于记录生活，大牛请绕行，
