sparkSql-实例：分组TopN

实例：分组TopN
有50W个店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop。
数据集：topn.csv

user_id,shop
u1,a
u2,b
u1,b
u1,a
u3,c
u4,b
u1,a
u2,c
u5,b
u4,b
u6,c
u2,c
u1,b
u2,a
u2,a
u3,a
u5,a
u5,a
u5,a

需求：每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数。

思路

1.查询每个店铺被每个用户访问次数。

2.计算每个店铺被用户访问次数排名,有了第一步每个店铺下所被访问用户的访问量，我们想获取前三，毫无疑问，我们需要使用到开窗函数 rank。

3.取每个店铺排名前3的数据。

代码

package shangxuetang

import org.apache.spark.sql.SparkSession

object TopN {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("").master("local[*]").getOrCreate()
    val df = spark.read.option("header",true).csv("D:\IDEAProject\sparksql\src\main\resources\topn.csv")
    //df.show()
    df.createTempView("temp1")
    //1.查询每个店铺被每个用户访问次数
    spark
      .sql(
        """
          |select shop,user_id,count(*) as accesscount
          |from temp1
          |group by shop,user_id
          |order by shop,user_id
          |""".stripMargin).createTempView("temp2")

    //2.计算每个店铺被用户访问次数排名,有了第一步每个店铺下所被访问用户的访问量，我们想获取前三，毫无疑问，我们需要使用到开窗函数 rank。

    spark
      .sql(
        """
          |select shop,user_id,accesscount,
          |rank() over (partition by shop order by accesscount desc) as accessrank
          |from temp2
          |order by shop
          |""".stripMargin).createTempView("temp3")

    //3.取每个店铺排名前3的数据
    spark
      .sql(
        """
          |select shop,user_id,accesscount,
          |accessrank
          |from temp3
          |where accessrank <= 3
          |order by shop,accesscount desc,accessrank desc
          |""".stripMargin).show()
    spark.stop()
  }
}

结果

//第一步结果：
+----+-------+-----------+
|shop|user_id|accesscount|
+----+-------+-----------+
|   a|     u1|          3|
|   a|     u2|          2|
|   a|     u3|          1|
|   a|     u5|          3|
|   b|     u1|          2|
|   b|     u2|          1|
|   b|     u4|          2|
|   b|     u5|          1|
|   c|     u2|          2|
|   c|     u3|          1|
|   c|     u6|          1|
+----+-------+-----------+

//第二步结果：
+----+-------+-----------+----------+
|shop|user_id|accesscount|accessrank|
+----+-------+-----------+----------+
|   a|     u1|          3|         1|
|   a|     u5|          3|         1|
|   a|     u2|          2|         3|
|   a|     u3|          1|         4|
|   b|     u1|          2|         1|
|   b|     u4|          2|         1|
|   b|     u2|          1|         3|
|   b|     u5|          1|         3|
|   c|     u2|          2|         1|
|   c|     u3|          1|         2|
|   c|     u6|          1|         2|
+----+-------+-----------+----------+

//最终结果：
+----+-------+-----------+----------+
|shop|user_id|accesscount|accessrank|
+----+-------+-----------+----------+
|   a|     u1|          3|         1|
|   a|     u5|          3|         1|
|   a|     u2|          2|         3|
|   b|     u1|          2|         1|
|   b|     u4|          2|         1|
|   b|     u2|          1|         3|
|   b|     u5|          1|         3|
|   c|     u2|          2|         1|
|   c|     u3|          1|         2|
|   c|     u6|          1|         2|
+----+-------+-----------+----------+