postgresql系列之窗⼝函数
本⽂是《sql基础教程》《postgresql实战》的读书笔记；具体可以参考这两本书相关章节。
⼀、窗⼝函数
1.1 基本概念
窗⼝函数可以进⾏排序、⽣成序列号等⼀般的聚合函数⽆法实现的⾼级操作；聚合函数将结果集进⾏计算并且通常返回⼀⾏。窗⼝函数也是基于结果集的运算。与聚合函数不同的是，窗⼝函数并不会将结果集进⾏分组合并输出⼀⾏；⽽是将计算的结果合并到基于结果集运算的列上。
思考：为什么说窗⼝函数是基于结果集的预算 ?
解读： 关于这个问题，则必须要谈谈 查询语句的逻辑处理顺序
SQL 逻辑处理顺序
-FROM
-WHERE
-GROUP BY
-HAVING
-SELECT
-表达式
-DISTINCT
-ORDER BY
-OFFSET-FETCH
注：在over()⼦句中指定的order by⼦句不应该与显⽰排序混淆，并且并且它不会改变结果的关系本质。
根据窗⼝函数的执⾏顺序的位置便可以知道：窗⼝函数是基于结果集进⾏运算的。它将计算出的结果合并到输出的结果集上。
1.2 语法相关
<；窗⼝函数>
OVER([PARTITION BY<；列清单>]
ORDER BY<；排序⽤列清单>)
over ：窗⼝函数关键字
partition by ：对结果集进⾏分组
order by：设定结果集的分组数据排序
**可作为窗⼝函数的函数分类: **
聚合函数：① 聚合函数（SUM、AVG、COUNT、MAX、MIN）
内置函数：② RANK、DENSE_RANK、ROW_NUMBER 等专⽤窗⼝函
1.3 ⼊门案例
聚合函数后接 over属性的窗⼝函数表⽰在⼀个查询结果集上应⽤聚合函数。
描述： 查询每名学⽣学习成绩并且显⽰课程的平均分。
-- 创建成绩表
create table score(
id rial 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)
使⽤ group by 和 窗⼝函数
但是：我在写sql查询的时候，不⼩⼼在窗⼝函数增加了排序字段。导致结果不是我想要的。如下图所⽰：思考：在窗⼝函数中使⽤order by 导致最后数据并⾮所希望的;
问题⼀：那什么时候应该⽤order by;
问题⼆：聚合函数类型的窗⼝函数中使⽤order by是什么意思呢。
尝试寻找规律
（⼀） 去除窗⼝函数中的 order by 和 最外层的 order by
顺序可能不⼀致，数据⾏能够对应上，最终数据也是正确的。
（⼆） 去除最外层的 order by 。 顺序不⼀致，记录相等，但最后结果不正确。
（三） 单独使⽤ subject进⾏排序，结果是正确的。
通过这种⽅式去探索可能不是⼀个好的主意；另辟蹊径 （参考《sql基础教程》）
1.4 作为窗⼝函数使⽤的聚合函数
先准备数据；
CREATE TABLE Product
(product_id CHAR(4)NOT NULL,
product_name VARCHAR(100)NOT NULL,
product_type VARCHAR(32)NOT NULL,
sale_price INTEGER,
purcha_price INTEGER,
regist_date DATE,
PRIMARY KEY(product_id));
INSERT INTO Product VALUES('0001','T恤','⾐服',1000,500,'2009-09-20');
INSERT INTO Product VALUES('0002','打孔器','办公⽤品',500,320,'2009-09-11'); INSERT INTO Product VALUES('0003','运动T恤','⾐服',4000,2800,NULL);
INSERT INTO Product VALUES('0004','菜⼑','厨房⽤具',3000,2800,'2009-09-20'); INSERT INTO Product VALUES('0005','⾼压锅','厨房⽤具',6800,5000,'2009-01-15'); INSERT INTO Product VALUES('0006','叉⼦','厨房⽤具',500,NULL,'2009-09-20'); INSERT INTO Product VALUES('0007','擦
菜板','厨房⽤具',880,790,'2008-04-28'); INSERT INTO Product VALUES('0008','圆珠笔','办公⽤品',100,NULL,'2009-11-11');
先⽤ SUM 函数作为窗⼝函数使⽤ 的例⼦
SELECT product_id, product_name, sale_price,
SUM(sale_price)OVER(ORDER BY product_id)AS current_sum
FROM Product;
但是我们得到的并不仅仅是合计值，⽽是按照 ORDER BY ⼦句指定 的 product_id的升序进⾏排列，计算出商品编号“⼩于⾃⼰”的商品 的销售单价的合计值。因此，计算该合计值的逻辑就像⾦字塔堆积那样，⼀⾏⼀⾏逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销 售额总额等的时候，通常都会使⽤这种称为累计的统计⽅法
使⽤其他聚合函数时的操作逻辑也和本例相同
SELECT product_id, product_name, sale_price,
AVG(sale_price)OVER(ORDER BY product_id)AS current_avg
FROM Product;
从结果中可以看到，current_avg 的计算⽅法确实是计算平 均值的⽅法，但作为统计对象的却只是“排在⾃⼰之上”的记录。像这样 以“⾃⾝记录（当前记录）”作为基准进⾏统计，就是将聚合函数当作窗⼝函数使⽤时的最⼤特征。
两个order by
OVER ⼦句中的 ORDER BY只是⽤来决定 窗⼝函数按照什么样的顺序进⾏计算的，对结果的排列顺序并没有影响； ⽽需要在lect的最后指定排序，不然整个结果集不确定顺序。主意：这两个order by的作⽤和意思完全不同。
