postgresql窗口函数

postgresql窗口函数

一、基本概念

窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作；聚合函数将结果集进行计算并且通常返回一行。窗口函数也是基于结果集的计算。与聚合函数不同的是，窗口函数并不会将结果集进行分组合并输出一行；而是将计算的结果合并到基于结果集运算的列上。

思考为什么窗口函数是基于结果的预算？
答：这是由sql语句的执行顺序造成的

-from
-where
-group by
-having 
-select 
	-表达式
	-distinct
-order by 
	-offset-fetch

注：在over()子句中指定的order by子句不应该与显示排序混淆，它不会改变结果的关系本质，它只是决定窗口函数按照什么样的顺序进行计算；
根据窗口函数的执行顺序的位置便可以知道：窗口函数是基于结果集进行运算的。它将计算出的结果合并到输出的结果集上。

二、语法

<窗口函数>
over ([partition by<字段名>] order by <字段名>)

• over：窗口函数关键字
• partition by：对结果集进行分组（分区）
• order by：设定结果集的分组数据排序（计算顺序）

窗口函数分类：

1. 聚合函数（SUM、AVG、COUNT、MAX、MIN）
2. 内置函数（RANK、DENSE_RANK 、ROW_NUMBER等专用窗口函数）

三、作为窗口函数使用的聚合函数

聚合函数后接over属性的窗口函数表示在一个查询结果集上应用聚合函数
以sum为例：

select product_id,product_name,sale_price,sum(sale_price) over(order by product_id) as move_sum
from product;

我们会发现得到的结果集不仅仅是合计值，而是按照order by子句指定的product_id的升序进行排序，计算出的商品编号“小于自己”的商品的销售单价的合计值。因此，计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销售总额等的时候，通常会使用这种称为累计的统计方法

使用其他聚合函数时的操作逻辑也和本例相同

四、对两个order by的理解

over子句中的order by 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排序顺序并没有什么影响；而需要在select的最后指定排序，不然整个结果不确定顺序。
注意：这两个order by 的作用和意思完全不同。

五、对partition by 与order by的理解

窗口函数兼具之前我们学过的group by 子句的分组功能以及order by 子句的排序功能。但是，partition by 子句并不具备group by 子句的汇总功能。通过partition by 分组后的记录集合称为窗口。此处的窗口并非“窗口”的意思，而是代表范围。这也是“窗口函数”的由来

六、内置函数之RANK 、DENSE_RANK 、ROW_NUMBER

• RANK()函数：计算时如果存在相同位次的记录，则会跳过之后的位次。
• DENSE_RANK函数：计算时如果存在相同位次的记录，不会跳过之后的位次。
• ROW_NUMBER函数：赋予唯一的连续位次。（不指定partition by 时，可用于做分页的行号；如果order by能够确定每一行都唯一，也可用于做分页的行号 ）

七、计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数
其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架

例如：

select product_id,
	   product_name, 
	   sale_price,
	   avg(sale_price) over (order by product_id rows 2 preceding) moving_avg
from product

rows 2 preceding:截止到之前2行
rows 2 following:截止到之后2行
rows between 1 preceding and 1 following