PostgreSQL數(shù)據(jù)庫(kù)中窗口函數(shù)的語(yǔ)法與使用
什么是窗口函數(shù)?
一個(gè)窗口函數(shù)在一系列與當(dāng)前行有某種關(guān)聯(lián)的表行上執(zhí)行一種計(jì)算。這與一個(gè)聚集函數(shù)所完成的計(jì)算有可比之處。但是窗口函數(shù)并不會(huì)使多行被聚集成一個(gè)單獨(dú)的輸出行,這與通常的非窗口聚集函數(shù)不同。取而代之,行保留它們獨(dú)立的標(biāo)識(shí)。在這些現(xiàn)象背后,窗口函數(shù)可以訪問(wèn)的不僅僅是查詢結(jié)果的當(dāng)前行。
- 可以訪問(wèn)與當(dāng)前記錄相關(guān)的多行記錄;
- 不會(huì)使多行聚集成一行, 與聚集函數(shù)的區(qū)別;
窗口函數(shù)語(yǔ)法
窗口函數(shù)跟隨一個(gè) OVER 子句, OVER 子句決定究竟查詢中的哪些行被分離出來(lái)由窗口函數(shù)處理。
可以包含分區(qū) (PARTITION BY) 和排序 (ORDER BY) 指令, 這二者都是可選的。
window_func() OVER([PARTITION BY field] [ORDER BY field])
如果沒(méi)有指定 PARTITION BY 和 ORDER BY 指令, 則等同于聚合函數(shù), 對(duì)全部數(shù)據(jù)進(jìn)行計(jì)算。
PARTITION BY 子句將查詢的行分組成為分區(qū), 窗口函數(shù)會(huì)獨(dú)立地處理它們。PARTITION BY 工作起來(lái)類似于一個(gè)查詢級(jí)別的 GROUP BY 子句, 不過(guò)它的表達(dá)式總是只是表達(dá)式并且不能是輸出列的名稱或編號(hào)。 如果沒(méi)有 PARTITION BY, 該查詢產(chǎn)生的所有行被當(dāng)作一個(gè)單一分區(qū)來(lái)處理。
ORDER BY 子句決定被窗口函數(shù)處理的一個(gè)分區(qū)中的行的順序。 它工作起來(lái)類似于一個(gè)查詢級(jí)別的 ORDER BY 子句, 但是同樣不能使用輸出列的名稱或編號(hào)。 如果沒(méi)有 ORDER BY, 行將被以未指定的順序被處理。
PostgreSQL 中的聚合函數(shù)也可以作為窗口函數(shù)來(lái)使用
除了這些內(nèi)置的窗口函數(shù)外,任何內(nèi)建的或用戶定義的通用或統(tǒng)計(jì)聚集(也就是有序集或假想集聚集除外)都可以作為窗口函數(shù)。僅當(dāng)調(diào)用跟著OVER子句時(shí),聚集函數(shù)才會(huì)作為窗口函數(shù);否則它們作為非窗口的聚集并為剩余的集合返回單行。
窗口函數(shù)示例
員工工資 (emp_salary) 表結(jié)構(gòu)如下:
SELECT emp_no, dep_name, salary FROM public.emp_salary order by dep_name, emp_no;
| emp_id | dep_name | salary |
|---|---|---|
| 7 | develop | 4200 |
| 8 | develop | 6000 |
| 9 | develop | 4500 |
| 10 | develop | 5200 |
| 11 | develop | 5200 |
| 2 | personnel | 3900 |
| 5 | personnel | 3500 |
| 1 | sales | 5000 |
| 3 | sales | 4800 |
| 4 | sales | 4800 |
如果要將每位員工與其部門的平均工資進(jìn)行對(duì)比, 需要這樣的結(jié)果:
| emp_id | dep_name | salary | avg |
|---|---|---|---|
| 7 | develop | 4200 | 5020 |
| 8 | develop | 6000 | 5020 |
| 9 | develop | 4500 | 5020 |
| 10 | develop | 5200 | 5020 |
| 11 | develop | 5200 | 5020 |
| 2 | personnel | 3900 | 3700 |
| 5 | personnel | 3500 | 3700 |
| 1 | sales | 5000 | 4866.66666666667 |
| 3 | sales | 4800 | 4866.66666666667 |
| 4 | sales | 4800 | 4866.66666666667 |
如果不用窗口函數(shù)來(lái)查詢, 則比較復(fù)雜, 當(dāng)然也能做到, 語(yǔ)句如下:
SELECT e0.emp_no, e0.dep_name, e0.salary, e2.avg_salary FROM public.emp_salary e0 join ( select e1.dep_name, avg(e1.salary) as avg_salary from public.emp_salary e1 group by e1.dep_name ) e2 on e2.dep_name = e0.dep_name order by e0.dep_name, e0.emp_no;
如果使用窗口函數(shù)進(jìn)行查詢, 則很容易做到, sql 語(yǔ)句如下:
SELECT emp_no, dep_name, salary, avg(salary) over(partition by dep_name) FROM public.emp_salary order by dep_name, emp_no;
但是如果要查詢隨著員工的增加, 各部門平均工資的變化, 如下表所示的結(jié)果, 不用窗口函數(shù)查詢的話就很難做到了。
| emp_id | dep_name | salary | avg |
|---|---|---|---|
| 7 | develop | 4200 | 4200 |
| 8 | develop | 6000 | 5100 |
| 9 | develop | 4500 | 4900 |
| 10 | develop | 5200 | 4975 |
| 11 | develop | 5200 | 5020 |
| 2 | personnel | 3900 | 3900 |
| 5 | personnel | 3500 | 3700 |
| 1 | sales | 5000 | 5000 |
| 3 | sales | 4800 | 4900 |
| 4 | sales | 4800 | 4866.66666666667 |
如果使用窗口函數(shù), 依然可以輕松完成, 語(yǔ)句如下:
SELECT emp_no, dep_name, salary, avg(salary) over(partition by dep_name order by emp_no) FROM public.emp_salary order by dep_name, emp_no;
可見(jiàn), 窗口函數(shù)在需要對(duì)查詢結(jié)果中的相關(guān)行進(jìn)行計(jì)算時(shí)有很大的優(yōu)勢(shì)。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,謝謝大家對(duì)腳本之家的支持。
相關(guān)文章
PostgreSQL去掉表中所有不可見(jiàn)字符的操作
這篇文章主要介紹了PostgreSQL去掉表中所有不可見(jiàn)字符的操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
Postgresql ALTER語(yǔ)句常用操作小結(jié)
這篇文章主要介紹了Postgresql ALTER語(yǔ)句常用操作小結(jié),本文講解了增加一列、刪除一列、更改列的數(shù)據(jù)類型、表的重命名、更改列的名字、字段的not null設(shè)置等常用操作的代碼示例,需要的朋友可以參考下2015-06-06
PostgreSQL截取字符串到指定字符位置詳細(xì)示例
這篇文章主要給大家介紹了關(guān)于PostgreSQL截取字符串到指定字符位置的相關(guān)資料,PostgreSQL數(shù)據(jù)庫(kù)拼接字符串函數(shù)是一種非常重要的函數(shù),使用它可以方便地將不同的字符串進(jìn)行拼接操作,從而得到我們需要的結(jié)果,需要的朋友可以參考下2023-07-07
postgresql數(shù)據(jù)庫(kù) timescaledb 時(shí)序庫(kù) 把大數(shù)據(jù)量表轉(zhuǎn)換為超表的問(wèn)題
這篇文章主要介紹了postgresql數(shù)據(jù)庫(kù) timescaledb 時(shí)序庫(kù) 把大數(shù)據(jù)量表轉(zhuǎn)換為超表,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-02-02
Postgresql 數(shù)據(jù)庫(kù) varchar()字符占用多少字節(jié)介紹
這篇文章主要介紹了Postgresql 數(shù)據(jù)庫(kù) varchar()字符占用多少字節(jié)介紹,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
解決sqoop import 導(dǎo)入到hive后數(shù)據(jù)量變多的問(wèn)題
這篇文章主要介紹了解決sqoop import 導(dǎo)入到hive后數(shù)據(jù)量變多的問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12

