当前位置：首页 > 数据库 > MySQL教程 > 安信6主管2540437PostgreSQL：窗口函数 > 正文

安信6主管2540437PostgreSQL：窗口函数

来源：这里教程网 时间：2026-03-01 15:30:34 作者：

语法

1
2
3
4
5
6
7
8
function_name ([expression [, expression ...]]) [FILTER ( WHERE  filter_clause)]
     OVER (window_definition)
     
window_definition:
   [existing_window_name]
   [PARTITION  BY  expression [, ...]]
   [ ORDER  BY  expression [ ASC  |  DESC  | USING operator] [NULLS { FIRST  |  LAST  }] [, ...]]
   [frame_clause]

  • OVER：表示窗口函数的关键字

  • PARTITION BY：对查询返回的结果集进行分组，之后窗口函数处理分组的数据。

  • ORDER BY：设定结果集的分组数据排序

    测试数据创建

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    -- 创建成绩表
    create  table  score(
    id serial  PRIMARY  key ,
    subject    character (32),
    stu_name   character (32),
    grade      NUMERIC (3,0)
    );
      
    -- 插入数据
    INSERT  INTO  SCORE(subject,stu_name,grade)  values 
    ( '语文' , '小王' ,80),
    ( '语文' , '小张' ,70),
    ( '语文' , '小李' ,80),
    ( '英语' , '小王' ,90),
    ( '英语' , '小张' ,70),
    ( '英语' , '小李' ,50),
    ( '数学' , '小王' ,100),
    ( '数学' , '小张' ,70),
    ( '数学' , '小李' ,65);

    avg() OVER()

    聚合函数后接 over属性的窗口函数表示在一个查询结果集上应用聚合函数。

    查询没名学生学习成绩并且显示课程的平均分：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    -- 对比
    -- 先算课程的平均分，再用 score 表与平均分表关联查询
    SELECT  s.stu_name, s.subject, s.grade, v.avgscore
       FROM  score s
       LEFT  JOIN  (
         SELECT  subject,  avg (grade)  AS  avgscore
           FROM  score
          GROUP  BY  subject
       ) v  ON  s.subject = v.subject
      ORDER  BY  s.stu_name, s.subject
      
    -- 使用窗口函数
    SELECT  s.stu_name, s.subject, grade,
            AVG (grade) OVER (PARTITION  BY  subject)  AS  avgscore
       FROM  score s
      ORDER  BY  s.stu_name, s.subject

    row_number()

    对结果集分组后的数据标注行号，从 1 开始

    1
    2
    3
    4
    5
    SELECT  row_number() OVER (PARTITION  BY  subject  ORDER  BY  score  desc ), *
       FROM  score;
       
    SELECT  row_number() OVER ( ORDER  BY  score  desc )  as  rownum, *
       FROM  score;

    rank()

    当组内某行字段值相同时，行号重复并且行号产生间隙

    1
    2
    SELECT  rank() OVER (PARTITION  BY  subject  ORDER  BY  score  desc ), *
       FROM  score;

    dense_rank()

    当组内某行字段值相同时，虽然行号重复，但行号不产生间隙

    1
    2
    SELECT  dense_rank() OVER (PARTITION  BY  subject  ORDER  BY  score  desc ), *
       FROM  score;

    lag()

    获取行偏移 offset 那行某个字段的数据，语法如下：

    1
    lag(value anyelement [, offset  integer  [,  default  anyelement]])

  • value：指定要返回记录的字段。 

  • offset：指行偏移量，可以是正整数或负整数，正整数表示取值结果集中向上偏移的记录，负整数表示取结果集中向下偏移的记录，默认值为 1。

  • default：指如果不存在 offset 偏移的行时用默认值填充，default 值默认为 null。

    获取向上偏移 1 行记录的 id 值

    1
    2
    SELECT  lag(id, 1) OVER (PARTITION  BY  subject  ORDER  BY  score  desc ), *
       FROM  score;

    first_value()

    用来取结果集每一个分组的第一行数据的字段值

    score 表按课程分组后取分组的第一行的分数

    1
    2
    SELECT  first_value(score) OVER (PARTITION  BY  subject  ORDER  BY  score  desc ), *
       FROM  score;

    last_value()

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

    score 表按课程分组后取分组的最后一行的分数

    1
    2
    SELECT  last_value(score) OVER (PARTITION  BY  subject  ORDER  BY  score  desc ), *
       FROM  score;

    nth_value()

    用来取结果集每一个分组的指定行数据的字段值

    1
    last_value(value  any , nth  integer )

  • value：指定表的字段

  • nth：指定结果集分组数据中的第几行，如果不存在则返回空

    score 表按课程分组后取分组的第二行的分数

    1
    2
    SELECT  nth_value(score, 2) OVER (PARTITION  BY  subject  ORDER  BY  score  desc ), *
       FROM  score;

    别名

    如果 SQL 中需要多次使用窗口函数，可以使用窗口函数别名：

    1
    SELECT  ...  FROM  ... WINDOW window_name  AS  ( window_definition )[, ...]
    1
    2
    3
    SELECT  avg (score) OVER(r),  sum (score) OVER(r), *
       FROM  score
      WINDOW r  as  (PARTITION  BY  subject  ORDER  BY  score  desc )

    • 编辑推荐：

    相关推荐

    最新软件资讯

    Shell脚本监控MySQL主从状态
    Shell脚本监控MySQL主从状态

    热文推荐

    天极热推