关于: PostgreSQL的窗口函数OVER

窗口函数

窗口函数是基于结果进行计算,于聚合函数不同的是窗口函数不会将结果集进行分组计算并输出一行,而是将计算出的结果合并到输出的结果集上,并返回多行。使用窗口函数能大幅度简化SQL代码。

使用说明

  • OVER 表示窗口函数的关键字
  • PARTITIOIN BY 属性对查询返回的结果进行分组,之后窗口函数处理分组的数据
  • ORDER BY 属性设定结果集的分子数据排序

- OVER()

聚合函数后接OVER属性的窗口函数表示在一个查询结果集上应用聚合函数。接下来,我们将看看 avg() 聚合函数后接 OVER属性的窗口函数, 此窗口函数用来计算分组后数据的平均值.

首先,我们创建新表score并插入对应数据, 如下所示:

CREATE TABLE score (id serial primary key,
                    subject character varying(32),
                    stu_name character varying(32),
                    score numeric(3,0)
                   );

INSERT INTO score (subject, stu_name, score) VALUES ('Chinese', 'Encore', 80);
INSERT INTO score (subject, stu_name, score) VALUES ('Chinese', 'Andy', 80);
INSERT INTO score (subject, stu_name, score) VALUES ('Chinese', 'Candy', 70);
INSERT INTO score (subject, stu_name, score) VALUES ('English', 'Andy', 80);
INSERT INTO score (subject, stu_name, score) VALUES ('English', 'Candy', 90);
INSERT INTO score (subject, stu_name, score) VALUES ('English', 'Encore', 70);
INSERT INTO score (subject, stu_name, score) VALUES ('Math', 'Encore', 80);
INSERT INTO score (subject, stu_name, score) VALUES ('Math', 'Andy', 75);
INSERT INTO score (subject, stu_name, score) VALUES ('Math', 'Candy', 90);

接下来,  如果我们想查询每个学生成绩并且显示课程的平均分,通常我们的做法是先计算出来课程的平均分,然后用score表于平均分表关联查询,查询代码如下:

SELECT s.subject, s.stu_name, s.score, tmp.avgscore 
FROM score s
LEFT JOIN (SELECT subject, avg(score) avgscore FROM score GROUP BY subject) tmp on s.subject = tmp.subject;


 subject | stu_name | score |      avgscore
---------+----------+-------+---------------------
 Chinese | Encore   |    80 | 76.6666666666666667
 Chinese | Andy     |    80 | 76.6666666666666667
 Chinese | Candy    |    70 | 76.6666666666666667
 English | Andy     |    80 | 80.0000000000000000
 English | Candy    |    90 | 80.0000000000000000
 English | Encore   |    70 | 80.0000000000000000
 Math    | Encore   |    80 | 81.6666666666666667
 Math    | Andy     |    75 | 81.6666666666666667
 Math    | Candy    |    90 | 81.6666666666666667
(9 rows)

Time: 0.584 ms

如果使用窗口函数,将会很容易实现以上需求, 具体查询如下:

SELECT subject, stu_name, score, avg(score) OVER(PARTITION BY SUBJECT) FROM score;

 subject | stu_name | score |         avg
---------+----------+-------+---------------------
 Chinese | Encore   |    80 | 76.6666666666666667
 Chinese | Andy     |    80 | 76.6666666666666667
 Chinese | Candy    |    70 | 76.6666666666666667
 English | Andy     |    80 | 80.0000000000000000
 English | Candy    |    90 | 80.0000000000000000
 English | Encore   |    70 | 80.0000000000000000
 Math    | Encore   |    80 | 81.6666666666666667
 Math    | Andy     |    75 | 81.6666666666666667
 Math    | Candy    |    90 | 81.6666666666666667
(9 rows)

Time: 0.375 ms

以上查询前三列都来源于表score, 第四列表示提取课程平均分,PARTITION BY subject表示根据字段subject进行分组.

最后,我们可以看到使用窗口函数 OVER(), 不管是从语法和查询速度上都略胜一筹。

或许,你和我一样,也想着可以在分组后再按照分数降序排列显示. 这个也很简单的,我们只需要在 OVER 的最后添加ORDER BY score,即可完成.

SELECT subject, stu_name, score, avg(score) OVER(PARTITION BY SUBJECT ORDER BY score) FROM score;

 subject | stu_name | score |         avg
---------+----------+-------+---------------------
 Chinese | Candy    |    70 | 70.0000000000000000
 Chinese | Andy     |    80 | 76.6666666666666667
 Chinese | Encore   |    80 | 76.6666666666666667
 English | Encore   |    70 | 70.0000000000000000
 English | Andy     |    80 | 75.0000000000000000
 English | Candy    |    90 | 80.0000000000000000
 Math    | Andy     |    75 | 75.0000000000000000
 Math    | Encore   |    80 | 77.5000000000000000
 Math    | Candy    |    90 | 81.6666666666666667
(9 rows)

Time: 0.417 ms

现在,再回头看看,从SQL上来看,窗口函数能帮我们简化大量查询语句。