PostgreSQL從小白到專家，是從入門逐漸能力提升的一個(gè)系列教程，內(nèi)容包括對(duì)PG基礎(chǔ)的認(rèn)知、包括安裝使用、包括角色權(quán)限、包括維護(hù)管理、、等內(nèi)容，希望對(duì)熱愛PG、學(xué)習(xí)PG的同學(xué)們有幫助，歡迎持續(xù)關(guān)注CUUG PG技術(shù)大講堂。

第25講：窗口函數(shù)

內(nèi)容1 : 窗口函數(shù)如何定義

內(nèi)容2 : 專用窗口函數(shù)的種類

內(nèi)容3 : 掌握常用的窗口函數(shù)

內(nèi)容4 : 熟練使用聚合函數(shù)作為窗口函數(shù)

內(nèi)容5 : 窗口函數(shù)的框架來計(jì)算移動(dòng)平均

“窗口”的由來

窗口函數(shù)也稱為 OLAP 函數(shù)。為了讓大家快速形成直觀印象，才起了這樣一個(gè)容易理解的名稱。

通過 PARTITION BY 分組后的記錄集合稱為“窗口”。

從詞語意思的角度考慮，可能“組”比“窗口”更合適一些，但是在SQL中，“組”更多的是用來特指使用 GROUP BY 分割后的記錄集合，因此，為了避免混淆，使用PARTITION BY 時(shí)稱為窗口。

注意：可以不指定 PARTITION BY ，會(huì)將這個(gè)表當(dāng)成一個(gè)“大窗口”。

窗口函數(shù)應(yīng)用場景

應(yīng)用場景：?

(1)用于分區(qū)排序

(2)動(dòng)態(tài)Group By

(3)Top N

(4)累計(jì)計(jì)算

(5)層次查詢

窗口函數(shù)的種類

窗口函數(shù)大體可以分為以下兩種：

1、能夠作為窗口函數(shù)的聚合函數(shù)(SUM、AVG、COUNT、MAX、MIN)。

2、RANK、DENSE_RANK、ROW_NUMBER 等專用窗口函數(shù)。

上面第一種應(yīng)用中將聚合函數(shù)書寫在語法的“< 窗口函數(shù) >”中，就能夠當(dāng)作窗口函數(shù)來使用了。聚合函數(shù)根據(jù)使用語法的不同，可以在聚合函數(shù)和窗口函數(shù)之間進(jìn)行轉(zhuǎn)換。

上面第二種應(yīng)用中的函數(shù)是標(biāo)準(zhǔn) SQL 定義的 OLAP 專用函數(shù)，這里將其統(tǒng)稱為“專用窗口函數(shù)”。從這些函數(shù)的名稱可以很容易看出其 OLAP 的用途。

專用窗口函數(shù)

RANK 函數(shù)

計(jì)算排序時(shí)，如果存在相同位次的記錄，則會(huì)跳過之后的位次。

比如：有 3 條記錄排在第 1 位時(shí)：1 位、1 位、1 位、4 位……

ROW_NUMBER 函數(shù)

賦予唯一的連續(xù)位次。

比如：有 3 條記錄排在第 1 位時(shí)：1 位、2 位、3 位、4 位……

DENSE_RANK 函數(shù)

同樣是計(jì)算排序，即使存在相同位次的記錄，也不會(huì)跳過之后的位次。

比如：有 3 條記錄排在第 1 位時(shí)：1 位、1 位、1 位、2 位……

RANK()函數(shù)

--示例：

select ename,job,sal, rank() over (PARTITION BY job ORDER BY sal) as rankin from emp;

PARTITION BY 能夠設(shè)定分組和排序的對(duì)象范圍。本例中，為了按照工作進(jìn)行分組和排序，我們指定了job。

ORDER BY 能夠指定按照哪一列、何種順序進(jìn)行排序。為了按照工資的升序進(jìn)行排列，我們指定了sal 。

DENSE_RANK()函數(shù)

--示例

select ename,job,sal,DENSE_RANK() over (PARTITION by job ORDER BY SAL ) as dense_rankin from emp;

ROW_NUMBER 函數(shù)

--示例：

select ename,job,sal,ROW_NUMBER() over (PARTITION BY job ORDER BY SAL ) as unique_rankin from emp;

專用窗口函數(shù)使用技巧

使用 RANK 或 ROW_ NUMBER 時(shí)無需任何參數(shù)，只需要像 RANK ()或者 ROW_ NUMBER() 這樣保持括號(hào)中為空就可以了。這也是專用窗口函數(shù)通常的使用方式。

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;

窗口函數(shù)的適用范圍

使用窗口函數(shù)的位置卻有非常大的限制。更確切地說，窗口函數(shù)只能書寫在一個(gè)特定的位置。這個(gè)位置就是 SELECT 子句之中。反過來說，就是這類函數(shù)不能在WHERE 子句或者 GROUP BY 子句中使用。

為什么窗口函數(shù)只能在 SELECT 子句中使用呢?

在 DBMS內(nèi)部，窗口函數(shù)是對(duì) WHERE 子句或者 GROUP BY 子句處理后的“結(jié)果”進(jìn)行的操作。大家仔細(xì)想一想就會(huì)明白，在得到用戶想要的結(jié)果之前，即使進(jìn)行了排序處理，結(jié)果也是錯(cuò)誤的。在得到排序結(jié)果之后，如果通過 WHERE 子句中的條件除去了某些記錄，或者使用 GROUP BY 子句進(jìn)行了匯總處理，那好不容易得到的排序結(jié)果也無法使用了。

作為窗口函數(shù)使用的聚合函數(shù)

--計(jì)算price值的累計(jì)結(jié)果

select name,price, SUM(price) over (order by name) as current_sumfrom product;

--計(jì)算SAL值的累計(jì)結(jié)果

select ename,sal,SUM(sal) over (ORDER BY ename) as current_sumfrom emp;

所有的聚合函數(shù)都能用作窗口函數(shù)，其語法和專用窗口函數(shù)完全相同。

使用 SUM 函數(shù)時(shí)，并不像 RANK 或者 ROW _ NUMBER 那樣括號(hào)中的內(nèi)容為空，而是和之前我們學(xué)過的一樣，需要在括號(hào)內(nèi)指定作為匯總對(duì)象的列。

指定框架(匯總范圍)

select name,price,avg (price) over (order by name rows 2 preceding) as moving_avg from product;

這里我們使用了 ROWS (“行”)和 PRECEDING (“之前”)兩個(gè)關(guān)鍵字，將框架指定為“截止到之前 ~ 行”，因此“ ROWS 2 PRECEDING ”就是將框架指定為“截止到之前 2 行”，也就是將作為匯總對(duì)象的記錄限定為如下的“最靠近的 3 行”。

最靠近的3行=自身(當(dāng)前記錄)+ 之前第1行的記錄 + 之前第2行的記錄

計(jì)算移動(dòng)平均

由于框架是根據(jù)當(dāng)前記錄來確定的，因此和固定的窗口不同，其范圍會(huì)隨著當(dāng)前記錄的變化而變化。

這樣的統(tǒng)計(jì)方法稱為移動(dòng)平均(moving average)。由于這種方法在希望實(shí)時(shí)把握“最近狀態(tài)”時(shí)非常方便，因此常常會(huì)應(yīng)用在對(duì)股市趨勢(shì)的實(shí)時(shí)跟蹤當(dāng)中。

使用關(guān)鍵字 FOLLOWING (“之后”)替換 PRECEDING ，就可以指定“截止到之后 ~ 行”作為框架了。

計(jì)算移動(dòng)平均—同時(shí)指定前后行

select name,price,avg (price) over (order by name rows between 1 preceding and 1 following) as moving_avgfrom product;

兩個(gè)order by

OVER 子句中的 ORDER BY 只是用來決定窗口函數(shù)按照什么樣的順序進(jìn)行計(jì)算的，對(duì)結(jié)果的排列順序并沒有影響。在 SELECT 語句的最后，使用 ORDER BY子句進(jìn)行指定按照 ranking 列進(jìn)行排列，結(jié)果才會(huì)順序顯示，但是如果使用了，會(huì)打亂原本窗口函數(shù)出來的顯示結(jié)果。

有些 DBMS(PG) 也可以按照窗口函數(shù)的 ORDER BY 子句所指定的順序?qū)Y(jié)果進(jìn)行排序。

在一條 SELECT 語句中使用兩次 ORDER BY 會(huì)有點(diǎn)別扭，但是盡管這兩個(gè) ORDER BY 看上去是相同的，但其實(shí)它們的功能卻完全不同。

總結(jié)

專用窗口函數(shù) rank()

row_number()

dense_ranking()。

將聚合函數(shù)作為窗口函數(shù)使用---需要帶參數(shù)

框架的用法---計(jì)算移動(dòng)平均

以上就是【PostgreSQL從小白到專家】第25講 - 窗口函數(shù)? 的內(nèi)容，歡迎一起探討交流釘釘交流群：35，82，24，60，往期視頻及文檔內(nèi)容聯(lián)系： CUUG