SparkSQL开窗函数分析使用示例
健鑫. 人气:0开窗函数能在每行的最后一行都显示聚合函数的结果,所以聚合函数可以用作开窗函数
聚合函数和开窗函数
聚合函数是将多行变成一行,如果要显示其他列,必须将列加入group by
开窗函数是将一行变成多行,将所有的信息显示出来
开窗函数
聚合开窗函数
聚合函数 over(partition by子句)
over关键字将聚合函数当作聚合开窗函数
SQL标准允许所有的聚合函数用作聚合开窗函数
示例:
// spark.sql("select id, name, age, count(name) from person").show 报错 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后面的括号还可以改变聚合函数的窗口范围
如果over后面的括号为空,则开窗函数会对所有行进行聚合运算
over后面的括号里可以用partition by 来定义行的分区来进行聚合运算
partition by进行分区之后,计算当前分区的聚合计算的结果
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| +---+----+---+-------+---+ * * */
排序开窗函数
ROW_NUMBER顺序排序
row_number() over(order by score)
排序开窗函数中使用partition by 需要放置在order by之前
实例
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)
使用该函数排序求出来的结果可以并列
示例
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连续排序
dense_rank() over(order by )
使用该函数,并列排名之后的排序+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) // 表示分成六个组,显示每个组的序号
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| +---+----+---+-------+----+ * * */
加载全部内容