postgresql数据库sql特性有哪些

共计 3713 个字符，预计需要花费 10 分钟才能阅读完成。

这篇文章主要介绍“postgresql 数据库 sql 特性有哪些”，在日常操作中，相信很多人在 postgresql 数据库 sql 特性有哪些问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”postgresql 数据库 sql 特性有哪些”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

--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);

到此，关于“postgresql 数据库 sql 特性有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！