共計 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 小編會繼續努力為大家帶來更多實用的文章！