语法

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 开始

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
)

ITPub博客

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

语法