PostgreSQL数据库在SQL和NoSQL方面有很多丰富的特性。1.PostgreSQL高级SQL特性这部分主要介绍PostgreSQL在SQL方面的高级特性，如WITH查询、批量插入、RETURNING返回修改后的数据、UPSERT、数据采样、聚合函数、窗口函数等。1.WITH查询WITH查询是PostgreSQL支持的高级SQL特性之一。此功能通常称为CTE（公用表表达式）。WITH查询定义了复杂查询中的辅助语句（可以理解为在查询表中临时定义），该特性常用于复杂查询或递归查询应用场景。先通过一个简单的CTE例子来理解WITH查询，如下：WITHtas(SELECTgenerate_series(1,3))SELECT*FROMt;执行结果如下：generate_series----------------123(3rows)在这个简单的CTE例子中，一开始定义了一个辅助语句t，然后查询t在主查询语句中。定义辅助语句就像是定义一个临时表。对于复杂的查询，如果不使用CTE，可以通过创建视图来简化SQL。WITH查询的一个重要属性是RECURSIVE。使用RECURSIVE属性可以引用自己的输出来实现递归。一般用于层次结构或树状结构的应用场景。例如，有一张表包含以下数据：idnamefatherid1China02Liaoning13Shandong14Shenyang25Dalian26Jinan37HepingDistrict48ShenheDistrict4使用PostgreSQL的WITH查询检索ID为7及以上的所有父节点，如下：(SELECT*FROMtest_areaWHEREid=7UNIONALLSELECTtest_area.*FROMtest_area,rWHEREtest_area.id=r.fatherid)SELECT*FROMrORDERBYid;查询结果如下：id|name|fatherid----+--------+----------1|China|02|辽宁|14|沉阳|27|和平区|4(4rows)2.批量插入批量插入是指一次插入多条数据，主要用于提高数据插入的效率。PostgreSQL有多种实现批量插入的方法：方法一：INSERTINTO..SELECT。批量插入表数据或函数。大多数关系数据库都支持这种方法。语法如下：INSERTINTOtable_nameSELECT...FROMsource_table方法二：INSERTINTOVALUES(),(),...()这种批量插入方式是在INSERT语句中通过VALUES关键字插入多条记录。通过一个例子很容易理解，如下所示：mydb=>CREATETABLEtbl_batch3(idint4,infotext);创建表mydb=>INSERTINTOTtbl_batch3(id,info)VALUES(1,'a'),(2,'b'),(3,'c');INSERT03数据如下：mydb=>SELECT*FROMtbl_batch3;id|info----+------1|a2|b3|c(3rows)这种批量插入的方式非常独特。一条SQL插入多行数据，与一条SQL插入一条数据的方式相比，可以减少与数据库的交互。减少数据库WAL（Write-AheadLogging）日志的产生，提高插入效率。通常很少有开发者了解PostgreSQL的这种批量插入方式。方法三：COPY或\COPY元命令COPY或\COPY元命令可以将某种格式的文件数据导入数据库。与INSERT命令相比，插入效率更高。通常，大量数据的文件导入一般在数据库服务器主机上进行。PostgreSQL超级用户使用COPY命令导入。将文件tbl_batch4.txt的1000万条数据导入表中，如下：mydb=#TRUNCATETABLEpguser.tbl_batch4;TRUNCATETABLEmydb=#COPYpguser.tbl_batch4FROM'/home/pg10/tbl_batch4.txt';COPY100000003，RETURNING返回修改后的数据PostgreSQL的RETURNING特性可以返回DML修改后的数据，具体有以下三种场景，INSERT语句后跟RETURNING属性返回插入的数据，UPDATE语句后跟RETURNING属性返回更新后的新值，以及DELETE语句后面跟着RETURNING属性返回被删除的，这个特性的好处是不需要额外的SQL来获取这些值，方便应用程序开发，下面通过例子来演示。RETURNING返回插入的数据。INSERT语句后跟RETURNING属性以返回插入的值。下面创建一个测试表并返回已插入的整行数据。mydb=>CREATETABLEtest_r1(idserial,flagchar(1));CREATETABLEmydb=>INSERTITABLEtest_r1(flag)VALUES('a')RETURNING*;id|flag----+-----1|a(1row)INSERT01RETURNING返回更新后的数据UPDATE后跟RETURNING属性返回UPDATE语句的更新值，如下：mydb=>SELECT*FROMtest_r1WHEREid=1;id|flag----+------1|a(1row)mydb=>UPDATEtest_r1SETflag='p'WHEREid=1RETURNING*;id|flag----+-----1|p(1row)UPDATE1RETURNING返回删除的数据DELETE后跟RETURNING属性返回删除的数据，如下所示Show:mydb=>DELETEFROMtest_r1WHEREid=2RETURNING*;id|flag----+---2|b(1row)DELETE14,UPSERTPPostgreSQL的UPSERT特性是指INSERT...ONCONFLICTUPDATE，用到的解决数据插入时数据冲突的问题，比如违反用户定义的约束，日志数据应用场景通常在一个事务中批量插入日志数据，如果其中一条数据违反了对表的约束，整个插入transaction会回滚，PostgreSQL的UPSERT特性可以解决这个问题。下面通过一个例子来理解UPSERT的功能，定义一个用户登录日志表，插入一条数据，如下：login_cnt)VALUES('法郎',1);INSERT01在user_logins表的user_name字段上定义主键。如果批量插入数据有重复，会报错，如下图：mydb=>INSERTINTOuser_logins(user_name,login_cnt)VALUES('matiler',1),('francs',1);ERROR:duplicatekeyvalueviolatesuniqueconstraint"user_logins_pkey“详细信息：密钥（用户名）=（francs）已经存在。上面的SQL尝试插入两条数据，其中matiler的数据没有违反主键冲突，而francs的数据违反了主键冲突，结果两个数据都插入不了。PostgreSQL的UPSERT可以处理冲突数据。例如插入数据冲突时，不报错，同时更新冲突数据，如下所示：mydb=>INSERTINTOuser_logins(user_name,login_cnt)VALUES('matiler',1),('francs',1)ONCONFLICT(user_name)DOUPDATESETlogin_cnt=user_logins.login_cnt+EXCLUDED.login_cnt,last_login_time=now();INSERT02上面的INSERT语句插入了两条数据，并设置了规则：当数据冲突时，更新登录次数字段的login_cnt值并加1，同时更新最新的登录时间last_login_time，ONCONFLICT(user_name)定义冲突类型为user_name字段，DOUPDATESET引用冲突动作，后面定义UPDATE语句。注意上面的SET命令中引用了user_loins表和内置表EXCLUDED，而原来的表user_loins访问的是表中已有的冲突记录，内置表EXCLUDED引用的是试图被插入，再次查询user_login表，如下：mydb=>SELECT*FROMuser_logins;user_name|login_cnt|last_login_time----------+------------+-------------------matiler|1|francs|2|2017-08-0815:23:13(2rows)一方面，相互冲突的法郎数据有一方面更新了login_cnt和last_login_time字段，另一方面新的数据matiler记录已经正常插入。5、数据采样数据采样（TABLESAMPLE）在数据处理中经常用到，尤其是当表数据量比较大的时候，随机查询表中一定数量的记录是很常见的。PostgreSQL早在9.5版本就提供了TABLESAMPLE数据采样功能，在9.5版本之前，数据采样通常是通过ORDERBYrandom()来实现的。这种方法虽然功能上足够随机返回指定行数据，但是性能很低，如下：表user_ini的数据量为100万，从100万中随机抽取上述SQL的执行时间为367毫秒。这种方式采用全表扫描排序，效率很低。当表数据量很大时，性能几乎无法接受。PostgreSQL9.5版本后支持TABLESAMPLE数据采样。语法如下：SELECT...FROMtable_nameTABLESAMPLEsampling_method(argument[,...])[REPEATABLE(seed)]sampling_method指的是采样方法。有两种主要类型：SYSTEM和BERNOULLI。下面详细介绍两种抽样方式，参数是指抽样百分比。SYSTEM抽样法SYSTEM抽样法是随机选择表上数据块上的数据。理论上，采样表中每个数据块被检索到的概率是相同的。SYSTEM采样方式是根据数据块级别，后面跟着采样参数，选择的。将检索块上的所有数据。创建test_sample测试表，插入150万条数据。采样因子设置为0.01，即返回1500000*0.01%=150条记录，执行如下SQL：上面的执行计划主要有两点：一方面，SampleScan扫描（采样模式为SYSTEM），执行时间0.166毫秒，性能良好；另一方面，优化器期望访问150条记录，但实际上返回107条记录。BERNOULLI抽样方法BERNOULLI抽样方法随机选择表的数据行并返回指定百分比的数据。BERNOULLI采样方法基于数??据行级别。理论上，抽样表的每一行被检索到的概率是相同的，所以BERNOULLI抽样方法提取的数据与SYSTEM抽样方法相比，具有更好的随机性，但其性能远低于SYSTEM抽样方法.下面演示BERNOULLI抽样方法，同样是基于test_sample测试表。设置采样方式为BERNOULLI，采样因子为0.01，如下图：从上面的执行计划可以看出，SampleScan扫描（采样方式为BERNOULLI），执行计划预计返回150条记录，而实际返回的是152条，从返回的记录数来看，非常接近150条（1000000*0.01%），但是执行时间是22.569毫秒，性能比SYSTEM采样方式差了136倍0.166毫秒。多次执行如下查询，可以看到返回记录数的变化，如下：mydb=>SELECTcount(*)FROMtest_sampleTABLESAMPLEBERNOULLI(0.01);count------151(1row)mydb=>SELECTcount(*)FROMtest_sampleTABLESAMPLEBERNOULLI(0.01);count------147(1row)从上面可以看出，BERNOULLI采样方式返回的数据量非常接近采样数据的百分比，而数据SYSTEM采样方式返回的是以数据块为单位的，被采样的块返回的都是上面的数据，所以SYSTEM采样方式返回的数据量偏差较大。此处演示了SYSTEM和BERNOULLI采样方法。SYSTEM抽样方式是基于数据块级别，随机抽取表数据块上的记录。所以这种方式提取的记录随机性不是很好，但是返回的数据是以数据块为单位的。采样性能非常高，适用于采样效率优先的场景，比如采样大小为GB的日志表；而BERNOULLI采样方式是基于数据行，采样数据的随机性优于SYSTEM采样方式，但性能比SYSTEM采样方式差很多，适用于优先考虑采样随机性的场景，读者可根据实际应用场景选择采样方式。6.聚合函数聚合函数可以计算结果集。常用的聚合函数有avg()、sum()、min()、max()、count()等，本节将介绍PostgreSQL函数的两个特殊函数的聚合，并给出测试实例。在介绍这两个聚合函数之前，我们先来看一个应用场景。如果一个表有如下数据，如下：country|city------+------China|TaipeiChina|HongKongChina|ShanghaiJapan|TokyoJapan|Osaka(5rows)需要如下结果套：中国台北、香港、上海、日本、东京、大阪，请问这个SQL怎么写？string_agg函数首先介绍string_agg函数。该函数的语法如下：string_agg(expression,delimiter)简单地说，string_agg函数可以将结果集中某个字段中的所有行拼接成一个字符串，并用指定的delimiter分隔符分隔。字符型数据；参数类型为(text,text)或(bytea,bytea)，函数返回的类型与输入参数类型一致。bytea属于二进制类型，很少使用。我们主要介绍文本类型的输入参数。本节开头的场景只要用string_agg函数就可以处理。将城市字段连接成字符串如下：mydb=>SELECTstring_agg(city,',')FROMcity;string_agg------------------------Taipei,HongKong,Shanghai,Tokyo,Osaka(1row)可见string_agg函数将输出结果集拼接成一个字符串，并用指定的逗号分隔符分隔。回到本文开头的问题，它是通过如下SQL实现的，如下图：mydb=>SELECTcountry,string_agg(city,',')FROMcityGROUPBYcountry;country|string_agg---------+----------------日本|东京、大阪中国|台北、香港、上海array_agg函数array_agg函数与string_agg函数类似，主要区别在于返回类型为数组，且数组数据类型与入参数据类型一致。array_agg函数支持两种语法，第一种如下：array_agg(expression)--输入参数为任意非数组类型。输入参数可以是任意非数组类型，返回结果为一维数组。array_agg函数将结果集中某个字段的所有行连接成一个数组。执行以下查询：mydb=>SELECTcountry,array_agg(city)FROMcityGROUPBYcountry;country|array_agg--------+----------------日本|{Tokyo,Osaka}China|{Taipei,HongKong,Shanghai}array_agg函数的输出结果是字符类型的数组，其他没有明显区别。使用array_agg函数的主要优点是您可以使用与数组相关的函数和运算符。7.窗口函数PostgreSQL提供了内置的窗口函数，如row_num(),rank(),lag()等。除了内置的窗口函数外，还可以使用聚合函数和OVER属性后面的自定义函数用作窗口函数。窗口函数的调用语法稍微复杂一点，如下：function_name([expression[,expression...]])[FILTER(WHEREfilter_clause)]OVER(window_definition)其中window_definition的语法如下：[existing_window_name][PARTITIONBY表达式[,...]][ORDERBY表达式[ASC|描述|使用运算符][NULLS{FIRST|LAST}][,...]][frame_clause]OVER是表示窗口函数的关键字。PARTITONBY属性对查询返回的结果集进行分组，之后窗口函数处理分组的数据。ORDERBY属性设置结果集中分组数据的排序。row_number()窗口函数创建分数表并插入测试数据，如下：CREATETABLEscore(idserialprimarykey,subjectcharactervarying(32),stu_namecharactervarying(32),scorenumeric(3,0));INSERTINTOscore(subject,stu_name,score)VALUES('Chinese','francs',70);INSERTINTOScore(subject,stu_name,score)VALUES('Chinese','matiler',70);INSERTINTOScore(subject,stu_name,score)VALUES('Chinese','tutu',80);INSERTINTOScore(subject,stu_name,score)VALUES('English','matiler',75);INSERTINTOScore(subject,stu_name,score)VALUES('English','francs',90);INSERTINTOScore(subject,stu_name,score)VALUES('English','tutu',60);INSERTINTOScore(subject,stu_name,score)VALUES('Math','francs',80);INSERTINTOScore(subject,stu_name,score)VALUES('Math','matiler',99);INSERTINTOscore(subject,stu_name,score)VALUES('Math','tutu',65);avg()OVER()窗口函数聚合函数后接OVER属性的窗口函数表示为将聚合函数应用于查询结果集。本节将演示avg()聚合函数后跟OVER属性的窗口函数。该窗口函数用于计算分组数据的平均值。查询每个学生的学习成绩，显示课程的平均分。通常先计算课程的平均分，然后将分数表与平均分表关联起来，如下图：上面的需求很容易通过使用窗函数实现，如下图：的前三列上面的查询来自score表，第四列表示课程的平均分，PARTITIONBYsubject表示按照字段subject进行分组。rank()窗口函数rank()窗口函数与row_number()窗口函数类似，主要区别在于当组中某一行的字段值相同时，行号重复，该行number产生一个gap（手册中解释为gaps），如下：上面的例子中，中文课程的前两条记录的score字段值为70，所以前两行的rank字段值为1，而第三行的rank字段值为3，产生了差距。dense_rank()窗函数dense_rank()窗函数与rank()窗函数类似，主要区别在于当组中某一行的字段值相同时，虽然行号重复，但行号不产生空隙（手册中解释为空隙），如下：上例中中文课程前两行rank字段值为1，而第三行rank字段值为2，且没有差距。PostgreSQL还支持很多其他内置的窗口函数，比如lag()、first_values()、last_values()等，这里不再介绍空格关系。2.小??结本文主要介绍PostgreSQL支持的一些SQL高级特性，如WITH查询、批量插入、RETURNING返回DML修改数据、UPSERT、数据采样、聚合函数、窗口函数等，理解这些函数可以简化SQL代码，提高开发效率，实现普通查询不易实现的功能。希望阅读本章后，您能够在实际工作中应用SQL的高级特性，同时探索PostgreSQL的其他SQL高级特性。PostgreSQL不仅是关系型数据库，还支持NoSQL特性。我们将在下一篇文章中介绍PostgreSQL的NoSQL特性。
