SparkSQL開窗函數(shù)分析使用示例

更新時(shí)間：2023年01月20日 10:49:37 作者：健鑫.

開窗函數(shù)的引入是為了既顯示聚集前的數(shù)據(jù)，又顯示聚集后的數(shù)據(jù)。即在每一行的最后一列添加聚合函數(shù)的結(jié)果。開窗用于為行定義一個(gè)窗口，它對一組值進(jìn)行操作，不需要使用 GROUP BY 子句對數(shù)據(jù)進(jìn)行分組，能夠在同一行中同時(shí)返回基礎(chǔ)行的列和聚合列

聚合函數(shù)和開窗函數(shù)

聚合函數(shù)是將多行變成一行，如果要顯示其他列，必須將列加入group by

開窗函數(shù)是將一行變成多行，將所有的信息顯示出來

開窗函數(shù)

聚合開窗函數(shù)

聚合函數(shù) over(partition by子句)

over關(guān)鍵字將聚合函數(shù)當(dāng)作聚合開窗函數(shù)

SQL標(biāo)準(zhǔn)允許所有的聚合函數(shù)用作聚合開窗函數(shù)

示例：

//    spark.sql("select id, name, age, count(name) from person").show 報(bào)錯(cuò)
    spark.sql("select id, name, age, count(name)over() sum from person").show
/*
* +---+----+---+---+
| id|name|age|sum|
+---+----+---+---+
|  1|  jx| 20|  6|
|  2|  zx| 21|  6|
|  3|  wz| 33|  6|
|  4|  qw| 11|  6|
|  5|  aa| 22|  6|
|  6|  aq| 45|  6|
+---+----+---+---+
*
* */

over后面的括號還可以改變聚合函數(shù)的窗口范圍

如果over后面的括號為空，則開窗函數(shù)會對所有行進(jìn)行聚合運(yùn)算

over后面的括號里可以用partition by 來定義行的分區(qū)來進(jìn)行聚合運(yùn)算

partition by進(jìn)行分區(qū)之后，計(jì)算當(dāng)前分區(qū)的聚合計(jì)算的結(jié)果

spark.sql("select id, name, age, area_id, count(name)over(partition by area_id) sum from person").show
/*
+---+----+---+-------+---+
| id|name|age|area_id|sum|
+---+----+---+-------+---+
|  1|  jx| 20|      1|  3|
|  2|  zx| 21|      1|  3|
|  3|  wz| 33|      1|  3|
|  5|  aa| 22|      3|  2|
|  6|  aq| 45|      3|  2|
|  4|  qw| 11|      2|  1|
+---+----+---+-------+---+
*
* */

排序開窗函數(shù)

ROW_NUMBER順序排序

row_number() over(order by score)

排序開窗函數(shù)中使用partition by 需要放置在order by之前

實(shí)例

spark.sql("select id, name, age, area_id, row_number() over(order by age) rank from person").show
spark.sql("select id, name, age, area_id, row_number() over(partition by area_id order by age) rank from person").show
/*
+---+----+---+-------+----+
| id|name|age|area_id|rank|
+---+----+---+-------+----+
|  4|  qw| 11|      2|   1|
|  1|  jx| 20|      1|   2|
|  2|  zx| 21|      1|   3|
|  5|  aa| 22|      3|   4|
|  3|  wz| 33|      1|   5|
|  6|  aq| 45|      3|   6|
+---+----+---+-------+----+
+---+----+---+-------+----+
| id|name|age|area_id|rank|
+---+----+---+-------+----+
|  1|  jx| 20|      1|   1|
|  2|  zx| 21|      1|   2|
|  3|  wz| 33|      1|   3|
|  5|  aa| 22|      3|   1|
|  6|  aq| 45|      3|   2|
|  4|  qw| 11|      2|   1|
+---+----+---+-------+----+
*
* */

RANK跳躍排序

rank() over(order by)

使用該函數(shù)排序求出來的結(jié)果可以并列

示例

spark.sql("select id, name, age, area_id, rank() over(order by age) rank from person").show
spark.sql("select id, name, age, area_id, rank() over(partition by area_id order by age) rank from person").show
/*
+---+----+---+-------+----+
| id|name|age|area_id|rank|
+---+----+---+-------+----+
|  4|  qw| 10|      2|   1|
|  1|  jx| 20|      1|   2|
|  2|  zx| 20|      1|   2|
|  5|  aa| 22|      3|   4|
|  7|  qq| 22|      3|   4|
|  3|  wz| 33|      1|   6|
|  6|  aq| 45|      3|   7|
+---+----+---+-------+----+
+---+----+---+-------+----+
| id|name|age|area_id|rank|
+---+----+---+-------+----+
|  1|  jx| 20|      1|   1|
|  2|  zx| 20|      1|   1|
|  3|  wz| 33|      1|   3|
|  5|  aa| 22|      3|   1|
|  7|  qq| 22|      3|   1|
|  6|  aq| 45|      3|   3|
|  4|  qw| 10|      2|   1|
+---+----+---+-------+----+
*
* */

DENSE_RANK連續(xù)排序

dense_rank() over(order by )

使用該函數(shù)，并列排名之后的排序+1

示例

spark.sql("select id, name, age, area_id, dense_rank() over(order by age) rank from person").show
spark.sql("select id, name, age, area_id, dense_rank() over(partition by area_id order by age) rank from person").show
/*
+---+----+---+-------+----+
| id|name|age|area_id|rank|
+---+----+---+-------+----+
|  4|  qw| 10|      2|   1|
|  1|  jx| 20|      1|   2|
|  2|  zx| 20|      1|   2|
|  5|  aa| 22|      3|   3|
|  7|  qq| 22|      3|   3|
|  3|  wz| 33|      1|   4|
|  6|  aq| 45|      3|   5|
+---+----+---+-------+----+
+---+----+---+-------+----+
| id|name|age|area_id|rank|
+---+----+---+-------+----+
|  1|  jx| 20|      1|   1|
|  2|  zx| 20|      1|   1|
|  3|  wz| 33|      1|   2|
|  5|  aa| 22|      3|   1|
|  7|  qq| 22|      3|   1|
|  6|  aq| 45|      3|   2|
|  4|  qw| 10|      2|   1|
+---+----+---+-------+----+
*
* */

NTILE分組排序

ntile(6) over(order by) // 表示分成六個(gè)組，顯示每個(gè)組的序號

spark.sql("select id, name, age, area_id, ntile(4) over(order by age) rank from person").show
spark.sql("select id, name, age, area_id, ntile(4) over(partition by area_id order by age) rank from person").show
/*
+---+----+---+-------+----+
| id|name|age|area_id|rank|
+---+----+---+-------+----+
|  4|  qw| 10|      2|   1|
|  1|  jx| 20|      1|   1|
|  2|  zx| 20|      1|   2|
|  5|  aa| 22|      3|   2|
|  7|  qq| 22|      3|   3|
|  3|  wz| 33|      1|   3|
|  6|  aq| 45|      3|   4|
+---+----+---+-------+----+
+---+----+---+-------+----+
| id|name|age|area_id|rank|
+---+----+---+-------+----+
|  1|  jx| 20|      1|   1|
|  2|  zx| 20|      1|   2|
|  3|  wz| 33|      1|   3|
|  5|  aa| 22|      3|   1|
|  7|  qq| 22|      3|   2|
|  6|  aq| 45|      3|   3|
|  4|  qw| 10|      2|   1|
+---+----+---+-------+----+
*
* */

到此這篇關(guān)于SparkSQL開窗函數(shù)分析使用示例的文章就介紹到這了,更多相關(guān)SparkSQL開窗函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: