Spark SQL 開窗函式

liwenjie001i發表於2020-03-23
SparkSQL函式
  1. 談到 SQL 的開窗函式，要說到HIVE了，因為這個是HIVE支援的特性，但是在Spark SQL中支援HIVE 的。那麼讓我們看一看開窗函式是什麼吧。
  2. 什麼是開窗函式呢 ?
  • 開窗函式也叫分析函式，有兩類：一類是聚合開窗函式，一類是排序開窗函式。
  • 開窗函式的呼叫格式為： 函式名(列名) over(partition by 列名 order by 列名)
  • 如果你沒有接觸過開窗函式上面這個格式你也許會有些疑惑，但你只要瞭解一些聚合函式，那麼理解開窗函式就非常容易了，我們知道聚合函式對一組值進行計算並返回單一的值，如sum(),count(),max(),min(),avg()等，這些函式常與group by 語句連用。但是一組資料只返回一組指是不能滿足需求的，如我們常想知道的各個地區的第一名是誰？ 各個班級的前幾名是誰？這個時候需要每一組返回多個值。 用開窗函式解決就非常方便。
  1. 首先我們提一個需求。下面是一張班級表 其中name為學生姓名，class 為班級班級，score 為成績，那麼我們提出一個需求：得出每個班級內成績最高的學生資訊。表名為 A。
    image.png
  • 我們先使用傳統的方法進行查詢,但是需要建立臨時表才可以所以效能也不夠好，那麼我們下面使用Spark SQL 中的開窗函式進行優化/
select  a.name, b.class, b.max from A  a 
     (select name,class,max(score) max from A group by class ) b
where  a.socre = b.score
  1. 開窗函式 （rank()、dense_rank()、row_number()）
  • 思想：簡單點就就在你查詢的結果上，直接多出來一個列(可以是聚合值或者是排序號，本題就是排序號)。
  • 先把sql 寫出來然後在在Spark SQL 中實現
  • 先開窗
select name,class,score ,rank() over(partition by class order by sorce)
  • 結果為下圖：如果多出來的一列就是我們開窗函式要做到的效果，那麼接下來在找到班級的分數最高的就太容易了
    image.png
  • 只需要加上一個查詢條件 就可以拿出想要的了。
select * from 
(select name,class,score ,rank() over(partition by class order by sorce)) as t
where t.rank = 1
  • Spark 程式碼如下：
object OverFunction extends App {

  val sparkConf = new SparkConf().setAppName("over").setMaster("local[*]")

  val spark = SparkSession.builder().config(sparkConf).getOrCreate()

  import spark.implicits._
  println("//***************  原始的班級表  ****************//")
  val scoreDF = spark.sparkContext.makeRDD(Array( Score("a", 1, 80),
    Score("b", 1, 78),
    Score("c", 1, 95),
    Score("d", 2, 74),
    Score("e", 2, 92),
    Score("f", 3, 99),
    Score("g", 3, 99),
    Score("h", 3, 45),
    Score("i", 3, 55),
    Score("j", 3, 78))).toDF("name","class","score")
  scoreDF.createOrReplaceTempView("score")
  scoreDF.show()

  println("//***************  求每個班最高成績學生的資訊  ***************/")
  println("    /*******  開窗函式的表  ********/")
  spark.sql("select name,class,score, rank() over(partition by class order by score desc) rank from score").show()

  println("    /*******  計算結果的表  *******")
  spark.sql("select * from " +
    "( select name,class,score,rank() over(partition by class order by score desc) rank from score) " +
    "as t " +
    "where t.rank=1").show()

  //spark.sql("select name,class,score,row_number() over(partition by class order by score desc) rank from score").show()

  println("/**************  求每個班最高成績學生的資訊（groupBY）  ***************/")

  spark.sql("select class, max(score) max from score group by class").show()

  spark.sql("select a.name, b.class, b.max from score a, " +
    "(select class, max(score) max from score group by class) as b " +
    "where a.score = b.max").show()

  spark.stop()
}
  1. 常用的函式
  • row_number()沒有重複值的排序（記錄相等也是不重複的），可以進行分頁使用
  • rank() 跳躍排序，有兩個第二名時後邊跟著的是第四名
  • dense_rank() 連續排序，有兩個第二名時仍然跟著第三名

相關文章