postgreSQL 窗口函数

0.明明前不久还在杵前端的…

pgsql 12 官方中文文档

1.为什么需要窗口函数

group by的分组查询方式对于复杂结果集的处理来说，粒度还是不够细，要是可以结果集中不同的组进行局部的操作就好了，对吧？

2.文档中出现的一些概念

说是概念，其实也就是对于窗口函数使用过程中出现的不同类型的情况的一些划分

逻辑层次

一整个的结果集(WHERE、GROUP BY、HAVING过滤后的)
	分区
		窗口帧
			当前行
			平级行

2.1 分区(partition by)

窗口函数不会使多行被聚集成一个单独的输出行，这与通常的非窗口聚集函数不同。取而代之，行保留它们独立的标识。在这些现象背后，窗口函数可以访问的不仅仅是查询结果的当前行

一个窗口函数调用总是包含一个直接跟在窗口函数名及其参数之后的OVER子句。这使得它从句法上和一个普通函数或非窗口函数区分开来。

2.2 窗口帧

如果要讨论窗口帧的默认行为，那order by则需要被讨论：

over(...)中是否有声明order by？
	有->窗口帧包括从分区开始到当前行的所有行，以及后续任何与当前行在ORDER BY子句上相等的行
	无->窗口帧包含该分区中所有的行

2.3 当前行 & 同级行

这也是由order by对某一列的’值’排序,并且值可能出现相同的情况，造成的。

so，同级行，即相同值的行

3.语法

官网有四种可选的语法，但是其他三种都可以说是这一种的不同写法，故这里只关注这一种了…

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

3.1 function_name

窗口函数

3.2 filter_clause

如果指定了FILTER，，其他行会被丢弃。只有是聚集的窗口函数才接受FILTER 。

3.3 window_definition

窗口声明语句

-- 其他三种没有啥展开好说的，这里关注下面这一种即可...
[ frame_clause ]

3.3.1 frame_clause

窗口的边界表达式

---

3.3.1.1 为什么需要 frame_clause ?

因为order by会导致同级行的出现，使用frame_clause可以帮助我们更加精准的命中我们想要的目标行

3.3.1.1.1 不用order by不就没有那么多的故事了吗？

last_value()、nth_value()这类需要知道特定行的窗口中行的执行顺序，不用order by结果可能很奇怪

---

回到 frame_clause的语法

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

ranges | rows | groups 是三种窗口帧的声明模式

frame_clause中的参数offset（偏移量）的释义 受帧声明的模式的影响较大

3.3.1.2 窗口帧的声明模式（帧模式）

3.3.1.2.1 ranges

order by，必要的

在RANGE或GROUPS模式中，CURRENT ROW的一个frame_start表示帧开始于当前行的第一个平级行

offset指定当前行中那一列的值与它在该帧中前面或后面的行中的列值的最大差值。(注意是该列的值的差值，并不是行序号的差值) => 窗口帧为指定差值内的所有行的集合

offset表达式的数据类型会随着排序列的数据类型而变化。

3.3.1.2.2 rows

没有同级行的概念，当前行(CURRENT ROW)即所在的那一行 => 当前行之前或者之后指定数量 （行序号的数量) 的行集合。

offset必须得到一个非空、非负的整数

3.3.1.2.3 groups

order by，必要的

offset也必须得到一个非空、非负的整数 => 当前行的平级组之前或者之后指定数量 (平级组的数量) 的平级组 （平级组是在ORDER BY顺序中等效的行集合）。

3.3.1.3 frame_start/frame_end

窗口帧的开始/结束的位置(这里的位置可以是具体的行序号或者是同级行组的序号或者是差值，这取决于帧模式)

3.3.1.3.1 默认(缺省)行为

从分区的开始行直到当前行的最后一个同等行

3.3.1.3.2 可选项

UNBOUNDED PRECEDING 
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING	

google机翻一波:

preceding 前面的
following 后面的

3.3.1.4 frame_exclusion

允许当前行周围的行被排除在帧之外，即便根据帧的开始和结束选项应该把它们包括在帧中。

3.3.1.4.1 可选项

EXCLUDE CURRENT ROW会把当前行排除在帧之外。
EXCLUDE GROUP会把当前行以及它在顺序上的平级行都排除在帧之外。
EXCLUDE TIES把当前行的任何平级行都从帧中排除，但不排除当前行本
EXCLUDE NO OTHERS 不排除当前行或其平级行 (默认行为)

4. Window语句在查询中的声明位置

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]		--window（窗口）声明
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]