PostgreSQL 高級SQL(五) 內(nèi)建窗口函數(shù)

????????前面?zhèn)z個章節(jié)我們介紹了窗口函數(shù)仆潮、滑動窗口函數(shù)的概念褂微,接下來我們介紹一下PG支持的原生通用窗口函數(shù)箫章,總共11個(9.6版本,中國社區(qū)官網(wǎng)文檔地址

通用窗口函數(shù)

1讲逛、row_number 函數(shù)

row_number函數(shù)可以給每隔數(shù)據(jù)行返回一個虛擬的自增ID,也就是相當于給行分配一個編號,這些編號不會出現(xiàn)重復店诗,即使over()里面沒有按照字段排序字段也能正常工作裹刮,

select country_name,"year",gdp,row_number() over(order by country_name,"year")

from country_gdp_year_final?

where country_code in('CHN','JPN','USA','DEU','CAN','FRA') and "year" between 2012 and 2017;

row_number函數(shù)

2、rank函數(shù)

rank的官方解釋是:帶間隙的當前行排名庞瘸; 與該行的第一個同等行的row_number相同

select country_name,"year",gdp,rank() over(order by "year" desc)

from country_gdp_year_final?

where country_code? in('CHN','JPN','USA','DEU','CAN','FRA');

rank

從上面的的結(jié)果我們可以看出 rank函數(shù)和row_number一樣可以將行編號捧弃,但是號碼可能重復,比如我們按照年份排序擦囊,年份相同的話rank值相同违霞,2017年的數(shù)據(jù)rank直接跳到了7,這就相當于上學的時候考試瞬场,用rank計算排名的話买鸽,如果同年級出現(xiàn)三個并列的第一名的話,那么計算的結(jié)果將是三個人的rank值都是第一贯被,但是實際上的第二高的分數(shù)的同學會被rank排名為第四名眼五,如果我們想第二高的分數(shù)的排名為2,我們可以使用dense_rank函數(shù);

3彤灶、dense_rank函數(shù)

select country_name,"year",gdp,dense_rank() over(order by "year" desc)

from country_gdp_year_final?

where country_code? in('CHN','JPN','USA','DEU','CAN','FRA');

dense_rank

????????從上面的結(jié)果我們可以看出dense_rank函數(shù)會把編號弄得更加緊密看幼,中間不會出現(xiàn)像rank那樣的斷層編碼。

4幌陕、percent_rank函數(shù)

????????官方文檔解釋:當前行的相對排名=(rank- 1) / (總行數(shù) - 1) ,

? ??????排名和rank值成正相關诵姜,rank值相同的行號?percent_rank獲取的結(jié)果也一樣,返回的結(jié)果是個小數(shù)范圍在[0,1]之間搏熄,可以等于0或者1

select country_name,"year",gdp,percent_rank() over(order by "year" desc),rank() over(order by "year" desc)

from country_gdp_year_final?

where country_code? in('CHN','JPN','USA') and "year" between 2014 and 2018;

percent_rank

5棚唆、cume_dist函數(shù)

????????官方文檔解釋:當前行的相對排名=(rank- 1) / (總行數(shù) - 1)

? ??????排名和rank值成正相關心例,rank值相同的行號?percent_rank獲取的結(jié)果也一樣宵凌,返回的結(jié)果是個小數(shù)范圍在[0,1]之間,可以等于0或者1

select country_name,"year",gdp,percent_rank() over(order by "year" desc),cume_dist() over(order by "year" desc),rank() over(order by "year" desc)

from country_gdp_year_final?

where country_code? in('CHN','JPN','USA') and "year" between 2014 and 2018;

cume_dist函數(shù)

6契邀、ntile函數(shù)

官方文檔解釋:從1到參數(shù)值的整數(shù)范圍摆寄,盡可能等分分區(qū),

????????ntile(num_buckets)坯门,num_buckets的值表示將結(jié)果集分成num_buckets組微饥,有限填滿前面的組,最后一組可能出現(xiàn)個數(shù)不足(非等分)情況古戴,實際上就是把每隔行分個組號欠橘。

select country_name,"year",gdp,percent_rank() over(order by "year" desc),cume_dist() over(order by "year" desc),

rank() over(order by "year" desc),ntile(4)? over(order by "year" desc)

from country_gdp_year_final?

where country_code? in('CHN','JPN','USA') and "year" between 2014 and 2018;

ntile

7、lag函數(shù)

????????官方文檔解釋:lag(value?anyelement?[,?offset?integer?[,?default?anyelement?]])现恼,返回value肃续, 它在分區(qū)內(nèi)當前行的之前offset個位置的行上計算;如果沒有這樣的行叉袍,返回default替代始锚。 (作為value必須是相同類型)。?offsetdefault都是根據(jù)當前行計算的結(jié)果喳逛。如果忽略它們瞧捌,則offset默認是1,default默認是空值

? ? ? ? 官方文檔的解釋很晦澀難懂润文,我們直接使用用例執(zhí)行一下看一下數(shù)據(jù)分布就好了

select country_name,"year",gdp,lag(gdp,1) over(order by "year" desc)

from country_gdp_year_final?

where country_code? in('CHN','JPN','USA') and "year" between 2014 and 2017;

lag

從上圖可以知道當前行的lag值是當前行的前offset行的值姐呐,沒有的話就返回default,default不想存在的話就返回null,從數(shù)據(jù)姐過再去看官方文檔的解釋的話可能清晰很多,lag函數(shù)可以在結(jié)果集的行內(nèi)移動典蝌,經(jīng)常使用到的場景是計算今年和全年的年產(chǎn)量的差值曙砂,

select country_name,"year",gdp,lag(gdp,1) over(order by country_name, "year" desc) -gdp

from country_gdp_year_final?

where country_code? in('CHN','JPN','USA') and "year" between 2014 and 2017;


lag

8、lead函數(shù)

官方文檔解釋:lead(value?anyelement?[,?offset?integer?[,?default?anyelement?]])?返回value骏掀,它在分區(qū)內(nèi)當前行的之后offset個位置的行上計算鸠澈;如果沒有這樣的行,返回default替代截驮。(作為value必須是相同類型)笑陈。offsetdefault都是根據(jù)當前行計算的結(jié)果。如果忽略它們侧纯,則offset默認是1新锈,default默認是空值

? ? 其實lead函數(shù)和lag函數(shù)的作用是相同的,如果lead的offset參數(shù)值為-N,那么lag的offset的參數(shù)為N的話計算結(jié)果是相同的眶熬,lag(gdp,-1)是lead(gdp,1)的替代

9妹笆、first_value、last_value娜氏、nth_value函數(shù)較為簡單不做介紹

????????至此我們講完了幾乎所有的窗口函數(shù)拳缠,希望這五篇關于PostgreSQL的文章能對大家在平時的開發(fā)中有所幫助

?著作權歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市贸弥,隨后出現(xiàn)的幾起案子窟坐,更是在濱河造成了極大的恐慌,老刑警劉巖,帶你破解...
    沈念sama閱讀 211,743評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件哲鸳,死亡現(xiàn)場離奇詭異臣疑,居然都是意外死亡,警方通過查閱死者的電腦和手機徙菠,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,296評論 3 385
  • 文/潘曉璐 我一進店門讯沈,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人婿奔,你說我怎么就攤上這事缺狠。” “怎么了萍摊?”我有些...
    開封第一講書人閱讀 157,285評論 0 348
  • 文/不壞的土叔 我叫張陵挤茄,是天一觀的道長。 經(jīng)常有香客問我冰木,道長穷劈,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 56,485評論 1 283
  • 正文 為了忘掉前任片酝,我火速辦了婚禮囚衔,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘雕沿。我一直安慰自己练湿,他們只是感情好,可當我...
    茶點故事閱讀 65,581評論 6 386
  • 文/花漫 我一把揭開白布审轮。 她就那樣靜靜地躺著肥哎,像睡著了一般。 火紅的嫁衣襯著肌膚如雪疾渣。 梳的紋絲不亂的頭發(fā)上篡诽,一...
    開封第一講書人閱讀 49,821評論 1 290
  • 那天,我揣著相機與錄音榴捡,去河邊找鬼杈女。 笑死,一個胖子當著我的面吹牛吊圾,可吹牛的內(nèi)容都是我干的达椰。 我是一名探鬼主播,決...
    沈念sama閱讀 38,960評論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼项乒,長吁一口氣:“原來是場噩夢啊……” “哼啰劲!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起檀何,我...
    開封第一講書人閱讀 37,719評論 0 266
  • 序言:老撾萬榮一對情侶失蹤蝇裤,失蹤者是張志新(化名)和其女友劉穎廷支,沒想到半個月后,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體栓辜,經(jīng)...
    沈念sama閱讀 44,186評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡恋拍,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,516評論 2 327
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了啃憎。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片芝囤。...
    茶點故事閱讀 38,650評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡似炎,死狀恐怖辛萍,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情羡藐,我是刑警寧澤贩毕,帶...
    沈念sama閱讀 34,329評論 4 330
  • 正文 年R本政府宣布,位于F島的核電站仆嗦,受9級特大地震影響辉阶,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜瘩扼,卻給世界環(huán)境...
    茶點故事閱讀 39,936評論 3 313
  • 文/蒙蒙 一谆甜、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧集绰,春花似錦规辱、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,757評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至碍岔,卻和暖如春浴讯,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背蔼啦。 一陣腳步聲響...
    開封第一講書人閱讀 31,991評論 1 266
  • 我被黑心中介騙來泰國打工榆纽, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人捏肢。 一個月前我還...
    沈念sama閱讀 46,370評論 2 360
  • 正文 我出身青樓奈籽,卻偏偏與公主長得像,于是被迫代替她去往敵國和親猛计。 傳聞我的和親對象是個殘疾皇子唠摹,可洞房花燭夜當晚...
    茶點故事閱讀 43,527評論 2 349

推薦閱讀更多精彩內(nèi)容