什么是窗口函数:
窗口函数解决了哪些问题:
假如有很多种不同类型的数据,如果我们想按照某个列进行分组,然后在对分组后的数据进行排序,在输出分组排序后的结果,那么依靠普通的函数是无法做到的,那么就应运而生了窗口函数,作用就是:先对数据分组,在对分组后的数据进行排序之类的操作,最后再把想要查询的列的结果查询出来。要注意窗口函数和聚合函数有着本质上的区别:聚合函数的最大的特点就是只能操作分组的字段,但是开窗函数可以操作所有的字段,并不受分区字段的限制
对sparkSql窗口函数的理解:
例子:
def test() = {
val sparkConf = new SparkConf().setMaster("local[2]")
val spark = SparkSession.builder().config(sparkConf).getOrCreate()
val sqlContext = spark.sqlContext
val data = Array(("lili", "ml", 90),
("lucy", "ml", 85),
("cherry", "ml", 80),
("terry", "ml", 85),
("tracy", "cs", 82),
("tony", "cs", 86),
("tom", "cs", 75))
val schemas = Seq("name", "subject", "score")
val df = spark.createDataFrame(data).toDF(schemas: _*)
df.createOrReplaceTempView("person_subject_score")
val sqltext = "select name, subject, score, rank() over (partition by subject order by score desc) as rank from person_subject_score";
val ret = sqlContext.sql(sqltext)
ret.show()
}
执行结果:
+------+-------+-----+----+
| name|subject|score|rank|
+------+-------+-----+----+
| tony| cs| 86| 1|
| tracy| cs| 82| 2|
| tom| cs| 75| 3|
| lili| ml| 90| 1|
| lucy| ml| 85| 2|
| terry| ml| 85| 2|
|cherry| ml| 80| 4|
+------+-------+-----+----+
窗口函数执行流程的理解:
//一定要知道,窗口函数的执行,是先分组,后排序,最后查询
select name, subject, score, rank() over (partition by subject order by score desc) as rank from person_subject_score
//不要被代码的书写顺序欺骗了,在SQL中代码的书写顺序和执行顺序并不是一样的。
//有开窗函数的SQL怎么执行:
//1.首先我们要知道,rank() 是一个窗口函数,但是底层是不知道rank()是不是窗口函数的,那么我们就需要加 //上一个 字段 来告底层,我这个rank()是窗口函数,这个字段就是 over()
//2.over() 字段的括号中的内容的执行顺序:
//首先我们要知道的是:我们使用窗口函数无非就是想分组,然后对分组后的数据进行对应的操作,所以我们应该执 //行的操作就是先分组,根据什么字段进行分组
//分组完成后,剩下的一般就是一些排序的操作 ,根据什么字段进行排序
//3.分组 排序 执行完毕之后,那么剩下的就是查询操作了。想要查询哪些列,最后在写上我们要查询的字段来自 哪个表格
窗口函数的更加细致的划分:
over表示这是个窗口函数
partition by对应的就是分组,即按照什么列分组
order by对应的是排序,按什么列排序
spark中的window_func包括下面三类:
1.排名函数(ranking function) 包括rank,dense_rank, row_number,percent_rank, ntile等,后面我们结合例子来看。
2.分析函数 (analytic functions) 包括cume_dist,lag等。
3.聚合函数(aggregate functions),就是我们常用的max, min, sum, avg等。
三个常用的排序的开窗函数的特点:
rank() : 如果相同,则并列,但序号上不连续
row_number() : 如果相同,则依然顺序递增
dense_rank() : 如果相同,则并列,但是序号上依然连续
具体区别看下文
+------+-------+-----+----+
| name|subject|score|rank|
+------+-------+-----+----+
| tony| cs| 86| 1|
| tracy| cs| 82| 2|
| tom| cs| 75| 3|
| lili| ml| 90| 1|
| lucy| ml| 85| 2|
| terry| ml| 85| 2|
|cherry| ml| 80| 4|
+------+-------+-----+----+
+------+-------+-----+----------+
| name|subject|score|row_number|
+------+-------+-----+----------+
| tony| cs| 86| 1|
| tracy| cs| 82| 2|
| tom| cs| 75| 3|
| lili| ml| 90| 1|
| lucy| ml| 85| 2|
| terry| ml| 85| 3|
|cherry| ml| 80| 4|
+------+-------+-----+----------+
+------+-------+-----+----------+
| name|subject|score|dense_rank|
+------+-------+-----+----------+
| tony| cs| 86| 1|
| tracy| cs| 82| 2|
| tom| cs| 75| 3|
| lili| ml| 90| 1|
| lucy| ml| 85| 2|
| terry| ml| 85| 2|
|cherry| ml| 80| 3|
+------+-------+-----+----------+
cume_dist 开窗函数:
下面再看个实例,我们想查看某个人在该专业的分位数,该怎么办?
这个时候就可以用到cume_dist函数了。
该函数的计算方式为:组内小于等于当前行值的行数/组内总行数
还是看代码:
select
name,
subject,
score,
cume_dist() over (partition by subject order by score desc) as cumedist
from person_subject_score
执行结果:
+------+-------+-----+------------------+
| name|subject|score| cumedist|
+------+-------+-----+------------------+
| tony| cs| 86|0.3333333333333333|
| tracy| cs| 82|0.6666666666666666|
| tom| cs| 75| 1.0|
| lili| ml| 90| 0.25|
| lucy| ml| 85| 0.75|
| terry| ml| 85| 0.75|
|cherry| ml| 80| 1.0|
+------+-------+-----+------------------+