大數據就業實戰培訓 Oracle就業實戰培訓
PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色權限、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。
第25講：窗口函數
內容1 : 窗口函數如何定義
內容2 : 專用窗口函數的種類
內容3 : 掌握常用的窗口函數
內容4 : 熟練使用聚合函數作為窗口函數
內容5 : 窗口函數的框架來計算移動平均
“窗口”的由來
窗口函數也稱為 OLAP 函數。為了讓大家快速形成直觀印象，才起了這樣一個容易理解的名稱。
通過 PARTITION BY 分組后的記錄集合稱為“窗口”。
從詞語意思的角度考慮，可能“組”比“窗口”更合適一些，但是在SQL中，“組”更多的是用來特指使用 GROUP BY 分割后的記錄集合，因此，為了避免混淆，使用PARTITION BY 時稱為窗口。
注意：可以不指定 PARTITION BY ，會將這個表當成一個“大窗口”。
窗口函數應用場景
應用場景：
(1)用于分區排序
(2)動態Group By
(3)Top N
(4)累計計算
(5)層次查詢
窗口函數的種類
窗口函數大體可以分為以下兩種：
1、能夠作為窗口函數的聚合函數(SUM、AVG、COUNT、MAX、MIN)。
2、RANK、DENSE_RANK、ROW_NUMBER 等專用窗口函數。
上面第一種應用中將聚合函數書寫在語法的“< 窗口函數 >”中，就能夠當作窗口函數來使用了。聚合函數根據使用語法的不同，可以在聚合函數和窗口函數之間進行轉換。
上面第二種應用中的函數是標準 SQL 定義的 OLAP 專用函數，這里將其統稱為“專用窗口函數”。從這些函數的名稱可以很容易看出其 OLAP 的用途。
專用窗口函數
RANK 函數
計算排序時，如果存在相同位次的記錄，則會跳過之后的位次。
比如：有 3 條記錄排在第 1 位時：1 位、1 位、1 位、4 位……
ROW_NUMBER 函數
賦予唯一的連續位次。
比如：有 3 條記錄排在第 1 位時：1 位、2 位、3 位、4 位……
DENSE_RANK 函數
同樣是計算排序，即使存在相同位次的記錄，也不會跳過之后的位次。
比如：有 3 條記錄排在第 1 位時：1 位、1 位、1 位、2 位……
RANK()函數
--示例：
select ename,job,sal, rank() over (PARTITION BY job ORDER BY sal) as rankin from emp;
PARTITION BY 能夠設定分組和排序的對象范圍。本例中，為了按照工作進行分組和排序，我們指定了job。
ORDER BY 能夠指定按照哪一列、何種順序進行排序。為了按照工資的升序進行排列，我們指定了sal 。
DENSE_RANK()函數
--示例
select ename,job,sal,DENSE_RANK() over (PARTITION by job ORDER BY SAL ) as dense_rankin from emp;
ROW_NUMBER 函數
--示例：
select ename,job,sal,ROW_NUMBER() over (PARTITION BY job ORDER BY SAL ) as unique_rankin from emp;
專用窗口函數使用技巧
使用 RANK 或 ROW_ NUMBER 時無需任何參數，只需要像 RANK ()或者 ROW_ NUMBER() 這樣保持括號中為空就可以了。這也是專用窗口函數通常的使用方式。
select ename,job,sal, RANK() OVER (PARTITION BY job ORDER BY sal) as rankin, DENSE_RANK() OVER (PARTITION BY job ORDER BY sal) as dense_rank, ROW_NUMBER() OVER (PARTITION BY job ORDER BY sal) as row_rankinfrom emp;
窗口函數的適用范圍
使用窗口函數的位置卻有非常大的限制。更確切地說，窗口函數只能書寫在一個特定的位置。這個位置就是 SELECT 子句之中。反過來說，就是這類函數不能在WHERE 子句或者 GROUP BY 子句中使用。
為什么窗口函數只能在 SELECT 子句中使用呢?
在 DBMS內部，窗口函數是對 WHERE 子句或者 GROUP BY 子句處理后的“結果”進行的操作。大家仔細想一想就會明白，在得到用戶想要的結果之前，即使進行了排序處理，結果也是錯誤的。在得到排序結果之后，如果通過 WHERE 子句中的條件除去了某些記錄，或者使用 GROUP BY 子句進行了匯總處理，那好不容易得到的排序結果也無法使用了。
作為窗口函數使用的聚合函數
--計算price值的累計結果
select name,price, SUM(price) over (order by name) as current_sumfrom product;
--計算SAL值的累計結果
select ename,sal,SUM(sal) over (ORDER BY ename) as current_sumfrom emp;
所有的聚合函數都能用作窗口函數，其語法和專用窗口函數完全相同。
使用 SUM 函數時，并不像 RANK 或者 ROW _ NUMBER 那樣括號中的內容為空，而是和之前我們學過的一樣，需要在括號內指定作為匯總對象的列。
指定框架(匯總范圍)
select name,price,avg (price) over (order by name rows 2 preceding) as moving_avg from product;
這里我們使用了 ROWS (“行”)和 PRECEDING (“之前”)兩個關鍵字，將框架指定為“截止到之前 ~ 行”，因此“ ROWS 2 PRECEDING ”就是將框架指定為“截止到之前 2 行”，也就是將作為匯總對象的記錄限定為如下的“最靠近的 3 行”。
最靠近的3行=自身(當前記錄)+ 之前第1行的記錄 + 之前第2行的記錄
計算移動平均
由于框架是根據當前記錄來確定的，因此和固定的窗口不同，其范圍會隨著當前記錄的變化而變化。
這樣的統計方法稱為移動平均(moving average)。由于這種方法在希望實時把握“最近狀態”時非常方便，因此常常會應用在對股市趨勢的實時跟蹤當中。
使用關鍵字 FOLLOWING (“之后”)替換 PRECEDING ，就可以指定“截止到之后 ~ 行”作為框架了。
計算移動平均—同時指定前后行
select name,price,avg (price) over (order by name rows between 1 preceding and 1 following) as moving_avgfrom product;
兩個order by
OVER 子句中的 ORDER BY 只是用來決定窗口函數按照什么樣的順序進行計算的，對結果的排列順序并沒有影響。在 SELECT 語句的最后，使用 ORDER BY子句進行指定按照 ranking 列進行排列，結果才會順序顯示，但是如果使用了，會打亂原本窗口函數出來的顯示結果。
有些 DBMS(PG) 也可以按照窗口函數的 ORDER BY 子句所指定的順序對結果進行排序。
在一條 SELECT 語句中使用兩次 ORDER BY 會有點別扭，但是盡管這兩個 ORDER BY 看上去是相同的，但其實它們的功能卻完全不同。
總結
專用窗口函數 rank()
row_number()
dense_ranking()。
將聚合函數作為窗口函數使用---需要帶參數
框架的用法---計算移動平均
以上就是【PostgreSQL從小白到專家】第25講 - 窗口函數 的內容，歡迎一起探討交流釘釘交流群：35，82，24，60，往期視頻及文檔內容聯系CUUG
