postgreSQL之开窗函数用法
- 320 次检阅

开窗函数语法相关

1.over 表示窗口函数关键字

2.partition by 对查询返回的结果集进行分组

3.order by 设定结果集的分组数据排序


可作为窗口函数的函数分类

1.聚合函数: SUM、AVG、COUNT、MAX、MIN

2.内置函数:RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数


avg() over()

演示avg()聚合函数后接over属性的窗口函数,此窗口函数用来计算分组后数据的平均值。

先创建一张成绩表

查询每名学生学习成绩并且显示课程的平均分。请问我自己会怎么做呢?如果是不用窗口函数先做的话,我能想到是这样: 1.先计算出课程的平均分。 2.然后用成绩表与平均分表关联 如下:

而使用窗口函数,基本上一句搞掂:


sum() over()

演示sum()聚合函数后接over属性的窗口函数,此窗口函数用来计算分组后数据的总分值

例如:查询每名学生学习成绩并且显示课程的总分


count() over()

演示count()聚合函数后接over属性的窗口函数,此窗口函数用来计算分组后数据的总条数值


max() over()

演示max()聚合函数后接over属性的窗口函数,此窗口函数用来计算分组后数据的最大值


min() over()

演示min()聚合函数后接over属性的窗口函数,此窗口函数用来计算分组后数据的最小值


row_number()

row_number()窗口函数对结果集分组后的数据标注行号,从1开始的..

如果不指定partition属性,row_number()就类似于oracle的rownum记录所有行号。如:


rank()

rank()窗口函数和row_number()函数类似, 主要区分为当组内某行字段值相同,行号重复并且行号产生间隙,如下:

从返回结果看到..第一二行都是1,第三行就3了,产生了间隙...


dense_rank()

dese_rank()函数和rank()函数类似,主要区别为当组内某个字段值相同时,虽然行号重复,但行号不产生间隙。


percent_rank()

percent_rank()函数:当前行的相对排名: (rank- 1) / (总行数 - 1),得到的数在0-1之间[0,1), 例如:限制序号在0~1之间(0作为第一个序)-->[0,1):


cume_dist()

cume_dist()函数:当前行的相对排名: (当前行前面的行数 或 与当前行同等的行的行数)/(总行数)

注意:上面的percernt_rank()函数默认是从0开始排序的,如果需要使用相对0~1之间(0,1)的排名 例如,限制序号在0~1之间相对排名(0,1)


first_value()

first_value()函数用来取结果集每一个分组的第一行数的字段值。如:

所以通过first_value()函数很容易查询到分组数据的最大值和最小值。例如查询成绩表按课程分组同时取每门课程的最高分,如:


last_value()

last_value函数用来取结果集每一个分组的最后一行数据的字段值。 如:


nth_value()

nth_value()函数用来取结果集每一个分组的指定行数据的字段值,语法:

例如成绩表按课程分组后去分组的第三行分数,如:


窗口函数别名的使用

如果sql中需要多次使用到窗口函数,可以使用窗口函数别名,语法如:

WINDOW 属性指定表的别名为window_name,可以给over属性引用,如下:


好了终于写完常用的窗口函数了啊呜...要不打个赏?

 

分享到:
, ,

这篇文章有1条评论

  1. NO:3038 / 發布於 - 2019-10-18 #1 回复

    非技术的路过。

发表评论