内容简介:PostgreSQL数据库在SQL和NoSQL方面具有很多丰富的特性,本文将从这一部分主要介绍PostgreSQL在SQL方面的高级特性,例如WITH查询、批量插入、RETURNING返回修改的数据、UPSERT、数据抽样、聚合函数、窗口函数。WITH查询是PostgreSQL支持的高级SQL特性之一,这一特性常称为CTE(Common Table Expressions),WITH查询在复杂查询中定义一个辅助语句(可理解成在一个查询中定义的临时表),这一特性常用于复杂查询或递归查询应用场景
PostgreSQL数据库在 SQL 和NoSQL方面具有很多丰富的特性,本文将从 《PostgreSQL实战》 一书的“ 第4章 SQL高级特性”和“ 第9章 PostgreSQL的NoSQL特性”中摘选部分内容介绍。
这一部分主要介绍PostgreSQL在SQL方面的高级特性,例如WITH查询、批量插入、RETURNING返回修改的数据、UPSERT、数据抽样、聚合函数、窗口函数。
WITH查询
WITH查询是PostgreSQL支持的高级SQL特性之一,这一特性常称为CTE(Common Table Expressions),WITH查询在复杂查询中定义一个辅助语句(可理解成在一个查询中定义的临时表),这一特性常用于复杂查询或递归查询应用场景
先通过一个简单的CTE示例了解WITH查询,如下所示:
WITH t as ( SELECT generate_series(1,3) ) SELECT * FROM t;
执行结果如下:
generate_series ----------------- 1 2 3 (3 rows)
这个简单的CTE示例中,一开始定义了一条辅助语句t取数,之后在主查询语句中查询t,定义的辅助语句就像是定义了一张临时表,对于复杂查询如果不使用CTE,可以通过创建视图方式简化SQL。
WITH查询的一个重要属性是RECURSIVE,使用RECURSIVE属性可以引用自己的输出,从而实现递归,一般用于层次结构或树状结构的应用场景。
例如,存在一张包含如下数据的表。
id name fatherid 1 中国 0 2 辽宁 1 3 山东 1 4 沈阳 2 5 大连 2 6 济南 3 7 和平区 4 8 沈河区 4
使用PostgreSQL的WITH查询检索ID为7以及以上的所有父节点,如下:
WITH RECURSIVE r AS ( SELECT * FROM test_area WHERE id = 7 UNION ALL SELECT test_area.* FROM test_area, r WHERE test_area.id = r.fatherid ) SELECT * FROM r ORDER BY id;
查询结果如下:
id | name | fatherid ----+--------+---------- 1 | 中国 | 0 2 | 辽宁 | 1 4 | 沈阳 | 2 7 | 和平区 | 4 (4 rows)
批量插入
批量插入是指一次性插入多条数据,主要用于提升数据插入效率,PostgreSQL有多种方法实现批量插入。
方式一:INSERT INTO..SELECT.
过表数据或函数批量插入,这种方式大部分关系数据库都支持,语法如下:
INSERT INTO table_name SELECT …FROM source_table
方式二:INSERT INTO VALUES (),(),…()
这种批量插入方式为一条INSERT语句中通过VALUES关键字插入多条记录,通过一个例子就很容易理解,如下所示:
mydb=> CREATE TABLE tbl_batch3(id int4,info text); CREATE TABLE mydb=> INSERT INTO tbl_batch3(id,info) VALUES (1,'a'),(2,'b'),(3,'c'); INSERT 0 3
数据如下:
mydb=> SELECT * FROM tbl_batch3; id | info ----+------ 1 | a 2 | b 3 | c (3 rows)
这种批量插入方式非常独特,一条SQL插入多行数据,相比一条SQL插入一条数据的方式能减少和数据库的交互,减少数据库WAL(Write-Ahead Logging)日志的生成,提升插入效率,通常很少有开发人员了解PostgreSQL的这种批量插入方式。
方式三:COPY或\COPY元命令
COPY
或 \COPY
元命令能够将一定格式的文件数据导入到数据库中,相比INSERT命令插入效率更高,通常大数据量的文件导入一般在数据库服务端主机通过PostgreSQL超级用户使用COPY命令导入。
将文件tbl_batch4.txt的一千万数据导入到表中,如下所示:
mydb=# TRUNCATE TABLE pguser.tbl_batch4; TRUNCATE TABLE mydb=# COPY pguser.tbl_batch4 FROM '/home/pg10/tbl_batch4.txt'; COPY 10000000
RETURNING返回修改的数据
PostgreSQL的RETURNING特性可以返回DML修改的数据,具体为以下三个场景 ,INSERT语句后接RETURNING属性返回插入的数据,UPDATE语句后接RETURNING属性返回更新后的新值,DELETE语句后接RETURNING属性返回删除的数据,这个特性的优点在于不需要额外的SQL获取这些值,能够方便应用开发,接着通过示例演示。
RETURNING返回插入的数据
INSERT语句后接RETURNING属性返回插入的值,以下创建测试表,并返回已插入的整行数据。
mydb=> CREATE TABLE test_r1(id serial,flag char(1)); CREATE TABLE mydb=> INSERT INTO test_r1(flag) VALUES ('a') RETURNING *; id | flag ----+------ 1 | a (1 row) INSERT 0 1
RETURNING返回更新后数据
UPDATE后接RETURNING属性返回UPDATE语句更新后的值,如下所示:
mydb=> SELECT * FROM test_r1 WHERE id=1; id | flag ----+------ 1 | a (1 row) mydb=> UPDATE test_r1 SET flag='p' WHERE id=1 RETURNING *; id | flag ----+------ 1 | p (1 row) UPDATE 1
RETURNING返回删除的数据
DELETE后接RETURNING属性返回删除的数据,如下所示:
mydb=> DELETE FROM test_r1 WHERE id=2 RETURNING *; id | flag ----+------ 2 | b (1 row) DELETE 1
UPSERT
PostgreSQL的UPSERT特性是指 INSERT ... ON CONFLICT UPDATE
,用来解决在数据插入过程中数据冲突的情况,比如违反用户自定义约束,日志数据应用场景通常在事务中批量插入日志数据,如果其中有一条数据违反表上的约束,则整个插入事务将会回滚,PostgreSQL的UPSERT特性可解决这一问题。
接下来通过例子来理解UPSERT的功能,定义一张用户登录日志表并插入一条数据,如下,
mydb=> CREATE TABLE user_logins(user_name text primary key, login_cnt int4, last_login_time timestamp(0) without time zone); CREATE TABLE mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('francs',1); INSERT 0 1
在user_logins表user_name字段上定义主键,批量插入数据中如有重复会报错,如下所示:
mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('matiler',1),('francs',1); ERROR: duplicate key value violates unique constraint "user_logins_pkey" DETAIL: Key (user_name)=(francs) already exists.
上述SQL试图插入两条数据,其中matiler这条数据不违反主键冲突,而francs这条数据违反主键冲突,结果两条数据都不能插入。PostgreSQL的UPSERT可以处理冲突的数据,比如当插入的数据冲突时不报错,同时更新冲突的数据,如下所示:
mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('matiler',1),('francs',1) ON CONFLICT(user_name) DO UPDATE SET login_cnt=user_logins.login_cnt+EXCLUDED.login_cnt,last_login_time=now(); INSERT 0 2
上述INSERT语句插入两条数据,并设置规则:当数据冲突时更新登录次数字段login_cnt值加1,同时更新最近登录时间last_login_time,ON CONFLICT(user_name)定义冲突类型为user_name字段,DO UPDATE SET是指冲突动作,后面定义了一个UPDATE语句,注意上述SET命令中引用了user_loins表和内置表EXCLUDED,引用原表user_loins访问表中已存在的冲突记录,内置表EXCLUDED引用试图插入的值,再次查询表user_login,如下所示:
mydb=> SELECT * FROM user_logins ; user_name | login_cnt | last_login_time -----------+-----------+--------------------- matiler | 1 | francs | 2 | 2017-08-08 15:23:13 (2 rows)
一方面冲突的francs这条数据被更新了login_cnt和last_login_time字段,另一方面新的数据matiler记录已正常插入。
数据抽样
数据抽样(TABLESAMPLE)在数据处理方面经常用到,特别是当表数据量比较大时,随机查询表一定数量记录很常见,PostgreSQL早在9.5版时就已经提供了TABLESAMPLE数据抽样功能,9.5版前通常通过ORDER BY random()方式实现数据抽样,这种方式虽然在功能上满足随机返回指定行数据,但性能很低,如下:
mydb=> EXPLAIN ANALYZE SELECT * FROM user_ini ORDER BY random() LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------- Limit (cost=25599.98..25599.98 rows=1 width=35) (actual time=367.867..367.868 rows=1 loops=1) -> Sort (cost=25599.98..28175.12 rows=1030056 width=35) (actual time=367.866..367.866 rows=1 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 25kB -> Seq Scan on user_ini (cost=0.00..20449.70 rows=1030056 width=35) (actual time=0.012..159.569 rows=1000000 loops=1) Planning time: 0.083 ms Execution time: 367.909 ms (7 rows)
表user_ini数据量为100万,从100万随机取一条上述SQL执行时间为367ms,这种方法走了全表扫描和排序,效率非常低,当表数据量大时,性能几乎无法接受。
9.5版本以后PostgreSQL支持TABLESAMPLE数据抽样,语法为以下:
SELECT … FROM table_name TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]
sampling_method指抽样方法,主要有两种:SYSTEM和BERNOULLI,接下来详细介绍这两种抽样方式,argument指抽样百分比。
SYSTEM抽样方式
SYSTEM抽样方式为随机抽取表上数据块上的数据,理论上被抽样表的每个数据块被检索的概率是一样的,SYSTEM抽样方式基于数据块级别,后接抽样参数,被选中的块上的所有数据将被检索。
创建test_sample测试表,并插入150万数据,抽样因子设置成0.01,意味着返回1500000*0.01%=150条记录,执行如下SQL。
mydb=> EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE SYSTEM(0.01); QUERY PLAN ---------------------------------------------------------------------------------- Sample Scan on test_sample (cost=0.00..3.50 rows=150 width=45) (actual time=0.099..0.146 rows=107 loops=1) Sampling: system ('0.01'::real) Planning time: 0.053 ms Execution time: 0.166 ms (4 rows)
以上执行计划主要有两点,一方面走了Sample Scan扫描(抽样方式为SYSTEM),执行时间为0.166毫秒,性能较好,另一方面优化器预计访问150条记录,实际返回107条。
BERNOULLI抽样方式
BERNOULLI抽样方式随机抽取表的数据行,并返回指定百分比数据,BERNOULLI抽样方式基于数据行级别,理论上被抽样表的每行记录被检索的概率是一样的,因此BERNOULLI抽样方式抽取的数据相比SYSTEM抽样方式具有更好的随机性,但性能上相比SYSTEM抽样方式低很多,下面演示下BERNOULLI抽样方式,同样基于test_sample测试表。
设置抽样方式为BERNOULLI,抽样因子为0.01,如下所示。
mydb=> EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE BERNOULLI (0.01); QUERY PLAN ---------------------------------------------------------------------------------- Sample Scan on test_sample (cost=0.00..14020.50 rows=150 width=45) (actual time=0.025..22.541 rows=152 loops=1) Sampling: bernoulli ('0.01'::real) Planning time: 0.063 ms Execution time: 22.569 ms (4 rows)
从以上执行计划看出走了Sample Scan扫描(抽样方式为BERNOULLI),执行计划预计返回150条记录,实际返回152条,从返回的记录数来看,非常接近150条(1000000*0.01%),但执行时间却要22.569毫秒,性能相比SYSTEM抽样方式0.166毫秒差了136倍。
多次执行以下查询,查看返回记录数的变化,如下所示:
mydb=> SELECT count(*) FROM test_sample TABLESAMPLE BERNOULLI(0.01); count ------- 151 (1 row) mydb=> SELECT count(*) FROM test_sample TABLESAMPLE BERNOULLI(0.01); count ------- 147 (1 row)
从以上看出,BERNOULLI抽样方式返回的数据量非常接近抽样数据的百分比,而SYSTEM抽样方式数据返回以数据块为单位,被抽样的块上的所有数据都被返回,因此SYSTEM抽样方式的数据量返回的偏差较大。
这里演示了SYSTEM和BERNOULLI抽样方式,SYSTEM抽样方式基于数据块级别,随机抽取表数据块上的记录,因此这种方式抽取的记录的随机性不是很好,但返回的数据以数据块为单位,抽样性能很高,适用于抽样效率优先的场景,例如抽样大小为GB的日志表;而BERNOULLI抽样方式基于数据行,相比SYSTEM抽样方式所抽样的数据随机性更好,但性能相比SYSTEM差很多,适用于抽样随机性优先的场景,读者可根据实际应用场景选择抽样方式。
聚合函数
聚合函数可以对结果集进行计算,常用的聚合函数有avg()、sum()、min()、max()、count()等,本节将介绍PostgreSQL两个特殊功能的聚合函数并给出测试示例。
在介绍两个聚合函数之前,先来看一个应用场景,假如一张表有以下数据,如下:
country | city ---------+------ 中国 | 台北 中国 | 香港 中国 | 上海 日本 | 东京 日本 | 大阪 (5 rows)
要求得到如下结果集:
中国 台北,香港,上海 日本 东京,大阪
这个SQL读者想想如何写?
string_agg函数
首先介绍string_agg函数,此函数语法如下:
string_agg(expression, delimiter)
简单的说string_agg函数能将结果集某个字段的所有行连接成字符串,并用指定delimiter分隔符分隔,expression表示要处理的字符类型数据;参数的类型为(text, text) 或 (bytea, bytea),函数返回的类型同输入参数类型一致,bytea属于二进制类型,使用情况不多,我们主要介绍text类型输入参数,本节开头的场景正好可以用string_agg函数处理。
将city字段连接成字符串如下:
mydb=> SELECT string_agg(city,',') FROM city; string_agg -------------------------- 台北,香港,上海,东京,大阪 (1 row)
可见string_agg函数将输出的结果集连接成了字符串,并用指定的逗号分隔符分隔,回到本文开头的问题,通过以下SQL实现,如下所示:
mydb=> SELECT country,string_agg(city,',') FROM city GROUP BY country; country | string_agg ---------+---------------- 日本 | 东京,大阪 中国 | 台北,香港,上海
array_agg函数
array_agg函数和string_agg函数类似,最主要的区别为返回的类型为数组,数组数据类型同输入参数数据类型一致,array_agg函数支持两种语法,第一种如下:
array_agg(expression) –输入参数为任何非数组类型
输入参数可以是任何非数组类型,返回的结果是一维数组,array_agg函数将结果集某个字段的所有行连接成数组,执行以下查询。
mydb=> SELECT country,array_agg(city) FROM city GROUP BY country; country | array_agg ---------+------------------ 日本 | {东京,大阪} 中国 | {台北,香港,上海}
array_agg函数输出的结果为字符类型数组,其他无明显区别,使用array_agg函数主要优点在于可以使用数组相关函数和操作符。
窗口函数
PostgreSQL提供内置的窗口函数,例如row_num()、rank()、lag()等,除了内置的窗口函数外,聚合函数、自定义函数后接OVER属性也可作为窗口函数。
窗口函数的调用语法稍复杂,如下所示:
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
其中window_definition语法如下:
[ existing_window_name ] [ PARTITION BY expression [, ...] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ frame_clause ]
- OVER表示窗口函数的关键字。
- PARTITON BY 属性对查询返回的结果集进行分组,之后窗口函数处理分组的数据。
- ORDER BY 属性设定结果集的分组数据的排序。
row_number() 窗口函数
创建一张成绩表并插入测试数据,如下所示:
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);
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)
以上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)
avg() OVER()窗口函数
聚合函数后接OVER属性的窗口函数表示在一个查询结果集上应用聚合函数,本小节将演示avg()聚合函数后接OVER属性的窗口函数,此窗口函数用来计算分组后数据的平均值。
查询每名学生学习成绩并且显示课程的平均分,通常是先计算出课程的平均分,之后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)
使用窗口函数很容易实现以上需求,如下所示:
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)
以上查询前三列来源于表score,第四列表示取课程的平均分,PARTITION BY subject表示根据字段subject进行分组。
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)
以上示例中,Chinese课程前两条记录的score字段值都为70,因此前两行的rank字段值1,而第三行的rank字段值为3,产生了间隙。
dense_rank ()窗口函数
dense_rank ()窗口函数和rank ()窗口函数相似,主要区别为当组内某行字段值相同时,虽然行号重复,但行号不产生间隙(手册上解释为gaps),如下:
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)
以上示例中,Chinese课程前两行的rank字段值1,而第三行的rank字段值为2,没有产生间隙。
PostgreSQL还支持很多其它内置窗口函数,例如、lag()、first_values()、last_values()等,篇幅关系不再介绍。
总结
本篇文章主要介绍了PostgreSQL支持的一些高级SQL特性,例如WITH查询、批量插入、RETURNING返回DML修改的数据、UPSERT、数据抽样、聚合函数、窗口函数,了解这些功能能够简化SQL代码,提升开发效率,并且实现普通查询不容易实现的功能,希望通过阅读本章读者能够在实际工作中应用SQL高级特性,同时挖掘PostgreSQL的其他高级SQL特性。
PosgreSQL不仅是关系型数据库,同时支持NoSQL特性,关于PostgreSQL的NoSQL特性将在下篇文章中介绍。
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- 快速掌握ECMAScript 6新特性
- PostgreSQL用户应掌握的高级SQL特性
- 泛型编程的第一步,掌握模板的特性!
- 掌握这些Go语言特性,你的水平将提高N个档次(二)
- 掌握这些 Go 语言特性,你的水平将提高 N 个档次(二)
- 掌握这些 Go 语言特性,你的水平将提高 N 个档次(一)
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
The NSHipster Fake Book (Objective-C)
Mattt Thompson / NSHipster Portland, Oregon / 2014 / USD 19.00
Fake Books are an indispensable tool for jazz musicians. They contain the melody, rhythm, and chord changes for hundreds of standards, allowing a player to jump into any session cold, and "fake it" th......一起来看看 《The NSHipster Fake Book (Objective-C)》 这本书的介绍吧!