spark的开窗函数实战

1、count(*)

2、row_number() over()排序

3、row_number() over(partition by……分区

4、【开窗嵌套开窗】rank() over()

5、dense_rank() over() 函数

 

一、count(*)  得到所有专业下所有老师的访问数:

val middleData: DataFrame = session.sql("select subject,t_name,count(*) cnts from temp group by subject,t_name")

+-------+--------+----+
|subject|  t_name|cnts|
+-------+--------+----+
|bigdata|   laoli|   2|
|bigdata| haiyuan|  15|
| javaee|chenchan|   6|
|    php|  laoliu|   1|
|    php|   laoli|   3|
| javaee|  laoshi|   9|
|bigdata|  lichen|   6|
+-------+--------+----+

 

二、row_number() over()【按照老师的访问数,降序开窗】

session.sql(
  """
    |select subject,t_name,cnts,row_number() over(order by cnts desc) rn from middleTemp
  """.stripMargin).show()<br><br>

+-------+--------+----+---+
|subject|  t_name|cnts| rn|
+-------+--------+----+---+
|bigdata| haiyuan|  15|  1|
| javaee|  laoshi|   9|  2|
| javaee|chenchan|   6|  3|
|bigdata|  lichen|   6|  4|
|    php|   laoli|   3|  5|
|bigdata|   laoli|   2|  6|
|    php|  laoliu|   1|  7|
+-------+--------+----+---+  

 

三、row_number() over(partition by.. 【根据学科进行分区后为每个分区开窗】

//根据学科进行分区后为每个分区开窗
session.sql(
  """
    |select subject,t_name,cnts,row_number() over(partition by subject order by cnts desc) rn from middleTemp
  """.stripMargin).show()<br><br>

+-------+--------+----+---+
|subject|  t_name|cnts| rn|
+-------+--------+----+---+
| javaee|  laoshi|   9|  1|
| javaee|chenchan|   6|  2|
|bigdata| haiyuan|  15|  1|
|bigdata|  lichen|   6|  2|
|bigdata|   laoli|   2|  3|
|    php|   laoli|   3|  1|
|    php|  laoliu|   1|  2|
+-------+--------+----+---+

 

四、伪列的使用:

//开窗生成的伪列不能用于直接查询,但是我们可以将开窗语句的结果集作为一张表或者说一个子查询,这时候伪列就是一个有效的列,可以进行再次嵌套查询,
session.sql(
  """
    |select * from (
    |select subject,t_name,cnts,row_number() over(partition by subject order by cnts desc) rn from middleTemp
    |) where rn <= 2
  """.stripMargin).show()<br><br>

+-------+--------+----+---+
|subject|  t_name|cnts| rn|
+-------+--------+----+---+
| javaee|  laoshi|   9|  1|
| javaee|chenchan|   6|  2|
|bigdata| haiyuan|  15|  1|
|bigdata|  lichen|   6|  2|
|    php|   laoli|   3|  1|
|    php|  laoliu|   1|  2|

 

五、【开窗嵌套开窗】rank() over() 函数

在row_number() over() 分区+开窗的基础上,再次进行rank() over() 按照cnts进行全部数据的开窗

//开窗嵌套开窗:
//rank() over() 函数
session.sql(
  """
    |select t.*,rank() over(order by cnts desc) rn1 from (
    |select subject,t_name,cnts,row_number() over(partition by subject order by cnts desc) rn from middleTemp
    |) t
    |where rn <= 2
  """.stripMargin).show()<br><br>

+-------+--------+----+---+---+
|subject|  t_name|cnts| rn|rn1|
+-------+--------+----+---+---+
|bigdata| haiyuan|  15|  1|  1|
| javaee|  laoshi|   9|  1|  2|
| javaee|chenchan|   6|  2|  3|
|bigdata|  lichen|   6|  2|  3|
|    php|   laoli|   3|  1|  5|
|    php|  laoliu|   1|  2|  6|
+-------+--------+----+---+---+

 

六、dense_rank() over() 函数 【三个开窗函数的业务对比】:

//dense_rank() over() 函数
//三个开窗函数的业务对比:
session.sql(
  """
    |select t.*,rank() over(order by cnts desc) rank,
    |row_number() over(order by cnts desc) row_n,
    |dense_rank() over(order by cnts desc) dense_n
    |from (
    |select subject,t_name,cnts,row_number() over(partition by subject order by cnts desc) row_n_par from middleTemp
    |) t
    |where row_n_par <= 2
  """.stripMargin).show()<br><br>

+-------+--------+----+---------+----+-----+-------+
|subject|  t_name|cnts|row_n_par|rank|row_n|dense_n|
+-------+--------+----+---------+----+-----+-------+
|bigdata| haiyuan|  15|        1|   1|    1|      1|
| javaee|  laoshi|   9|        1|   2|    2|      2|
| javaee|chenchan|   6|        2|   3|    3|      3|
|bigdata|  lichen|   6|        2|   3|    4|      3|
|    php|   laoli|   3|        1|   5|    5|      4|
|    php|  laoliu|   1|        2|   6|    6|      5|
+-------+--------+----+---------+----+-----+-------+

七、整合为一句SQL完成:

//合并两个SQL语句:
session.sql(
  """
    |select t.*,rank() over(order by cnts desc) rank,
    |row_number() over(order by cnts desc) row_n,
    |dense_rank() over(order by cnts desc) dense_n
    |from
    |(select subject,t_name,cnts,row_number() over(partition by subject order by cnts desc) row_n_par from
    |(select subject,t_name,count(*) cnts from temp group by subject,t_name)) t
    |where row_n_par <= 2
  """.stripMargin).show()<br><br>

+-------+--------+----+---------+----+-----+-------+
|subject|  t_name|cnts|row_n_par|rank|row_n|dense_n|
+-------+--------+----+---------+----+-----+-------+
|bigdata| haiyuan|  15|        1|   1|    1|      1|
| javaee|  laoshi|   9|        1|   2|    2|      2|
| javaee|chenchan|   6|        2|   3|    3|      3|
|bigdata|  lichen|   6|        2|   3|    4|      3|
|    php|   laoli|   3|        1|   5|    5|      4|
|    php|  laoliu|   1|        2|   6|    6|      5|
+-------+--------+----+---------+----+-----+-------+

转:https://www.cnblogs.com/Komorebi-john/p/11328714.html#_label1

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值