窗口函数基于结果集进行计算，将计算出的结果合并到输出的结果集上，并返回多行。使用窗口函数能大幅度简化SQL代码。
gaussdb提供内置的窗口函数，例如row_num()、rank()、lag()等，除了内置的窗口函数外，聚合函数、自定义函数后接OVER属性也可以作为窗口函数。
1，创建测试表并插入数据。
postgres=# DROP TABLE IF EXISTS scores; NOTICE: table "scores" does not exist, skipping DROP TABLE postgres=# CREATE TABLE scores(id serial PRIMARY KEY,subject varchar(32),stu_name varchar(32),score numeric(3,0)); CREATE TABLE postgres=# INSERT INTO scores(subject,stu_name,score) VALUES(‘Chinese‘,‘user1‘,80),(‘Chinese‘,‘user2‘,90),(‘Chinese‘,‘user3‘,90),(‘math‘,‘user1‘,90),(‘math‘,‘user2‘,80),(‘math‘,‘user3‘,100),(‘English‘,‘user1‘,80),(‘English‘,‘user2‘,90),(‘English‘,‘user3‘,70); INSERT 0 9 postgres=# SELECT * FROM scores; id | subject | stu_name | score ----+---------+----------+------- 1 | Chinese | user1 | 80 2 | Chinese | user2 | 90 3 | Chinese | user3 | 90 4 | math | user1 | 90 5 | math | user2 | 80 6 | math | user3 | 100 7 | English | user1 | 80 8 | English | user2 | 90 9 | English | user3 | 70 (9 rows)
2，avg() OVER()计算分组后数据的平均值。
postgres=# SELECT subject,stu_name,score,avg(score) OVER(PARTITION BY subject) FROM scores; subject | stu_name | score | avg ---------+----------+-------+--------------------- Chinese | user1 | 80 | 86.6666666666666667 Chinese | user2 | 90 | 86.6666666666666667 Chinese | user3 | 90 | 86.6666666666666667 English | user3 | 70 | 80.0000000000000000 English | user1 | 80 | 80.0000000000000000 English | user2 | 90 | 80.0000000000000000 math | user1 | 90 | 90.0000000000000000 math | user2 | 80 | 90.0000000000000000 math | user3 | 100 | 90.0000000000000000 (9 rows)
3，row_number() OVER()对分组后的数据标注行号，从1开始。
postgres=# SELECT row_number() OVER(PARTITION BY subject ORDER BY score DESC),* FROM scores; row_number | id | subject | stu_name | score ------------+----+---------+----------+------- 1 | 2 | Chinese | user2 | 90 2 | 3 | Chinese | user3 | 90 3 | 1 | Chinese | user1 | 80 1 | 8 | English | user2 | 90 2 | 7 | English | user1 | 80 3 | 9 | English | user3 | 70 1 | 6 | math | user3 | 100 2 | 4 | math | user1 | 90 3 | 5 | math | user2 | 80 (9 rows)
4，rank() OVER()与row_number() OVER()类似主要区别是当组内某行字段值相同时，行号重复并且行号产生间隙。
postgres=# SELECT rank() OVER(PARTITION BY subject ORDER BY score DESC),* FROM scores; rank | id | subject | stu_name | score ------+----+---------+----------+------- 1 | 2 | Chinese | user2 | 90 1 | 3 | Chinese | user3 | 90 3 | 1 | Chinese | user1 | 80 1 | 8 | English | user2 | 90 2 | 7 | English | user1 | 80 3 | 9 | English | user3 | 70 1 | 6 | math | user3 | 100 2 | 4 | math | user1 | 90 3 | 5 | math | user2 | 80 (9 rows)
5，dense_rank() OVER()与rank() 类似，主要区别为当组内某行字段值相同时，虽然重复行号，但行号不产生间隙。
postgres=# SELECT dense_rank() OVER(PARTITION BY subject ORDER BY score DESC),* FROM scores; dense_rank | id | subject | stu_name | score ------------+----+---------+----------+------- 1 | 2 | Chinese | user2 | 90 1 | 3 | Chinese | user3 | 90 2 | 1 | Chinese | user1 | 80 1 | 8 | English | user2 | 90 2 | 7 | English | user1 | 80 3 | 9 | English | user3 | 70 1 | 6 | math | user3 | 100 2 | 4 | math | user1 | 90 3 | 5 | math | user2 | 80 (9 rows)
6，lag() OVER()可以获取行偏移offset那行字段的数据。
postgres=# SELECT LAG(id,-1) OVER(),* FROM scores; lag | id | subject | stu_name | score -----+----+---------+----------+------- 2 | 1 | Chinese | user1 | 80 3 | 2 | Chinese | user2 | 90 4 | 3 | Chinese | user3 | 90 5 | 4 | math | user1 | 90 6 | 5 | math | user2 | 80 7 | 6 | math | user3 | 100 8 | 7 | English | user1 | 80 9 | 8 | English | user2 | 90 | 9 | English | user3 | 70 (9 rows) postgres=# SELECT LAG(id,1,100) OVER(),* FROM scores;--不存在时指定默认值 lag | id | subject | stu_name | score -----+----+---------+----------+------- 100 | 1 | Chinese | user1 | 80 1 | 2 | Chinese | user2 | 90 2 | 3 | Chinese | user3 | 90 3 | 4 | math | user1 | 90 4 | 5 | math | user2 | 80 5 | 6 | math | user3 | 100 6 | 7 | English | user1 | 80 7 | 8 | English | user2 | 90 8 | 9 | English | user3 | 70 (9 rows)
7，first_value() OVER()用来取结果集每一个分组的第一行数据的字段值。
postgres=# SELECT first_value(score) OVER(PARTITION BY subject ORDER BY score DESC),* FROM scores; first_value | id | subject | stu_name | score -------------+----+---------+----------+------- 90 | 2 | Chinese | user2 | 90 90 | 3 | Chinese | user3 | 90 90 | 1 | Chinese | user1 | 80 90 | 8 | English | user2 | 90 90 | 7 | English | user1 | 80 90 | 9 | English | user3 | 70 100 | 6 | math | user3 | 100 100 | 4 | math | user1 | 90 100 | 5 | math | user2 | 80 (9 rows)
8，last_value() OVER()用来取结果集每一个分组的最后一行数据的字段值。
postgres=# SELECT last_value(score) OVER(PARTITION BY subject),* FROM scores; last_value | id | subject | stu_name | score ------------+----+---------+----------+------- 90 | 1 | Chinese | user1 | 80 90 | 2 | Chinese | user2 | 90 90 | 3 | Chinese | user3 | 90 90 | 9 | English | user3 | 70 90 | 7 | English | user1 | 80 90 | 8 | English | user2 | 90 100 | 4 | math | user1 | 90 100 | 5 | math | user2 | 80 100 | 6 | math | user3 | 100 (9 rows)
9，nth_value() OVER()用来取结果集每一个分组的指定行数据的字段值。
postgres=# SELECT nth_value(score,2) OVER(PARTITION BY subject),* FROM scores; nth_value | id | subject | stu_name | score -----------+----+---------+----------+------- 90 | 1 | Chinese | user1 | 80 90 | 2 | Chinese | user2 | 90 90 | 3 | Chinese | user3 | 90 80 | 9 | English | user3 | 70 80 | 7 | English | user1 | 80 80 | 8 | English | user2 | 90 80 | 4 | math | user1 | 90 80 | 5 | math | user2 | 80 80 | 6 | math | user3 | 100 (9 rows)
10，如果窗口函数需要多次使用，可以使用窗口函数别名。
postgres=# SELECT avg(score) OVER(r),sum(score) OVER(r),* FROM scores WINDOW r AS (PARTITION BY subject); avg | sum | id | subject | stu_name | score ---------------------+-----+----+---------+----------+------- 86.6666666666666667 | 260 | 1 | Chinese | user1 | 80 86.6666666666666667 | 260 | 2 | Chinese | user2 | 90 86.6666666666666667 | 260 | 3 | Chinese | user3 | 90 80.0000000000000000 | 240 | 9 | English | user3 | 70 80.0000000000000000 | 240 | 7 | English | user1 | 80 80.0000000000000000 | 240 | 8 | English | user2 | 90 90.0000000000000000 | 270 | 4 | math | user1 | 90 90.0000000000000000 | 270 | 5 | math | user2 | 80 90.0000000000000000 | 270 | 6 | math | user3 | 100 (9 rows)