SparkSQL练习题-DF读取.log文件，算子聚合去重开窗TOPN

package day0614Work


import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.{DataFrame, SparkSession}
import org.apache.spark.sql.functions._

object workSQL {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession =
      SparkSession.builder()
        .appName("ReadSQL")
        .master("local")
        .getOrCreate()


    import spark.implicits._

    val df: DataFrame = spark.read.textFile("D:\\tmp\\work0614\\0614\\jd_visit.log")
      .map(v => {
        val p: Array[String] = v.split(" ")
        (p(0), p(1))
      })
      .toDF("user_id", "shop")

    df.printSchema()
    // 打印DataFrame数据
    df.show(false)

    //计算店铺UV
    val uvDF = df.groupBy("shop").agg(countDistinct("user_id").alias("uv"))
    // 打印结果
    uvDF.show(false)

    // 按照店铺分组并按照 visit_count 降序、user_id 升序排列
    val windowSpec = Window.partitionBy("shop").orderBy(desc("visit_count"), asc("user_id"))
    //如果visit_count重复，就按照user_id排序
    // 计算每个店铺每个访客的访问次数，并添加访问次数排名
    val rankedDF = df.groupBy("shop", "user_id")
      .agg(count("shop").alias("visit_count"))
      .withColumn("rank", rank().over(windowSpec))

    // 过滤出排名在前三的记录
    val top3DF = rankedDF.where(col("rank") <= 3)

    // 打印结果
    top3DF.show(false)
  }
}

root
 |-- user_id: string (nullable = true)
 |-- shop: string (nullable = true)

+-------+----+
|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   |
+-------+----+

+----+---+
|shop|uv |
+----+---+
|c   |3  |
|b   |4  |
|a   |4  |
+----+---+

23/06/15 09:24:22 INFO CodeGenerator: Code generated in 16.2444 ms
+----+-------+-----------+----+
|shop|user_id|visit_count|rank|
+----+-------+-----------+----+
|a   |u1     |3          |1   |
|a   |u2     |2          |2   |
|a   |u3     |1          |3   |
|b   |u1     |2          |1   |
|b   |u4     |2          |2   |
|b   |u2     |1          |3   |
|c   |u2     |2          |1   |
|c   |u3     |1          |2   |
|c   |u6     |1          |3   |
+----+-------+-----------+----+