PostgreSQL 窗口函数 ( Window Functions ) 如何使用？

本是古典何须时尚 2021-12-05 15:17 562阅读 0赞

# 一、为什么要有窗口函数 #

--------------------

我们直接用例子来说明，这里有一张学生考试成绩表`testScore`：

![896608-20190717031712162-1570051900.png][]

现在有个需求，需要查询的时候多出一列`subject_avg_score`，为此科目所有人的平均成绩，好跟每个人的成绩做对比。

传统方法肯定是用聚合，但是写起来很麻烦也很累赘，这时候窗口函数就排上了用场。

因为**窗口函数不会像聚合一样将参与计算的行合并成一行输出，而是将计算出来的结果带回到了计算行上。**

# 二、窗口函数的使用 #

--------------------

### 1、聚合和窗口函数的区别 ###

聚合：聚合函数(sum，min，avg……) + GROUP BY

窗口函数：聚合函数(sum，min，avg……) + OVER ( …… )

### 2、使用 ###

还用上面的例子：

##### (1) 取每个不同科目的平均值`subject_avg_score` \[这正是上面提到的需求\] #####

这里的 OVER 里用到了 `PARTITION BY`

SELECT *, 
           avg("score") OVER (PARTITION BY "subject") as "subject_avg_score"
    FROM "testScore"

![896608-20190717031726475-1892199034.png][]

##### (2) 取所有成绩的平均值`subject_avg_score` #####

这里的 OVER 里为空

SELECT *, 
           avg("score") OVER () as "subject_avg_score"
    FROM "testScore"

![896608-20190717031734763-2032402294.png][]

###  ###

##### (3) 取此人该科目成绩班上排第几名`subject_rank_score` #####

这里的 OVER 里用到了 `PARTITION BY` \+ `ORDER BY`

> ORDER BY 只能用在一些特殊的聚合函数里，比如这里的 rank()

SELECT *, 
            avg("score") OVER (PARTITION BY "subject") as "subject_avg_score",
                    rank() OVER (PARTITION BY "subject" ORDER BY "score" DESC) as "subject_rank_score" 
    FROM "testScore"

![896608-20190717031747732-1573788469.png][]

> 拓展知识：**rank()、dense\_rank()、row\_number() 区别**
> 
> rank() 最适合用来做排名的功能，它是若两人并列第一，那第三个人就排名第三
> 
> dense\_rank() 跟 rank() 的区别是，若两人并列第一，那第三个人紧随其后排名第二
> 
> row\_number() 则单纯是序号，所以不会出现多个人并列的情况。

##### (4) 提取 OVER 变量 #####

如果在 sql 里写了很多重复的 OVER()，可以提取成一个 window 变量，简化代码。

SELECT *, 
            avg("score") OVER window_frame as "subject_avg_score",
            avg("score") OVER window_frame as "subject_avg_score_2",
            avg("score") OVER window_frame as "subject_avg_score_3"
    FROM "testScore"  
    window window_frame as (PARTITION BY "subject")

![896608-20190717031759990-1881531549.png][]

--------------------

#### 参考资料 ####

官方文档：[http://www.postgres.cn/docs/9.3/tutorial-window.html][http_www.postgres.cn_docs_9.3_tutorial-window.html]

[Postgresql窗口函数（一）][Postgresql]

[Postgresql窗口函数（二）][Postgresql 1]

转载于:https://www.cnblogs.com/xjnotxj/p/11198566.html

[896608-20190717031712162-1570051900.png]: /images/20211205/aa47abd28c2b4d809e1026f237f8d365.png
[896608-20190717031726475-1892199034.png]: /images/20211205/79d5fc874a7a4f63958e289e89c42c7f.png
[896608-20190717031734763-2032402294.png]: /images/20211205/9d91e331cd4c42b49cb2fe1f610dc961.png
[896608-20190717031747732-1573788469.png]: /images/20211205/1f13e4b36f0f4d2ba2bbef8536045a73.png
[896608-20190717031759990-1881531549.png]: /images/20211205/793357de0b7a483ca187e4b3e6d39998.png
[http_www.postgres.cn_docs_9.3_tutorial-window.html]: http://www.postgres.cn/docs/9.3/tutorial-window.html
[Postgresql]: https://segmentfault.com/a/1190000008332604
[Postgresql 1]: https://segmentfault.com/a/1190000010082851