PG系列5-SQL高级特性2——聚合函数和窗口函数_pgsql 聚合函数

---

1、聚合函数

聚合函数可以对结果集进行计算，常用的聚合函数有 avg（）、sum（）、min（）、max（）、count（）等，本节 将介绍PostgreSQL两个特殊功能的聚合函数并给出测试示例。 在介绍两个聚合函数之前，先来看一个应用场景，假如一 张表有以下数据：

 中国 台北 
 中国 香港 
 中国 上海 
 日本 东京 
 日本 大阪 
1
2
3
4
5

要求得到如下结果集：

  中国     台北，香港，上海 
  日本     东京，大阪 
1
2

想想这个SQL如何写？

1.1 string_agg函数

首先介绍string_agg函数，此函数语法如下所示： string_agg(expression, delimiter)
简单地说string_agg函数能将结果集某个字段的所有行连 接成字符串，并用指定delimiter分隔符分隔，expression表示要处理的字符类型数据；参数的类型为（text，text）或（bytea，bytea），函数返回的类型同输入参数类型一致， bytea属于二进制类型，使用情况不多，我们主要介绍text类型输入参数，本节开头的场景正好可以用string_agg函数处理。 首先创建测试表并插入以下数据：

CREATE TABLE city (country varchar(64),city varchar(64)); 
INSERT INTO city VALUES ('中国','台北'); 
INSERT INTO city VALUES ('中国','香港'); 
INSERT INTO city VALUES ('中国','上海'); 
INSERT INTO city VALUES ('日本','东京'); 
INSERT INTO city VALUES ('日本','大阪'); 
1
2
3
4
5
6

数据如下所示：

mydb=> SELECT * FROM city; 
country | city 
------------+------ 
	中国 | 台北 
	中国 | 香港 
	中国 | 上海 
	日本 | 东京 
	日本 | 大阪 
(5 rows)
1
2
3
4
5
6
7
8
9

将city字段连接成字符串的代码如下所示：

mydb=> SELECT string_agg(city,',') FROM city;
		string_agg 
-------------------------- 
	台北,香港,上海,东京,大阪 
(1 row)
1
2
3
4
5

可见string_agg函数将输出的结果集连接成了字符串，并 ]用指定的逗号分隔符分隔，回到本文开头的问题，通过SQL实现，如下所示：

mydb=> SELECT country,string_agg(city,',') FROM city GROUP BY country; 
	country | string_agg 
------------+---------------- 
		日本 | 东京,大阪 
		中国 | 台北,香港,上海 
(2 rows) 
1
2
3
4
5
6

1.2 array_agg函数

array_agg函数和string_agg函数类似，最主要的区别为返回的类型为数组，数组数据类型同输入参数数据类型一致， array_agg函数支持两种语法，第一种如下所示：

 array_agg(expression) --输入参数为任何非数组类型 
1

输入参数可以是任何非数组类型，返回的结果是一维数组，array_agg函数将结果集某个字段的所有行连接成数组，例如执行以下查询：

mydb=> SELECT country,array_agg(city) FROM city GROUP BY country; 
	country | array_agg 
------------+------------------ 
		日本 | {东京,大阪} 
		中国 | {台北,香港,上海} 
1
2
3
4
5

array_agg函数输出的结果为字符类型数组，其他无明显区别，使用array_agg函数主要优点在于可以使用数组相关函数和操作符。
第二种array_agg函数语法如下所示：

array_agg(expression) --输入参数为任何数组类型 
1

第一种array_agg函数的输入参数为任何非数组类型，这里输入参数为任何数组类型，返回类型为多维数组： 首先创建数组表。

mydb=> CREATE TABLE test_array3(id int4[]); 
CREATE TABLE 
mydb=> INSERT INTO test_array3(id) VALUES (array[1,2,3]); 
INSERT 0 1 
mydb=> INSERT INTO test_array3(id) VALUES (array[4,5,6]); 
INSERT 0 1
1
2
3
4
5
6

数据如下所示：

mydb=> SELECT * FROM test_array3; 
	  id 
	--------- 
	{1,2,3} 
	{4,5,6} 
(2 rows) 
1
2
3
4
5
6

使用array_agg函数，如下所示：

mydb=> SELECT array_agg(id) FROM test_array3; 
	array_agg 
-------------------
{{1,2,3},{4,5,6}} 
(1 row) 
1
2
3
4
5

也可以将array_agg函数输出类型转换成字符串，并用指定分隔符分隔，使用array_to_string函数，如下所示：

mydb=> SELECT array_to_string( array_agg(id),',') FROM test_array3; 
 array_to_string 
----------------- 
 1,2,3,4,5,6 
(1 row)
1
2
3
4
5

2、窗口函数

上一节介绍了聚合函数，聚合函数将结果集进行计算并且通常返回一行。窗口函数也是基于结果集进行计算，与聚合函数不同的是窗口函数不会将结果集进行分组计算并输出一行， 而是将计算出的结果合并到输出的结果集上，并返回多行。使用窗口函数能大幅简化SQL代码。

2.1 窗口函数语法

PostgreSQL提供内置的窗口函数，例如row_num（）、 rank（）、lag（）等，除了内置的窗口函数外，聚合函数、自定义函数后接OVER属性也可作为窗口函数。 窗口函数的调用语法稍复杂，如下所示：

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

其中window_definition语法如下：

[ existing_window_name ] 
[ PARTITION BY expression [, ...] ] 
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] 
[ frame_clause ] 
1
2
3
4

说明如下：

• OVER表示窗口函数的关键字。
• PARTITON BY属性对查询返回的结果集进行分组，之后窗口函数处理分组的数据。
• ORDER BY属性设定结果集的分组数据排序。 后续小节将介绍常用窗口函数的使用。

2.2 avg（）OVER（）

聚合函数后接OVER属性的窗口函数表示在一个查询结果集上应用聚合函数，本节将演示avg（）聚合函数后接OVER 属性的窗口函数，此窗口函数用来计算分组后数据的平均值。 创建一张成绩表并插入测试数据，如下所示：

CREATE TABLE score ( id serial primary key, 
						subject character varying(32), 
						stu_name character varying(32), 
						score numeric(3,0) ); 
INSERT INTO score ( subject,stu_name,score ) VALUES ('Chinese','francs',70); 
INSERT INTO score ( subject,stu_name,score ) VALUES ('Chinese','matiler',70); 
INSERT INTO score ( subject,stu_name,score)  VALUES ('Chinese','tutu',80); 
INSERT INTO score ( subject,stu_name,score ) VALUES ('English','matiler',75); 
INSERT INTO score ( subject,stu_name,score ) VALUES ('English','francs',90); 
INSERT INTO score ( subject,stu_name,score ) VALUES ('English','tutu',60); 
INSERT INTO score ( subject,stu_name,score ) VALUES ('Math','francs',80); 
INSERT INTO score ( subject,stu_name,score ) VALUES ('Math','matiler',99); 
INSERT INTO score ( subject,stu_name,score ) VALUES ('Math','tutu',65); 
1
2
3
4
5
6
7
8
9
10
11
12
13

查询每名学生学习成绩并且显示课程的平均分，通常是先计算出课程的平均分，然后用score表与平均分表关联查询，如下所示：

mydb=> SELECT s.subject, s.stu_name,s.score, tmp.avgscore FROM score s LEFT JOIN (SELECT subject, avg(score) avgscore FROM score GROUP BY subject) tmp ON s.subject = tmp.subject; 
subject |   stu_name   | score |  avgscore 
------------+----------+-------+--------------------- 
Chinese |    francs    | 70 | 73.3333333333333333 
Chinese |    matiler   | 70 | 73.3333333333333333 
Chinese |    tutu 	   | 80 | 73.3333333333333333 
English |    matiler   | 75 | 75.0000000000000000 
English |    francs    | 90 | 75.0000000000000000 
English |    tutu 	   | 60 | 75.0000000000000000 
   Math |    francs    | 80 | 81.3333333333333333 
   Math |    matiler   | 99 | 81.3333333333333333 
   Math |    tutu	   | 65 | 81.3333333333333333 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

使用窗口函数很容易实现以上需求，如下所示：

mydb=> SELECT subject,stu_name, score, avg(score) OVER(PARTITION BY subject) FROM score; 
    subject | stu_name | score | avg 
------------+----------+-------+--------------------- 
	Chinese | 	francs |   70  | 73.3333333333333333 
	Chinese |  matiler |   70  | 73.3333333333333333 
	Chinese | 	  tutu |   80  | 73.3333333333333333 
	English |  matiler |   75  | 75.0000000000000000 
	English |   francs |   90  | 75.0000000000000000 
	English |     tutu |   60  | 75.0000000000000000 
	   Math |   francs |   80  | 81.3333333333333333 
	   Math |  matiler |   99  | 81.3333333333333333 
	   Math |     tutu |   65  | 81.3333333333333333 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

以上查询前三列来源于表score，第四列表示取课程的平均分，PARTITION BY subject表示根据字段subject进行分组。

2.3 row_number（）

row_number（）窗口函数对结果集分组后的数据标注行号，从1开始，如下所示：

mydb=> SELECT row_number() OVER (partition by subject ORDER BY score desc),* FROM score; 
	row_number| id | subject | stu_name | score 
--------------+----+---------+----------+------- 
			1 | 3  | Chinese | tutu 	| 80 
			2 | 1  | Chinese | francs   | 70 
			3 | 2  | Chinese | matiler  | 70 
			1 | 5  | English | francs   | 90 
			2 | 4  | English | matiler  | 75 
			3 | 6  | English | tutu 	| 60 
			1 | 8  | Math 	 | matiler 	| 99 
			2 | 7  | Math 	 | francs 	| 80 
			3 | 9  | Math 	 | tutu 	| 65 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

以上row_number（）窗口函数显示的是分组后记录的行号，如果不指定partition属性，row_number（）窗口函数显示表所有记录的行号，类似oracle里的ROWNUM，如下所示：

mydb=> SELECT row_number() OVER (ORDER BY id) AS rownum ,* FROM score; 
	rownum | id | subject | stu_name | score 
-----------+----+---------+----------+------- 
	 	 1 |  1 | Chinese |   francs | 70 
		 2 |  2 | Chinese |  matiler | 70 
		 3 |  3 | Chinese | 	tutu | 80 
		 4 |  4 | English |  matiler | 75 
		 5 |  5 | English |   francs | 90 
  		 6 |  6 | English | 	tutu | 60 
		 7 |  7 | 	 Math |   francs | 80 
		 8 |  8 | 	 Math |  matiler | 99 
		 9 |  9 | 	 Math | 	tutu | 65 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

2.4 rank（）

rank（）窗口函数和row_number（）窗口函数相似，主要区别为当组内某行字段值相同时，行号重复并且行号产生间隙 （手册上解释为gaps），如下所示：

mydb=> SELECT rank() OVER(PARTITION BY subject ORDER BY score),* FROM score; 
	rank | id | subject | stu_name | score 
---------+----+---------+----------+------- 
	   1 | 2 |  Chinese | matiler  | 70 
	   1 | 1 |  Chinese | francs   | 70 
	   3 | 3 |  Chinese | tutu     | 80 
	   1 | 6 |  English | tutu 	   | 60 
	   2 | 4 |  English | matiler  | 75 
	   3 | 5 |  English | francs   | 90 
	   1 | 9 | 	   Math | tutu 	   | 65 
	   2 | 7 |     Math | francs   | 80 
	   3 | 8 |     Math | matiler  | 99 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

以上示例中，Chinese课程前两条记录的score字段值都为 70，因此前两行的rank字段值为1，而第三行的rank字段值为 3，产生了间隙。

2.5 dense_rank（）

dense_rank（）窗口函数和rank（）窗口函数相似，主要区别为当组内某行字段值相同时，虽然行号重复，但行号不产生间隙，如下所示：

mydb=> SELECT dense_rank() OVER(PARTITION BY subject ORDER BY score),* FROM score; 
	dense_rank | id | subject | stu_name | score 
---------------+----+---------+----------+------- 
			1 | 2 | Chinese | matiler    | 70 
			1 | 1 | Chinese | francs     | 70 
			2 | 3 | Chinese | tutu       | 80 
			1 | 6 | English | tutu       | 60
			2 | 4 | English | matiler    | 75 
			3 | 5 | English | francs     | 90 
			1 | 9 | Math    | tutu 		 | 65 
			2 | 7 | Math    | francs 	 | 80 
			3 | 8 | Math    | matiler 	 | 99 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

以上示例中，Chinese课程前两行的rank字段值1，而第三行的rank字段值为2，没有产生间隙。

2.6 lag（）

另一重要窗口函数为lag（），可以获取行偏移offset那行某个字段的数据，语法如下：
lag(value anyelement [, offset integer [, default anyelement ]]) 其中：

• value指定要返回记录的字段。
• offset指行偏移量，可以是正整数或负整数，正整数表 示取结果集中向上偏移的记录，负整数表示取结果集中向下偏 移的记录，默认值为1。
• default是指如果不存在offset偏移的行时用默认值填充， default值默认为null。

例如，查询score表并获取向上偏移一行记录的id值，如下所示：

mydb=> SELECT lag(id,1)OVER(),* FROM score; 
	lag | id | subject | stu_name | score 
--------+----+---------+----------+------- 
        | 1 | Chinese | francs 	  | 70 
      1 | 2 | Chinese | matiler   | 70 
      2 | 3 | Chinese | tutu 	  | 80 
      3 | 4 | English | matiler   | 75 
      4 | 5 | English | francs 	  | 90 
      5 | 6 | English | tutu 	  | 60 
      6 | 7 | Math 	  | francs    | 80 
      7 | 8 | Math    | matiler   | 99 
	  8 | 9 | Math    | tutu 	  | 65 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

查询score表并获取向上偏移两行记录的id值，并指定默认值，代码如下所示：

mydb=> SELECT lag(id,2,1000)OVER(),* FROM score; 
	 lag | id | subject | stu_name | score 
---------+----+---------+----------+------- 
1000 | 1 | Chinese | francs | 70 
1000 | 2 | Chinese | matiler | 70 
   1 | 3 | Chinese | tutu | 80 
   2 | 4 | English | matiler | 75 
   3 | 5 | English | francs | 90 
   4 | 6 | English | tutu | 60 
   5 | 7 | 	  Math | francs | 80 
   6 | 8 | 	  Math | matiler | 99 
   7 | 9 | 	  Math | tutu | 65 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

以上演示了lag（）窗口函数取向上偏移记录的字段值， 将offset设置成负整数可以取向下偏移记录的字段值。

2.7 first_value（）

first_value（）窗口函数用来取结果集每一个分组的第一 行数据的字段值。 例如score表按课程分组后取分组的第一行的分数，如下所示：

mydb=> SELECT first_value(score) OVER( PARTITION BY subject ),* FROM score; 
first_value | id | subject | stu_name | score 
----------------+----+---------+----------+------- 
		 70 | 1 | Chinese | francs | 70 
		 70 | 2 | Chinese | matiler | 70 
		 70 | 3 | Chinese | tutu | 80 
		 75 | 4 | English | matiler | 75 
		 75 | 5 | English | francs | 90 
		 75 | 6 | English | tutu | 60 
		 80 | 7 | Math | francs | 80 
		 80 | 8 | Math | matiler | 99 
		 80 | 9 | Math | tutu | 65 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

通过first_value（）窗口函数很容易查询分组数据的最大值或最小值，例如score表按课程分组同时取每门课程的最高分，如下所示：

mydb=> SELECT first_value(score) OVER( PARTITION BY subject ORDER BY score desc),* FROM score; 
first_value | id | subject | stu_name | score 
----------------+----+---------+----------+------- 
         80 | 3 | Chinese | tutu | 80 
         80 | 1 | Chinese | francs | 70 
         80 | 2 | Chinese | matiler | 70 
         90 | 5 | English | francs | 90 
         90 | 4 | English | matiler | 75 
         90 | 6 | English | tutu | 60 
         99 | 8 | Math | matiler | 99 
         99 | 7 | Math | francs | 80 
         99 | 9 | Math | tutu | 65 
(9 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13

2.8 last_value（）

last_value（）窗口函数用来取结果集每一个分组的最后一 行数据的字段值。 例如score表按课程分组后取分组的最后一行的分数，如下所示：

mydb=> SELECT last_value(score) OVER( PARTITION BY subject ),* FROM score; 
last_value | id | subject | stu_name | score 
---------------+----+---------+----------+------- 
		80 | 1 | Chinese | francs | 70 
		80 | 2 | Chinese | matiler | 70 
		80 | 3 | Chinese | tutu | 80 
		60 | 4 | English | matiler | 75 
		60 | 5 | English | francs | 90 
		60 | 6 | English | tutu | 60 
		65 | 7 | Math | francs | 80 
		65 | 8 | Math | matiler | 99 
		65 | 9 | Math | tutu | 65 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

2.9 nth_value（）

nth_value（）窗口函数用来取结果集每一个分组的指定行 数据的字段值，语法如下所示： nth_value(value any, nth integer) 其中： value指定表的字段。
nth指定结果集分组数据中的第几行，如果不存在则返回空。例如score表按课程分组后取分组的第二行的分数，如下所示：

mydb=> SELECT nth_value(score,2) OVER( PARTITION BY subject ),* FROM score; 
	nth_value | id | subject | stu_name | score 
--------------+----+---------+----------+------- 
		   70 | 1 | Chinese | francs 	| 70 
		   70 | 2 | Chinese | matiler 	| 70 
		   70 | 3 | Chinese | tutu 		| 80 
		   90 | 4 | English | matiler 	| 75 
		   90 | 5 | English | francs 	| 90 
		   90 | 6 | English | tutu 		| 60 
		   99 | 7 | Math 	| francs 	| 80 
		   99 | 8 | Math 	| matiler 	| 99 
		   99 | 9 | Math 	| tutu 		| 65 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13

2.10 窗口函数别名的使用

如果SQL中需要多次使用窗口函数，可以使用窗口函数别名，语法如下： SELECT .. FROM .. WINDOW window_name AS ( window_definition ) [, ...] WINDOW属性指定表的别名为window_name，可以给 OVER属性引用，如下所示：

mydb=> SELECT avg(score) OVER(r),sum(score) OVER(r),* FROM SCORE WINDOW r as (PARTITION BY subject); 
					avg | sum | id | subject | stu_name | score 
------------------------+-----+----+---------+----------+-------

	73.3333333333333333 | 220 | 1 | Chinese | francs | 70 
	73.3333333333333333 | 220 | 2 | Chinese | matiler | 70 
	73.3333333333333333 | 220 | 3 | Chinese | tutu | 80 
	75.0000000000000000 | 225 | 4 | English | matiler | 75 
	75.0000000000000000 | 225 | 5 | English | francs | 90 
	75.0000000000000000 | 225 | 6 | English | tutu | 60 
	81.3333333333333333 | 244 | 7 | Math | francs | 80 
	81.3333333333333333 | 244 | 8 | Math | matiler | 99 
	81.3333333333333333 | 244 | 9 | Math | tutu | 65 
(9 rows) 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

以上介绍了常用的窗口函数，可根据实际应用场景使用相应的窗口函数。