1 package com.bawei.review01
 2 
 3 import org.apache.spark.rdd.RDD
 4 import org.apache.spark.sql.{DataFrame, SparkSession}
 5 
 6 case class StuScore(id:Int,clazz:String,score:Int)
 7 object SparkSqlZK {
 8 
 9   def main(args: Array[String]): Unit = {
10     val spark = SparkSession.builder().appName("data-from-file").master("local").getOrCreate()
15 
16     val lineRDD: RDD[String] = spark.sparkContext.textFile("./coredata/stu.txt")
17 
18     val stuscoreRdd: RDD[StuScore] = lineRDD.map(line => {
19       val strings: Array[String] = line.split(" ")
20       StuScore(strings(0).toInt, strings(1), strings(2).toInt)
21     })
22     //把RDD转换成DataFrame[StuScore]
23     import spark.implicits._
24     val stuscoreDF: DataFrame = stuscoreRdd.toDF()
25     stuscoreDF.createOrReplaceTempView("stuscore")
26 
27     spark.sql("select *,substring(clazz,0,2) jie from stuscore").createOrReplaceTempView("stuscore2")
28     //stuscore2
29     //id clazz score jie
30 
31     //开窗函数 row_number() over（partition by jie order by score desc） rk
32 
33     spark.sql("select * from " +
34            " (select *,row_number() over(partition by jie order by score desc) rk from stuscore2)" +
35            " where rk<=3").show()
36   }
37 }