PostgreSQL>視窗函式的用法

PostgreSQL之視窗函式的用法

 

轉載請註明出處:https://www.cnblogs.com/funnyzpc/p/9311281.html

 

　　PostgreSQL的高階特性本準備三篇的(遞迴、視窗函式、JSON)，結果中間一直一直加班 和遺忘 拖到現在才寫到中篇，欸，加班真不是一件好事情。

　　談談我對加班的看法吧=> 如果加班能控制在一個小時內，這樣會比較好(當然如果不加班的話更好)，偶爾適當的加班能提高工作進度，對創業公司來說尤為重要；但，糟糕的地方也不少，加班時間長了容易造成思維緩慢，這對腦子本來就不快的人來說傷害尤其的大(我就是個例子)，也容易造成頸椎病、高血壓、過勞。。。等等可怕的疾病，尤其還是做IT的一定要注意到這個問題，以上這些話可能有童鞋不會在意，那我就在這裡說說我見過的真實的例子，我上一家公司的CTO有比較嚴重的脊椎病，(他說)坐的時間久了背部尤其的難受，上一家公司總監也經常加班，可能再加上本身體質的原因，心臟現在已經裝上了起搏器，同樣是上一家公司，我的一同事，也就比我大三歲左右，頭髮已經有相當部分白了哎，每見到這樣的事兒都很難受，人一輩子，如果沒有足夠的時間去關注生活，關注健康，我們生活內容還剩下什麼？

　　這次我就簡單的講講PostgreSQL的高階特性>視窗函式

　　我先用表格列出PostgreSQL裡面的視窗函式，(源文件在這裡>http://www.postgres.cn/docs/9.3/functions-window.html，推薦去postgre的中文社群看看)

 　　講第一個問題之前我先扔出一個需求>如何給查詢出來的資料新增一列序號，用最簡單的方式實現？

　　　　Oracle>使用rownum快速生成

　　　　MySql>使用變數定義:(@i:=@i+1)  as row

　　　　SqlServer>通過定義儲存過程的方式

　　　　PostgreSQL>通過函式generate_series(start_value,end_value)

　　額，以上方式我大概都用過，對於Oracle的方式雖然語句簡單，但是涉及到排序的時候可就亂了，mysql的方式也還算可以，但是這樣並沒有通用性，子查詢的時候會相當麻煩,同時個人覺得這更像是儲存過程和sql的結合體，也破壞了Sql本該有的形式，PostgreSQL的方式雖然不錯，但是總要指定起始和終止值，這個在生成測試資料的時候還好用，具體業務開發用起來可就麻煩多了；這裡，當然有更好的實現方式>視窗函式，這個屬性在主流的資料庫系統中都有實現(以前用oracle的時候竟然沒發現這麼好用的東西，好遺憾)。

　　這裡我先放出表結構語句:

DROP TABLE IF EXISTS "public"."products";
CREATE TABLE "public"."products" (
    "id" varchar(10) COLLATE "default",
    "name" text COLLATE "default",
    "price" numeric,
    "uid" varchar(14) COLLATE "default",
    "type" varchar(100) COLLATE "default"
)
WITH (OIDS=FALSE);

BEGIN;
INSERT INTO "public"."products" VALUES ('0006', 'iPhone X', '9600', null, '電器');
INSERT INTO "public"."products" VALUES ('0012', '電視', '3299', '4', '電器');
INSERT INTO "public"."products" VALUES ('0004', '辣條', '5.6', '4', '零食');
INSERT INTO "public"."products" VALUES ('0007', '薯條', '7.5', '1', '零食');
INSERT INTO "public"."products" VALUES ('0009', '方便麵', '3.5', '1', '零食');
INSERT INTO "public"."products" VALUES ('0005', '鉛筆', '7', '4', '文具');
INSERT INTO "public"."products" VALUES ('0014', '作業本', '1', null, '文具');
INSERT INTO "public"."products" VALUES ('0001', '鞋子', '27', '2', '衣物');
INSERT INTO "public"."products" VALUES ('0002', '外套', '110.9', '3', '衣物');
INSERT INTO "public"."products" VALUES ('0013', '圍巾', '93', '5', '衣物');
INSERT INTO "public"."products" VALUES ('0008', '香皂', '17.5', '2', '日用品');
INSERT INTO "public"."products" VALUES ('0010', '水杯', '27', '3', '日用品');
INSERT INTO "public"."products" VALUES ('0015', '洗髮露', '36', '1', '日用品');
INSERT INTO "public"."products" VALUES ('0011', '毛巾', '15', '1', '日用品');
INSERT INTO "public"."products" VALUES ('0003', '手錶', '1237.55', '5', '電器');
INSERT INTO "public"."products" VALUES ('0016', '繪圖筆', '15', null, '文具');
INSERT INTO "public"."products" VALUES ('0017', '汽水', '3.5', null, '零食');
COMMIT;

這我先用第一個函式row_number() ,一句即可實現>

  select type,name,price,row_number() over(order by price asc) as idx from products ;

結果>

用視窗函式的好處不僅僅可實現序號列，還可以在over()內按指定的列排序,上圖是按照price列升序。

　　這裡，對於以上提到的一個問題，根據上面的資料 我再做個擴充>如果需要在類別(type)內按照價格(price) 升序排列(就是在類別內做排序)，該怎麼做呢？

　　當然也很簡單，只需要在視窗(over())中宣告分隔方式 Partition .

　　分類排序序號，row_number() 實現>

 select type,name,price,row_number() over(PARTITION by type order by price asc) as idx from products ;

　　查詢結果>

 　　上面的問題這裡需求完美實現，額，這裡其實還可以做個擴充，你可以注意到零食類別內的 方便麵和汽水價格是一樣的，如何將零食和汽水並列第一呢？答案是：用視窗函式>rank()

　　 分類排序序號並列, rank() 實現>

 SELECT type,name,price,rank() over(partition by type order by price asc) from products;

　　SQL輸出>

　　需求又完美的實現了，但，注意到沒，零食類別中的第三個 辣條 排到第三了，如果這裡需要在類別裡面能保持序號不重不少(將辣條排名至第二)，如何實現呢？答案>使用視窗函式 dense_rank()

　　分類排序序號並列順序，dense_rank() 實現>

 SELECT type,name,price,dense_rank() over(partition by type order by price asc) from products;

　　SQL輸出>

　　OK,以上的幾個視窗函式已經能實現大多數業務需求了，如果有興趣可以看看一些特殊業務可能用到的功能，比如說如何限制序號在0到1之間排序呢？

　　限制序號在0~1之間(0作為第一個序),視窗函式 percernt_rank() >

 SELECT type,name,price,percent_rank() over(partition by type order by price asc) from products;

　 SQL語句輸出>

注意:上面的percernt_rank()函式預設是從0開始排序的，如果需要使用相對0~1之間的排名，需要這樣：

　　限制序號在0~1之間相對排名，視窗函式 cume_dist() 實現>

SELECT type,name,price,cume_dist() over(partition by type order by price asc) from products;

　　SQL語句輸出>

注意:上面的序號是相對於0開始排序的。

　　對於排序序號還可以限制最大序號，這樣做：

　　限制最大序號為指定數字序號 ntile(val1) 實現 >

SELECT type,name,price,ntile(2) over(partition by type order by price asc) from products;

　　SQL語句輸出 >

 

　　視窗函式還可以實現在子分類排序的情況下取偏移值，這樣實現>

　　獲取到排序資料的每一項的偏移值(向下偏移) , lag(val1,val2,val3) 函式實現>

SELECT id,type,name,price,lag(id,1,'') over(partition by type order by price asc) as topid from products;

　　SQL語句輸出 >

注意:函式lag(val1,val2,val3) 中的三個引數分別為->(輸出的上一條記錄的欄位,偏移值,無偏移值的預設值)；以上這裡的偏移值為1，偏移欄位為id，無偏移預設值為空('')

　　若獲取資料項偏移值(向上偏移) , lead(val1,val2,val3)>

SELECT id,type,name,price,lead(id,1,'') over(partition by type order by price asc) as downid from products;

 　 SQL 語句輸出 >

　　當然，視窗函式還可以實現每個子類排序中的第一項的某個欄位的值，可以這樣實現:

　　獲取分類子項排序中的第一條記錄的某個欄位的值， first_value(val1) 實現>

 SELECT id,type,name,price,first_value(name) over(partition by type order by price asc) from products;

　　SQL語句輸出>

注意:以上函式取的是排序子類記錄中的第一條記錄的name欄位。

　　當然也可以向下取分類排序中的最後一條記錄的某個欄位, last_value(val1)實現>

 SELECT id,type,name,price,last_value(name) over(partition by type order by price range between unbounded preceding and unbounded following) from products; -- order by type asc ;-- ,price asc;

　　SQL 語句輸出 >

額，這裡需要說明的是，當取分類在最後一條記錄的時候 自然排序下不可以在over() 使用排序欄位，不然取得的值為相對於當前記錄的值，故這裡按價格(price) 升序的時候指定 排序欄位 -> range between unbounded preceding and unbounded following

　　視窗函式還能在分類排序下取得指定序號記錄的某個欄位，這樣：

　　取得排序欄位專案中指定序號記錄的某個欄位值, nth_value(val1,val2)>

SELECT id,type,name,price,nth_value(name,2) OVER(partition by type order by price range between unbounded preceding and unbounded following ) from products;

　　SQL語句輸出 >

　　額，視窗函式在單獨使用的時候能省略很多不必要的查詢 ，比如子查詢、聚合查詢，當然視窗函式能做得更多(配合聚合函式使用的時候) ,額，這裡我給出一個示例 >

　　SQL查詢語句 ，視窗函式+聚合函式 實現 >

sum(price) over (partition by type) 類別金額合計,
(sum(price) over (order by type))/sum(price) over() 類別總額佔所有品類商品百分比,
round(price/(sum(price) over (partition by type rows between unbounded preceding and unbounded following)),3) 子除類別百分比,
rank() over (partition by type order by price desc) 排名,
sum(price) over() 金額總計
from products ORDER BY type,price asc;

　　SQL 語句輸出>

 上面的語句看起來會有點兒暈，查詢語句子項就像是在輸出引數項裡面直接寫子查詢的感覺，事實上為使語句有更好的可讀性，視窗條件可以放在from後面 ,這樣子>

select 
    id,type,name,price,
    sum(price) over w1 類別金額合計,
    (sum(price) over (order by type))/sum(price) over() 類別總額佔所有品類商品百分比,
    round(price/(sum(price) over w2),3) 子除類別百分比,
    rank() over w3 排名,
    sum(price) over() 金額總計
from 
    products 
WINDOW 
    w1 as (partition by type),
    w2 as (partition by type rows between unbounded preceding and unbounded following),
    w3 as (partition by type order by price desc)
ORDER BY 
    type,price asc; 

 

現在是 2018-07-22 21:59:31 ，各位晚安~