PostgreSQL>視窗函數的用法

来源:https://www.cnblogs.com/funnyzpc/archive/2018/07/22/9311281.html
-Advertisement-
Play Games

PostgreSQL之視窗函數的用法 轉載請註明出處:https://www.cnblogs.com/funnyzpc/p/9311281.html PostgreSQL的高級特性本準備三篇的(遞歸、視窗函數、JSON)，結果中間一直一直加班 和遺忘 拖到現在才寫到中篇，欸，加班真不是一件好事情。 ...


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的時候竟然沒發現這麼好用的東西，好遺憾)。

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

 1 DROP TABLE IF EXISTS "public"."products";
 2 CREATE TABLE "public"."products" (
 3     "id" varchar(10) COLLATE "default",
 4     "name" text COLLATE "default",
 5     "price" numeric,
 6     "uid" varchar(14) COLLATE "default",
 7     "type" varchar(100) COLLATE "default"
 8 )
 9 WITH (OIDS=FALSE);
10 
11 BEGIN;
12 INSERT INTO "public"."products" VALUES ('0006', 'iPhone X', '9600', null, '電器');
13 INSERT INTO "public"."products" VALUES ('0012', '電視', '3299', '4', '電器');
14 INSERT INTO "public"."products" VALUES ('0004', '辣條', '5.6', '4', '零食');
15 INSERT INTO "public"."products" VALUES ('0007', '薯條', '7.5', '1', '零食');
16 INSERT INTO "public"."products" VALUES ('0009', '速食麵', '3.5', '1', '零食');
17 INSERT INTO "public"."products" VALUES ('0005', '鉛筆', '7', '4', '文具');
18 INSERT INTO "public"."products" VALUES ('0014', '作業本', '1', null, '文具');
19 INSERT INTO "public"."products" VALUES ('0001', '鞋子', '27', '2', '衣物');
20 INSERT INTO "public"."products" VALUES ('0002', '外套', '110.9', '3', '衣物');
21 INSERT INTO "public"."products" VALUES ('0013', '圍巾', '93', '5', '衣物');
22 INSERT INTO "public"."products" VALUES ('0008', '香皂', '17.5', '2', '日用品');
23 INSERT INTO "public"."products" VALUES ('0010', '水杯', '27', '3', '日用品');
24 INSERT INTO "public"."products" VALUES ('0015', '洗髮露', '36', '1', '日用品');
25 INSERT INTO "public"."products" VALUES ('0011', '毛巾', '15', '1', '日用品');
26 INSERT INTO "public"."products" VALUES ('0003', '手錶', '1237.55', '5', '電器');
27 INSERT INTO "public"."products" VALUES ('0016', '繪圖筆', '15', null, '文具');
28 INSERT INTO "public"."products" VALUES ('0017', '汽水', '3.5', null, '零食');
29 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後面 ,這樣子>

 1 select 
 2     id,type,name,price,
 3     sum(price) over w1 類別金額合計,
 4     (sum(price) over (order by type))/sum(price) over() 類別總額占所有品類商品百分比,
 5     round(price/(sum(price) over w2),3) 子除類別百分比,
 6     rank() over w3 排名,
 7     sum(price) over() 金額總計
 8 from 
 9     products 
10 WINDOW 
11     w1 as (partition by type),
12     w2 as (partition by type rows between unbounded preceding and unbounded following),
13     w3 as (partition by type order by price desc)
14 ORDER BY 
15     type,price asc; 

 

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 如何進入單用戶模式(CentOS6.9)
    環境: CentOS6.9_64位 步驟1 虛擬機此時處在關機模式，開機後在下圖界面4秒倒計時結束前，按 步驟2 此時會進入下圖所示界面，接著按一下 步驟3 此時會進入下圖所示的界面，選擇第2項，接著按一下 步驟4 此時會進入下圖所示的界面，此時可以輸入字元，請輸入 ，輸入完畢之後按一下 鍵 1 也 ...
  • Docker 系列二（操作鏡像）.
    一、鏡像管理 1、拉取鏡像 -- Docker 鏡像倉庫地址 ：一般是 功能變數名稱或者IP[:埠號]。預設地址是 Docker Hub -- 倉庫名 ：兩段式名稱，即 用戶名/軟體名。對於Docker Hub，如果不給出用戶名，則預設為 library，也就是官方鏡像。 從下載過程中可以看到我們之前 提 ...
  • 提示"libc.so.6: version `GLIBC_2.14' not found"
    啟動php 提示"libc.so.6: version `GLIBC_2.14' not found"，原因可能是glibc版本太低，php使用了較高的glibc版本引起的 1，首先查看系統glibc的版本 $ strings /lib64/libc.so.6 |grep GLIBC_ GLIBC_ ...
  • win7 + ubuntu16.04LTS雙系統安裝（Legacy引導模式）
    前言 由於虛擬機運行在本人的老機器上實在是比較卡，又還不想換機器，再加上linux的向(ai)往(zhe)之(teng)情，所以決定裝個ubuntu（16.04LTS）雙系統，並計劃將ubuntu變為以後的主要使用系統。由於本人機器相對有年頭了，使用的是Legacy引導模式。（本文記於2018年6月 ...
  • Linux 文本處理命令
    最近在使用 BASH 進行處理 文本文件的時候，對於文本處理真的是力不從心，今天進行搜集一下linux 中文本處理相關的命令，這樣你在進行書寫shell 腳本的時候，就能寫出更好的方案。 命令搜集： 如果其中有命令還不是非常熟悉，請花時間看一看，多瞭解一下。必定事半功倍。 保持更新，轉載請標明出處。 ...
  • Mysql--函數
    函數定義：是一組預先定義好的SQL語句集合，理解為批處理語句。 ①提高代碼的重用性 ②簡化操作 ③減少編譯次數和減少和資料庫的連接次數，提高效率 ④安全性調高 函數和存儲過程的區別 函數：有且僅有一個返回值，適合作為數據處理後的一個標識。 存儲過程：返回值多種（可有可無）做批量插入和批量更新。 一、 ...
  • Oracle ORA12514 監聽程式當前無法識別連接描述符中請求的服務
    在連接資料庫的時候，有時會遇到一個“ORA12514：監聽程式當前無法識別連接描述符中請求的服務”的錯誤，這個錯誤其實就是資料庫動態註冊（關於動態註冊會在稍後講解）不生效，導致監聽器無法識別客戶端連接符中提供的服務名，從而拒絕建立資料庫連接時報的錯誤信息，所以就需要對監聽器配置做修改。 在這裡，還需 ...
  • Percona-Tookit工具包之pt-table-usage
    占座 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類