当前位置：首页 > PHP实战中知识总结 > PgSQL - pgsql窗口函数解析

PHP实战中知识总结 / PgSQL - pgsql窗口函数解析

一、窗口函数

聚合函数可以将查询的结果集进行计算并且通常返回一行，窗口函数与聚合函数一样，也是基于结果集的运算，不同的是窗口函数并不会将结果集进行分组合并后输出一行，而是将计算的结果合并到结果集运算列上，窗口函数可以访问的不仅仅是查询结果的当前行。

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

二、语法解析

window_function() OVER ([PARTITION BY <字段1,字段2>] ORDER BY <排序字段> [desc|asc])
或者：
// 定义window子句
window window_function(arg1, arg2,..) OVER (
  [PARTITION BY partition_expression]
  [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST }]
  [frame_clause] )

（1）OVER：窗口函数关键字，OVER子句决定究竟查询中的哪些行被分离出来由窗口函数处理。OVER子句中的PARTITION BY子句指定了将具有相同PARTITION BY表达式值的行分到组或者分区。对于每一行，窗口函数都会在当前行同一分区的行上进行计算

（2）window_function：窗口函数名，可不带参数

（3）PARTITION BY：可选参数。该子句把行分为多个组或分区应用于窗口函数，如果没有PARTITION BY子句，窗口函数则把所有结果集作为单个分区

（4）ORDER BY ：指定每个窗口函数应用的分区中行的顺序，使用NULLS FIRST或NULLS LAST选项指定是否null值放在结果集的最前或最后。默认是NULLS LAST选项

（5）frame_clause：子句定义当前分区行中的一组子集行，并对其应用窗口函数，该子集行称为帧

三、通用窗口函数

聚合函数只有在调用之后有一个OVER子句时才作为窗口函数;否则，它们充当普通的聚合，并为整个集合返回一行。

函数	描述
row_number()	返回其分区内的当前行数，从1开始计数
rank()	返回当前行的排名，包含间隔;即对等组中第一行的row_numbe
dense_rank()	返回当前行的排名，不包括间隔;这个功能有效地计数对等组
percent_rank()	返回当前行的相对排名，即(rank- 1) / (总的分区行数 - 1)。因此，该值的范围从0到1(包含在内)。
cume_dist()	返回当前行的相对排名：(前面的行数或与当前行相同的行数)/(总行数)
ntile(num_buckets integer)	返回一个从1到参数值的整数，并将分区划分为尽可能相等的值
lag ( value anyelement [, offset integer [, default anyelement ]] )	返回分区中在当前行之前offset行的value;如果没有这样的行，则返回default(必须与value具有相同的类型)。 offset和default都是针对当前行求值的。如果省略，offset默认为1，default为NULL。
lead ( value anyelement [, offset integer [, default anyelement ]] )	返回分区中在当前行之后offset行的value；如果没有这样的行，则返回default(必须与value具有相同的类型)。 offset和default都是针对当前行求值的。如果省略，offset默认为1，default为NULL。
first_value(value anyelement)	返回在窗口框架的第一行求得的value
last_value(value anyelement)	返回在窗口框架的最后一行求得的value
nth_value(value anyelement,n integer)	返回在窗口框架的第n行求得的value(从1开始计数);如果没有这样的行，则返回NULL。

（1）列出的所有函数都依赖于相关窗口定义的ORDER BY子句指定的排序顺序。仅考虑ORDER BY列时不能区分的行被称为是同等行

（2）first_value、last_value和nth_value只考虑“窗口帧”内的行，它默认情况下包含从分区的开始行直到当前行的最后一个同等行。这对last_value可能不会给出有用的结果，有时对nth_value也一样。你可以通过向OVER子句增加一个合适的帧声明（RANGE或GROUPS）来重定义帧

四、使用实例

// 按type分类并计算每个分类的price平均值，将计算的结果合并到结果集运算列
postgres=# SELECT type,name,price,avg(price) OVER (PARTITION BY type) as avg from products;
 type |  name  | price |     avg
--------+----------+---------+-----------------------
 电器  | 电视   |  3299 | 4712.1833333333333333
 电器  | iPhone X |  9600 | 4712.1833333333333333
 电器  | 手表   | 1237.55 | 4712.1833333333333333
 零食  | 方便面  |   3.5 |  5.0250000000000000
 零食  | 汽水   |   3.5 |  5.0250000000000000
 零食  | 辣条   |   5.6 |  5.0250000000000000
 零食  | 薯条   |   7.5 |  5.0250000000000000
 衣物  | 鞋子   |   27 |  76.9666666666666667
 衣物  | 围巾   |   93 |  76.9666666666666667
 衣物  | 外套   |  110.9 |  76.9666666666666667

对于每一行，在它的分区中的行集被称为它的窗口帧。一些窗口函数只作用在窗口帧中的行上，而不是整个分区。默认情况下，如果使用ORDER BY，则帧包括从分区开始到当前行的所有行，以及后续任何与当前行在ORDER BY子句上相等的行。如果ORDER BY被忽略，则默认帧包含整个分区中所有的行。
如果行的顺序不重要时ORDER BY可以忽略。PARTITION BY同样也可以被忽略，在这种情况下会产生一个包含所有行的分区

// 在原先结果集上新增一列，显示所有price的总和（计算总和包含了分区内的所有行，因此可以忽略over中的表达式）
postgres=# SELECT type,name,price,avg(price) OVER (PARTITION BY type) as avg,sum(price) over() as sum from products;
 type |  name  | price |     avg     |  sum
------+----------+---------+-----------------------+----------
 电器 | 电视   |  3299 | 4712.1833333333333333 | 14387.55
 电器 | iPhone X |  9600 | 4712.1833333333333333 | 14387.55
 电器 | 手表   | 1237.55 | 4712.1833333333333333 | 14387.55
 零食 | 方便面  |   3.5 |  5.0250000000000000 | 14387.55
 零食 | 汽水   |   3.5 |  5.0250000000000000 | 14387.55
 零食 | 辣条   |   5.6 |  5.0250000000000000 | 14387.55
 零食 | 薯条   |   7.5 |  5.0250000000000000 | 14387.55
 衣物 | 围巾   |   93 |  76.9666666666666667 | 14387.55
 衣物 | 外套   |  110.9 |  76.9666666666666667 | 14387.55
 衣物 | 鞋子   |   27 |  76.9666666666666667 | 14387.55
(10 rows)

在一个查询中可以包含多个窗口函数，每个窗口函数都可以用不同的OVER子句来按不同方式划分数据，但是它们都作用在由虚拟表定义的同一个行集上。