ITPub博客

首页 > 数据库 > PostgreSQL > 【postgresl】PG数据库sql特性简单解析

【postgresl】PG数据库sql特性简单解析

原创 PostgreSQL 作者:xysoul_云龙 时间:2019-03-22 07:29:16 0 删除 编辑
--SQL高级特性
--with查询,cte(common table expressions),with查询在复杂查询中定义一个辅助语句,这一特性常用于复杂查询或地柜查询应用场景,例如 
with t as (select generate_series(1,3)) select * from t;
--上述语句中,定义了辅助语句t取数,之后在主查询语句中查询t,复杂语句例如
with regional_sales as (select region,sum(amount) as total_sales from orders group by region),to_regions as (select region from regional_sales where total_sales >(select sum(total_sales/10 from regional_sales))
select region,product,sum(quantity) as product_units,sum(amount) as product_sales from orders  
where region in (select region from top_regions) group by region,product; 
--递归查询cte,recursive属性可引用自己的输出,例如
with recursive t (x) as ( select 1 union select x+1 from t where x <5) select sum(x) from t;
id  name	fatherid
1	中国	0
2	辽宁	1
3	山东	1
4	沈阳	2
5	大连	2
6	济南	3
7	和平区	4
8	沈河区	4
--查询,例如id=7时,输出中国辽宁沈阳和平区
with recursive r as ( select * from test_area where id=7 union all select test_area.* from test_area,t where test_area.id=r.fatherid) select string_agg(name,'') from (select name from r order by id) n;
--批量插入
insert into .. select ..
insert into values(),(),()
copy/copy() 命令
--returning 返回修改的数据,* 可替换成某列,insert/delete/update
insert into test_r1(flag) values ('a') returning *;
--upsert,insert ... on conflict update,用来解决插入过程中数据冲突问题,例如违反用户自定义约束,例如批量插入,如有违反,事物回滚
insert into user_logins (user_name,login_cnt) values ('aaa',1),('bbb',1) on conflict(username) do update set 
login_cnt=user_logins.login_cnt+EXCLUDED.login_cnt,last_login_time=now();
--do update set 可替换为do nothing
--数据抽样,9.5之前通过order by random()方式,性能低下,9.5之后语句如下:
select ... from table_name tablespample sampling_method (argument [,...]) [REPEATABLE (seed)]
--sampling_method指抽样方法,主要两种,system和bernoulli,argument指抽样百分比
--system方式,基于数据块级别,随机抽取
select *  from test_sample tablesample system(0.01);
--explain analyze ,表示实际执行sql,并显示执行计划和时间,planning time表示sql语句解析生成执行计划的时间,execution time表示sql实际执行时间
--查看表占用的数据块数量
select relname,relpages from pg_class where relname='test_sample';
--ctid,隐藏列,表示逻辑数据块编号,第二位表示逻辑块上数据的逻辑编号
select ctid,* from test_sample tablesample system(0.01);
--bernoulli 抽样方式,随机抽取表的行数,性能相对低于system方式,但随机性更好
select * from test_sample tablesample bernoulli(0.01);
##聚合函数
--string_agg,主要将结果集下某个字段所有行连接成字符串,并指定delimiter分隔符分割,expression表示类型,主要是text
string_agg(expression,delimiter)
select string_agg(city,',') from city;
--array_agg 返回数组,同上类似
select country,array_agg(city) from city group by country;
##窗口函数
--avg() over(),第四列根据subject分组,取课程平均分,
select subject,stu_name,score,avg(score) over(partition by subject)from score;
--row_number(),对结果集分组后的数据标注行号
select row_number() over (partition by subject order by score desc),* from score;
--rank() 表示当组内某行字段相同时,行号重复且行号产生间隙(例如,1,1,3)
select rank() over (partition by subject order by score),* from score;
--demse_rank() 表示当组内某行字段相同时,行号重复且行号不产生间隙(例如,1,1,2)
select demse_rank() over (partition by subject order by score),* from score;
--lag(),可以获取行偏移offset那行某个字段的数据
lag(value anyelement [,offset integer [, default anyelement ]])
--value 指定要返回记录的字段,offset指行偏移量,可以是正数或负数,默认1,default是指如果不存在offset用默认填充,默认值null
select lag(id,1) over(),* from score;
select lag(id,2,1000) over(),* from score;
--first_value(),取结果集每一个分组第一行数据
select first_value(score) over(partition by subject order by score desc),* from score;
--以上按照课程分组,并取每门课程最高分
--last_value(),最后一行数据
--nth_value(),每组指定行的数据
select nth_value(score,2) over(partition by subject),* from score;
--窗口函数别名,多次使用,可以使用别名
select ... from .. window window_name as (window_definition),[,...]
select avg(score) over(r),sum(score) over(r),* from score window r as (partition by subject);


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29487349/viewspace-2639020/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
主要从事数据库相关工作,其他操作系统、中间件等也有涉及,热衷分享、开源,支持国产,期待中华民族全面的伟大复兴。近日骤然醒悟,欲在IT江湖中闯荡一番,如有幸在诸多侠客中留点踪迹,也算不虚此行。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博:文盲筱烨 微信公众号:筱烨视点

注册时间:2014-02-15

  • 博文量
    167
  • 访问量
    722997