前言

GaussDB最早就是基于PostgreSQL上进行修改的，后面才慢慢分家，因此想要学习GaussDB的语法，除了华为云官网的帮助文档外，还可以参考PostgreSQL(当前最新版本为15，发布于2022年12月10日)

华为云数据仓库服务GaussDB(DWS)官网文档地址

https://support.huaweicloud.cn/sqlreference-dws/dws_06_0003.html

PostgreSQL官网文档地址

https://www.postgresql.org/docs/15/functions-window.html

GaussDB(GWS)支持的数据类型

华为云官网中有列出 GuassDB 所支持的数据类型，其地址如下
https://support.huaweicloud.cn/sqlreference-dws/dws_06_0008.html

• 数值类型（TINYINT、SMALLINT、INTEGER、BINARY_INTEGER、BIGINT）
• 货币类型（money）
• 布尔类型（BOOLEAN）
• 字符类型（CHAR、CHARACTER、NCHAR、VARCHAR、VARCHAR2、NVARCHAR2、CLOB、TEXT）
• 二进制类型（BLOB、RAW、BYTEA）
• 日期/时间类型（DATE、TIME、TIMESTAMP、SMALLDATETIME、INTERVAL、reltime）
• 几何类型
• 网络地址类型
• 位串类型
• 文本搜索类型
• UUID类型
• JSON类型
• RoaringBitmap类型
• HLL数据类型
• 对象标识符类型
• 伪类型
• 列存表支持的数据类型
• XML类型

窗口函数

下面进入正题，工作中是否有遇到如下几种情况？

1. 根据区域，对门店的销售数据进行排序
2. 找出每个部门绩效前3的员工对齐进行奖励
3. 用户前后两次消费间隔的时间

对于这类问题，最好的解决办法，就是使用窗口函数。用好窗口函数，能极大成都的简化SQL语句的复杂程度，提升数据库查询效率。

窗口函数是sql中一类特别的函数，通过查询筛选出的行的某些部分，窗口调用函数实现了类似于聚合函数的功能。 但是两者又不同，通俗的讲，聚合函数是将结果合并成一行（每组一条数据），但是窗口函数是扫描所有的行，然后你的表有几行，结果就是有几行。

窗口函数可以调用的不仅有内建的窗口函数，还有任何用户定义的聚合函数和内建聚合函数，常见的min(),max(),count(),sum(),avg()就是可用在窗口函数中的内建聚合函数

分类

窗口函数根据其功能和特点，在业界有着许多种分类方式，常见的如下

根据窗口大小是否固定，可以将窗口函数分为两类

1. 静态窗口：无论多少条数据，窗口大小始终都是固定的
2. 动态窗口：不同的数据量，对应的窗口大小也不同

根据其特性，可分为

1. 序号函数：row_number, rank, dense_rank
2. 分布函数: percent_rank, cume_dist
3. 前后函数: lag, lead
4. 头尾函数: first_value, last_value
5. 整型函数: ntile, nth_value

根据业务功能及用途，也可以将窗口函数分为如下四类

1. 用于聚合计算的窗口函数: sum, count, avg
2. 用于分组排序的窗口函数: row_number, rank, dense_rank
3. 用于排名比较的窗口函数：percent_rank, cume_dist
4. 用于整数分区的窗口函数: ntile, nth_value
5. 用于获取前后的窗口函数: lag, lead
6. 用于获取头尾的窗口函数: first_value, last_value

建表并插入测试数据

下文中，会根据业务功能分类，分别来实践并讲解下这几种窗口函数的用途

但在讲解前，首先要做的就是建表

CREATE TABLE STUDENT (
	NAME VARCHAR(32),
   SUBJECT VARCHAR(32),
   SCORE INTEGER
)

建立了测试表后，我们就可以通过随机函数，插入测试数据(因为我对军事比较感兴趣，因此下文就以战国四大名将，作为测试数据 @^_^@)

INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('白起','马射', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('白起','平射', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('白起','负重', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('白起','摔跤', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('王翦','马射', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('王翦','平射', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('王翦','负重', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('王翦','摔跤', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('廉颇','马射', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('廉颇','平射', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('廉颇','负重', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('廉颇','摔跤', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('李牧','马射', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('李牧','平射', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('李牧','负重', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('李牧','摔跤', random()*100);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('神秘人','马射', 90);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('神秘人','平射', 95);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('神秘人','负重', 95);
INSERT INTO STUDENT (NAME, SUBJECT, SCORE) VALUES ('神秘人','摔跤', 100);

由于是逐条插入的，而非批量插入，guassdb要执行比较久

聚合计算

聚合函数有：sum, count, avg 等等。最开始，我执行如下函数

SELECT *， avg(score), sum(score) FROM STUDENT GROUP BY name

结果DAS提示我执行失败

• 失败原因：ERROR: column “student.subject” must appear in the GROUP BY clause or be used in an aggregate function
• 报错原因：选择显示的字段必须出现在在 GROUP BY 中
• 解决方案：在SELECT 的选项中加上 group by 的字段

修改后的SQL语句如下

SELECT name， avg(score), sum(score), count(1) FROM STUDENT GROUP BY name

聚合函数执行成功

分组排序

• row_number 不管你成绩是否一样，得到的就是按成绩排序后的行号
• rank和dense_rank 同样是对成绩排序，同样成绩名次一样。但不同的是rank会跳过并列的数据的个数往下编号，而dense_rank不会跳过任何序号，只会接着上一个序号往下编号

SELECT *，
	row_number() over (partition by name order by score asc),
    rank() over (partition by name order by score asc),
    dense_rank() over (partition by name order by score asc)
FROM STUDENT

排名比较

该类窗口函数的作用就是用来计算，当前行占总行数的百分比

• percent_rank = (rank - 1) / (总行数 - 1)
• cume_dist = 前面的行数或与当前行相同的行数 / 总行数

SELECT *，
	percent_rank() over (partition by name order by score asc),
    cume_dist() over (partition by name order by score asc)
FROM STUDENT

整数分区

1. ntile()函数表示将数据分成尽可能相等的分区，n传入多少就分成几组，若是n是1，则所有数据都在一组相当于没分区，若是n>=总的条数，那也不会报错，只是每条记录都是一组数据。

   • 这个函数可以用来解决随机分组的问题，例如将班级中的学生随机分成2组。
   • order by random():表示随机排序，也就是一个打乱的一个过程，这里不是必须的，可以删掉的
   • window_function() over ():over后面不加partition by的时候，表示不再分区，而是对整表进行操作。

SELECT *，
	ntile(2) over (partition by name order by random())
FROM STUDENT

2. nth_value()返回窗口中排名第n行的计算值。这个函数可以使用于在显示每个科目考得前n名的成绩的同学。以每科考前2名成绩的同学为例：

SELECT *，
	nth_value(score, 2) over (partition by subject order by score asc)
FROM STUDENT

获取前后

• lag 用来获取前一个值
• lead 用来获取后一个值

SELECT *，
	lag(score) over (partition by name order by score asc),
    lag(score) over (partition by name order by score desc),
    lead(score) over (partition by name order by score asc),
    lead(score) over (partition by name order by score desc)
FROM STUDENT

从实验可以看出，first_value和last_value的取值与排序方式无关！

获取头尾

• first_value 用来取到当前为止排第一的值
• last_value 用来取到当前位置排最后的值

SELECT *，
	first_value(score) over (partition by name order by score asc),
    first_value(score) over (partition by name order by score desc),
    last_value(score) over (partition by name order by score asc),
    last_value(score) over (partition by name order by score desc)
FROM STUDENT

从实验可以看出，first_value和last_value的取值与排序方式无关！

总结

以上就是窗口函数的所有基本概念了，读懂它们，能让你的SQL语句写的更漂亮，大大简化代码结构，提升数据库执行效率

【一起来玩转PB级数仓GaussDB(DWS)，分享你的技术经验与体验心得，赢开发者大礼包！】第19期有奖征文火热进行中！

此外，在云声平台提出您的宝贵建议，标题以【云驻计划-定向征文】开头，还有机会赢取额外奖励。