sql题：每个网站访问top3 的用户

最新推荐文章于 2024-08-05 12:37:12 发布

keepHungery

最新推荐文章于 2024-08-05 12:37:12 发布

阅读量688

点赞数

分类专栏：自己总结的SQL题文章标签： sql 数据库

本文链接：https://blog.csdn.net/keepHungery/article/details/126271078

版权

自己总结的SQL题专栏收录该内容

2 篇文章 0 订阅

订阅专栏

代码

object SparkTop3Demo{
  def main(args: Array[String]): Unit = {
    val ssc: SparkSession = SparkSession.builder().appName("top3").master("local[*]").getOrCreate()

    val frame: DataFrame = ssc.read.option("delimiter", "|").csv("G:\\MySparl-Learn\\SparkSQL-learn\\inputData\\userVisit.csv").toDF("user","web");

    frame.createOrReplaceTempView("userVisit");
    val frame1: DataFrame = ssc.sql(
      """
        |select  user,web,count(*) ct  from userVisit   group by user,web
        |""".stripMargin)
    frame1.show()
    val frame2: DataFrame = ssc.sql(
      """
        |select
        |user,
        |web,
        |ct,
        |rk
        |from
        |(
        |select
        |user,
        |web,
        |ct,
        |rank() over(partition by web  order by ct desc ) rk
        |from
        |(
        |select  user,web,count(*) ct  from userVisit   group by user,web
        |) t1
        |)t2
        |where rk <=3
        |""".stripMargin)
  frame2.show();
    while (true){}
    ssc.close()
  }


}