postgresql数据库sql特性有哪些
这篇文章主要介绍“postgresql数据库sql特性有哪些”,在日常操作中,相信很多人在postgresql数据库sql特性有哪些问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”postgresql数据库sql特性有哪些”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
--SQL高级特性--with查询,cte(commontableexpressions),with查询在复杂查询中定义一个辅助语句,这一特性常用于复杂查询或地柜查询应用场景,例如withtas(selectgenerate_series(1,3))select*fromt;--上述语句中,定义了辅助语句t取数,之后在主查询语句中查询t,复杂语句例如withregional_salesas(selectregion,sum(amount)astotal_salesfromordersgroupbyregion),to_regionsas(selectregionfromregional_saleswheretotal_sales>(selectsum(total_sales/10fromregional_sales))selectregion,product,sum(quantity)asproduct_units,sum(amount)asproduct_salesfromorderswhereregionin(selectregionfromtop_regions)groupbyregion,product;--递归查询cte,recursive属性可引用自己的输出,例如withrecursivet(x)as(select1unionselectx+1fromtwherex<5)selectsum(x)fromt;idnamefatherid1中国02辽宁13山东14沈阳25大连26济南37和平区48沈河区4--查询,例如id=7时,输出中国辽宁沈阳和平区withrecursiveras(select*fromtest_areawhereid=7unionallselecttest_area.*fromtest_area,twheretest_area.id=r.fatherid)selectstring_agg(name,'')from(selectnamefromrorderbyid)n;--批量插入insertinto..select..insertintovalues(),(),()copy/copy()命令--returning返回修改的数据,*可替换成某列,insert/delete/updateinsertintotest_r1(flag)values('a')returning*;--upsert,insert...onconflictupdate,用来解决插入过程中数据冲突问题,例如违反用户自定义约束,例如批量插入,如有违反,事物回滚insertintouser_logins(user_name,login_cnt)values('aaa',1),('bbb',1)onconflict(username)doupdatesetlogin_cnt=user_logins.login_cnt+EXCLUDED.login_cnt,last_login_time=now();--doupdateset可替换为donothing--数据抽样,9.5之前通过orderbyrandom()方式,性能低下,9.5之后语句如下:select...fromtable_nametablespamplesampling_method(argument[,...])[REPEATABLE(seed)]--sampling_method指抽样方法,主要两种,system和bernoulli,argument指抽样百分比--system方式,基于数据块级别,随机抽取select*fromtest_sampletablesamplesystem(0.01);--explainanalyze,表示实际执行sql,并显示执行计划和时间,planningtime表示sql语句解析生成执行计划的时间,executiontime表示sql实际执行时间--查看表占用的数据块数量selectrelname,relpagesfrompg_classwhererelname='test_sample';--ctid,隐藏列,表示逻辑数据块编号,第二位表示逻辑块上数据的逻辑编号selectctid,*fromtest_sampletablesamplesystem(0.01);--bernoulli抽样方式,随机抽取表的行数,性能相对低于system方式,但随机性更好select*fromtest_sampletablesamplebernoulli(0.01);##聚合函数--string_agg,主要将结果集下某个字段所有行连接成字符串,并指定delimiter分隔符分割,expression表示类型,主要是textstring_agg(expression,delimiter)selectstring_agg(city,',')fromcity;--array_agg返回数组,同上类似selectcountry,array_agg(city)fromcitygroupbycountry;##窗口函数--avg()over(),第四列根据subject分组,取课程平均分,selectsubject,stu_name,score,avg(score)over(partitionbysubject)fromscore;--row_number(),对结果集分组后的数据标注行号selectrow_number()over(partitionbysubjectorderbyscoredesc),*fromscore;--rank()表示当组内某行字段相同时,行号重复且行号产生间隙(例如,1,1,3)selectrank()over(partitionbysubjectorderbyscore),*fromscore;--demse_rank()表示当组内某行字段相同时,行号重复且行号不产生间隙(例如,1,1,2)selectdemse_rank()over(partitionbysubjectorderbyscore),*fromscore;--lag(),可以获取行偏移offset那行某个字段的数据lag(valueanyelement[,offsetinteger[,defaultanyelement]])--value指定要返回记录的字段,offset指行偏移量,可以是正数或负数,默认1,default是指如果不存在offset用默认填充,默认值nullselectlag(id,1)over(),*fromscore;selectlag(id,2,1000)over(),*fromscore;--first_value(),取结果集每一个分组第一行数据selectfirst_value(score)over(partitionbysubjectorderbyscoredesc),*fromscore;--以上按照课程分组,并取每门课程最高分--last_value(),最后一行数据--nth_value(),每组指定行的数据selectnth_value(score,2)over(partitionbysubject),*fromscore;--窗口函数别名,多次使用,可以使用别名select...from..windowwindow_nameas(window_definition),[,...]selectavg(score)over(r),sum(score)over(r),*fromscorewindowras(partitionbysubject);
到此,关于“postgresql数据库sql特性有哪些”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注亿速云网站,小编会继续努力为大家带来更多实用的文章!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。