postgresql數(shù)據(jù)庫sql特性有哪些

這篇文章主要介紹“postgresql數(shù)據(jù)庫sql特性有哪些”，在日常操作中，相信很多人在postgresql數(shù)據(jù)庫sql特性有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”postgresql數(shù)據(jù)庫sql特性有哪些”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

--SQL高級特性
--with查詢，cte(common table expressions),with查詢在復(fù)雜查詢中定義一個輔助語句，這一特性常用于復(fù)雜查詢或地柜查詢應(yīng)用場景,例如 
with t as (select generate_series(1,3)) select * from t;
--上述語句中，定義了輔助語句t取數(shù)，之后在主查詢語句中查詢t,復(fù)雜語句例如
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	濟(jì)南	3
7	和平區(qū)	4
8	沈河區(qū)	4
--查詢，例如id=7時，輸出中國遼寧沈陽和平區(qū)
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 返回修改的數(shù)據(jù),* 可替換成某列,insert/delete/update
insert into test_r1(flag) values ('a') returning *;
--upsert,insert ... on conflict update,用來解決插入過程中數(shù)據(jù)沖突問題，例如違反用戶自定義約束，例如批量插入，如有違反，事物回滾
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
--數(shù)據(jù)抽樣,9.5之前通過order by random()方式，性能低下,9.5之后語句如下：
select ... from table_name tablespample sampling_method (argument [,...]) [REPEATABLE (seed)]
--sampling_method指抽樣方法，主要兩種,system和bernoulli，argument指抽樣百分比
--system方式,基于數(shù)據(jù)塊級別，隨機抽取
select *  from test_sample tablesample system(0.01);
--explain analyze ,表示實際執(zhí)行sql，并顯示執(zhí)行計劃和時間，planning time表示sql語句解析生成執(zhí)行計劃的時間，execution time表示sql實際執(zhí)行時間
--查看表占用的數(shù)據(jù)塊數(shù)量
select relname,relpages from pg_class where relname='test_sample';
--ctid,隱藏列，表示邏輯數(shù)據(jù)塊編號，第二位表示邏輯塊上數(shù)據(jù)的邏輯編號
select ctid,* from test_sample tablesample system(0.01);
--bernoulli 抽樣方式，隨機抽取表的行數(shù)，性能相對低于system方式，但隨機性更好
select * from test_sample tablesample bernoulli(0.01);
##聚合函數(shù)
--string_agg,主要將結(jié)果集下某個字段所有行連接成字符串，并指定delimiter分隔符分割，expression表示類型，主要是text
string_agg(expression,delimiter)
select string_agg(city,',') from city;
--array_agg 返回數(shù)組,同上類似
select country,array_agg(city) from city group by country;
##窗口函數(shù)
--avg() over(),第四列根據(jù)subject分組，取課程平均分，
select subject,stu_name,score,avg(score) over(partition by subject)from score;
--row_number()，對結(jié)果集分組后的數(shù)據(jù)標(biāo)注行號
select row_number() over (partition by subject order by score desc),* from score;
--rank() 表示當(dāng)組內(nèi)某行字段相同時，行號重復(fù)且行號產(chǎn)生間隙（例如，1,1,3）
select rank() over (partition by subject order by score),* from score;
--demse_rank() 表示當(dāng)組內(nèi)某行字段相同時，行號重復(fù)且行號不產(chǎn)生間隙（例如，1,1,2）
select demse_rank() over (partition by subject order by score),* from score;
--lag(),可以獲取行偏移offset那行某個字段的數(shù)據(jù)
lag(value anyelement [,offset integer [, default anyelement ]])
--value 指定要返回記錄的字段，offset指行偏移量，可以是正數(shù)或負(fù)數(shù)，默認(rèn)1，default是指如果不存在offset用默認(rèn)填充，默認(rèn)值null
select lag(id,1) over(),* from score;
select lag(id,2,1000) over(),* from score;
--first_value()，取結(jié)果集每一個分組第一行數(shù)據(jù)
select first_value(score) over(partition by subject order by score desc),* from score;
--以上按照課程分組，并取每門課程最高分
--last_value()，最后一行數(shù)據(jù)
--nth_value(),每組指定行的數(shù)據(jù)
select nth_value(score,2) over(partition by subject),* from score;
--窗口函數(shù)別名，多次使用，可以使用別名
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);

到此，關(guān)于“postgresql數(shù)據(jù)庫sql特性有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>