- 談到 SQL 的開窗函數(shù)夺颤，要說到HIVE了佑附，因?yàn)檫@個(gè)是HIVE支持的特性瞭亮，但是在Spark SQL中支持HIVE 的垂券。那么讓我們看一看開窗函數(shù)是什么吧麻养。
- 什么是開窗函數(shù)呢 ?
- 開窗函數(shù)也叫分析函數(shù)嘴办，有兩類：一類是聚合開窗函數(shù)十艾，一類是排序開窗函數(shù)芬为。
- 開窗函數(shù)的調(diào)用格式為： 函數(shù)名(列名) over(partition by 列名 order by 列名)
- 如果你沒有接觸過開窗函數(shù)上面這個(gè)格式你也許會(huì)有些疑惑派近，但你只要了解一些聚合函數(shù)攀唯，那么理解開窗函數(shù)就非常容易了，我們知道聚合函數(shù)對(duì)一組值進(jìn)行計(jì)算并返回單一的值渴丸，如sum(),count(),max(),min(),avg()等革答，這些函數(shù)常與group by 語句連用。但是一組數(shù)據(jù)只返回一組指是不能滿足需求的曙强，如我們常想知道的各個(gè)地區(qū)的第一名是誰残拐？ 各個(gè)班級(jí)的前幾名是誰？這個(gè)時(shí)候需要每一組返回多個(gè)值碟嘴。 用開窗函數(shù)解決就非常方便溪食。
-
首先我們提一個(gè)需求。下面是一張班級(jí)表 其中name為學(xué)生姓名娜扇，class 為班級(jí)班級(jí)错沃，score 為成績栅组，那么我們提出一個(gè)需求：得出每個(gè)班級(jí)內(nèi)成績最高的學(xué)生信息。表名為 A枢析。
image.png
- 我們先使用傳統(tǒng)的方法進(jìn)行查找,但是需要?jiǎng)?chuàng)建臨時(shí)表才可以所以性能也不夠好玉掸，那么我們下面使用Spark SQL 中的開窗函數(shù)進(jìn)行優(yōu)化/
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
- 開窗函數(shù) （rank()、dense_rank()醒叁、row_number()）
- 思想：簡單點(diǎn)就就在你查詢的結(jié)果上司浪，直接多出來一個(gè)列(可以是聚合值或者是排序號(hào)，本題就是排序號(hào))把沼。
- 先把sql 寫出來然后在在Spark SQL 中實(shí)現(xiàn)
- 先開窗
select name,class,score ,rank() over(partition by class order by sorce)
-
結(jié)果為下圖：如果多出來的一列就是我們開窗函數(shù)要做到的效果啊易，那么接下來在找到班級(jí)的分?jǐn)?shù)最高的就太容易了
image.png - 只需要加上一個(gè)查詢條件 就可以拿出想要的了。
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("http://*************** 原始的班級(jí)表 ****************//")
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("http://*************** 求每個(gè)班最高成績學(xué)生的信息 ***************/")
println(" /******* 開窗函數(shù)的表 ********/")
spark.sql("select name,class,score, rank() over(partition by class order by score desc) rank from score").show()
println(" /******* 計(jì)算結(jié)果的表 *******")
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("/************** 求每個(gè)班最高成績學(xué)生的信息（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()
}
- 常用的函數(shù)
- row_number()沒有重復(fù)值的排序（記錄相等也是不重復(fù)的）饮睬，可以進(jìn)行分頁使用
- rank() 跳躍排序租谈，有兩個(gè)第二名時(shí)后邊跟著的是第四名
- dense_rank() 連續(xù)排序，有兩個(gè)第二名時(shí)仍然跟著第三名
