窗口函数

窗口函数

• 语法

  窗口函数 over (partition by 用于分组的列名 order by 用于排序的列名)
  

• 窗口函数的分类

  • 专用窗口函数
    • rank
    • dense_rank
    • row_number
  • 聚合函数
    • sum
    • avg
    • count
    • max
    • min
  • 取值函数
    • first_value：返回分组内第一个值
    • last_value：返回分组内最后一个值
    • nth_value：返回分组内第n行

• 特点：一般用在select子句中，同时具有分组和排序功能，不减少原表的行数。

• 专用窗口函数示例

  #对每个年级的同学分别按成绩降序排名
  select *,
  	rank() over (partition by grade order by score desc) ranking
  from class_table;
  

  若不需要分组，也可以省略partition by语句。

• rank、dense_rank、row_number的区别

  • rank：排名时若有并列名次，则并列的这几名名次一样，但是会占用相应的位置。例如名次为1、2、3、4、5、5、5、8、9
  • dense_rank：排名时若有并列名次，则并列的这几名名次一样，不会占用位置，例如名次为1、2、3、4、5、5、5、6、7
  • row_number：排名时若有并列名次，并列的这几名名次不一样，仍然每个人占用一个名次，例如第5、6、7个人的得分一样，名次为1、2、3、4、5、6、7、8、9

• 聚合函数

  • 示例

    #对每个年级学生的成绩求和、平均值、计数。得到的是当前学生与前面学生的累计和、均值、计数
    select *,
    	sum(score) over (partition by grade order by score desc) current_sum,
    	avg(score) over (partition by grade order by score desc) current_avg,
    	count(score) over (partition by grade order by score desc) current_count
    from class_table;