聚合函数将结果集进行计算并且通常返回一行。窗口函数也是基于结果集的运算。与聚合函数不同的是，窗口函数并不会将结果集进行分组合并输出一行；而是将计算的结果合并到基于结果集运算的列上。

语法

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：设定结果集的分组数据排序

测试数据创建

-- 创建成绩表
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属性的窗口函数表示在一个查询结果集上应用聚合函数。

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

-- 对比
-- 先算课程的平均分，再用 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 开始

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()

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

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

dense_rank()

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

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

lag()

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

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

value：指定要返回记录的字段。
offset：指行偏移量，可以是正整数或负整数，正整数表示取值结果集中向上偏移的记录，负整数表示取结果集中向下偏移的记录，默认值为 1。
default：指如果不存在 offset 偏移的行时用默认值填充，default 值默认为 null。

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

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

first_value()

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

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

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

last_value()

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

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

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

nth_value()

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

last_value(value any, nth integer)

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

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

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

别名

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

SELECT ... FROM ... WINDOW window_name AS ( window_definition )[, ...]

SELECT avg(score) OVER(r), sum(score) OVER(r), *
  FROM score
 WINDOW r as (PARTITION BY subject ORDER BY score desc)

PostgreSQL：窗口函数

语法